Dynymic SQL (8i) And Ref Cursor

From: Henrik Bachmann <Henrik.Bachmann_at_bim-consulting.de>
Date: Mon, 07 Feb 2000 14:56:25 +0100
Message-ID: <5mjt9sg115fipdo2q9938homrrnvg0eb4q@4ax.com>

Hi there anybody!

I think there is a way of passing a ref cursor as parameter in a dynamic sql statement;
but after compiling the package body the following error occurs:

PLS-00457 in USING clause, expressions have to be of SQL types

Cause: An expression of wrong type is in USING clause. In USING clause, an expression cannot be

         of non-SQL types such as BOOLEAN, INDEX TABLE, and record.

Action: Change the expression type to a SQL type.

Anybody out there who can give me hint? Server: Oracle8i Enterprise Edition Release 8.1.5.0.0 PL/SQL: PL/SQL Release 8.1.5.0.0

Thanks.

Henrik

---

CREATE OR REPLACE PACKAGE rs_test IS
TYPE TFreezeResult Is Ref Cursor;
Procedure rsc_test;
END; CREATE OR REPLACE PACKAGE BODY rs_test IS

Procedure rsc_test IS
BEGIN

	DECLARE
		Stmt     Varchar2(4000);
		x_CRSR   TFreezeResult;

   begin
      Stmt:='BEGIN SELemp(:x_CRSR);end;';
      execute immediate Stmt using in out x_CRSR;

   end;
END; Procedure SELemp(Crsr IN OUT TFreezeResult) Is Begin
  Declare
   x_stmt Varchar2(4000);
  Begin

          x_stmt:='SELECT * FROM emp';
    If Not(Crsr%IsOpen) Then

              Open Crsr For x_stmt;
    End If;
  END;
END; END rs_test;

---

Best regards

Henrik Bachmann
mailto:Henrik.Bachmann_at_bim-consulting.de

B.I.M.-Consulting Magdeburg        http://www.bim-consulting.de/

D-39124 Magdeburg                  

Received on Mon Feb 07 2000 - 07:56:25 CST