Database technology: Spool command usages

First, introduce the parameter meaning of spool

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

SET feedback off--to echo the number of record bars processed by this SQL command, which defaults to on;

Set heading off--Displays column headings, default to On;

Set pagesize 50000--Sets the number of rows per page with the default of 14. When the value is set to 0 o'clock, the output is one page and the column headings are not displayed, and in order to avoid paging, we can usually set to 0.

SET Termout off-whether the output is displayed on the screen, or the execution result of the command in the script, default to On;

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

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

SET Trimspool on--Removes the redirection (spool) output trailing spaces for each line, and defaults to off;

SET Null text-when displayed, replaces the null value with the text value;

SET Serveroutput off--whether to display with Dbms_output. Put_Line packet output information;--When writing stored procedures, most will be the necessary information output;

Set NewPage None--Sets the separation between page and page {1|n| none}; At 0 o'clock there is a small black box at the beginning of each page, n blank rows between pages and pages when the value is N, and no spacing between pages and pages when none is available;

Set Linesize 1000--sets the number of characters {80|n} that a row can hold; the output is greater than the number of characters that the set row can hold, and the row is displayed. According to the actual character settings, too large export speed is slow;

This article URL address: http://www.bianceng.cn/database/Oracle/201410/45569.htm

Set wrap on--the length of the trip is greater than the set line length (set linesize n command); On, the extra characters are displayed on the other line, otherwise the extra characters will be cut off and not shown;

SET verify off--whether to show statements before and after substitution variables are substituted;

SET Colsep '; --field output separator;

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

The suggested format for exporting text data:

Sql*plus Environment Settings--

SET echo Off--The SQL statement that is executing in the script is not displayed when executing a SQL script with the start command

SET heading off--the name of the field is not displayed

Set pagesize 0--Sets the output number of rows per page, in order to avoid pagination, can be set to 0. When set to 0 o'clock, the output is one page and the column headers are not displayed with the feature set heading off.

SET NewPage None--there is no separation between pages and pages

SET trimout on--removes extra space after each line of standard output

SET Trimspool on--removing redirects (spool) output trailing spaces per line

Set Linesize 1000--sets the number of characters a row can hold

SET feedback off--to echo the number of record bars processed by this SQL command, which defaults to on;

Common format 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 + filename

SQL code that needs to be executed

Spool off

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, in addition to Windows export is best not to use plsql export, slow, directly with the commend under the Sqlplus Command minimized window execution.

Instance

Requirements

The ① 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

④ the delimiter in the exported text using commas (English) as Fields

⑤ 200 characters per president in exported text

⑥ 999 rows per page of exported text

⑦ the space behind each line in the exported text must be intercepted

Writing Scripts Txt_format.sql

--set partially set exported text format

Set echo off--The display of the SQL statement being executed in the script is closed

Set feedback off--closes the number of record bars processed by this SQL command by default to ON, which removes the last selected number of rows

Set verify off--you can turn off and open the prompt confirmation message

Set heading off--when the output of the closed caption is set to OFF, the field name of the select result is removed and only data is displayed

Set term off--do not output execution results on screen