ORACLE SPOOL Usage Summary

Jun 22 posted in Oracle (RSS), 472 views.

On SPOOL (SPOOL is SQLPLUS command, not the things inside the SQL syntax.)

SPOOL data for SQL, the best definition to its own format, to facilitate the direct import procedures, SQL statements such as:
select taskindex ||'|'|| commonindex ||'|'|| tasktype ||'|'|| to_number (to_char (sysdate, 'YYYYMMDD')) from ssrv_sendsms_task;

common spool settings
set colsep ''; / / domain separator output
set echo off; / / show the start of the script to start each sql command, the default is on
set feedback off; / / echo sql commands the number of records processed, the default is on
set heading off; / / output domain title, default is on
set pagesize 0; / / output number of lines per page, default is 24, in order to avoid paging, can be set to 0.
set termout off; / / show the script command in the implementation of the results, the default is on
set trimout on; / / standard output to remove the trailing spaces on each line, default to off
set trimspool on; / / remove redirection (spool) output of the trailing spaces on each line, default to off

The text of the proposed export data format:
SQL * PLUS environment settings SET NEWPAGE NONE
SET HEADING OFF
SET SPACE 0
SET PAGESIZE 0
SET TRIMOUT ON
SET TRIMSPOOL ON
SET LINESIZE 2500

Note: LINESIZE set to slightly larger, so the data is truncated, it should be and the corresponding export TRIMSPOOL a combination of the text to prevent too many spaces in the rear. However, if set too LINESIZE will significantly reduce the rate of export, while export of the best in WINDOWS not use PLSQL Export, speed is relatively slow COMMEND directly under the command SQLPLUS window minimize implementation.

The field contains a lot of line breaks should be given to enter filtering rules to form a text file comparison. Under normal circumstances, we use methods of SPOOL to export table in the database as a text file when the two methods will be adopted, such as the following:

Method one: use the following format script
set colsep '|' - set up | for the list separator set trimspool on
set linesize 120
set pagesize 2000
set newpage 1
set heading off
set term off
set num 18
set feedback off
spool file name path + select * from tablename;
spool off

Method two: use the following script
set trimspool on
set linesize 120
set pagesize 2000
set newpage 1
set heading off
set term off
spool file name path + select col1 ||','|| col2 ||','|| col3 ||','|| col4 ||'..' from tablename;
spool off

The above method of comparison, that is, a way to set the delimiter used by sqlplus for their own use and then set the field delimiter to split on, methods separator 2 will be spliced in the SELECT statement, that is, manual control of output format.

In practice, found a guide through the methods of data out of great uncertainty, such an approach guided by the sqlldr to import data from a time when the possibility of error in more than 95%, especially for high-volume data table, such as the one million records, especially the table, and export data files crazy big.

Second, the methods of data export file formats are structured, data file size may be the way to a 1 / 4. Guided by this method from the data file and then import sqlldr when the possibility of error is very small, the basic can be successfully imported.

Therefore, the practice I suggest that you use to control second-hand work of the spool file format, which would reduce the possibility of error, to avoid a lot of detours.

Self-test cases: ssrv_sendsms_task table will be exported to the text of the data (operating system, Oracle 9i database on SUSE LINUX Enterprise Server 9)

spool_test.sh script is as follows:
#! / bin / sh
DB_USER = zxdbm_ismp # DB USER
DB_PWD = zxin_smap # DB PASSWORD
DB_SERV = zx10_40_43_133 # DB SERVICE NAME

sqlplus-s $ DB_USER / $ DB_PWD @ $ DB_SERV <<EOF #-s parameter to shield print additional information on the screen only shows the implementation of sql query from the DB by the information, the implementation of filter function in the spool file to write entry of other information.
set trimspool on
set linesize 120
set pagesize 2000
set newpage 1
set heading off
set term off
spool promt.txt
select taskindex ||'|'|| commonindex ||'|'|| tasktype ||'|'|| to_number (to_char (sysdate, 'YYYYMMDD')) from ssrv_sendsms_task;
spool off
EOF

Implementation. / Spool_test.sh generated sp_test.txt, reads as follows:
83 | 115 | 1 | 20080307
85 | 115 | 11 | 20080307
86 | 115 | 10 | 20080307
84 | 115 | 2 | 20080307
6 | 5 | 14 | 20080307
7 | 5 | 12 | 20080307
9 | 5 | 15 | 20080307

Note: The above self-test cases, spool promt.txt document of the target to generate promt.txt, in the HP-UNX environment shell script to call Oracle function of the spool, if the above code packaged as a logic function, and then to call this function, then the shell script will not generate the final document promt.txt. Can only direct the implementation of the logic of code, package failure after spool function.
Promt.txt in the relative path to the next, the following 2 methods of implementation in the shell environment, the two can only choose between the two exist side by side spool function is invalid. Promt.txt assumptions to generate the path for the file: / home/zxin10/zhuo/batchoperate/spoolfile
Way [1]
echo "start spool in shell .."

sqlplus-s zxdbm_ismp / zxin_smap <<EOF
set pagesize 0
set echo off feed off term off heading off trims off
set colsep '|'
set trimspool on
set linesize 10000
set trimspool on
set linesize 120
set newpage 1
spool / home/zxin10/zhuo/batchoperate/spoolfile/promt.txt
select batchindex ||'|'|| productid ||'|'|| contentid ||'|'|| optype ||'|'|| uploadfile from zxdbm_700.s700_batch_operation where status = 1;
spool off
EOF
echo "end .."
Approach [2]
echo "start spool in shell .."
cd / home/zxin10/zhuo/batchoperate/spoolfile
sqlplus-s zxdbm_ismp / zxin_smap <<EOF
set pagesize 0
set echo off feed off term off heading off trims off
set colsep '|'
set trimspool on
set linesize 10000
set trimspool on
set linesize 120
set newpage 1
spool promt.txt
select batchindex ||'|'|| productid ||'|'|| contentid ||'|'|| optype ||'|'|| uploadfile from zxdbm_700.s700_batch_operation where status = 1;
spool off
EOF
echo "end .."