Occasionally I will get a script that will run fine in SQL Developer or Toad, but requires modification in order to be run successfully from SQL*Plus. Here is a worst case example containing multiple statements each with blank lines, semicolons, and forward slashes:
INSERT INTO t1 VALUES ('a
;
/
');
INSERT INTO t1 VALUES ('b
;
/
');
DELETE FROM t1 WHERE c1 = 'c
;
/
';
For various reasons these statements need to be run from SQL*Plus. The blank lines are easy to resolve with a simple…
set sqlblanklines on
I am aware that the sqlterminator
can be changed and/or turned off, but both would require modifications to the code, the former moves the problem without resolving it and neither resolves the embedded slash issue.
The best answer would be a way to allow these statements to run without modification by changing the environment in some way (as sqlblanklines does). If that isn't possible, then perhaps there is a way to programmatically modify the scripts. I am trying to avoid manual changes.
Best Answer
You can do most of this by using a login.sql. login.sql is executed during - surprising - login and is loaded from your SQLPATH or current directory. For the examples you gave, your really chose the worst case.
Problem is the sqlterminator. Whatever you put in there, the forward slash is maintained as a free sqlterminator. Next to that, sqlplus first scans for the sqlterminator and does this before scanning to the string terminator. A bug if you ask me. The forward slash can be used in a string as long as it is not alone on a seperate line. As soon as sqlplus finds the character specified as sqlterminator, it ignores everything else and stops reading.
The forward slash can be handled, as long as it is not alone on a line.
login.sql contains:
leigh.sql contains:
run the script:
No need to fiddle with begin/end blocks. Can't handle sqlterminator inside command, no matter where it is, in a string or not, can't handle lines with forward slash alone on a line in a string.