Keep cursor open throughout multiple function calls ?

From: Igor Izvekov <igoriz_at_cmtk.net>
Date: 14 Feb 2002 16:34:38 -0800
Message-ID: <9f17469e.0202141634.72deb0ee@posting.google.com>

Hi,

I'm trying to download bulk of data from Oracle table using Pro*C. To expedite the process, I'm using host arrays. On server side, I have a function which populates my arrays and returns number of rows fetched. Since I cannot be sure I have host array big enough, I want to call the function multiple times until I get all the data. I hoped that if I don't close a cursor, I'd be able to fetch next chunk of data without refetching the old one. But it looks like every time I call the function, cursor is already closed. Anybody knows why ?

Here is how I created a function :

CREATE OR REPLACE PACKAGE FETCH_CURSORS AS    TYPE STR_TABLE IS TABLE OF VARCHAR(20) INDEX BY BINARY_INTEGER;    FUNCTION FETCH_P(OID OUT STR_TABLE,N IN NUMBER) RETURN NUMBER;    CURSOR C IS SELECT * FROM TABLE_NAME; END FETCH_CURSORS;
/

CREATE OR REPLACE PACKAGE BODY FETCH_CURSORS AS    FUNCTION FETCH_P(OID OUT STR_TABLE,N IN NUMBER)

         RETURN NUMBER IS
            LINE TABLE_NAME%ROWTYPE;
            CNT NUMBER;
         BEGIN
            IF NOT C%ISOPEN THEN
               OPEN C;
            END IF;
            CNT:=0;
            FOR I IN 1..N
            LOOP
               FETCH C INTO LINE;
               IF C%NOTFOUND THEN
                  CLOSE C;
                  EXIT;
               END IF;
               OID(I):=LINE.STD_PART_OID;
               CNT:=CNT+1;
            END LOOP;
            RETURN CNT;
         END;

END FETCH_CURSORS;
/

show errors

And here's how I'm doing it on C side :

 EXEC SQL BEGIN DECLARE SECTION
 long rows;
 VARCHAR host_array[10][20];
 EXEC SQL END DECLARE SECTION;
 EXEC SQL
 while (1)
   {

      EXEC SQL EXECUTE
         BEGIN
            :rows:=FETCH_CURSORS.FETCH_P(:host_array,10);
         END;
      END-EXEC;
      printf("%d rows returned\n",rows);
      for (i=0;i<rows;i++)
      {
         printf("%d %s\n",host_array[i].arr);
      }
      if (rows<10) break;

   }

When I run it, I get identical sets of 10 rows, so it seems that cursor C inside fetch_p function is always closed. Is there any way to keep it open ?

Any help is appreciated.

P.S. I know I can download table data with sqlplus, but I need Pro*C. Received on Thu Feb 14 2002 - 18:34:38 CST