| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.tools -> Re: Loading and using variables
It doesn't. I think the issue that was confusing is that you posted a PL/SQL block and then a query inside of it. If all you wanted to do was to see the output in the second query, then you WOULD NOT have to store it in a local variable, because you wouldn't be able to see it then, UNLESS you used dbms_output.put_line.
Something like:
set serveroutput on size 1000000
declare
myvar number;
criteriavar varchar2(31);
begin
.... setup criteriavar ...
select count(*) into myvar from table2 where criteria = criteriavar;
dbms_output.put_line( 'myvar = ' || to_char(myvar ) );
end;
For what you are doing, it appears you'd want to simply grab the results from one query, then use that in a SQL statement to show output to the screen. If that's the case, use the bind variable as I posted in my last bit of source, THEN put your select count(*) statement outside the pl/sql block as the next sql command to process, ie.
variable criteriavar varchar2(31)
begin
select ... into criteriavar ...
end;
/
select count(*) from ... where ... = criteriavar
Save this as a .SQL file, then execute it at the SQL>@myscript
<toadsprocket_at_my-deja.com> wrote in message
news:8pltpl$56c$1_at_nnrp1.deja.com...
> That would work but I guess my confusion is that once I get the
> information from the first query into the variable I just want to use
> it in a query that returns information to the screen. If I change the
> second query to read:
>
> SELECT COUNT(*) from Table2 where criteria = criteria;
>
> Then I get the result printed back to the screen for me, why if I use
> the variable does the output have to go anywhere other than the screen.
> That's the part that's confusing me :)
>
> Thanks
>
> -Paul-
>
>
> In article <FPtv5.5337$oc3.286331_at_news.flash.net>,
> "Eric Givler" <egivler_at_flash.net> wrote:
> > How about?
> >
> > variable result varchar2(32)
> > variable mycount number
> >
> > BEGIN
> > SELECT DISTINCT(Data)
> > into :result
> > FROM TABLE1
> > WHERE Criteria = OtherCriteria;
> >
> > SELECT COUNT(*)
> > into :mycount
> > from TABLE2 WHERE Criteria = :Result
> > END;
> > /
> >
> > print result
> > print mycount
> >
> > <paul_at_davishome.com> wrote in message news:8pk6is$4b4
$1_at_nnrp1.deja.com...
> > > I need to write a PL/SQL routine for the office that loads a field
from
> > > a database, inserts into a variable and then uses it later on in
> > > another query later in the SQL block. Here is an example of what I
mean:
> > >
> > > DECLARE
> > > Result VARCHAR2(32)
> > > BEGIN
> > > SELECT DISTINCT(Data)
> > > FROM TABLE1
> > > INTO Result
> > > WHERE Criteria = OtherCriteria;
> > >
> > > SELECT COUNT(*) from TABLE2 WHERE Criteria = Result
> > > END;
> > >
> > > The reason for doing this is that the Select Count is actually a big
> > > monster query to get the latest results from a number of tables, the
> > > Result is the latest identifier and changes each month. I don't
want to
> > > update the query each month and I certainly don't want to add
another
> > > sub-query into a routine that we spent time optimizing :)
> > >
> > > The above code generates an error on the final line of code,
> > > complaining about wanting an INTO statement. I'm stumped on this one
> > > and can use any help available.
> > >
> > > Cheers
> > >
> > > -Paul-
> > >
> > >
> > > Sent via Deja.com http://www.deja.com/
> > > Before you buy.
> >
> >
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Wed Sep 13 2000 - 07:34:04 CDT
 |
 |