Why does SQL Developer update Oracle Data Dictionaries while SQL*Plus does not

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.

Sounds like the network buffer is being filled but not flushed. SQL*Net performance in this area is often managed by setting tcp.nodelay=yes in sqlnet.ora; also look at the send_buf_size and recv_buf_size parameters.

SQL*Dev working and not SQL*Plus are what pointed me to this type of troubleshooting approach. SQL*Developer uses jdbc behind the scenes to do its fetching.

hth!