Sanjoy, August 11, 2003 - 4:34 pm UTC
Thanks Tom for your advice and a prompt response
Why 942 and not 4043 in exception_init????
DD, August 12, 2003 - 7:41 am UTC
ops$tkyte@ORA920> desc t
ERROR:
ORA-04043: object t does not exist
But then why did you use
pragma exception_init( table_does_not_exist, -942 );
Why did you use 942 and not 4043 for the exception init??
does a drop table which does not exist give 942??
I assume so but the first line was confusing.
Thanks
DD
August 12, 2003 - 8:56 am UTC
because a drop gives a 942.
the first line was there just to show you that T in fact did not exist so the exception handler MUST have been invoked, caught the exception and ignored it.
Sanjoy, August 12, 2003 - 11:21 am UTC
The above code works fine but when I use it in a loop I get error, here is how my proc looks like
CREATE OR REPLACE PROCEDURE SCP_TESTA IS
tmpVar NUMBER;
CURSOR stage_cursor is
SELECT stage_table, product_category, product_attribute
FROM pcm;
rec_stage_cursor stage_cursor%rowtype;
v_sql LONG;
table_does_not_exist exception;
pragma exception_init( table_does_not_exist, -942 );
BEGIN
tmpVar := 0;
OPEN stage_cursor;
LOOP
FETCH stage_cursor into rec_stage_cursor;
EXIT WHEN stage_cursor%NOTFOUND;
BEGIN
v_sql := 'SELECT count(*) into tmpVar FROM STG_' || rec_stage_cursor.stage_table ||'';
EXECUTE IMMEDIATE v_sql;
EXCEPTION
WHEN table_does_not_exist THEN
v_sql := 'CREATE TABLE STG_' || rec_stage_cursor.stage_table ||' AS SELECT sysdate dummy_date FROM dual';
EXECUTE IMMEDIATE v_sql;
END;
SCP_SPC ( rec_stage_cursor.stage_table, rec_stage_cursor.product_category, rec_stage_cursor.product_attribute );
DBMS_OUTPUT.PUT_LINE('EXECUTION OF PROC COMPLETED');
END LOOP;
END SCP_TESTA;
The compilation of the above works fine but at execution time I get the error
ERROR at line 1:
ORA-00905: missing keyword
ORA-06512: at "SYSADM.SCP_TESTA", line 18
ORA-06512: at line 2
August 12, 2003 - 11:30 am UTC
that is not how to do a select into using dynamic sql -- that is why.
Also, that is not the question you asked. That is a horse of a totally different color.
This is how to do the above:
CREATE OR REPLACE PROCEDURE SCP_TESTA
as
table_already_exists exception;
pragma exception_init( table_already_exists, -955 );
BEGIN
for x in (select stage_table, product_category, product_attribute from pcm)
loop
begin
execute immediate 'create table stg_'||x.stage_table ||
' as select sysdate dummy_date from dual';
exception
when table_already_exists then NULL;
end;
scp_spc( x.stage_table, x.product_category, x.product_attribute );
end loop;
END SCP_TESTA;
/
and just fyi -- a select into using dynamic sql is:
ops$tkyte@ORA920> declare
2 l_cnt number;
3 begin
4 execute immediate 'select count(*) from dual' INTO l_cnt;
5 end;
6 /
PL/SQL procedure successfully completed.
but here, doing a count(*) is pointless.
execute immediate and sql
Jerry, December 11, 2003 - 11:24 pm UTC
Can I run ddl using execute immediate together with other sq code? I got errors when running:
SQL> begin
2 execute immediate 'create table t (t1 integer)';
3 delete from t;
4 commit;
5 end;
6 /
delete from t;
*
ERROR at line 3:
ORA-06550: line 3, column 13:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 3, column 1:
PL/SQL: SQL Statement ignored
However, if I remove the delete, it works.
SQL> begin
2 execute immediate 'create table t (t1 integer)';
3 end;
4 /
PL/SQL procedure successfully completed.
Could you please help me understand this? Thanks!
December 13, 2003 - 10:36 am UTC
think about it -- when that block of code is compiled -- does T exist?
Nope.
Therefore, the block of code cannot possibly compile.
If you dynamically CREATE the table, you are forced to dynamically ACCESS the table.
Chicken and Egg problem here, you cannot compile the code because T does not exist so the static sql cannot be processed.
Great help
Jerry, December 13, 2003 - 6:11 pm UTC
Thanks so much for the explaination! It's great help!
HM, April 05, 2004 - 2:41 pm UTC
Tom,
What privileges does a user require to be able to do ddl using execute immediate? I am getting Insufficient privileges when trying to create view wihtin a stored procedure.
April 05, 2004 - 5:22 pm UTC