Re: Output parameter and dynamic sql

Hi Romeo,

thanks, it worked!

Robert Scheer

rolympia_at_hotmail.com (Romeo Olympia) wrote in message news:<42fc55dc.0407202248.2b953649_at_posting.google.com>...
> A quick look of your code shows two things that need revising:
> (1) You need to use the INTO clause of EXECUTE IMMEDIATE when you want
> to get the results of a single-row query. In your case, the "count(*)"
> value.
> (2) Use bind arguments whenever possible (USING clause). That's the
> "where username" condition for you.
>
> So try something like this:
>
> ...
> sCall := 'SELECT COUNT(*) FROM vw_Scores WHERE UserName = :username';
>
> EXECUTE IMMEDIATE sCall INTO nTotal USING user;
> ...
>
> Do read the PL/SQL guide for more detailed info. HTH.
>
> rbscheer_at_my-deja.com (Robert Scheer) wrote in message news:<cfd22ab6.0407201757.996c4ca_at_posting.google.com>...
> > Hi.
> >
> > I have a stored procedure on a Oracle 8.1.6 database that generates a
> > dynamic sql statement. This stored procedure has an output parameter
> > that needs to return a count from a view. I can generate and run the
> > sql successfuly, but when I try to return the count I get errors. I am
> > showing the relevant part of the procedure, since the rest of
> > concatenations are used to compose the sql.
> >
> > CREATE OR REPLACE PROCEDURE getScoreEntries (user VARCHAR2, level
> > NUMBER, ret OUT NUMBER)
> > AS
> >
> > sCall VARCHAR2(500);
> > nTotal NUMBER;
> >
> > BEGIN
> > sCall := 'SELECT COUNT(*) INTO nTotal FROM vw_Scores WHERE
> > UserName=''' || user || '''';
> > sCall := sCall || --dynamic sql continues here...
> >
> > EXECUTE IMMEDIATE sCall;
> > ret := nTotal;
> >
> > END getTotalEntries;
> >
> > Can anyone help me with this issue?
> >
> > Thanks,
> > Robert Scheer
Received on Thu Jul 22 2004 - 16:03:00 CEST