I encountered another problem with the following statement in a procedure:
execute immediate 'create table ABC.XXX as SELECT * FROM XYZ WHERE Name ='||v_Name||' or vName is null';
when I call this procedure by passing v_Name='US', I received an error message on this line :
ORA-00904: invalid column name
vName is a varchar2 data type. I have other statements as:
execute immediate 'create table ABC.XXX as SELECT * FROM XYZ WHERE id ='||v_id||' or vName is null';
v_id is an integer, and it worked fine.
So I guess it's the syntax that mess things up, can someone suggest please, thanx.
04-16-2001, 01:13 PM
You're missing your quotes...
execute immediate 'create table ABC.XXX as SELECT * FROM XYZ WHERE Name ='''||v_Name||''' or NAME is null';
04-16-2001, 01:31 PM
Ok, this is what I've done since I can't really tell from the post which quite are inside and which are outside:
I put double-quotes outside of the existing single-quotes, it compiled, but I'm still getting invalid column error.
Then I tried the other way round, it wouldn't compile.
04-16-2001, 01:45 PM
I had also faced the same problem sometime back while writing a procedure. I got around it by using bind variables in the sql instead of variable name and then using the statement execute immediate using variable.
execute immediate 'create table ABC.XXX as SELECT * FROM XYZ WHERE Name =:name or :name is null' using v_name;
See if this helps and give me the feedback.
All the best! :D
04-16-2001, 01:57 PM
Don't use double quotes. Use multiple single quotes.
04-16-2001, 01:58 PM
I tried Kmesser's method, I got this message:
ORA-01027: bind variables not allowed for data definition operations
It's not working for ddls.
04-16-2001, 02:09 PM
What are you trying to do with this?
:name is null