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?

import cx_Oracle as oracle
connection = oracle.connect('usr/pwd@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?

[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@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

[Gerhard Häring]

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?
[...]
Am I missing something obvious here?

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.connect("user/password@my_conn")
cur = con.cursor()

l_SchemaName = cur.var(cx_Oracle.STRING)
l_DbName = cur.var(cx_Oracle.STRING)
l_DomainName = cur.var(cx_Oracle.STRING)

cur.callproc("TP_Lookup.GetSchema", (l_SchemaName, l_DbName, l_DomainName))

print "You are connected to",
print "the schema", l_SchemaName.getvalue(),
print "at %s.%s" % (l_DbName.getvalue(), l_DomainName.getvalue())

[Diez B. Roggisch]

Gerhard Häring wrote:

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

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]

Thanks!

On 11/9/05, Gerhard Häring <[e-mail]> wrote:

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?
[...]
Am I missing something obvious here?

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.connect("user/password@my_conn")
cur = con.cursor()

l_SchemaName = cur.var(cx_Oracle.STRING)
l_DbName = cur.var(cx_Oracle.STRING)
l_DomainName = cur.var(cx_Oracle.STRING)

cur.callproc("TP_Lookup.GetSchema", (l_SchemaName, l_DbName, l_DomainName))

print "You are connected to",
print "the schema", l_SchemaName.getvalue(),
print "at %s.%s" % (l_DbName.getvalue(), l_DomainName.getvalue())