Friday, January 28, 2011

Escape / (Forward Slash) in Oracle SQL*Plus

Escape / (Forward Slash) in Oracle SQL*Plus

Problem:
I am running following anonymous block and it's giving error. It's because SQL Plus is considering forward slash (/) in variable "a" assignment as block terminator. How to escape that? The use of following block is to store test case in Database. This is simple example real test cases are complex and involve print after /.
DECLARE
A varchar2(1024) := NULL;
BEGIN
A := 'set serveroutput on;
BEGIN
INSERT INTO table_name
VALUES (5067);
END ;
/

' ;
INSERT INTO x VALUES (A) ;

COMMIT ;
END ;
/

Output
SQL> DECLARE
2 A varchar2(1024) := NULL;
3 BEGIN
4 A := 'set serveroutput on;
5 BEGIN
6 INSERT INTO table_name
7 VALUES (5067);
8 END ;
9 /
ERROR:
ORA-01756: quoted string not properly terminated


SQL>
SQL> ' ;
SP2-0042: unknown command "' " - rest of line ignored.
SQL> INSERT INTO x VALUES (A) ;
INSERT INTO x VALUES (A)
*
ERROR at line 1:
ORA-00984: column not allowed here


SQL>
SQL> COMMIT ;

Commit complete.

SQL> END ;
SP2-0042: unknown command "END " - rest of line ignored.
SQL> /

Commit complete.

SQL>

Running same block from Toad works fine.

Solution
DECLARE
A varchar2(1024) := NULL;
BEGIN
A := 'set serveroutput on;
BEGIN
INSERT INTO table_name
VALUES (5067);
END ;' || '
/' || '


' ;
INSERT INTO x VALUES (A) ;

COMMIT ;
END ;
/