cx-oracle-users

[cx-oracle-users] How is cursor.callfunc() supposed to work?

[James C. M. <Jam...@or...>]

Hi everybody,
I'm migrating a collection of python scripts from one db
(Oracle backend, but we get to use some Siebel middleware)
to another (raw Oracle 11g).

I've been trying to use the cursor.callfunc() method with
one of the API calls that we're allowed to use, but no matter
what variation of arguments I supply, I just cannot get the
method to work.


cursor.callfunc('package.api.functionname', cx_Oracle.NUMBER, [], { \
     'ID' : 13741059,
     'comments' : 'Fixed in rev:csetid',
     'commenttype' : 'N',
     'opaqueid' : 'NULL',
     'error_code' : 0,
     'error_mesg' : 'NULL'} )

This would fail with

Traceback (most recent call last):
   File "<stdin>", line 1, in <module>
cx_Oracle.DatabaseError: ORA-06550: line 1, column 13:
PLS-00306: wrong number or types of arguments in call to 'FUNCTIONNAME'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


The same issue occured when I used positional params instead
of keyword params.

Yes, I have checked and rechecked that I am supplying all the
args to the function which are mandatory, as well as those which
are merely optional. I also found Anthony's post from 2002
(http://mail.python.org/pipermail/python-list/2002-September/788482.html)
which mentions setinputsizes(), and tried using that route as well.
No joy there either



I eventually fell back to using a Statement ala



cursor.execute("""
DECLARE
outnum NUMBER;
outmsg VARCHAR2(5000);
BEGIN
package.api.functionname(
     ID => 13741059,
     comments => 'Fixed in changeset: 14209:585efc418ca7',
     commenttype => 'N',
     opaqueid => NULL,
     error_code => bugoutnum,
     error_mesg => bugoutmsg);
END;
""")
connection.commit()

... which worked.


So ... should I try to use cursor.callfunc() in the future,
or just stick with statements like the above?

I'm using cx_Oracle 5.1.1, Oracle Instant Client 11.2, running
with python2.6 on Solaris 11.


Pointers to where (and how) I misread the docs would be greatly
appreciated.

Thankyou in advance,
James C. McPherson
--
Oracle
http://www.jmcp.homeunix.com/blog

Re: [cx-oracle-users] How is cursor.callfunc() supposed to work?

[Paul M. <p.f...@gm...>]

On 22 February 2012 06:07, James C. McPherson
<Jam...@or...> wrote:
> I eventually fell back to using a Statement ala
>
> cursor.execute("""
> DECLARE
> outnum NUMBER;
> outmsg VARCHAR2(5000);
> BEGIN
> package.api.functionname(
>     ID => 13741059,
>     comments => 'Fixed in changeset: 14209:585efc418ca7',
>     commenttype => 'N',
>     opaqueid => NULL,
>     error_code => bugoutnum,
>     error_mesg => bugoutmsg);
> END;
> """)
> connection.commit()
>
> ... which worked.
>
>
> So ... should I try to use cursor.callfunc() in the future,
> or just stick with statements like the above?

>From this code, it appears the api call is a procedure, rather than a
function (no return value). The cursor.callfunc method won't work for
procedures, I suspect (no return type - you appear to have used
cx_Oracle.NUMBER in your tests, for some reason...). So I'd suggest
that you need to stick with the execute of a block solution.

Paul

Re: [cx-oracle-users] How is cursor.callfunc() supposed to work?

[Paul M. <p.f...@gm...>]

On 22 February 2012 07:38, Paul Moore <p.f...@gm...> wrote:
>> So ... should I try to use cursor.callfunc() in the future,
>> or just stick with statements like the above?
>
> From this code, it appears the api call is a procedure, rather than a
> function (no return value). The cursor.callfunc method won't work for
> procedures, I suspect (no return type - you appear to have used
> cx_Oracle.NUMBER in your tests, for some reason...). So I'd suggest
> that you need to stick with the execute of a block solution.

... or use cursor.callproc, which I just noticed :-)

Paul

Re: [cx-oracle-users] How is cursor.callfunc() supposed to work?

[James C. M. <Jam...@or...>]

On 22/02/12 05:40 PM, Paul Moore wrote:
> On 22 February 2012 07:38, Paul Moore<p.f...@gm...>  wrote:
>>> So ... should I try to use cursor.callfunc() in the future,
>>> or just stick with statements like the above?
>>
>>  From this code, it appears the api call is a procedure, rather than a
>> function (no return value). The cursor.callfunc method won't work for
>> procedures, I suspect (no return type - you appear to have used
>> cx_Oracle.NUMBER in your tests, for some reason...). So I'd suggest
>> that you need to stick with the execute of a block solution.
>
> ... or use cursor.callproc, which I just noticed :-)

Hmmm. I thought it was declared as a function but on
re-reading the 1page snipped that is its documentation
I see in bold type "Procedure".

I think what had me confused is that the last 2 params
are modified by the routine and on error contain an
error code and a message.

Thanks for the tip, I'll see how it goes.

Cheers!

James C. McPherson
--
Oracle
http://www.jmcp.homeunix.com/blog

Re: [cx-oracle-users] How is cursor.callfunc() supposed to work?

[James C. M. <Jam...@or...>]

Hi Paul,

On 22/02/12 05:44 PM, James C. McPherson wrote:
> On 22/02/12 05:40 PM, Paul Moore wrote:
...
>>>    From this code, it appears the api call is a procedure, rather than a
>>> function (no return value). The cursor.callfunc method won't work for
>>> procedures, I suspect (no return type - you appear to have used
>>> cx_Oracle.NUMBER in your tests, for some reason...). So I'd suggest
>>> that you need to stick with the execute of a block solution.
>>
>> ... or use cursor.callproc, which I just noticed :-)
>
> Hmmm. I thought it was declared as a function but on
> re-reading the 1page snipped that is its documentation
> I see in bold type "Procedure".

Well ... waddayaknow .... after mucking around for a bit
(restarting my python shell to clean out some extraneous
bind variables and paying attention to the returned message
when I got the args wrong) it works!


Id= 13741059
comments= "new comment to enter"
commenttype= 'N'
error_code = 0
error_mesg = cursor.var(cx_Oracle.STRING)

keywordargs={ID : Id, 'comments' : comments,
     'commenttype' : 'N', 'error_code' : error_code,
     'error_mesg' : error_mesg }

rval = cursor.callproc('bug.bug_api.create_bug_line', [], keywordargs)

 >>> if rval.__contains__("Text line inserted successfully on %d" % Id):
...     print "hey, it worked!"
... else:
...     print "boooo"
...
hey, it worked!


:-D

Thankyou for your help with this problem.


James C. McPherson
--
Oracle
http://www.jmcp.homeunix.com/blog