?
Solved

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

Posted on 2011-03-11
9
Medium Priority
?
278 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 1000 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 1000 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 59

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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

719 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