Shulou(Shulou.com)05/31 Report--

This article is about how to use the spool command in sqlplus. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

For the SQL of SPOOL data, it is best to define the format yourself to facilitate the program's direct import, such as SQL statements:

In the T_port table: select t.dslamip | |'|'| | t.dslamno from t_port t where t.dslamipkeeper 200.224.49.5'

Settings commonly used in spool

Set colsep'|'; / / domain output delimiter: it's best to specify it yourself in SQL.

Set echo off; / / displays each sql command in the script started by start. The default is on.

Set feedback off; / / echoes the number of records processed by this sql command. Default is on.

Set heading off; / / output domain title, default is on

Set pagesize 0; / / outputs lines per page, which defaults to 24, and can be set to 0 to avoid paging.

Set termout off; / / displays the execution result of the command in the script. Default is on.

Set trimout on; / / removes trailing spaces from each line of standard output. Default is off.

Set trimspool on; / / remove trailing spaces for each line of spool output. Default is off.

Set linesize 2500; / / set the size for each line

Note: LINESIZE should be slightly larger to avoid data truncation, it should be used in conjunction 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 slow down the export speed. In addition, it is best not to use PLSQL export under WINDOWS, because the speed is relatively slow. Use the SQLPLUS command under COMMEND to minimize the window execution directly.

Fields that contain many carriage return newline characters should be filtered to form a more regular text file. Typically, we use the SPOOL method to export a table in to a text file in two ways, such as the following:

Method 1: script in the following format

Set colsep'|'--setting | column delimiter

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 + file name

Select * from tablename

Spool off

Method 2: 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 methods, that is, method 1 uses a set delimiter and then sqlplus uses the set separator to divide the field, and method 2 splices the separator in the SELECT statement, that is, manually controls the output format.

In practice, it is found that the data derived from method 1 has great uncertainty, and the possibility of error when the data derived from this method is imported by sqlldr is more than 95%, especially for a large number of data tables, such as 1 million records, and the exported data files are extremely large.

On the other hand, the format of the data file exported by method 2 is very regular, and the size of the data file may be about 1x4 of method 1. When the data files exported by this method are imported by sqlldr, the possibility of error is very small, and basically all of them can be imported successfully.

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

Spool path + file name

Select col1 | |','| | col2 | |','| | col3 | |','| | col4 | |'..' From tablename

Spool off

Thank you for reading! This is the end of this article on "how to use spool commands in sqlplus". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.