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 ;
/
Friday, January 28, 2011
Subscribe to:
Posts (Atom)