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