Solved

** How to set Run-Time Variables in PL/SQL

Posted on 2011-03-11
9
266 Views
Last Modified: 2012-05-11
In SQL Server, to reduce repetitive typing, I used DECLARE and SET:

DECLARE  @R1 datetime
SET @R1 = '03/10/2011'

SELECT...
WHERE ORDER_DATE = @R1
  AND INVOICE_DATE = @R1

How can I do this in PL/SQL?

Thanks!
0
Comment
Question by:dunkin1969
9 Comments
 
LVL 73

Accepted Solution

by:
sdstuber earned 250 total points
ID: 35111555
declare
    r1 date := to_date('03/10/2011','mm/dd/yyyy');
begin
SELECT ...
WHERE ORDER_DATE = r1
  AND INVOICE_DATE = r1
end;

put the select in a loop, use it in a cursor, or select INTO something
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 250 total points
ID: 35111562
string or date variables in the tables?

declare
    R1 date := to_date('03/10/2011','MM/DD/YYYY');
begin  
   select ... into ...
   where trunc(order_date) = r1 and trunc(invoice_date)=r1;

end;
/


TRUNC will not allow indexes on those columsn to be used.  If you have indexes the where clause can be tweaked to use >= and <=
0
 

Author Comment

by:dunkin1969
ID: 35111853
sorry guys, my knowledge doesn't go as far as the select statement.  Not sure how to put the select into a loop, cursor, into, etc.

In SQL server I never had to do that.  Sorry.
I know, I'm a buffoon.
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 35111867
slightwv, why did you put trunc? if we need trunc and use indexes, I prefer

declare
    R1 date := to_date('03/10/2011','MM/DD/YYYY');
begin  
   select ... into ...
   where
   order_date >= r1 and order_date < r1+1
   and
   invoice_date >= r1 and invoice_date < r1+1
end;
/

or use between instead of >= & <
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35111886
>>slightwv, why did you put trunc? if we need trunc and use indexes, I prefer

I said it was based on the requirements and mentioned the 'index' issue and mentioned the >= <= option.

If order_date and invoice date don't have a time portion or indexes, why not use it?

>>Not sure how to put the select into a loop, cursor, into, etc.

Please post your requirements:  Inputs, outputs, etc...
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35111899
>>order_date >= r1 and order_date < r1+1

For what it's worth, I would probably actually use BETWEEN but again, it depends on the exact needs at the time.
0
 
LVL 4

Expert Comment

by:subratabiswas
ID: 35121219
You have mentioned PLSQL in your question, but the example and subsequent comments from you indicate that the far simpler approach shown below is what you want. It is almost identical to what you used in SQLSERVER. Please note that this works in SQLPLUS (the ubiquitous command line tool) and SQLDEVELOPER (excellent free GUI tool from Oracle). This may work with some other tools also, but the first line being an extension to standard, may not work in all tools.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options

SQL> def r1 = '01-Jan-2011'
SQL> select count(*) from oe_order_headers_all where last_update_date > to_date('&&r1', 'dd-Mon-yyyy');
old   1: select count(*) from oe_order_headers_all where last_update_date > to_date('&&r1', 'dd-Mon-yyyy')
new   1: select count(*) from oe_order_headers_all where last_update_date > to_date('01-Jan-2011', 'dd-Mon-yyyy')

  COUNT(*)
----------
     51823

SQL>
0
 
LVL 4

Expert Comment

by:subratabiswas
ID: 35121230
The two lines of command that are issued in the above example are

def r1 = '01-Jan-2011'
select count(*) from oe_order_headers_all where last_update_date > to_date('&&r1', 'dd-Mon-yyyy');


Call to to_date may not be necessary, if the environment has the default NLS date format
0
 
LVL 4

Expert Comment

by:subratabiswas
ID: 35121266
Not to disregard the excellent suggestions using PLSQL above; they would provide all the power and sophistication  of PLSQL and its programming capabilities. My examples are only to demonstrate that Oracle can stay simple too.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now