Execute Immediate
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Execute Immediate

  1. #1
    Join Date
    Mar 2002
    Posts
    25

    Unhappy

    Hi
    I have a problem executing a native dinamic sql.
    here is my example:
    execute immediate 'Insert into table_name(col1,col2,col3) Values ( '||sysdate||','||v_col2||','||v_col3||')';
    col1 is a date column.
    when I run this command I get an ORA-917 " Missing Comma".

    Pls Help

  2. #2
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    That's bad way of using NDS, in my opinion.
    You shouldn't build body of SQL dynamically if you don't have to.

    My advice is:
    Code:
    execute immediate 'Insert into table_name(col1,col2,col3) values (:c1,:c2,:c3)' using sysdate, v_col2, v_col3;
    but the same you can achieve even without NDS:
    Code:
    Insert into table_name(col1,col2,col3) values (sysdate, v_col2, v_col3);

  3. #3
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    ALES is correct, but if you still want to do it your problem is the single quotes. If you want a single quote to appear in a string you must put 2 single quotes to tell Oracle that the string hasn't ended yet:

    execute immediate 'Insert into table_name(col1,col2,col3) Values ( ''||sysdate||'',''||v_col2||'',''||v_col3||'')';

    Note. These are two single quotes, not double quotes.

    Cheers


    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  4. #4
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    I'm sorry Tim, your example is just partly correct.
    Assuming col1 is of DATE type we should insert a value of proper type so we mustn't enclose the sysdate function between quotes. And we have to enclose the other columns between triple quotes to get the correct result:
    Code:
    execute immediate 'Insert into table_name(col1,col2,col3) Values ( sysdate,'''||v_col2||''','''||v_col3||''')';
    Ales


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