Solved

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

Posted on 2011-03-11
9
271 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 74

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 77

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 51

Expert Comment

by:Huseyin KAHRAMAN
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
 
LVL 77

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 77

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

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…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

735 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