尝试从SQL Developer导出时SPOOL返回空文件 [英] SPOOL returns empty files when trying to export from SQL Developer

问题描述

我有一系列要输出到.csv文件的查询.我唯一需要查询数据库的工具是SQL Developer.

I have a series of queries that I want to output to .csv files. The only tool I have to query the database is SQL Developer.

我可以运行每个查询，然后使用SQL Developer中的导出"对话框将它们写入文件，但这很麻烦，尤其是当每天需要对多个文件执行此操作时.

I could run each query and then use the Export dialogue in SQL Developer to write them to files, but that's cumbersome, particularly when this needs to be done for multiple files every day.

这对某些人有用 使用SQL Developer直接将查询导出到CSV

但这对我不起作用.

例如，如果我尝试

spool "C:\Users\james.foreman\Downloads\Temp\myfile.csv"

select distinct placement_type
FROM jf_placements;

spool off;

然后在SQL Developer的脚本输出窗格中，我看到了

then in the script output pane of SQL Developer, I see

无法创建SPOOL文件C:\ Users \ james.foreman \ Downloads \ Temp \ myfile.csv

Cannot create SPOOL file C:\Users\james.foreman\Downloads\Temp\myfile.csv

，尽管创建了myfile.csv，但没有结果. (查询返回两行.)

and although myfile.csv is created, there's no results. (There are two rows returned by the query.)

我的第一个想法是写入C:\ Users \ james.foreman \ Downloads \ Temp时出现权限问题 但这似乎不是这种情况，因为如果我删除myfile.csv然后运行SQL，则会重新创建myfile.csv文件，但其中没有任何内容.

My first thought was that there was a permissions issue writing to C:\Users\james.foreman\Downloads\Temp but that doesn't appear to be the case, because if I delete the myfile.csv and then run the SQL, the myfile.csv file is recreated, but it never has anything in it.

因此，我认为这是配置问题，无论是我运行SQL Developer的Windows机器，还是设置了SQL Developer的问题.我应该在哪里寻找进一步的调查?

So I assume this is a configuration issue, either with the Windows machine I'm running SQL Developer on, or with my SQL Developer set up. Where should I look to investigate further?

@Devolus对>将表导出到多个csv的过程的答案文件包含指令在SQL窗口中，右键单击->将窗口更改为->命令窗口"，但是如果我右键单击SQL窗口，则看不到更改窗口"选项.

@Devolus 's answer to Procedure to export table to multiple csv files includes the instruction "In the SQL Window right click -> Change Window to -> Command Window" but if I right click on the SQL Window, I don't see a Change Window option.

(正在运行Windows 7，SQL Developer版本4.0.2.15，内部版本15.21，数据库是Oracle 11.2)

(Running Windows 7, SQL Developer Version 4.0.2.15, Build 15.21, database is Oracle 11.2)

推荐答案

创建文件但没有数据的事实，也许最后一条语句SPOOL OFF尚未执行.在脚本中添加新行，然后重试.

The fact that the file is created, but has no data, perhaps, the last statement, SPOOL OFF is not yet executed. Add a new line in the script and try again.

例如，您的脚本如下所示:

For example, your script would look like :

    spool "C:\Users\james.foreman\Downloads\Temp\myfile.csv"

    select distinct placement_type
    FROM jf_placements
    /

    spool off
    /

-- need a new line to make sure spool off executes

这篇关于尝试从SQL Developer导出时SPOOL返回空文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！