問題描述
如何編寫將Oracle數據庫表數據導出到excel文件的程序 (How to write a Procedure which exports Oracle database table data into excel file)
set feedback off
set heading off
set underline off
set colsep ','
spool /u01/app/oracle/export/mysheet.csv
select * from mytab'
spool off
這在命令提示符下工作正常,但我需要為此編寫一個程序,誰能幫忙
參考解法
方法 1:
I have used this package from oracle‑developer.net in a production database and it has worked flawlessly. It works on Oracle 8i onwards.
方法 2:
Use the results from your SQL and then use the PL/SQL package UTL_FILE to open and write to a file, separating your results with commas in a variable first.
As an example:
vline
would contain the results from your SELECT with each column separated by a comma as in SELECT col1 ||','|| col2 ||',||
... INTO vline
...
vfile
is the name of your file.
UTL_FILE.PUT_LINE(vfile, vline));
Search Oracle docs for more info on the UTL_FILE package.
HTH
(by Jash、kevinskio、tale852150)