Skip to Main Content

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Calling a procedure with CLOB output parameters from ADO over ODBC

13433Sep 26 2001
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?


Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 24 2001
Added on Sep 26 2001
0 comments
486 views