Re: Output parameter and dynamic sql

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

Don't see an INTO clause after the execute immediate statement.

Other than that: do you think anyone here can guess what your unspecified errors are?

Sybrand Bakker
Senior Oracle DBA Received on Wed Jul 21 2004 - 10:54:46 CEST