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.
NLS_LANG can't be changed from inside a session, however other settings can.
You can't change the character set once a database connection has been established (ie: the 2nd part of NLS_LANG), but you can change the language with:
alter session set NLS_LANGUAGE=SPANISH
... and the territory with:
alter session set NLS_TERRITORY=SPAIN
Best Answer
The script creates certain objects, and it will put them in the current schema. Oracle wishes that these objects be created on SYSTEM schema, hence the requirement.
There are two Oracle schemas, SYS and SYSTEM, because they have two purposes. In short, SYS truly is the "special" user in respect to database internals, while SYSTEM works normally.
The SYS is the owner of the database, the only user with access to X$ tables, the only user which acts as SYSDBA (internally), that bypasses logon triggers, and that, for some obscure reason, cannot obtain read-consistent view of data. As a schema it holds crucial Oracle stuff, mainly the data dictionary.
The SYSTEM is fairly normal schema with DBA privilege (database administrator, not the same as SYSDBA), only that it is built-in and contains some additional (but also quite important) Oracle stuff. You shouldn't put your own stuff in this schema, if not instructed so by Oracle.