URGENT! VB-Oracle: Retrieving resultset from stored procedure using Oracle in OraHome92 driver

Hi,

We have a desktop VB application which connects to an Oracle 9i database. Uptill now it has been using the Microsoft ODBC for Oracle driver (MSORCL32.DLL) to connect to the Oracle DSNs. Now we have to change over to the "Oracle in OraHome92" driver (SQORA32.DLL).

When the above change is made, the application connects fine and the select queries (simple fetches without aggregate functions) execute alright.
However I am facing issues, when I try to execute complex SQL statements such as statements invoking stored procedures and returning resultsets (which are PL/SQL tables in most cases).

The sql code looks something like this...
[code]

CREATE OR REPLACE PACKAGE wEmployees AS
TYPE tEname IS TABLE OF EMP.ENAME%TYPE INDEX BY BINARY_INTEGER; PROCEDURE GetEmpName(iEno IN NUMBER,
oEname OUT tEname);
END wEmployees;
/
CREATE OR REPLACE PACKAGE BODY wEmployees AS PROCEDURE GetEmpName(iEno IN NUMBER,
oEname OUT tEname)
IS
BEGIN
if iEno = 0 then
oEname(1) := 'ZERO';
else
oEname(1) := 'NONZERO';
end if;
END GetEmpName;
END wEmployees;
[/code]

The call goes like this (typical ODBC syntax). The call is routed through ADO command object (ADO 2.7).

[code]

{ Call wEmployees.GetEmpName(?, {RESULTSET 100, oEname}) }
[/code]

This gives an oracle error "oEname needs to be defined". This error was not encountered when DSN was configured using Microsoft ODBC Driver for Oracle previously.

If anybody has worked on something similar or can give pointers on the same, and similar such issues that we might encounter because of the driver change, please reply (u may mail at vikramjs_at_gmail.com). Thanks & Regards,
V!kram Received on Sat Jan 29 2005 - 01:37:09 CST