turn from: http://blog.csdn.net/ahngzw77/article/details/8652722 for SQL spool data, it is best to define your own format 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;
Spool commonly used settings set COLSEP ";
Field output delimiter set echo off;
Displays each SQL command in the script starting from start, default to on set feedback off;
echo the number of record bars processed by this SQL command, the default is on set heading off;
Output field header, default is on set pagesize 0;
Output number of rows per page, default is 24, in order to avoid paging, can be set to 0. Set Termout off;
Shows the execution result of the command in the script, the default is on set trimout on;
Remove trailing spaces for each line of standard output, default to Off set Trimspool on;
Remove redirect (spool) output trailing spaces per line, default to off the recommended 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 to be slightly larger, lest the data be truncated, it should be combined with the corresponding trimspool to prevent the exported text has too many trailing spaces.
However, if the linesize setting is too large, will greatly reduce the speed of the export, in addition to the export under Windows is best not to export with plsql, slower, directly with the commend under the Sqlplus Command minimized window execution.
For a field containing a lot of carriage return line feed should be given and filtered to form a more formal text file.
Typically, we use the spool method to export a table from a database to a text file using two methods, such as the following:
Method One: Use the following format script
Set Colsep ' | '--settings | For column separators
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 + file name
Select Col1| | ', ' | | col2| | ', ' | | col3| | ', ' | | col4| | '.. ' from TableName; Spool off
Compare the above method, that is, method one using the set delimiter and then by the sqlplus themselves using the set delimiter to split the field, method two will be the delimiter stitching in the SELECT statement, that is, manually control the output format.
In practice, it is found that the data obtained through the method is of great uncertainty, and the possibility of error when the data is imported by Sqlldr is more than 95%, especially for the large quantity data table,
This is especially true for tables with 1 million records, and the exported data file is wildly large. and method two exported data file format is very regular, the size of the data file may be the method of about 1/4. When the data files are imported by Sqlldr by this method,
The likelihood of an error is minimal, and the basic can be imported successfully.
Therefore, in practice I suggest that you use method two manual to control the format of the spool file, so as to 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 OS SUSE LINUX Enterprise Server 9)
The 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/[email protected] $DB _serv<<eof #-S
The parameter mask prints to other information on the screen, showing only the information queried from the DB after SQL execution, filtering out other information written in the file when the spool function executes.
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 after generating 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 9|5| 15|20080307
Note: In the above test example, the target generation file Promt.txt in spool promt.txt,
Invoking Oracle's spool function in a shell script in a hp-unx environment, if you encapsulate the above logic code as a function and then call this function, the Promt.txt file will not eventually be generated in the shell script.
Only logical code can be executed directly, and the spool function fails after encapsulation.
For promt.txt in the relative path, the following 2 methods are executed in the shell environment, the two can only choose one, both coexist then the spool function will be invalidated.
Assume that the path to the Promt.txt file is:/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 ..."
Way [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 ..."
Example
How Oracle data is exported directly to a text file
Oracle data can be exported to a text file using the spool buffer pool technology in Oracle.
1), enter the buffering start command in Oracle PL/SQL, and specify the file name of the output:
Spool D:output.txt
2), enter your SQL query at the command line:
0select Mobile from customer.
0select Mobile from client.
......
3), enter the buffer result command at the command line:
Spool off.
The system outputs the results from the buffer pool to the "output.txt" file.
The TAB key separates
Example (Syoyou_xamshain.sql file)
Set NewPage 0
Set Linesize 10000
Set pagesize 0
Set und Off
Set Trimspool on
Set COLSEP,
Set echo off
Set Feedback off
Spool Syoyou_xamshain.csv
0select
SHIMEI_CD, Valid_term_start_ymd, Shain_nmj_sei, Shain_nmj_na, Shain_nmhk_sei,
Shain_nmhk_na, Shain_nmhe_sei, Shain_nmhe_middle, Shain_nmhe_na, SEI_BETSU_KBN,
nok00j0001. NOZJ1301 (Birth_day),
Saiyou_ymd, KEI_BETSU_CD, Retire_ymd, BANK_CD, BANK_NMJ, BANK_NMK, BANK_SHITEN_CD,
BANK_SHITEN_NMJ, BANK_SHITEN_NMK, Yokin_type, Kouza_meigi_kana,
nok00j0001. NOZJ1301 (Kouza_no), SSO_TAISHO_FLG,
Valid_term_end_ymd, NEXT_SYSTEM_TAISHO_FLG, IDOU_FLG, YUUKOU_FLG, CREATE_USER_CD,
Create_time_stamp, UPDATE_USER_CD, Update_time_stamp
From Xamshain.
Spool off http://www.programgo.com/article/77002703879/
Oracle Export TXT text file
