Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
This issue can easily happen when using command line execution of SQLPLUS. While you are inside the SQLPLUS program, the command line editor is also still active. As a result, items that are relevant to the command line editor (blank spaces are interpreted as command/variable, semi-colons are seen as end of command). That is why having a '@' in a password causes nothing but heartache when you try to sign in (everything to the right of @ is seen as the name of a DB). During a major deployment, we found issues with blank spaces that forced us to deploy stuff through TOAD. SQLPLUS was useless to –  TomV Jun 4 '12 at 15:15
    
Thank you for your thoughts. So, to clarify is your answer that what I am requesting is impossible? –  Leigh Riffel Jun 4 '12 at 15:38
    
Can you convert the carriage returns within the string to chr(10)s insert is listed on one line? –  Chris Saxon Jul 13 '12 at 15:42
    
@ChrisSaxon I can, but this issue is how to distinguish returns that should be encoded and returns that need to be left alone as part of the syntax. If you have a way to do this please post this as an answer. –  Leigh Riffel Jul 13 '12 at 15:47
add comment

4 Answers

up vote 2 down vote accepted

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:

prompt run login.sql
show sqlterminator
show sqlblanklines
set sqlblanklines on
set sqlterminator ';'
show sqlterminator
show sqlblanklines
prompt ready login.sql
set echo on

leigh.sql contains:

INSERT INTO t1 VALUES ('fail bc semicolon
a;a
/
'); 

INSERT INTO t1 VALUES ('fail bc solo /


aa
/
');

INSERT INTO t1 VALUES ('ok / not solo


aa
/a
');

DELETE FROM t1 WHERE a = 'c


a/
';

run the script:

sqlplus leigh/leigh@orcl @leigh
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Aug 9 22:36:20 2012

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

run login.sql
sqlterminator ";" (hex 3b)
sqlblanklines OFF
sqlterminator ";" (hex 3b)
sqlblanklines ON
ready login.sql
SQL> INSERT INTO t1 VALUES ('fail bc semicolon
  2  a;a
  3  /
ERROR:
ORA-01756: quoted string not properly terminated


SQL> ');
SP2-0042: unknown command "')" - rest of line ignored.
SQL> 
SQL> INSERT INTO t1 VALUES ('fail bc solo /
  2  
  3  
  4  aa
  5  /
ERROR:
ORA-01756: quoted string not properly terminated


SQL> ');
SP2-0042: unknown command "')" - rest of line ignored.
SQL> 
SQL> INSERT INTO t1 VALUES ('ok / not solo
  2  
  3  
  4  aa
  5  /a
  6  ');

1 row created.

SQL> 
SQL> DELETE FROM t1 WHERE a = 'c
  2  
  3  
  4  a/
  5  ';

0 rows deleted.

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.

share|improve this answer
    
Thank you for the proof. I'm already using a login.sql file setup similarly. So basically this confirms that why I would like to do can't be done. –  Leigh Riffel Aug 10 '12 at 12:10
    
a little variation is to use a runner script that does the settings and calls the real script. Would become sqlplus leigh/leigh@orcl @runner leigh. That is a bit more flexible than login.sql –  ik_zelf Aug 10 '12 at 12:45
add comment

Insert statements with blank lines and semi-colons will succeed if placed inside BEGIN...END blocks. This change could be done using a script, but the script would fail if it contained DDL statements that cannot be run inside a block without execute immediate.

This solution also does not resolve the embedded / issue.

share|improve this answer
    
In the past I've used a perl/DBD script to do this to avoid sqlplus. Happy to share... –  FreshPhilOfSO Apr 4 '12 at 20:46
    
@Phil Thank you, but SQLPlus works fine in 99.9% of situations, so I'd rather not add another tool to the mix. –  Leigh Riffel Apr 4 '12 at 21:17
add comment

My workaround:

         begin
             INSERT INTO t1 VALUES ('a

             ;
             ');
         end;
         /

It seems like command terminator is ignored inside the body statement.

share|improve this answer
    
See my answer for why this isn't a good solution for DDL statements or embedded slashes. –  Leigh Riffel Aug 24 '12 at 18:22
add comment
set sqlterminator OFF
<...>
set sqlterminator ON
share|improve this answer
1  
And how will this help? –  dezso Sep 24 '13 at 11:45
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.