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.
That's relatively easy. Using the UTL_FILE package in Oracle you can create a file in the servers filesystem and write the output of any PL/SQL statement to it. You can actually also use it to read data from a file.
http://psoug.org/reference/utl_file.html
You might also want to read up a little on the Directory object type as well, since that's a prerequisite for using the UTL_FILE package; http://psoug.org/reference/directories.html
--
Here's an example of a procedure that writes ascii data to an existing Oracle directory object. The user running the code needs to have read/write access to the Oracle Directory object.
CREATE PROCEDURE put_local_ascii_data (p_data IN CLOB,
p_dir IN VARCHAR2,
p_file IN VARCHAR2) IS
-- --------------------------------------------------------------------------
l_out_file UTL_FILE.file_type;
l_buffer VARCHAR2(32767);
l_amount BINARY_INTEGER := 32767;
l_pos INTEGER := 1;
l_clob_len INTEGER;
BEGIN
l_clob_len := DBMS_LOB.getlength(p_data);
l_out_file := UTL_FILE.fopen(p_dir, p_file, 'w', 32767);
WHILE l_pos <= l_clob_len LOOP
DBMS_LOB.read (p_data, l_amount, l_pos, l_buffer);
IF g_convert_crlf THEN
l_buffer := REPLACE(l_buffer, CHR(13), NULL);
END IF;
UTL_FILE.put(l_out_file, l_buffer);
UTL_FILE.fflush(l_out_file);
l_pos := l_pos + l_amount;
END LOOP;
UTL_FILE.fclose(l_out_file);
EXCEPTION
WHEN OTHERS THEN
IF UTL_FILE.is_open(l_out_file) THEN
UTL_FILE.fclose(l_out_file);
END IF;
RAISE;
END;
The p_data parameter is a CLOB that would ( in this case ) contain all the ascii data being written to the p_dir directory and the p_file name ( which gets created at runtime ).
You might need to do some clob conversions on your data, depending on the data types in your query results that you'd use to populate the clob object.
Best Answer
Prepend the scripts with an
@
in yourmaster.sql
script: