1

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.

  • 2
    One other comment: if you are running a query, hitting F5 will return the query result in the (in my opinion less-helpful) Script Output window. Hitting Ctrl+Enter on the other hand will return the query results in the grid-style Query Result window. That tripped me up so hopefully will help others. – user106720 Sep 26 '16 at 14:23
1

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.

  • 2
    You are halfway there, you just have to use && instead of &. See: link – Twinkles Oct 28 '13 at 16:07
1

You may find the following discussion interesting: How to declare a variable and use it in the same SQL script?

  ACCEPT v_yr PROMPT "Please enter year: " default 2013

  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);
  • Above code only works as intended when entire block of code (both ACCEPT... and SELECT... parts) are highlighted before pressing the 'Run Statement' button in SQL Developer. Results will be output to grid. – Baodad Oct 29 '13 at 17:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.