Dynamic SQL within PL/SQL procedure and ORA-00905

[traveller]

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;

[PAVB]

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.

[traveller]

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;

[PAVB]

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...

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:

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.

[traveller]

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.

[PAVB]

Nicely done. Way to go.