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.

Answer 1

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;

Answer 2

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;

Answer 3

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.