Stored Procedures returning cursors fail

[roybrown]

Coldfusion MX 6.1 Standard with latest updater on Windows 2000 Server.

I was previously accessing an Oracle 9i DB via the Oracle ODBC Driver (v.
9.2.0.1.0) using the the ODBC Socket. My application utilizes a couple of
stored procedures that return cursors to cold fusion. Under this old
configuration, the procedures worked.

I recently, patched Oracle with the latest security patches. As such, I had
to upgrade the ODBC driver to match. I am now using v. 9.2.0.6.5. After
performing this update, my procedures that return cursors fail with the
following error message:

[Macromedia][SequeLink JDBC Driver]Network problem, session aborted due to
internal error in remote procedure call, connection closed.

I have run these procedures directly inside of sqlplus and they seem to be
working just fine. Has anyone else run into this? Any ideas?

[paross1]

I don't know if ithis is related to your problem, but I searched Oracle's
Metalink and found at least one bug report regarding stored proceedure calls
with this driver. It recommends backing off to the previous version of the
driver, or upgrading to 10g.

Bug No. 4235212
Problem description:
-------------------
When executing a stored procudure using the following syntax:
(SQLCHAR*)"{CALL Criteria (?,?,?)}", SQL_NTS); .
The following error results: SQLExecDirect failed Stored procedure call
failed!!!
SQLSTATE = S1000
NATIVE ERROR = 6550
MSG = [Oracle][ODBC][Ora]ORA-06550: line 1, column 22:
PLS-00561: character set mismatch on value for parameter 'FINAL_SQL'
ORA-06550: line 1, column 7: PL/SQL: Statement ignored .
When the call is changed to the following syntax: (SQLCHAR*)"BEGIN Criteria
(?,?,?); END;", SQL_NTS); .
The error does not occur.

Upgrade the Oracle ODBC Driver to version 10.1.0.2.0 or downgrade to version
9.2.0.5.4.

Phil

[roybrown]

Thanks for the information. I tried updating to the latest Oracle ODBC drivers. I still get the same response. Hopefully, I'll turn up a solution.

[jpal00]

We just ran into this problem.
It may have to do with a LONG datatype in a table that you are running the
procs on. If this is the case, you may have an option "Force retrieve long
datatypes" in the workarounds tab in the MS ODBC Administrator on your server.
If so, enable the option and re-run the procs. Hope this helps..