Dynamic SQL within PL/SQL procedure and ORA-00905
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Dynamic SQL within PL/SQL procedure and ORA-00905

  1. #1
    Join Date
    Jan 2006
    Posts
    39

    Dynamic SQL within PL/SQL procedure and ORA-00905

    I tried to use dynamic SQL within a PL/SQL procedure to re-create materialized view. Because the SQL statement for the creation is long, I store these mv name, tablespace, refresh method, refresh key and SQL select statement in a local table called xxx_mv_information. Then I create procedure with dynamic SQL in. The procudure was compiled successfully. When I run the procedure, it always give me ORA-00905 missing key word error. I have carefully check the syntax and can not find the problems. Please help me to figure out where the problem is. Thanks a lot in advance. Here is the procedure I created.

    CREATE OR REPLACE PROCEDURE sp_recreat_mvs
    IS
    v_schema user_mviews.owner%type;
    v_sql_string varchar2(1000);
    v_sql_stmt varchar2(2000);

    BEGIN

    SELECT DISTINCT OWNER
    INTO v_schema
    FROM USER_MVIEWS;


    SELECT TABSPACE||' '||REF_METHOD||' '||REF_KEY||' '||SQL_STRING
    INTO v_sql_string
    FROM XXX_MV_INFORMATION
    ORDER BY MV_NUM;

    FOR x IN (SELECT MV_NAME FROM XXX_MV_INFORMATION ORDER BY MV_NUM)

    LOOP

    v_sql_stmt := 'CREATE MATERIALIZED VIEW ' ||v_schema||'.'||x.mv_name||v_sql_string;

    EXECUTE IMMEDIATE v_sql_stmt;

    END LOOP;

    COMMIT;

    END;
    Last edited by traveller; 02-26-2008 at 05:03 PM.

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    You have to double-check your dynamic sql syntax.

    Print out your v_sql_stmt command line before the EXECUTE IMMEDIATE statement, for sure you are going to see that you need a blank space before v_sql_string and who knows what else might be wrong.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Jan 2006
    Posts
    39
    PAVB:

    You are right. The syntax is very important in this regard. I missed a space again and it works after adding space. But I have another piece of code here. I know is the space problem to cause the ORA-00905 missing keyword error. But I tried many ways and can not figure out where the space is missing or whatelse. Please comment on it. Thanks a lot.

    v_sql_stmt := 'CREATE MATERIALIZED VIEW '||v_schema||'.'||mv_record.mv_name|| mv_record.tabspace|| mv_record.ref_method|| mv_record.ref_key|| mv_record.sql_string;

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Ok... I'll teach you how to fish otherwise you are going to keep asking for a fish a couple of times a day.

    First thing to do is to convert your code below...
    Code:
    v_sql_stmt := 'CREATE MATERIALIZED VIEW '||v_schema||'.'||mv_record.mv_name|| mv_record.tabspace|| mv_record.ref_method|| mv_record.ref_key|| mv_record.sql_string;
    ...into something like this:
    Code:
    select  'CREATE MATERIALIZED VIEW '||'v_schema'||'.'||'mv_record.mv_name'||'mv_record.tabspace'||'mv_record.ref_method'||'mv_record.ref_key'||'mv_record.sql_string'
    from    dual;
    Please execute it and tell me if what it returns looks like the right syntax for the Create Materialized View command.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Jan 2006
    Posts
    39

    Talking

    PVAB:

    Thanks for your teaching.

    Actually, I used dbms_output.put_line(v_sql_stmt) in SQL plus to find problem.
    Then I add space in string. Then it works. Thanks for your help.

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Nicely done. Way to go.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width