Re: Output parameter and dynamic sql

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 Wed Jul 21 2004 - 08:48:55 CEST