Execute Immediate

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