cx_Oracle callproc output parameters

[infidel]

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?
[color=blue][color=green][color=darkred]
>>> import cx_Oracle as oracle
>>> connection = oracle.connect( 'usr/pwd@tns')
>>> cursor = connection.curs or()
>>> network_name, = cursor.callproc ('my_pkg.get_ne twork_name_sp', ('',))[/color][/color][/color]
Traceback (most recent call last):
File "<interacti ve 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:
[color=blue][color=green][color=darkred]
>>> network_name, = cursor.callproc ('my_pkg.get_ne twork_name_sp', (' ' * 32,))[/color][/color][/color]

Am I missing something obvious here?

[Diez B. Roggisch]

Re: cx_Oracle callproc output parameters

infidel wrote:[color=blue]
> 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?
>
>[color=green][color=darkred]
>>>>import cx_Oracle as oracle
>>>>connectio n = oracle.connect( 'usr/pwd@tns')
>>>>cursor = connection.curs or()
>>>>network_nam e, = cursor.callproc ('my_pkg.get_ne twork_name_sp', ('',))[/color][/color]
>
> Traceback (most recent call last):
> File "<interacti ve 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:
>
>[color=green][color=darkred]
>>>>network_nam e, = cursor.callproc ('my_pkg.get_ne twork_name_sp', (' ' * 32,))[/color][/color]
>
>
> Am I missing something obvious here?[/color]

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_nam e() from dual

and wouldn't have to care about sizes.

regards,

Diez

[Gerhard Häring]

Re: cx_Oracle callproc output parameters

infidel wrote:[color=blue]
> 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?
> [...]
> Am I missing something obvious here?[/color]

You have to use variable objects to the callproc() that will hold the
output values. This is an example using three VARCHAR output parameters.

HTH,

-- Gerhard


import cx_Oracle

con = cx_Oracle.conne ct("user/password@my_con n")
cur = con.cursor()

l_SchemaName = cur.var(cx_Orac le.STRING)
l_DbName = cur.var(cx_Orac le.STRING)
l_DomainName = cur.var(cx_Orac le.STRING)

cur.callproc("T P_Lookup.GetSch ema", (l_SchemaName, l_DbName, l_DomainName))

print "You are connected to",
print "the schema", l_SchemaName.ge tvalue(),
print "at %s.%s" % (l_DbName.getva lue(), l_DomainName.ge tvalue())

[Diez B. Roggisch]

Re: cx_Oracle callproc output parameters


Gerhard Häring wrote:[color=blue]
> You have to use variable objects to the callproc() that will hold the
> output values. This is an example using three VARCHAR output parameters.[/color]

Oh boy, one never stops learning... I still thing a single in-out-value
is crying for a function - but in case of several parameters, this of
course is way more elegant as it requires no knowledge about the
column-size beforehand.

Regards,

Diez

[Lao Tzu]

Re: cx_Oracle callproc output parameters

Thanks!

On 11/9/05, Gerhard Häring <gh@ghaering.de > wrote:[color=blue]
> infidel wrote:[color=green]
> > 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?
> > [...]
> > Am I missing something obvious here?[/color]
>
> You have to use variable objects to the callproc() that will hold the
> output values. This is an example using three VARCHAR output parameters.
>
> HTH,
>
> -- Gerhard
>
>
> import cx_Oracle
>
> con = cx_Oracle.conne ct("user/password@my_con n")
> cur = con.cursor()
>
> l_SchemaName = cur.var(cx_Orac le.STRING)
> l_DbName = cur.var(cx_Orac le.STRING)
> l_DomainName = cur.var(cx_Orac le.STRING)
>
> cur.callproc("T P_Lookup.GetSch ema", (l_SchemaName, l_DbName, l_DomainName))
>
> print "You are connected to",
> print "the schema", l_SchemaName.ge tvalue(),
> print "at %s.%s" % (l_DbName.getva lue(), l_DomainName.ge tvalue())
>
>[/color]