[Zope-DB] Problem with DCOracle2 stored procedure calls

[Matthew T. Kromer]

Christian Long wrote:

>Hi
>
>I'm having trouble with some stored procedure calls on DCOracle2.  I think
>the trouble is with dynamic binding.
>
>See version & environment info at end of message
>
>
>I have 2 stored procedures - call them M4 and M5. They have similar (but not
>the same) parameters, and are called from  pytyhon in a similar manner.  M4
>succeds and M5 fails.  M5 fails because a null string termination character
>\x00 has been appended to certain argument, so the argument value does not
>match the value in the database table.
>
>Both stored procedures succeed when called from Oracle SQL*Plus
>
>My Python code and the stored procedure signatures are all at the end of the
>message
>  
>

Hi Christian,

I can see from the trace data that the conflict is coming from the fact 
that the actual binding is happening as type SQLT_CHR and it was set up 
as SQLT_STR, essentially CHAR vs VARCHAR2.

The dynamic bind happens because it is an IN/OUT parameter.  The code 
sniffed the procedure definition, saw that it wanted VARCHAR2, and set 
up the binding array as type 5 (SQLT_STR).  This datatype uses a 
terminal NULL to signify end of string.

However, something else has caused the actual BIND to get set up as 
SQLT_CHR.   I dont know that I have enough context to know that your 
trouble spot is really the culprit spot.  It is showing me that in the 
first case, it is bound as SQLT_CHR and in the second case is bound as 
SQLT_STR. 

Because you are passing in Python string objects, and your procedure 
definitions all reference VARCHAR2, I am not sure why a binding is EVER 
happening as SQLT_CHR.

One thing you can do that would help is to get the DCOracle2 procedure 
object and print it's doc string, e.g.

print cursor.procedures.prociapi.adjust_inventory.__doc__
print cursor.procedures.prociapi.icbm_move.__doc__

This will print out what DCOracle2 thinks the procedures are, rather 
than what you declared them to be (in case it is making a mistake).

I'd be willing to bet Oracle is answering the describe call with 
SQLT_CHR which is what is causing the goof.

Having said that, the particular code that notices that the data types 
are in conflict doesnt really know what to do in that case.  It might be 
as simple as adjusting the defined type in the binding array to match 
the result type provided by Oracle, in which case the result objects 
would be converted to python strings properly (without the trailing 
NULL).  I'm not positive that will work though, because I'm not 100% 
clear exactly what code is making the error.


-- 
Matt Kromer
Zope Corporation  http://www.zope.com/