[20120817]sqlplus 輸出html格式.txt

[20120817]sqlplus 輸出html格式.txt

許多blog介紹在sqlplus輸出excel檔案的方法,實際上執行的是:
set markup HTML ON
生成的是html格式的檔案,只不過MS OFFICE可以當作電子表格檔案開啟!

利用這個特性,可以使用firefox開啟.

參考連結如下:

建立html.sql檔案:

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

-- get the last SQL*Plus output in HTML

-- after Tanel Poder

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

set termout off

set markup HTML ON HEAD " -

 -

" -

BODY "" -

TABLE "border='1' align='center' summary='Script. output'" -

SPOOL ON ENTMAP ON PREFORMAT OFF

spool myoutput.html

l

/

spool off

set markup html off spool off

host "E:\Program Files\Mozilla Firefox\firefox.exe" myoutput.html

set termout on

--注意修改firefox.exe的執行路徑!呼叫html.sql之前,先執行要輸出的sql語句.
--指令碼巧妙的利用sqlplus的list(l)命令,調出上次執行的sql語句.

SQL> help l

 LIST

 ----

 Lists one or more lines of the most recently executed SQL command

 or PL/SQL block which is stored in the SQL buffer. Enter LIST with

 no clauses to list all lines. In SQL*Plus command-line you can also

 use ";" to list all the lines in the SQL buffer. The buffer has no

 command history list and does not record SQL*Plus commands.

 L[IST] [n | n m | n  * | n LAST | * | * n | * LAST | LAST]