3

I need to include single quotes in dbms_output statement. I've tried this:

 dbms_output.put_line('\''first_name||'\'');

Here first_name is variable; I need to display this inside single quotes.

William Robertson
  • 15,273
  • 4
  • 38
  • 44
Thiyagu ATR
  • 2,224
  • 7
  • 30
  • 44
  • if first_name holds xyx then my output would be 'xyz'.this is wat i tried to get. – Thiyagu ATR Feb 04 '13 at 15:43
  • possible duplicate of [How to handle a single quote in Oracle SQL](http://stackoverflow.com/questions/2875257/how-to-handle-a-single-quote-in-oracle-sql) – A.B.Cade Feb 04 '13 at 16:24
  • Possible duplicate of [How to handle a single quote in Oracle SQL](https://stackoverflow.com/questions/2875257/how-to-handle-a-single-quote-in-oracle-sql) – William Robertson Feb 20 '18 at 10:25

2 Answers2

10

you'd escape by doubling up:

 dbms_output.put_line('''' || first_name || '''');

or using the q-quote mechanism:

 dbms_output.put_line(q'[']'||first_name||q'[']');

eg:

SQL> var b1 varchar2(30)
SQL> exec :b1 := 'this is a test';

PL/SQL procedure successfully completed.

SQL> exec  dbms_output.put_line(q'[']'||:b1||q'[']');
'this is a test'

PL/SQL procedure successfully completed.

SQL> exec  dbms_output.put_line(''''||:b1||'''');
'this is a test'

PL/SQL procedure successfully completed.

SQL> exec  dbms_output.put_line(chr(39)||:b1||chr(39));
'this is a test'

PL/SQL procedure successfully completed.
DazzaL
  • 21,638
  • 3
  • 49
  • 57
0

Note that this is only for PL/SQL and must be active.

begin
DBMS_OUTPUT.PUT_LINE('DELETE FROM MYTABLE WHERE MYFIELD <> ''A'';');
end;
/

Assuming you want to match a CHAR 'A'.

vesperto
  • 804
  • 1
  • 6
  • 26