I have an old application that works when connecting to both a 10G and 11G Oracle database using the Microsoft ODBC for Oracle driver. However, I'd like to use the Oracle ODBC driver. Currently it works for some of the calls, but not others. The ones where it fails seem to be those where a user-defined type is included in the procedure definition. Below is a sample of the type and an excerpt of the procedure declaration from the package:
TYPE t_date_filled IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
o_date_filled OUT t_date_filled,
The preceding works fine if the Microsoft driver is used, but gives the following error when I switch to the Oracle driver:
[Oracle][ODBC][Ora]ORA-06550: line 1, column 119; PLS-00201: identifier 'O_DATE_FILLED' must be declared.
Obviously the Oracle driver doesn't recognize the user defined type, but I'm not sure how to declare it to fix the problem.
Any suggestions are appreciated.
Oracle's ODBC driver doesn't support Associative Arrays. Microsoft's driver did that as a way to return resultsets from stored procedures, and the syntax is proprietary to their driver. With Oracle's ODBC driver, you'll want to return a REF CURSOR instead.
Hope it helps,