oracle中如何把查询结果导出到excel里面？

使用pl/sql ，查询结果另存为。
或者sqlplus的spool

一般客户端都有这个功能，pl/sql在数据结果列表中有导出查看结果选项，导出格式选EXCEL就行

Export Data into Excel using PL/SQL Scripts
10/17/2005
By ITtoolbox Popular Q&A Team for ITtoolbox as adapted from Oracle-Apps-L discussion groupSummary:
I would like to export data from a table into Excel through PL/SQL scripts. Are there any codes available?Full Article:
Disclaimer: Contents are not reviewed for correctness and are not endorsed or recommended by ITtoolbox or any vendor. Popular Q&A contents include summarized information from ITtoolbox Oracle Applications discussion unless otherwise noted.1. Adapted from a response by venkay2 On Tuesday, September 06, 2005The following code may be helpful to you.v_OutPutFile :=
UTL_FILE.FOPEN(p_outfile_loc,p_file_name,'w');
p_file_name='test.csv'2. Adapted from a response by rafique On Wednesday, September 07, 2005We used the following PL/SQL code to convert table data to excel. Sample
code as follows.PROCEDURE EXCEL_FILE IScursor dtl is select * from accounts_icd order by auth_dt,ich_ven_cd;row_num number;
col_num number;
cell_val number;app_acc OLE2.OBJ_TYPE;
dbs OLE2.OBJ_TYPE;
dc OLE2.OBJ_TYPE;
args_acc OLE2.OBJ_TYPE;
app OLE2.OBJ_TYPE;
args OLE2.LIST_TYPE;
args2 OLE2.LIST_TYPE;
ws OLE2.OBJ_TYPE;
wb OLE2.OBJ_TYPE;
wss OLE2.OBJ_TYPE;
wbs OLE2.OBJ_TYPE;
cell OLE2.OBJ_TYPE;
ctr1 NUMBER := 0;
ctr2 NUMBER := 0;
ctr3 PLS_INTEGER := 0; --NUMBER := 0;
m_row number:=1;
m_len number:=0;m_flag number:=0;Begin---------------- Initialise Excelapp := OLE2.CREATE_OBJ('Excel.Application');
OLE2.SET_PROPERTY(app, 'Visible','True');
wbs := OLE2.GET_OBJ_PROPERTY(app,'Workbooks');
wb := OLE2.INVOKE_OBJ(wbs,'Add');
wss := OLE2.GET_OBJ_PROPERTY(app,'Worksheets');
ws := OLE2.INVOKE_OBJ(wss,'Add');---For I in 1..2 loopargs := OLE2.CREATE_ARGLIST;OLE2.ADD_ARG(args, m_row); --- Row
OLE2.ADD_ARG(args, I); --- Columncell := OLE2.GET_OBJ_PROPERTY(ws,'Cells', args);
OLE2.DESTROY_ARGLIST(args);if I=1 then
OLE2.SET_PROPERTY(cell, 'Value','Auth. Date');
elsif I=2 then
OLE2.SET_PROPERTY(cell, 'Value','ICD Doc.No');
end if;OLE2.RELEASE_OBJ(cell);
end loop;
--- Heading-- DataFor J in Dtl loopFor I in 1..2 loop-- Repeat Row
args := OLE2.CREATE_ARGLIST;OLE2.ADD_ARG(args, m_row); --- Row
OLE2.ADD_ARG(args, I); --- Columncell := OLE2.GET_OBJ_PROPERTY(ws,'Cells', args);
OLE2.DESTROY_ARGLIST(args);if I=1 then
OLE2.SET_PROPERTY(cell, 'Value',J.AUTH_DT);
elsif I=2 then
OLE2.SET_PROPERTY(cell, 'Value',J.ICD_DOC_NO);
end if
end loop
end loop
---- EndOLE2.RELEASE_OBJ(wbs);
OLE2.RELEASE_OBJ(ws);
OLE2.RELEASE_OBJ(wbs);
OLE2.RELEASE_OBJ(wb);
OLE2.RELEASE_OBJ(app);END;

可以直接用SQL Navigator 这个第三方工具来生成即可~~

SQL Navigator 版本 4.5

PLSQL Developer 工具不錯,可以從數據庫中導在Excel里,也可以把Excel資料匯入Oracle中.

PLSQL Developer 导出

用第三方工具吧，pb，sqldeveloper都行，非用sqlplus才是没事找事。

plsql 就可以的 pb的数据窗口也可以 saveas

Export Data into Excel using PL/SQL Scripts

pb，sqldeveloper 导出或者复制粘贴

先把你要导出的数据字段用特殊符号如"&"连接起来.
spool test.txt
select 字段1||'&'||字段2||... from table .
spool off
然后EXCEL中有一个可以根据特殊字符把一行字符分开处理的功能.这要就直接导入了.我就是这样用的.

好像这用PL SQL DEL的多噢
我用TOAD
一般第三方工具都有导入导出EXECL功能的

pl sql工具啊，选中导出结果，然后右键就有导出功能啊，导出为CSV

用pl/sql导出的格式中有excel格式的么

不好意思，我错了，CRV格式的就是

PL/SQL~选择查询出来的结果Copy to Excel!

调试易

oracle中如何把查询结果导出到excel里面？

解决方案 »