Excecute Immediate error

[themikeaustin]

All,

When I try to run this code from within PL/SQL Developer, I receive an "ORA-00900 Invalid SQL Statement". When I remove the string delimiters and concatenation text, the create table statement runs fine in SQL*Plus.

EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE BasicInfo ( ' ||
'FeeBillID NUMBER, ' ||
'BusinessDimID NUMBER, ' ||
'DateDimID NUMBER, ' ||
'FeeDimID NUMBER, ' ||
'FeeMethod VARCHAR2(10), ' ||
'FlatFeeAmount NUMBER(9,2), ' ||
'ElectionCode VARCHAR2(12), ' ||
'DebitCardFeeAmount NUMBER(9,2), ' ||
'FeeCapAmount NUMBER(9,2), ' ||
'FeeAmount NUMBER(9,2), ' ||
'IsAdjustment CHAR, ' ||
'Sign NUMBER, ' ||
'OtherCharges NUMBER(9,2), ' ||
'CreditAdjustment NUMBER(9,2), ' ||
'DebitAdjustment NUMBER(9,2) ) ' ||
'ON COMMIT PRESERVE ROWS; ';

This statement also fails:

EXECUTE IMMEDIATE
'select * from finance_ml_qa.subscriberelection;'
(with or without the semicolon)

I am running 10g.

Any help appreciated.

Mike

 

[fidelfs]

You can prevent that kind of error if you store it in a variable and then you execute the variable.

l_var := 'create global temp ....'
execute immediate l_var;

Also performance wise is not a good idea to create temp tables on the fly. You should create it and forget it, but that is a different issue not related to this forum.

 

[themikeaustin]

Thanks! That works for me!

Originally posted by fidelfs:
You can prevent that kind of error if you store it in a variable and then you execute the variable.

l_var := 'create global temp ....'
execute immediate l_var;

Also performance wise is not a good idea to create temp tables on the fly. You should create it and forget it, but that is a different issue not related to this forum.