Creating Easy Way to update variables in large code

Question

I am using Oracle SQL Developer and have a rather large query built. The query is going to be run on a monthly or quarterly basis. I was wondering if there was a way that I can do a declare statment up top and then in the code just reference these variables created. That way when someone wants to run the query they can just change the dates at the top of the code rather then have to dig through all of it. I am kind of new to Oracle SQL Developer but I know in other sql codes I built I could simply declare the variable and then set it and then in the code call the variable name. Below is an example of what I know how to do but i am having trouble in Oracle SQL Developer.

Example: I have a data base that contains the columns Business, business type(small,medium,large) number of deposits, deposit amount and deposit date. I want to build a query that outputs a quarterly summary of the number of deposits and the deposit amount and be able to change the quarter and size of the business.

Example Code from my previous SQL expereince this is an example of what I am trying to do since i can not disclose my code with the table names etc in them.

Declare @busstype,@qbegindate,@qenddate
Set @busstype = 'small'
Set @qbegindate = '01-JAN-2013'
Set @qenddate = '01-MAR-2013'

Select business,numberofdeposits,depositamount

From business_transactions

Where ('@qbegindate'<=depositdate<='@qenddate'
And businesstype = '@busstype')
Group By Business

The results would list out the businesses name and then the total deposits and total amount. I know this code is not right but its just an example of what I am looking to do in Oracle SQL Developer. The query I have built is working fine I just find it a pain to dig through the code to change dates and criteria and was wondering how I would do something like this since i have figured out that I am not able to do this in ORACLE Sql Developer.

I don't know about sqldeveloper, but in sqlplus you might achive what you need using "define". You would then reference the defined values by prefixing them with an apersand as in "where &begindate..." — Martin Drautzburg
– Martin Drautzburg, Commented Apr 18, 2014 at 19:55

vav · Accepted Answer · 2014-04-18 20:02:24Z

1

Here is an example with predefined variable:

set feedback off
var abc varchar2

begin
:abc := 'abc';
end;
/

select :abc as a from dual;

Output:

A                              
--------------------------------
abc

answered Apr 18, 2014 at 20:02

vav

4,6942 gold badges22 silver badges40 bronze badges

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

2 Comments

Jon Heller Over a year ago

+1 But it's worth noting this will only work in SQL Developer when used with "Run Script (F5)".

vav Over a year ago

you can put script in a file and execute using @"\yourpPath\yourscript.sql"

Jon Heller · Accepted Answer · 2014-04-19 02:02:22Z

1

Common table expressions allow variables to be defined at the top of the query. For performance and style reasons this is generally not a good way to use common table expressions. The advantage is this query can be run in any IDE and it is completely self-contained.

--Variables - change these before running.
with busstype as   (select 'small' value from dual),
     qbegindate as (select date '2013-01-01' value from dual),
     qenddate   as (select date '2013-03-01' value from dual)
--Query - do not modify code below.
select business,numberofdeposits,depositamount
from business_transactions
where depostiddate between
      (select value from qbegindate)
      and 
      (select value from qenddate)
  and businesstype = (select value from busstype)
group by business, numberofdeposits,depositamount;

answered Apr 19, 2014 at 2:02

Jon Heller

37.1k6 gold badges84 silver badges150 bronze badges

1 Comment

user3549613 Over a year ago

Thanks for the responses. I will give these a try. The project was on hold for a few weeks so I have not been back on it. I will post on this if I get it to work.

Collectives™ on Stack Overflow

Creating Easy Way to update variables in large code

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related