having issues with single quotes using like 'string token' in execute immediate statement

Question

Here is the section of code, I am using ' ' blah ' ' to escape single quotes but I guess its not working:

declare
my_func varchar2(20) :='test_func';

begin 
execute immediate 'insert into TABLE_TEST (OUTPUT) select ' || my_func || ' from dual where TABLE_TEST.FUNCTION_NAME like ' 'VALIDATION1_%' '  ';
end;

I am getting the following error:

PLS-00103: Encountered the symbol "VALIDATION1_%" when expecting one of the following:

   & = - + ; < / > at in is mod remainder not rem return
   returning <an exponent (**)> <> or != or ~= >= <= <> and or
   like like2 like4 likec between into using || bulk member
   submultiset
The symbol "* was inserted before "VALIDATION1_%" to continue.

score 3 · Accepted Answer · edited May 23 '17 at 11:43

3

It looks like you are trying to escape the single quote with another single quote (which is good), but there is an extra space in between the two. That has to go.

Change

' from dual where TABLE_TEST.FUNCTION_NAME like ' 'VALIDATION1_%' '  '

to

' from dual where TABLE_TEST.FUNCTION_NAME like ''VALIDATION1_%''  '

edited May 23 '17 at 11:43

Community

1
1

answered Jan 17 '15 at 00:06

Thilo

257,207
101
511
656

Doing that gives me an ORA-00904 invalid identifier error for TABLE_TEST.FUNCTION_NAME – Nate Silver Jan 17 '15 at 00:16
Well, does TABLE_TEST have a column FUNCTION_NAME ? – Thilo Jan 17 '15 at 00:47
Yes it does have the column FUNCTION_NAME – Nate Silver Jan 17 '15 at 00:51
1

I guess I figured it out, nothing to do with your solution which does solve my original problem. I needed to add table_test next to dual so it should have read ' from dual, table_test where .........' – Nate Silver Jan 17 '15 at 01:17

score 1 · Answer 2 · answered Jan 20 '15 at 02:11

1

In cases like this it's much simpler to use the new q syntax for literal strings, e.g.:

execute immediate 'insert into TABLE_TEST (OUTPUT) select ' || my_func ||
  q[' from dual where TABLE_TEST.FUNCTION_NAME like 'VALIDATION1_%' ]';

answered Jan 20 '15 at 02:11

Jeffrey Kemp

59,135
14
106
158

having issues with single quotes using like 'string token' in execute immediate statement

2 Answers2