Re: Variables in TOAD, New to Oracle

From: Michael Austin <maustin_at_firstdbasource.com>
Date: Fri, 25 Jun 2004 18:46:30 GMT
Message-ID: <aq_Cc.8765$bW4.877@newssvr24.news.prodigy.com>

Jim wrote:

> "Dave" <david.sharples3_at_ntlXworld.com> wrote in message news:<4aJCc.3984$pE6.2706_at_newsfe4-gui>...
> 

>>"Jim" <jdz99_at_hotmail.com> wrote in message
>>news:3eed9d77.0406241133.70fbb814_at_posting.google.com...
>>
>>>To all,
>>> I am trying to create a variable that gets referenced many times in
>>>a query(this is not the complete query) and I want to be able to just
>>>change the month and year once.
>>>
>>>DECLARE
>>>l_Month VARCHAR2(6) := 'MAY-04';
>>>
>>>BEGIN
>>>
>>>select * from apps.gl_periods_v where period_set_name =
>>>'TUS_445_MTHLY' and period_name = 'MAY-04';
>>>
>>>END;
>>>
>>>ORA-06550: line 6, column 1:
>>>PLS-00428: an INTO clause is expected in this SELECT statement
>>>ORA-06550: line 6, column 1:
>>>PL/SQL: SQL Statement ignored
>>>
>>>
>>>Tanks,
>>>Jim
>>
>>well where do you expect the output to go to, you have to select it into a
>>variable and then return it to your application

> 
> 
> My mistake, It should have been this,
> 
> DECLARE
> l_Month VARCHAR2(6) := 'MAY-04';
> 
> BEGIN
> 
>  select * from apps.gl_periods_v where period_set_name =
>  'TUS_445_MTHLY' and  period_name = l_Month;
> 
> END;
> 
> 
> I expect the output to go to the grid within TOAD.
> 
> Thanks,
> Jim

You can expect it to go anywhere you want, but PL/SQL expects to return the result into 1) a variable - for singleton select statements and 2) cursors if you are expecting more than one row to be returned.

And if you don't know the definitions of either, you are in over your head.

Michael Austin. Received on Fri Jun 25 2004 - 13:46:30 CDT