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;