调试经验——使用SQLPLUS导出数据到Excel时的环境变量设置

最新推荐文章于 2022-12-09 10:19:11 发布

原创最新推荐文章于 2022-12-09 10:19:11 发布 · 721 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

收录于

数据库（DB）

本文介绍如何使用SQLPLUS从Oracle数据库中导出数据到.xls格式文件，包括设置环境变量、调整输出格式及使用SET命令进行配置的详细说明。

问题描述：

要使用SQLPLUS从Oracle数据库中出数据为.xls格式。大概需要如下设置环境变量。

sqlplus yourDatabase/yourPassword

set linesize 32767
set pagesize 50000
set arraysize 5000

set term off verify off feedback off

set markup html on entmap ON spool on preformat off

SPOOL C:MYOUTPUT.XLS

@'C:\testDir\testFile.sql'

spool off

exit

语法详解（参考链接：https://docs.oracle.com/cd/E11882_01/server.112/e16604/toc.htm）：

SET TERMOUT
SET TERMOUT OFF suppresses the display so that you can spool output from a script without seeing it on the screen.
If both spooling to file and writing to terminal are not required, use SET TERMOUT OFF in SQL scripts to disable terminal output.

SET VER[IFY] {ON | OFF}
Controls whether to list the text of a SQL statement or PL/SQL command before and after replacing substitution variables with values. ON lists the text; OFF suppresses the listing.

SET FEED[BACK] {6 | n | ON | OFF}
Displays the number of records returned by a script when a script selects at least n records.
ON or OFF turns this display on or off. Turning feedback ON sets n to 1. Setting feedback to zero is equivalent to turning it OFF.
SET FEEDBACK OFF also turns off the statement confirmation messages such as 'Table created' and 'PL/SQL procedure successfully completed' that are displayed after successful SQL or PL/SQL statements.

SET MARK[UP] HTML [ON | OFF] [HEAD text] [BODY text] [TABLE text] [ENTMAP
{ON | OFF}] [SPOOL {ON | OFF}] [PRE[FORMAT] {ON | OFF}]
Outputs HTML marked up text.
To be effective, SET MARKUP commands that change values in dynamic report output must occur before statements that produce query output. The first statement that produces query output triggers the output of information affected by SET MARKUP such as HEAD and TABLE settings. Subsequent SET MARKUP commands have no effect on the information already sent to the report.
SET MARKUP only specifies that SQL*Plus output will be HTML encoded. You must use SET MARKUP HTML ON SPOOL ON and the SQL*Plus SPOOL command to create and name a spool file, and to begin writing HMTL output to it. SET MARKUP has the same options and behavior as SQLPLUS -MARKUP.
See MARKUP Options on page 3-7 for detailed information. For examples of usage, see SET MARK[UP] HTML [ON | OFF] [HEAD text] [BODY text] [TABLE text] [ENTMAP {ON | OFF}] [SPOOL {ON | OFF}] [PRE[FORMAT] {ON | OFF}] on page 12-128, and Generating HTML Reports from SQL*Plus on page 7-1.
Use the SHOW MARKUP command to view the status of MARKUP options.

ENTMAP {ON | OFF}
Enables entity mapping to be turned on or off for selected columns in HTML output.
This feature enables you to include, for example, HTML hyperlinks in a column of data, while still mapping entities in other columns of the same report. By turning entity mapping off for a column containing HTML hyperlinks, the HTML anchor tag delimiters, <, >, " and &, are correctly interpreted in the report. Otherwise they would be replaced with their respective entities, <, >, " and &, preventing web browsers from correctly interpreting the HTML.
Entities in the column heading and any COMPUTE labels or output appearing in the column are mapped or not mapped according to the value of ENTMAP for the column.
The default setting for COLUMN ENTMAP is the current setting of the MARKUP HTML ENTMAP option.
For more information about the MARKUP HTML ENTMAP option, see SET MARKUP Options on page 3-7.

SPOOL {ON|OFF}
SPOOL ON or OFF specifies whether or not SQL*Plus writes the HTML opening tags, <HTML> and <BODY>, and the closing tags, </BODY> and </HTML>, to the start and end of each file created by the SQL*Plus SPOOL filename command. The default is OFF.
You can turn SPOOL ON and OFF as required during a session.
SQL*Plus writes several HTML tags to the spool file when you issue the SPOOL filename command.
When you issue any of the SQL*Plus commands: EXIT, SPOOL OFF or SPOOL filename, SQL*Plus appends the following end tags and closes the file:
</BODY>
</HTML>
You can specify <HEAD> tag contents and <BODY> attributes using the HEAD and BODY options

PRE[FORMAT] {ON|OFF}
PREFORMAT ON or OFF specifies whether or not SQL*Plus writes output to the <PRE> tag or to an HTML table. The default is OFF, so output is written to a HTML table by default. You can turn PREFORMAT ON and OFF as required during a session.

Note: To produce report output using the HTML <PRE> tag, you must set PREFORMAT ON. For example:
SQLPLUS -M "HTML ON PREFORMAT ON"
or
SET MARKUP HTML ON PREFORMAT ON

SET ARRAYSIZE
Sets the number of rows that SQL*Plus will fetch from the database at one time. Valid values are 1 to 5000.
The effectiveness of setting ARRAYSIZE depends on how well Oracle Database fills network packets and your network latency and throughput. In recent versions of SQL*Plus and Oracle Database, ARRAYSIZE may have little effect. Overlarge sizes can easily take more SQL*Plus memory which may decrease overall performance.