cx_Oracle callproc output parameters

[Diez B. Roggisch]

infidel wrote:
> I have a stored procedure that has a single output parameter.  Why do I
> have to pass it a string big enough to hold the value it is to receive?
>  Why can't I pass an empty string or None?
> 
> 
>>>>import cx_Oracle as oracle
>>>>connection = oracle.connect('usr/pwd at tns')
>>>>cursor = connection.cursor()
>>>>network_name, = cursor.callproc('my_pkg.get_network_name_sp', ('',))
> 
> Traceback (most recent call last):
>   File "<interactive input>", line 1, in ?
> DatabaseError: ORA-06502: PL/SQL: numeric or value error: character
> string buffer too small
> ORA-06512: at "USR.MY_PKG", line 35
> ORA-06512: at line 1
> 
> The following works fine, but I don't like having to do it:
> 
> 
>>>>network_name, = cursor.callproc('my_pkg.get_network_name_sp', (' ' * 32,))
> 
> 
> Am I missing something obvious here?

Yes - where should the oracle store the data if you pass None 
(null-pointer!) or a too short string? The C-Api of oracle requires an 
INOUT-Paramter to be properly dimensioned - its like other c-calls, that 
take a pointer and a size argument. Thus you don't have to deal with 
freeing malloc'ed memory in the caller.

I'm not sure about it, but possibly a _return_-value might help here, 
possible by using a function inbstead of a procedure. Did you try that? 
Of course it would require to rewrite your procedure to be a function, 
or if that is not possible due to others using it too, wrap it in a 
p/sql function. I'm a bit rusty on p/sql, so I can't wirte it out of my 
head.

Then you could e.g. do

select my_pkg.wrapped_get_network_name() from dual

and wouldn't have to care about sizes.

regards,

Diez