Re: How does one escape special characters

Roger Xu wrote:

>Thanks.
>
>Could you tell me what is wrong with the following statements?
>
>

I can, but a little debugging excercise will be beneficial for your muscles and joints.

>SQL> l
> 1 declare
> 2 cmd varchar2(128);
> 3 begin
> 4 cmd:='SELECT id FROM emp WHERE name = ''ROGER XU'';';
> 5 dbms_output.put_line(cmd);
> 6 execute immediate cmd;
> 7* end;
>SQL> /
>SELECT id FROM emp WHERE name = 'ROGER XU';
>declare
>*
>ERROR at line 1:
>ORA-00911: invalid character
>ORA-06512: at line 6
>
>
>

And the script below works! Fancy that! All you need to do is to find out what is the difference.

SQL> ed
Wrote file /tmp/buffer.sql

  1 declare
  2 cmd varchar2(128);
  3 begin
  4 cmd:='SELECT empno FROM emp WHERE ename = ''ROGER XU''';   5 dbms_output.put_line(cmd);
  6 execute immediate cmd;
  7* end;
SQL> /
SELECT empno FROM emp WHERE ename = 'ROGER XU'

PL/SQL procedure successfully completed.

SQL

-- 
Mladen Gogala
Oracle DBA

--
http://www.freelists.org/webpage/oracle-l