Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Variables in TOAD, New to Oracle

Re: Variables in TOAD, New to Oracle

From: Chris <cs123._no_spam__at_telstra.com>
Date: Sat, 26 Jun 2004 11:45:51 GMT
Message-ID: <PldDc.65262$sj4.61486@news-server.bigpond.net.au>

"Jim" <jdz99_at_hotmail.com> wrote in message news:3eed9d77.0406250729.1ffb880b_at_posting.google.com... > "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

Just do this

 select * from apps.gl_periods_v where period_set_name =  'TUS_445_MTHLY' and period_name = :PERIOD;

You'll be prompted to enter the value of :PERIOD ie MAY-04

The result will goto the normal output pane

or not using binds

 select * from apps.gl_periods_v where period_set_name =  'TUS_445_MTHLY' and period_name = 'MAY-04';

or using plsql

for a single row result

 DECLARE
 l_Month VARCHAR2(6) := 'MAY-04';
v_gl_periods apps.gl_periods_v%rowtype;
 BEGIN  select * from apps.gl_periods_v
into v_gl_periods
where period_set_name = 'TUS_445_MTHLY' and period_name = l_Month;

 END; or for multi row result

 DECLARE
 l_Month VARCHAR2(6) := 'MAY-04';
BEGIN
for v_result in (select * from apps.gl_periods_v into v_gl_periods
where period_set_name = 'TUS_445_MTHLY' and period_name = l_Month) loop
-- do something here with v_result
end loop;

 END; Received on Sat Jun 26 2004 - 06:45:51 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US