your code is correct, a single quote within a string must be replaced by two single quotes. So this should run as-is.
Can you show your exact query?
Or the full SP source?
Thanks for your reply.
Problem solved. The two single quotes is correct. The problem here is because of the enter in the sql statement. it should be written either in one line or use || to connect strings in different lines, like
set str_sql = 'update table ';
set str_sql = str_sql || 'set cola = 0, ';
set str_sql = str_sql || 'set colb = ''test''';
execute immediate str_sql;
this is strange as it should work with linebreaks, too. In fact I prefer it over concatenating as the SQL code is more readable and you don't have to check for a leading or trailing blank in your strings.
I just put your code in an SP and got a "table doesn't exist" error.
Btw, you can simplify your code to:
set str_sql = 'update table '
|| 'set cola = 0, '
|| 'set colb = ''test''';
I getting following error then running my stored procedure having dynamic SQL.
CALL Failed 7689: PROC_POPULATE_TEST:Invalid dynamic SQL statement.
This is my stored procedure.
Answered in the other thread. Please open a new topic for new questions - go to hte main page for this forum and click the big button at the top.