如何在Oracle SQL Developer中使用假脱机将Select语句的结果正确导出到Csv文件中 [英] How to properly Export results of select statement into Csv file using spool in oracle sql developer

问题描述

代码:

set heading off
  set arraysize 1
  set newpage 0
  set pages 0
  set feedback off
  set echo off
  set verify off

spool 'c:\farmerList.csv'
/

select FIRSTNAME','LASTNAME','TRN','CELL','PARISH

spool off

文件将保存到目录中，但是它将以"csv格式"保存"select FIRSTNAME"，"LASTNAME"，"TRN"，"CELL"，"PARISH"，而不是查询结果.我在做什么错了?

The file is being saved to the directory, however it is saving the "select FIRSTNAME','LASTNAME','TRN','CELL','PARISH" and not the results of the query in csv format. What am i doing wrong?

推荐答案

您的选择不完整，因为您没有from子句，但不确定是否在复制粘贴中丢失了该子句.因为它没有任何可运行的内容，因为从未执行过部分语句(在下一行没有终止;或/).如果您确实有from farmers;子句，则可能会显示该命令以及一个ORA-00923错误.

Your select is incomplete as you don't have a from clause, but not sure if you've lost that in the copy-and-paste. As it is there is nothing to run, since the partial statement is never executed (no terminating ; or / on the next line). If you did have a from farmers; clause then it would show the command plus an ORA-00923 error, probably.

您不能只在字段之间加上引号逗号，而是需要使用||串联符号将字段与该字符连接起来:

You can't just put a quoted comma between the fields, you need to concatenate the fields with that character using the || concatenation symbol:

spool 'c:\farmerList.csv'

select FIRSTNAME
    ||','|| LASTNAME
    ||','|| TRN
    ||','|| CELL
    ||','|| PARISH
from farmers;

提供一个包含

Joe,Grundy,X,Y,Ambridge

这些字段不必放在单独的行上，我觉得这样更容易阅读和跟踪逗号.

The fields don't have to be on separate lines, I jut find that easier to read and keep track of the commas.

在假脱机命令后不需要/-如果存在，它将重新执行spool之前的最后一条语句-除非假脱机文件名周围没有引号，否则除非它包含空格，但不会受伤.

You don't need the / after the spool command - that will re-excute the last statement before the spool, if there is one - and you don't need the quotes around the spool file name unless it contains spaces, but they don't hurt.

还有一个 set colsep 您可以使用该命令使列分隔符变成逗号，但是您需要担心填充问题，因此，我发现(几乎)在将列连接在一起时会更容易.

There's also a set colsep command which you can use to make the column separator into a comma, but you have to worry about padding, so I find it easier to concatenate the columns together as you're (almost) doing.

除了用于SQL * Plus，因为我没有在标题中注意到SQL Developer参考.假脱机在Developer中有点奇怪，因为它似乎可以捕获并回显您可能不需要的内容，并且并非所有set命令都可以工作(取决于版本).

Except that's for SQL*Plus, as I didn't notice the SQL Developer reference in the title. Spool is a bit odd in Developer as it seems to trap and echo things you probably don't want, and not all of the set commands work (which ones depends on the version).

我认为，更安全，更可取的方式是运行不带连接逗号的普通查询:

The safer and preferred way, I think, is to run a normal query without concatenated commas:

select FIRSTNAME, LASTNAME, TRN, CELL, PARISH
from farmers;

并使用运行"而不是运行脚本"，以便结果显示在查询结果窗口的网格视图中.右键单击网格，然后选择导出".然后，您可以另存为CSV甚至XLS，并且可以根据需要选择不包含标题行.

and with 'run' rather than 'run script', so that the results appear in the grid view in the query result window. Right-click on the grid and choose 'export'. You can then save as a CSV, or even as an XLS, and can choose to not have a header row if you prefer.

这篇关于如何在Oracle SQL Developer中使用假脱机将Select语句的结果正确导出到Csv文件中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！