Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Variables in TOAD, New to Oracle
"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