Output parameter and dynamic sql
Date: 20 Jul 2004 18:57:09 -0700
Message-ID: <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)
sCall VARCHAR2(500);
BEGIN
EXECUTE IMMEDIATE sCall;
AS
nTotal NUMBER;
sCall := 'SELECT COUNT(*) INTO nTotal FROM vw_Scores WHERE
UserName=''' || user || '''';
sCall := sCall || --dynamic sql continues here...
ret := nTotal;
END getTotalEntries;
Can anyone help me with this issue?
Thanks,
Robert Scheer
Received on Wed Jul 21 2004 - 03:57:09 CEST