SQL Developer script output to datagrid

Question

In Oracle SQL Developer, I can get simple query results returned in the 'Query Results' grid, but if I need to use variable in script, I need to use the 'Run Script' option and my results show up in 'Script Output' window, and I can't export it to csv format. Here is my sample code:

    var CatCode char(5) ;
    exec :CatCode := 'ZK';
    SELECT * FROM Products WHERE CategoryCode = :CatCode;

Any help would be appreciated. Thanks.

thatjeffsmith · Answer 1 · 2018-03-29T16:02:07.613

2

Just add a /*csv*/ to your query, the tool will bring back the output in CSV automatically when executed as a script (F5).

Or use a substitution variable instead. &Var vs :Var, run with F9, SQLDev will prompt you for the value.

VAR stcode CHAR(2);

EXEC :stcode := 'NC';

SELECT /*csv*/
    *
  FROM
    untappd
 WHERE
    venue_state   =:stcode;

Or to go straight to the grid so you can use can use the Grid Export feature.

SELECT
    *
  FROM
    untappd
 WHERE
    venue_state   =:stcode2;

Execute with Ctrl+Enter or F9

Supply the input parameter in the pop up dialog, click OK.

Shazaam.

edited Mar 29 '18 at 16:02

answered Mar 28 '18 at 22:52

thatjeffsmith

20,522
6
37
120

Yeah, it works but I need it in data grid. My query returns 50K+ records and I need to export it to csv. I dont have enough rights to create procedure so I have to do it in worksheet only. Is there any other solution to export query results into csv? – mak101 Mar 29 '18 at 15:34
There are tons of variable in my actual query and most of the time it will have default value. So I don't want any popup window for values rather I would change in script and run. – mak101 Mar 29 '18 at 18:25
@mak101 so don't do that then, run it as a script, use the CSV comment, and spool it as a file. it'll run quicker and in fewer steps. – thatjeffsmith Mar 29 '18 at 19:50
@mak101 you can spool 50k records to CSV using my solution...or with slight variations – thatjeffsmith Mar 29 '18 at 19:51

score 0 · Accepted Answer · answered Mar 30 '18 at 12:43

Here you go you can run this one to be ensure. it's running.

    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 C:\Users\**direcotory**\sql\Test1.csv; --this is file path to save data
    var CatCode char(5) ;
    exec :CatCode := 'ZK';
    SELECT * FROM Products WHERE CategoryCode = :CatCode;
    spool off;

score 0 · Answer 3 · answered Mar 30 '18 at 16:40

0

Thanks @thatjeffsmith and Paras, spool option gave me new direction and it worked. I slightly changed your code and it works great.

var CatCode char(5) ;
exec :CatCode := 'ZK';
set feedback off;
SET SQLFORMAT csv;
spool "c:\temp\spoolTest.csv"
SELECT * FROM Products WHERE CategoryCode = :CatCode;
spool off;
SET SQLFORMAT;
set feedback on;

answered Mar 30 '18 at 16:40

mak101

147
1
11

I have one more question, How do I spool to dynamic file? I want to add CatCode in my file name, I tried: var csvFile varchar2(100) exec :csvFile := 'c:\temp\MyCSV_' || trim(:CatCode) || '.csv'; spool csvFile But it does not work. Any tips? – mak101 Mar 30 '18 at 17:50

SQL Developer script output to datagrid

3 Answers3