Oracle创建假脱机文件,sql - 如何使用SQLPLUS假脱机到CSV格式的文件？

sql - 如何使用SQLPLUS假脱机到CSV格式的文件？

我想提取一些CSV输出格式的查询。 不幸的是，我不能使用任何花哨的SQL客户端或任何语言来做到这一点。 我必须使用SQLPLUS。

我该怎么做？

13个解决方案

143 votes

您也可以使用以下内容，但它确实在字段之间引入了空格。

set colsep , -- separate columns with a comma

set pagesize 0 -- No header rows

set trimspool on -- remove trailing blanks

set headsep off -- this may or may not be useful...depends on your headings.

set linesize X -- X should be the sum of the column widths

set numw X -- X should be the length you want for numbers (avoid scientific notation on IDs)

spool myfile.csv

select table_name, tablespace_name

from all_tables

where owner = 'SYS'

and tablespace_name is not null;

输出将如下：

TABLE_PRIVILEGE_MAP ,SYSTEM

SYSTEM_PRIVILEGE_MAP ,SYSTEM

STMT_AUDIT_OPTION_MAP ,SYSTEM

DUAL ,SYSTEM

...

这比输入所有字段并用逗号连接它们要简单得多。 如果需要，您可以使用简单的sed脚本来删除逗号之前出现的空格。

像这样的东西可能有用...(我的sed技能非常生疏，所以这可能需要工作)

sed 's/\s+,/,/' myfile.csv

Gabe answered 2019-06-08T20:42:10Z

32 votes

我将此命令用于提取维度表(DW)数据的脚本。 所以，我使用以下语法：

set colsep '|'

set echo off

set feedback off

set linesize 1000

set pagesize 0

set sqlprompt ''

set trimspool on

set headsep off

spool output.dat

select '|',

from

where

spool off

并且工作。 我不使用sed格式化输出文件。

Hallison Batista answered 2019-06-08T20:42:40Z

20 votes

我看到一个类似的问题......

我需要从SQLPLUS中删除CSV文件，但输出有250列。

我做了什么来避免恼人的SQLPLUS输出格式：

set linesize 9999

set pagesize 50000

spool myfile.csv

select x

from

(

select col1||';'||col2||';'||col3||';'||col4||';'||col5||';'||col6||';'||col7||';'||col8||';'||col9||';'||col10||';'||col11||';'||col12||';'||col13||';'||col14||';'||col15||';'||col16||';'||col17||';'||col18||';'||col19||';'||col20||';'||col21||';'||col22||';'||col23||';'||col24||';'||col25||';'||col26||';'||col27||';'||col28||';'||col29||';'||col30 as x

from (

... here is the "core" select

)

);

spool off

问题是你会丢失列标题名称...

你可以添加这个：

set heading off

spool myfile.csv

select col1_name||';'||col2_name||';'||col3_name||';'||col4_name||';'||col5_name||';'||col6_name||';'||col7_name||';'||col8_name||';'||col9_name||';'||col10_name||';'||col11_name||';'||col12_name||';'||col13_name||';'||col14_name||';'||col15_name||';'||col16_name||';'||col17_name||';'||col18_name||';'||col19_name||';'||col20_name||';'||col21_name||';'||col22_name||';'||col23_name||';'||col24_name||';'||col25_name||';'||col26_name||';'||col27_name||';'||col28_name||';'||col29_name||';'||col30_name from dual;

select x

from

(

select col1||';'||col2||';'||col3||';'||col4||';'||col5||';'||col6||';'||col7||';'||col8||';'||col9||';'||col10||';'||col11||';'||col12||';'||col13||';'||col14||';'||col15||';'||col16||';'||col17||';'||col18||';'||col19||';'||col20||';'||col21||';'||col22||';'||col23||';'||col24||';'||col25||';'||col26||';'||col27||';'||col28||';'||col29||';'||col30 as x

from (

... here is the "core" select

)

);

spool off

我知道它有点硬核，但它对我有用......

Karlos answered 2019-06-08T20:43:37Z

15 votes

使用较新版本的客户端工具，可以使用多种选项来格式化查询输出。 其余的是将其假脱机到文件或将输出保存为文件，具体取决于客户端工具。 以下是一些方法：

在SQL * Plus

使用SQL * Plus命令，您可以格式化以获得所需的输出。 使用SPOOL将输出假脱机到文件。

例如，

SQL> SET colsep ,

SQL> SET pagesize 20

SQL> SET trimspool ON

SQL> SET linesize 200

SQL> SELECT * FROM scott.emp;

EMPNO,ENAME ,JOB , MGR,HIREDATE , SAL, COMM, DEPTNO

----------,----------,---------,----------,---------,----------,----------,----------

7369,SMITH ,CLERK , 7902,17-DEC-80, 800, , 20

7499,ALLEN ,SALESMAN , 7698,20-FEB-81, 1600, 300, 30

7521,WARD ,SALESMAN , 7698,22-FEB-81, 1250, 500, 30

7566,JONES ,MANAGER , 7839,02-APR-81, 2975, , 20

7654,MARTIN ,SALESMAN , 7698,28-SEP-81, 1250, 1400, 30

7698,BLAKE ,MANAGER , 7839,01-MAY-81, 2850, , 30

7782,CLARK ,MANAGER , 7839,09-JUN-81, 2450, , 10

7788,SCOTT ,ANALYST , 7566,09-DEC-82, 3000, , 20

7839,KING ,PRESIDENT, ,17-NOV-81, 5000, , 10

7844,TURNER ,SALESMAN , 7698,08-SEP-81, 1500, , 30

7876,ADAMS ,CLERK , 7788,12-JAN-83, 1100, , 20

7900,JAMES ,CLERK , 7698,03-DEC-81, 950, , 30

7902,FORD ,ANALYST , 7566,03-DEC-81, 3000, , 20

7934,MILLER ,CLERK , 7782,23-JAN-82, 1300, , 10

14 rows selected.

SQL>

SQL Developer Version 4.1

或者，您可以在SQL Developer中使用新的/*csv*/提示。

/*csv*/

例如，在我的SQL Developer版本3.2.20.10中：

现在您可以将输出保存到文件中。

SQL Developer版本4.1

在SQL Developer 4.1版中新增，使用以下内容就像sqlplus命令一样运行并作为脚本运行。 不需要查询中的提示。

SET SQLFORMAT csv

现在您可以将输出保存到文件中。

Lalit Kumar B answered 2019-06-08T20:44:58Z

11 votes

如果你使用12.2，你可以简单地说

set markup csv on

BobC answered 2019-06-08T20:45:21Z

9 votes

这很粗糙，但是：

set pagesize 0 linesize 500 trimspool on feedback off echo off

select '"' || empno || '","' || ename || '","' || deptno || '"' as text

from emp

spool emp.csv

/

spool off

Tony Andrews answered 2019-06-08T20:45:45Z

8 votes

我知道这是一个旧线程，但是我注意到没有人提到下划线选项，它可以删除列标题下的下划线。

set pagesize 50000--50k is the max as of 12c

set linesize 10000

set trimspool on --remove trailing blankspaces

set underline off --remove the dashes/underlines under the col headers

set colsep ~

select * from DW_TMC_PROJECT_VW;

Doc answered 2019-06-08T20:46:09Z

7 votes

您可以显式格式化查询以生成带有以下内容的分隔字符串：

select '"'||foo||'","'||bar||'"'

from tab

并根据需要设置输出选项。 作为一个选项，SQLPlus上的COLSEP变量将允许您生成分隔文件，而无需显式生成字段连接在一起的字符串。 但是，您必须在可能包含嵌入逗号字符的任何列上的字符串周围加引号。

ConcernedOfTunbridgeWells answered 2019-06-08T20:46:40Z

4 votes

更喜欢在sqlplus提示符中使用“set colsep”而不是逐个编辑col名称。 使用sed编辑输出文件。

set colsep '","' -- separate columns with a comma

sed 's/^/"/;s/$/"/;s/\s *"/"/g;s/"\s */"/g' $outfile > $outfile.csv

CC. answered 2019-06-08T20:47:04Z

3 votes

我曾经写过一个小的SQL * Plus脚本，它使用dbms_sql和dbms_output来创建一个csv(实际上是一个ssv)。 你可以在我的githup存储库中找到它。

René Nyffenegger answered 2019-06-08T20:47:28Z

1 votes

使用vi或vim编写sql，使用colsep和control-A(在vi和vim中使用ctrl-v在ctrl-A之前)。 一定要将linesize和pagesize设置为合理的东西并打开trimspool和trimout。

将其假脱机到文件。然后...

sed -e 's/,/;/g' -e 's/ *{ctrl-a} */,/g' {spooled file} > output.csv

那个sed的东西可以变成一个脚本。 ctrl-A之前和之后的“*”挤出了所有无用的空间。 是不是很好，他们打扰从sqlplus启用html输出但不是本机csv ?????

我这样做是因为它处理数据中的逗号。 我把它们变成了分号。

Charles Stepp answered 2019-06-08T20:48:13Z

1 votes

您应该知道字段的值可能包含逗号和引号字符，因此一些建议的答案不起作用，因为CSV输出文件不正确。要替换字段中的引号字符并将其替换为双引号字符，可以使用oracle提供的REPLACE函数将单引号更改为双引号。

set echo off

set heading off

set feedback off

set linesize 1024 -- or some other value, big enough

set pagesize 50000

set verify off

set trimspool on

spool output.csv

select trim(

'"' || replace(col1, '"', '""') ||

'","' || replace(col2, '"', '""') ||

'","' || replace(coln, '"', '""') || '"' ) -- etc. for all the columns

from yourtable

/

spool off

或者，如果您想要字段的单引号字符：

set echo off

set heading off

set feedback off

set linesize 1024 -- or some other value, big enough

set pagesize 50000

set verify off

set trimspool on

spool output.csv

select trim(

'"' || replace(col1, '''', '''''') ||

'","' || replace(col2, '''', '''''') ||

'","' || replace(coln, '''', '''''') || '"' ) -- etc. for all the columns

from yourtable

/

spool off

Rob Heusdens answered 2019-06-08T20:48:45Z

-2 votes

你可以使用csv提示。 请参阅以下示例：

select /*csv*/ table_name, tablespace_name

from all_tables

where owner = 'SYS'

and tablespace_name is not null;

Adilson Silva answered 2019-06-08T20:49:09Z