-
Hi all,
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.
-
You're missing your quotes...
execute immediate 'create table ABC.XXX as SELECT * FROM XYZ WHERE Name ='''||v_Name||''' or NAME is null';
Jeff Hunter
-
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.
-
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.
Example
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!
KN
-
Don't use double quotes. Use multiple single quotes.
-
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.
-
What are you trying to do with this?
:name is null
Jeff Hunter
-
Thanks all.
Multiple single quotes worked.
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
|