Re: Help with variables in PL/SQL

Thanks for the help, everyone. I defined it with a & and used &&s in the function call, and it works like a champ! I'm kinda diggin' this whole PL/SQL thing - seems incredibly more powerful than T-SQL. Then again, you all already know that, I'm sure......

Mark.Powell_at_eds.com (Mark D Powell) wrote in message news:<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 - 16:16:27 CEST