SQL 在csv文件中导出oracle表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27290985/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
export oracle table in a csv file
提问by user2443476
I am writting an sql script to export the content of tables in csv files (one table per file). I managed to write a successful script, thanks to the spool function like this :
我正在编写一个 sql 脚本来导出 csv 文件中表的内容（每个文件一个表）。多亏了像这样的 spool 函数，我设法编写了一个成功的脚本：
spool export.csv append
select 'fielda;fieldb;...' from dual
select fielda,fieldb,.... from table
spool off.
The first problem with this, is that I have to do a select from dual to get only the fields name on the first line.
与此有关的第一个问题是，我必须从 dual 中进行选择才能仅获取第一行的字段名称。
The second problem with this, is that I have to write each field, and it becomes very painfull when you have 10 tables each having more than 20 fields. So my question was, is there any pl sql function, that takes in parameter only the table name, and export the full content in a csv file.
第二个问题是，我必须写每个字段，当你有 10 个表，每个表有 20 个以上的字段时，这会变得非常痛苦。所以我的问题是，是否有任何 pl sql 函数，只接受表名的参数，并将完整内容导出到 csv 文件中。
Thanks in advance.
提前致谢。
回答by Shantanu
Below might work for you
下面可能对你有用
set termout off
set serveroutput off
set feedback off
set colsep ';'
set lines 100000
set pagesize 0
set echo off
set feedback off
spool on
spool D:\csv_generator_tmp.sql
select qr from
(select 'select '||a.column_name||'||'';''||' qr,a.COLUMN_ID
from user_tab_cols a
where a.table_name = upper('cust')
and a.column_id=1
union
select b.column_name||'||'';''||',b.COLUMN_ID
from user_tab_cols b
where b.table_name = upper('cust') and b.column_id<>1
and b.column_id<>(select max(c.column_id) from user_tab_cols c)
union
select d.column_name||' from cust;',d.COLUMN_ID
from user_tab_cols d
where d.table_name = upper('cust')
and d.column_id=(select max(d.column_id) from user_tab_cols d))
order by column_id asc;
spool off;
spool on
spool D:\cust.csv
@D:\csv_generator_tmp.sql
spool off;
/
回答by wmorrison365
No but you can use user_tablesand user_tab_colsto select the columns for tables of interest:
不，但您可以使用user_tables和user_tab_cols选择感兴趣的表的列：
select utc.table_name, utc.column_name
from user_tab_cols utc
where utc.table_name = 'CIRCUIT'
You could manage this through a cursor and generate your select columns. You can then execute this query.
您可以通过游标进行管理并生成您的选择列。然后您可以执行此查询。
回答by Exhausted
Its better to go with UTL_FILE. I would refer to user the Ask tom link https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::p11_question_id:88212348059
最好搭配UTL_FILE。我会参考用户提问链接https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::p11_question_id:88212348059
with that you can create the file regularly by calling the function.
有了它，您可以通过调用该函数定期创建文件。
回答by Alex B
You can use Python and cx_Oracle module to extract data to disk in CSV format.
您可以使用 Python 和 cx_Oracle 模块以 CSV 格式将数据提取到磁盘。
Here's how you connect to Oracle using cx_Oracle:
以下是使用 cx_Oracle 连接到 Oracle 的方法：
constr='scott/tiger@localhost:1521/ORCL12'
con = cx_Oracle.connect(constr)
cur = con.cursor()
After data fetch you can loop through Python list and save data in CSV format.
获取数据后，您可以遍历 Python 列表并以 CSV 格式保存数据。
for i, chunk in enumerate(chunks(cur)):
f_out.write('\n'.join([column_delimiter.join(row[0]) for row in chunk]))
f_out.write('\n')
I used this approach when I wrote TableHunter-For-Oracle
我在写TableHunter-For-Oracle时使用了这种方法
