Returning recordsets from dynamic sql

From: Håkan Helsing <hakanh_at_klar.se>
Date: 28 May 2002 23:50:35 -0700
Message-ID: <c95fc137.0205282250.7585cab7@posting.google.com>

I want to return a recordset from my stored procedure in Oracle. I call the stored procedure from VB using ADO. The following stored procedure works fine.

CREATE OR REPLACE PACKAGE MYTEST IS

	   type TestCur is ref cursor;
	   procedure TestSelect(TestCursor in out TestCur);

END MYTEST;
/

CREATE OR REPLACE PACKAGE BODY MYTEST as

	procedure TestSelect(TestCursor in out TestCur)
	IS
	BEGIN
	  open TestCursor for select * from sa3206dt;
	END TestSelect;

END MYTEST;
/

My problem is that I want to build the select statement dynamically. I tried the procedure below, but get error when trying to call it via ADO. CREATE OR REPLACE PACKAGE MYTEST IS

	   type TestCur is ref cursor;
	   procedure TestSelect(TestCursor in out TestCur);

END MYTEST;
/

CREATE OR REPLACE PACKAGE BODY MYTEST as

	procedure TestSelect(TestCursor in out TestCur)
	IS
          strSQL VARCHAR (100);
	BEGIN
	  strSQL := 'open TestCursor for select * from sa3206dt'
          execute immediate strSQL;
	END TestSelect;

END MYTEST;
/

Naturally I don't want to hardcode the selectstring, I will build it from inparameters, this is just for test.

/Hakan
Received on Wed May 29 2002 - 01:50:35 CDT