Re: Help with variables in PL/SQL

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 19 Jun 2003 18:38:15 -0700
Message-ID: <2687bb95.0306191738.3ad566c8_at_posting.google.com>


swayzack1_at_yahoo.com (Morgan Clark) wrote in message news:<11203f39.0306191037.3e9758fb_at_posting.google.com>...
> Hi... I'm trying to do what I consider to be a real simple thing, but
> I can't get it to work right! I have a query that I'm going to have
> to run every now and then - I just have it saved as a text file that
> I'm going to copy and paste into SQL/Plus when I need to. Not the
> best way, I know, but we have very limited permissions to the actual
> database (it's a data warehouse hosted offsite)...
>
> In my query, I look at a certain date a bunch of times. I'd love to
> make it a variable. In T-SQL (what I'm used to), I could do something
> like:
> DECLARE _at_theDate datetime
> SET _at_theDate = '5/30/2003'
>
> SELECT whatever
> FROM wherever
> WHERE thisdate > function(_at_theDate) AND thatdate <= function(@theDate)
>
> ... point is, I reference the same date in the SELECT a whole bunch
> of times, and it'd be nice to just set it once. The research I've
> done on Oracle all has examples of declaring a variable, then using
> SQL that either returns one row, or manipulates data in some way -
> nothing that just returns a set of data in a SELECT. I've read about
> cursors, but they seem to be used just to loop through the results and
> manipulate the data... I just want to display the result of a SELECT
> statement that I'm using a variable in. Can somebody help me out???
> I know this is a stupid question, and I apologize, but it's such a
> stupid question that it's tough to find the answer to....... Thanks.

Morgan, since your example appears to be just a query perhaps the SQLPLus label substitution will work. See the SQLPlus manual accept statement
and then you would reference your data variable as &mydate whenever you want it used for either a pure SQLPlus script or in anonymous PL/SQL. If you need PL/SQL look up reference cursors. You can define a reference cursor in SQLPlus and pass the results of a cursor back to SQLPlus using it
from which you can use the SQLPlus print command to display the results.  

declare
mydate date := 'dd-mon-yy';
begin
....
end;

HTH -- Mark D Powell -- Received on Fri Jun 20 2003 - 03:38:15 CEST

Original text of this message