ODBC Drivers – VARCHAR2(32767) Stored Proc param issue

[Jim Kosloskey]

I have been fighting this issue for a few days and geting nowhere.

Cloverleaf 5.6.2 Data Direct Connect5.2 Drivers AIX platform.

I am invoking a Stored Procedure and an INPUT Parameter is defined in the Stored Procedure as VARCHAR2(32767).

In my sqlbindparameter I am specifying SQL_VARCHAR and have adjusted the rest of the arguments (buffer length, etc.) for virtually every combination.

No matter what I have done I get this error:

[DataDirect] [ODBC Oracle Wire Protocol driver]Invalid precision value. Error in parameter 2.

parameter 2 is the parameter in question.

I only get this error when I have data for that parameter which is larger than 4000 bytes (the default max for VARCHAR2 when no precision is specified). Any data less than 4001 – no issue.

I have searched the internet and it appears that somehow the sizes above 4000 I place in the slqbindparameter command are not being taken or I do not have something in the sqlbindparameter set up correctly.

I am reaching out to see if anyone has invoked an Oracle Stored Procedure with an INPUT parameter defined as VARCHAR2(32767) (or larger than 4000) successfully.

If so, can you share your sqlbindparameter statement so I can see where I have gone astray?

Also if there was some connect string (or odbc.ini) setting you need to specify to get that to work.

Or if there was anything special you recall outside of the sqlbindparameter settings that is needed.

Thanks

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

Topic

[Calvin Palmer]

Jim,

I’ve always been taught that 4000 is THE MAX and cannot be exceeded. Not sure if this helps… 🙁

[Jim Kosloskey]

Calvin,

Rersearch indicates parameters can exceed 4000 up to a max of 32767.

Table columns on the other hand are limited to 4000 maximum.

This was taken right from the Oracle 10g documentation.

So on that point I think we are on solid ground.

However, in practice, I have just not been able to get that to work without error.

Thanbks for the input and if you have any additional suggestions please let me know.

If necessary we can change to CLOB Datatype but I really want to make sure we can get a varchar2 over 4000 to work if it is supposed to.

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

[Jim Kosloskey]

I thought I would follow up in case anyone is intereted.

Apparently there is a bug in the DataDirect Connect5.2 drivers which come with Cloverleaf 5.6 such that VARCHAR2 of size 4000 or above do not work. Gives the error I was experiencing.

This is fixed with Connect5.3 SP2 and beyond.

Of course with Cloverleaf we cannot independently acquire later drivers than that which come with the release of Cloverleaf.

Now I have made that work in the past (out of release drivers) but it is not worth the effort – the vendor (Cloverleaf) should allow indpendent upgrade of drivers without requiring an upgrade of the base product in my opinion – after all IBM can with its use of Data Direct drivers.

Anyway the workaround I used was to define the parameter as CLOB on the Oracle side and SQL_LONGVARCHAR within Tcl. This is somewhat inefficient but since it is only one parameter it won’t be a significant issue.

Bottom line – if you are pre Connect5.2 you CANNOT use VARCHAR2 (Oracle) with sizes of 4000 bytes or larger up to the upper limit of 32767.

Once you get beyond Connect5.2 that should function.

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

[Author]

Replies