-
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
-
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);
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|