Oracle Spool Usage Summary __oracle

For SQL spool data, it is best to define the format yourself to facilitate direct import of the program, SQL statements such as:
Select Taskindex| | | | commonindex| | | | tasktype| | | | To_number (To_char (sysdate, ' YYYYMMDD ')) from Ssrv_sendsms_task; Common settings for Spool
Set Colsep '; Field Output Separator
Set echo off; Displays each SQL command in the script that starts the start, and defaults to On
Set feedback off; Echoes the number of record bars processed by this SQL command, which defaults to on
Set heading off; Output field header, default to On
Set pagesize 0; Output the number of rows per page, the default is 24, to avoid paging, can be set to 0.
Set Termout off; Displays the execution result of the command in the script, by default on
Set trimout on; Remove the trailing spaces for each line of the standard output by default off
Set Trimspool on; Remove the redirection (spool) output trailing spaces for each line, and the default is off

The suggested format for exporting text data:
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 should be slightly larger, lest the data be truncated, and it should be combined with the corresponding trimspool to prevent the exported text from having too many trailing spaces.
However, if the linesize setting is too large, it will greatly reduce the speed of the export, and in addition, export in Windows is best not to export with plsql, slow,
Directly with the commend under the Sqlplus Command minimized window execution. For the field contains a lot of carriage return line breaks should be given and filtered to form a more regular text file.
In general, we use the spool method to export a table in a database to a text file in two ways, such as the following:

Method One: Use the following format script
Set Colsep ' | '--set | To column 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 path + filename
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 path + filename
Select Col1| | ', ' | | col2| | ', ' | | col3| | ', ' | | col4| | '. ' FROM TableName;
Spool off

Compare the above method, that is, the method uses the set separator and then the Sqlplus itself uses the set delimiter to split the field, method two separates the separator in the SELECT statement,
That is, manual control of the output format. In practice, it is found that the data derived from the method is very uncertain, and the data derived from this method is then imported by Sqlldr.
The likelihood of error is more than 95%, especially for large quantities of data tables, such as the 1 million-record table, and the exported data files are wildly large. and method Two-exported data text
The format of the piece is very regular, the size of the data file may be about 1/4 of the method. After the data files which are derived from this method are imported by SQLLDR, the possibility of error is very small, the basic
can be imported successfully. Therefore, in practice I suggest that you use method two manual to control the format of spool file, this can reduce the possibility of error, avoid a lot of detours.

Self-test Example:
Export data from the Ssrv_sendsms_task table to text (database Oracle 9i operating system 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 masking other information that is printed to the screen, only displays information that is queried from DB after SQL execution, and filters out other information written in the file when the spool function is executed. 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
Execute./spool_test.sh generates Sp_test.txt,
The contents are 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 20080307
Note: In the above example, the target generated file Promt.txt in the spool promt.txt, the spool function of Oracle is invoked in the shell script in the HP-UNX environment, if the above
The logical code is encapsulated as a function and then invoked, then the Promt.txt file is eventually not generated in the shell script. Logic can only be executed directly
Code, the spool function fails after encapsulation. For promt.txt in the relative path, the following 2 methods in the shell environment, the two can only choose one, the two coexist spool
function is invalidated. Suppose the path generated by the Promt.txt file is:/home/zxin10/zhuo/batchoperate/spoolfile mode [1] echo "Start spool in shell ..."
Sqlplus-s zxdbm_ismp/zxin_smap<<eof set pagesize 0 set echo off feed out term off heading off trims off set Colsep '|'
Set Trimspool on set linesize 10000 set Trimspool on set linesize set newpage 1 spool/home/zxin10/zhuo/batchoperate/s Poolfile/promt.txt Select Batchindex| | | | productid| | | | contentid| | | | optype| | | | UploadFile from Zxdbm_700.s700_batch_operation where Status=1; Spool off EOF echo "End ..." mode [2] echo "Start spool in shell ..." Cd/home/zxin10/zhuo/batchoperate/spoolfile sqlplus-s zxdb M_ismp/zxin_smap<<eof set pagesize 0 set echo off feed off term out heading off trims off set colsep ' | ' Set TRIMSPO OL on set linesize 10000 set Trimspool on set linesize 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 ..."