问题描述：
要使用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.
7391
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
