Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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. –  Raj Oct 25 '13 at 12:42
    
@Raj, Thanks for your comment. You should make it the answer to this question. Short answer: you can't in SQL Developer; Long answer: here's your best alternative. –  Baodad Oct 25 '13 at 14:52
    
done as an answer. –  Raj Oct 28 '13 at 13:36
add comment

2 Answers

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.

share|improve this answer
    
OK. I deleted the var... exec... lines and changed the three instances of :v_yr in the original query to &v_yr. When I run the query, I am prompted 3 times for the same variable. If you could edit the answer to include the ref_cursor solution, that outputs to the grid, I would consider this for the solution. (I'm sorry I should have researched your comment more before I asked you to post it as an answer.) –  Baodad Oct 28 '13 at 15:11
1  
You are halfway there, you just have to use && instead of &. See: link –  Twinkles Oct 28 '13 at 16:07
add comment

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);
share|improve this answer
    
I tried following the direction in the link you mentioned, and that's how I came up with the code in my original question. Unless someone can correct me, using var and exec in SQL Developer will not output to the results grid. –  Baodad Oct 29 '13 at 17:07
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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