Calling a procedure with CLOB output parameters from ADO over ODBC
Calling a stored procedure from ADO that has a CLOB output parameter fails. Here is the error message:
[Oracle][ODBC][Ora]ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'MYPROCEDURE' ORA-06550: line 1, column 7: PL/SQL: Statement ignored.
Here is the procedure declaration:
CREATE PROCEDURE MYPROCEDURE (insid IN VARCHAR, inskey IN VARCHAR, outdata OUT clob)
And the VB code:
....
objCmd.CommandText = "MYPROCEDURE"
objCmd.CommandType = adCmdStoredProc
objCmd.Parameters.Append objCmd.CreateParameter("insid", adVarChar, adParamInput, 32)
objCmd.Parameters.Append objCmd.CreateParameter("inskey", adVarChar, adParamInput, 32)
objCmd.Parameters.Append objCmd.CreateParameter("outdata", adLongVarChar, adParamOutput, 100000000)
objCmd.Parameters.Item(0).Value = mstrSessionId
objCmd.Parameters.Item(1).Value = mstrSessionKey
objCmd.Execute ' FAILS HERE !!!
....
=============================================
Here comes the spooky part. The error appears when ADO is used with the Oracle ODBC driver. Using Oracle OLEDb provider generates the same error. However, changing a property on the command object makes it work. Here is the altered VB code:
....
objCmd.Properties("SPPrmsLOB") = True
objCmd.Execute
....
So, using the OLEDb provider with the SPPrmsLOB property set to True makes things work!
The question is, can procedures with LOB output parameters work with an ODBC connection (not an OLEDb)?
Thanks!
PS: Some months ago I found an ODBC FAQ (or ODBC Driver FAQ) document somewhere on the OTN site. Has anybody seen this document and where can I find it?