Output parameter and dynamic sql

From: Robert Scheer <rbscheer_at_my-deja.com>
Date: 20 Jul 2004 18:57:09 -0700
Message-ID: <cfd22ab6.0407201757.996c4ca@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 Tue Jul 20 2004 - 20:57:09 CDT