Solved

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

Posted on 2011-03-11
9
274 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 56

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
 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to recover a database from a user managed backup
Suggested Courses

623 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