Re: Help with variables in PL/SQL

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

Another Sql server to Oracle comparison.

It's very simple in Oracle. Just define a script variable once in the beginning of the script as,
DEFINE theDate = '&theDate'
Then replace _at_ in the function call with single &. You will be prompted once for the date.

OR

Replace _at_ with two && in the function call. Again you will be only prompted once for the value.

BTW.. You would need to enclose function parameter inside single quotes '&theDate'.

Regards
/Rauf Sarwar Received on Fri Jun 20 2003 - 00:02:37 CEST