EXECUTE IMMEDIATE

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';

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! :D

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

Thanks all.

Multiple single quotes worked.