SQL Developer: how to script output to query results grid

oracle-sql-developer

I am used to MS SQL Server Studio where I can DECLARE and SET variables and use them in my query, and then be presented with the results in a datagrid that I can copy and paste into Excel.

In Oracle SQL Developer, I can get simple query results returned in the 'Query Results' grid, but if I need to script something more complex (with variables), I need to use the 'Run Script' option and my results are ouput to the 'Script Output' window, where it's much harder to select the results for copying into Excel.

Here is example code, where the variable :v_yr is used three times:

var v_yr number;
exec :v_yr := 2014;

select job.employee_no,
mas.last_name,
mas.first_name,
pay.contract_salary,
pay.contract_days_actual,
CASE job.contract_year WHEN :v_yr THEN cal.contract_days_actual END,
pay.full_year_salary,
pay.full_year_contract_days,
CASE job.contract_year WHEN :v_yr THEN sal.annual_rate END
from hrs.employee_jobs job
LEFT OUTER Join hrs.employee_master mas on job.employee_no = mas.employee_no
LEFT OUTER JOIN hrs.employee_pays pay on job.employee_no = pay.employee_no AND
    job.job_seq_no = pay.job_seq_no
LEFT OUTER JOIN hrs.schedule_amounts sal on pay.schedule_id = sal.schedule_id AND
    pay.schedule_no = sal.schedule_no AND
    pay.schedule_level = sal.schedule_level AND
    pay.schedule_step = sal.schedule_step
LEFT OUTER JOIN hrs.calendar_master cal on pay.calendar = cal.calendar
where job.contract_year in (2013,:v_yr);

In my Googling so far, it seems that in order to display these results in a grid, I will need to set up this query as some kind of stored procedure, assign the results to a cursor. I don't know enough about Oracle programming to understand this answer yet.

Best Answer

if you remove first two lines (var ... and exec), you can run this in sql_developer and get your results in a grid.

Now, if you must have variable declaration, you could replace the : to a & in "job.contract_year in (2013,:v_yr)" so it will prompt you to enter a value at run time.

If you must convert this to a procedure, then you need to go the ref_cursor route.

Related Question