ORA-01044 after migration Oracle-DB 8i to 9i / return large OLE DB RecordSets to VB6

After a migration from Oracle 8i to 9i (Windows) we found that some of our stored procedures that return some thousand records to a VB6 component via OLE DB RecordSet objects fail with ORA-01044 ("Größe 6000000 des Puffers für Variable überschreitet Höchstwert von 4194304") [german] (english: size string of buffer bound to variable exceeds maximum string)

Our setup:
+ Oracle 9.2.0.3.0
+ VB6 Application using OLEDB for Oracle ...
+ MDAC 2.8 msdaora.dll - 2.80.1022.0 (srv03_rtm.030324-2048)

I am calling a stored procedure from VB like this one:

{? = call trev.p_planung.GET_ALL_KONTEN(?,?,{resultset 3611, l_konto_id, l_name,l_ro_id, l_beschreibung, l_typ, l_plg_id})}

If setting the parameter "resultset" beyond a certain limit, I will eventually get this ORA-01044 error. This even happens, if the returned number of records is smaller than what supplied in the resultset parameter (I manually set the "resultset" param in the stored procedure string). E.g.:

resultset = 1000 -> ORA-06513: PL/SQL: Index der PL/SQL-Tabelle ungültig für Language-Array vom Host
resultset = 2000 -> OK (actual return: 1043 Recordsets) resultset = 3000 -> ORA-01044: Größe 6000000 des Puffers für Variable überschreitet Höchstwert von 4194304
resultset = 3500 -> ORA-01044: Größe 7000000 des Puffers für Variable überschreitet Höchstwert von 4194304
... therefore one record in this example is calculated as 7000000/3500=2000 bytes.

In Oracle 8i we never had this problem. As this is a huge application using a lot stored procedures, changing all "select" stored procedures to "get data by chunks" (as suggested in some forum threads in OTN) ist _not_ an option.

Is there any other known solution to this problem in Oracle 9i? Is it possible to _increase_ the maximum buffer size (Oracle documentation: ORA-01044 ... Action: Reduce the buffer size.)? What buffer size is meant here - which part
in the communication chain supplies this buffer?

Interesting: We can call the stored procedure above with the same parameters as given in VB from e.g. Quest SQL Navigator or sql plus successfully and retrieve all data!

Thanks for any help!
Sven Bombach
To reply via InetMail: sven.bombach[replace with @]t-systems.com Received on Mon Feb 21 2005 - 07:07:57 CST