Re: Returning recordsets from dynamic sql

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: 29 May 2002 04:24:58 -0700
Message-ID: <a6d06107.0205290324.339b3d65@posting.google.com>

Hello

Try this:

set serveroutput on;
set feedback off;

create table tabletest (f1 varchar2(30), f2 varchar2(30));

insert into tabletest values ('R', 'Roaming');
insert into tabletest values ('T', 'Taco Bell');
insert into tabletest values ('F', 'Fine');
insert into tabletest values ('M', 'Monsun');

CREATE OR REPLACE PACKAGE MYTEST IS   type TestCur is ref cursor;

  procedure TestSelect(

       tab_name   in varchar2,
       field_name in varchar2,
       val        in varchar2,
       TestCursor in out TestCur);

  procedure openCursor;

END MYTEST;
/

CREATE OR REPLACE PACKAGE BODY MYTEST as

   procedure TestSelect(

          tab_name   in varchar2,
          field_name in varchar2,
          val        in varchar2,
          TestCursor in out TestCur)

   IS
     strSQL VARCHAR (100);
   BEGIN
     strSQL := 'select * from ' || tab_name || ' where ' || field_name || ' = :1 ';

     open TestCursor for strSQL using val;

   END TestSelect;

   procedure openCursor is

     tc TestCur;
     f1 tabletest.f1%type;
     f2 tabletest.f2%type;
   begin
     TestSelect('tabletest','f1','F',tc);
     loop
       fetch tc into f1,f2;
       exit when tc%notfound;
       dbms_output.put_line(f2);
     end loop;
   end openCursor;
     
END MYTEST;     

/

begin
  MYTEST.openCursor;
end;
/

drop table tabletest;

HTH Rene

> 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 - 06:24:58 CDT