如何使用SQLPLUS后台打印到CSV格式的文件？ [英] How do I spool to a CSV formatted file using SQLPLUS?

问题描述

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

I want to extract some queries to a CSV output format. Unfortunately, I can't use any fancy SQL client or any language to do it. I must use SQLPLUS.

我如何做？

推荐答案

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 
...

比输入所有字段和用逗号连接起来要简单得多。

This would be a lot less tedious than typing out all of the fields and concatenating them with the commas. You could follow up with a simple sed script to remove whitespace that appears before a comma, if you wanted.

这样的东西可能会工作...（我的sed技能是可以工作的。）你可以使用一个简单的sed脚本来删除逗号之前出现的空格。非常生锈，因此这很可能需要工作）

Something like this might work...(my sed skills are very rusty, so this will likely need work)

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

这篇关于如何使用SQLPLUS后台打印到CSV格式的文件？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！