Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 280
  • Last Modified:

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

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
dunkin1969
Asked:
dunkin1969
2 Solutions
 
sdstuberCommented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
dunkin1969Author Commented:
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
HainKurtSr. System AnalystCommented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
slightwv (䄆 Netminder) Commented:
>>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
 
subratabiswasCommented:
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
 
subratabiswasCommented:
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
 
subratabiswasCommented:
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now