EXECUTE IMMEDIATE
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: EXECUTE IMMEDIATE

  1. #1
    Join Date
    Dec 2000
    Posts
    87
    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.

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    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
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Dec 2000
    Posts
    87
    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.

  4. #4
    Join Date
    Feb 2000
    Location
    NJ, United States
    Posts
    250

    Talking

    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

  5. #5
    Join Date
    Aug 2000
    Posts
    462
    Don't use double quotes. Use multiple single quotes.

  6. #6
    Join Date
    Dec 2000
    Posts
    87
    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.

  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    What are you trying to do with this?
    :name is null
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  8. #8
    Join Date
    Dec 2000
    Posts
    87
    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
  •  



Click Here to Expand Forum to Full Width