DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

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

Threaded View

  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.

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