SPOOL command Use instance "Oracle Export plain Text Format file"

SPOOL command Use instance "Oracle Export plain Text Format file"

SET echo off-whether to display the SQL statement being executed in the script when executing an SQL script with the start command;

SET feedback off-whether to echo the number of record bars processed by this SQL command, the default is on;

Set heading off-The column header is displayed, default is on;

Set pagesize 50000--Sets the number of rows per page and defaults to 14. When the value is set to 0 o'clock, the output is one page and the column headings are not displayed, in order to avoid paging, we can usually set it to 0.
SET Termout off-whether the output is displayed on the screen, or the execution result of a command in the script, the default is on;

SET trimout on; --Remove the extra space behind each line of the standard output, the default is off;

SET timing off-Displays the execution time spent on each SQL statement;

SET Trimspool On-Removes the trailing space for each line of redirection (spool) output, which is off by default;

SET NULL Text--Displays the text value in place of the null value;

SET Serveroutput off-is displayed with Dbms_output. Put_Line packet output information;--when writing a stored procedure, most of the necessary information will be output;

Set NewPage None-sets the separation between page and page {1|n| none}; 0 o'clock has a small black box at the beginning of each page; n blank lines between pages and pages when the value is N, and when none, there are no gaps between pages and pages;
Set Linesize 1000--sets the number of characters {80|n} that a row can hold, and the output content is greater than the number of characters that the set row can hold. According to the actual character set, too large export speed is slow;
Set wrap on-when the travel length is greater than the set line length (set with the Set linesize n command), when the value is on, the extra characters are displayed on another line, otherwise the extra characters will be cut off and not displayed;

SET Verify off-Shows whether the substitution variable is substituted before and after the statement;

SET Colsep "; --field output delimiter;

------------------------------------------------------------------------------------------------------

Suggested formats for exporting text data:
Sql*plus Environment Settings--

SET echo off--when executing an SQL script with the start command, the SQL statement executing in the script is not displayed

SET heading off--does not display the name of the field

Set pagesize 0-Sets the output per page line number, in order to avoid paging, can be set to 0. When set to 0 o'clock, the output is a page and the column headings do not appear with set heading off included features.

SET newpage None--no separation between page and page
SET trimout on-removes extra space after standard output per line
SET Trimspool on-remove redirect (spool) output trailing spaces per line
Set Linesize 1000--sets the number of characters a row can hold

SET feedback off-whether to echo the number of record bars processed by this SQL command, the default is on;

Common formats for exporting text data when working in use:

SET echo off

SET pagesize 0

SET Feedback off

SET Trimout on

SET Trimspool on

SET Linesize 1000

Spool path + file name

The SQL code that needs to be executed

Spool off

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.

Instance

Requirements

① export process is not displayed on the Sql*plus screen
② exported text does not contain executed SQL commands
③ the exported text does not contain a field name
④ using commas (English) as delimiters for fields in exported text
⑤ 200 characters per president in exported text
⑥ 999 lines per page in exported text
⑦ the space after each line in the exported text must be intercepted

Writing a script Txt_format.sql

--set section Setting the exported text format
Set echo off--closes the display of the SQL statement being executed in the script
Set feedback off--turns off the number of records processed by this SQL command, which is on by default, removing the last number of rows that have been selected
Set verify off--can close and open the prompt confirmation message
Set heading off--the output of the off header is set to off to remove the field name of the select result, displaying only the data
Set term off--does not output execution results on the screen
Set Trimspool on-remove extra space after each line "linesize-actual number of characters = extra space"
Set trimout on-removes extra space after standard output per line
Set Linesize 200--sets a maximum of 200 characters per line
Set pagesize 0--sets a maximum of 999 rows of records per page
Set Termout off--the screen does not display query data
[email protected]/home/oracle/leonarding/get_table.sql--set which table to export the data and the appearance of the export, this is the second method
Spool Ls_test.txt--where <file> is file_name[.ext] [cre[ate]| rep[lace]| App[end]]select rnc_id| | ' | ' | | rnc_label_cn| | ' | ' | | Rnc_label_en from RNC;
--only close the spool export operation, the output file will be seen in the output, this sentence can not be written in spool off will be an error after
Spool off
Exit

Note: Note the above two places: the set section and the spool section, which is used to format the exported text, which executes an SQL script again: Get_table.sql for export. Now let's look at another script Get_table.sql

Select Rnc_id| | ' | ' | | rnc_label_cn| | ' | ' | | Rnc_label_en from RNC;

So let's take a look at the results ls_test.txt

876947945| tjrnc04_260| tjrnc04_260
1128405375| tjrnc03_259| tjrnc03_259
1723122318| tjrnc05_261| tjrnc05_261
4290614068| tjrnc06_262| tjrnc06_262
145277785| tjrnc14_270| tjrnc14_270
96954| tjrnc02_258| tjrnc02_258
96956| tjrnc10_266| tjrnc10_266
96907| tjrnc08_264| tjrnc08_264
96926| tjrnc09_265| tjrnc09_265
96930| tjrnc12_268| tjrnc12_268
96932| tjrnc11_267| tjrnc11_267
96934| tjrnc13_269| tjrnc13_269
96946| tjrnc01_257| tjrnc01_257
96948| tjrnc15_271| tjrnc15_271
96950| tjrnc16_272| tjrnc16_272
96952| tjrnc17_273| tjrnc17_273
96906| tjrnc07_263| tjrnc07_263

Source: >

The following article mainly introduces how to correctly export the Oracle data to TXT format, Oracle data export to TXT in the actual application of the proportion is still more common, the following article is mainly on the actual operation of the description of the program, the following is the main content of the text description.

The SQL functionality provided by the Oracle database is very good and powerful, and the more you use it, the more you feel shallow and ignorant.

Last week to do a system demonstration need to hand-make some text data as a data source, want to steal lazy use the Toad tool directly query after export to CSV format. Today is just a bit of time, tidy up a bit of ideas, with SQL to achieve the generation of text data. Write a general framework, and then come to perfect when you are free.

The steps are as follows:

Create a new SQL script d:\czrk.sql with the following code: SET echo off

1. set feedback off  
2. set newpage none  
3. set pagesize  50000  
4. set linesize 20000  
5. set verify off  
6. Set pagesize 0  
7. set term off  
8. set trims on  
Li>set heading off  
9. set trimspool on  
10. Set trimout on  
11. set timing off  
12. set verify off  
13. set colsep& nbsp;|  
14. spool d:\czrk.txt  
15. select sfzh | |   ', '  | |  xm | |   ', '  | |  xb | |   ', '  
    | |  csrq | |   ', '  | |  mz| |   ', '  | |  xzqh| |   ', '  | |  jzdz FROM m_czrk WHERE rownum<=10000;  
16. spool off   

Oracle data exported to TXT format: Sqlplus connect to database, execute script

1. Sqlplus Dc/[email protected];
2. Sqlplus>@d:\czrk.sql

This generates 10,000 data in the D drive.

The above related content is to export the Oracle data to TXT format introduction, hope you can gain.

SPOOL command Use instance "Oracle Export plain Text Format file"