Re: Returning recordsets from dynamic sql

hakanh_at_klar.se (=?ISO-8859-1?Q?H=E5kan_Helsing?=) wrote in message news:<c95fc137.0205282250.7585cab7_at_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

I'm getting more and more convinced that people who have been exposed to sqlserver are permanently handicapped for the rest of their life. They can't read manuals anymore, and they can't search google. They also specifically learn you should never search comp.databases.oracle.server on the word 'resultset' and 'ref cursor'

Anyway, the correct syntax is open <cursorvar> for SQLstring using bind_variable; where sqlstring can be a variable or a hardcoded literal string

Please always consult the pl/sql manual and/or the book on pl/sql by Steven Feuerstein. You will learn more from it than by just posting all your questions.

Regards
Sybrand Bakker
Senior Oracle DBA Received on Wed May 29 2002 - 06:07:58 CDT