Using Dynamic Variables in Oracle SQL Developer

Question

Trying to setup a WHILE LOOP in Oracle SQL Developer but I'm having a lot of trouble trying to set/define a variable. I'm used to working in PHP where I would declare this on my PHP scripts.

Here is what I have below. The script is unfinished I am basically going to have it loop through weeks when it is done.

VARIABLE MYDateVar2 varchar2(40);
EXEC :MYDateVar2 := '01-JAN-14';

select customer_name, 
sum(CASE when to_char(to_date(PLANNED_SHIP_DATE), 'WW') = 40 then (REVISED_QTY_DUE - QTY_SHIPPED) * SALE_UNIT_PRICE end) as Wk40
from customer_order_join 
where planned_ship_date >= :MYDateVar2 
group by customer_name;

So I am having trouble basically placing the variable 'MYDateVar2' back into the script. I've tried using @@ and : before but Oralce SQL Developer keeps prompting me for values. I also know I should probably set the varchar to be DATE but that should be fine for now.

Can someone please let me know how I properly insert a variable into the script? Thanks!

Alex Poole · Accepted Answer · 2014-09-04 16:22:23Z

1

What you're doing is fine, as long as you tell SQL Developer to run the whole script, not just the select statement; you need to Run Script (F5), rather than Run Statement (Ctrl+Enter). If it runs the statement stand-alone then it will always prompt for the bind variable value.

Not directly relevant, but... presumably you've used a date format that is valid for your client's NLS settings, but you shouldn't rely on that; it's safer to always explicitly set the format:

where planned_ship_date >= to_date(:MYDateVar2, 'DD-MON-RR')

If you're going to loop, though, then you need to be writing PL/SQL, so you might as well declare the variable inside the block rather than at client level, unless you want to pass the same value into multiple blocks or stored procedure calls.

answered Sep 4, 2014 at 16:22

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mike Over a year ago

This definitely seemed to get me past that hurdle. I do lose the nice table structure view though and can't just right click export to Excel. Do I now need to add an export command in the script? Thanks!

Alex Poole Over a year ago

Right, you don't get the results in the grid-view Query Result window anymore. Your script could be doing multiple selects from different tables, which would make grid view impractical. You could spool the results to a file (or multiple files), but it depends what you're doing next. Not sure how that fits in with looping though.

Collectives™ on Stack Overflow

Using Dynamic Variables in Oracle SQL Developer

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related