Can a stored procedure return a record set with a dynamic SQL cursor
From: sarojini <sarojini_at_multimodalinc.com>
Date: 11 Jul 2001 08:47:29 -0700
Message-ID: <70040f22.0107110747.238cb939_at_posting.google.com>
TYPE CUR_TRF IS REF CURSOR RETURN TRF_REC; END TRF; I then define my stored procedure as
PROCEDURE SP_TRAFFIC
(p_CurTrfVar IN OUT TRF.CUR_TRF,
WHERECLAUSE IN VARCHAR2 := NULL)
AS
BEGIN
OPEN p_CurTrfVar FOR
'SELECT TRF_CAT_I, TRF_CAT_C
FROM TRAFFIC
WHERE' || NVL(WHERECLAUSE, '1=1'); END SP_TRAFFIC; The stored procedure works fine with a static SQL statement, but the moment I turn it into a dynamic SQL statement, it complains that the cursor variable cannot be defined with an Open or dynamic SQL statement.
I need the cursor as a IN OUT parameter to feed another program - Crystal reports.
Is there a solution to this or a workaround ? Thanks for the help.
Date: 11 Jul 2001 08:47:29 -0700
Message-ID: <70040f22.0107110747.238cb939_at_posting.google.com>
[Quoted] I am using Oracle 8i.
I define my reference cursor in the package
PACKAGE TRF AS
TYPE TRF_REC IS RECORD
( TRF_CAT_I NUMBER,
TRF_CAT_CODE VARCHAR2(50),
);
TYPE CUR_TRF IS REF CURSOR RETURN TRF_REC; END TRF; I then define my stored procedure as
PROCEDURE SP_TRAFFIC
(p_CurTrfVar IN OUT TRF.CUR_TRF,
WHERECLAUSE IN VARCHAR2 := NULL)
AS
BEGIN
OPEN p_CurTrfVar FOR
'SELECT TRF_CAT_I, TRF_CAT_C
FROM TRAFFIC
WHERE' || NVL(WHERECLAUSE, '1=1'); END SP_TRAFFIC; The stored procedure works fine with a static SQL statement, but the moment I turn it into a dynamic SQL statement, it complains that the cursor variable cannot be defined with an Open or dynamic SQL statement.
I need the cursor as a IN OUT parameter to feed another program - Crystal reports.
Is there a solution to this or a workaround ? Thanks for the help.
Sarojini Received on Wed Jul 11 2001 - 17:47:29 CEST