-
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.
-
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.
-
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;
-
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.
-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|