FAQ
Does anyone know, how can I call Oracle stored functions from Python.
I'm using cx_Oracle.

The function takes three parameters and returns one result.

I tried:

params = ['param1', 'param2', 'param3']
v_Cursor.callproc("SCH.PKG.MyFunction", params)

and I got the error:

cx_Oracle.DatabaseError: ORA-06550: line 1, column 8:
PLS-00221: 'MyFunction' is not a procedure or is undefined

But I can execute the function from sqlplus

declare result number;

BEGIN
result := SCH.PKG.MyFunction('param1', 'param2', 'param3');
END;

Any hints?


--
??????

Yes, I've heard of "decaf." What's your point?

Search Discussions

  • Anthony Tuininga at Sep 13, 2002 at 3:18 pm
    Oracle differentiates between functions and procedures so you must
    identify that you are calling a function, not a procedure. The DB API
    does not allow for this, so you must use an anonymous PL/SQL block and
    some non DB API code. The way to do this is as follows:

    Replace the data types as appropriate.

    v_Vars = v_Cursor.setinputsizes(p_Result = cx_Oracle.NUMBER)
    v_Cursor.execute("""
    begin
    :p_Result := sch.pkg.myfunction(:p_Param1, :p_Param2, :p_Param3);
    end;""",
    p_Param1 = 1,
    p_Param2 = "Some string",
    p_Param3 = "A different string")
    print "Result:", v_Vars["p_Result"].getvalue()

    If anyone has suggestions about how to improve this, fire away... :-)
    On Fri, 2002-09-13 at 08:37, ?????? ?. wrote:
    Does anyone know, how can I call Oracle stored functions from Python.
    I'm using cx_Oracle.

    The function takes three parameters and returns one result.

    I tried:

    params = ['param1', 'param2', 'param3']
    v_Cursor.callproc("SCH.PKG.MyFunction", params)

    and I got the error:

    cx_Oracle.DatabaseError: ORA-06550: line 1, column 8:
    PLS-00221: 'MyFunction' is not a procedure or is undefined

    But I can execute the function from sqlplus

    declare result number;

    BEGIN
    result := SCH.PKG.MyFunction('param1', 'param2', 'param3');
    END;

    Any hints?


    --
    ??????

    Yes, I've heard of "decaf." What's your point?

    --
    http://mail.python.org/mailman/listinfo/python-list
    --
    Anthony Tuininga
    anthony at computronix.com

    Computronix
    Distinctive Software. Real People.
    Suite 200, 10216 - 124 Street NW
    Edmonton, AB, Canada T5N 4A3
    Phone: (780) 454-3700
    Fax: (780) 454-3838
    http://www.computronix.com
  • ґРЬшРЭ і. at Sep 13, 2002 at 4:22 pm

    Oracle differentiates between functions and procedures so you must
    identify that you are calling a function, not a procedure. The DB API
    does not allow for this, so you must use an anonymous PL/SQL block and
    some non DB API code. The way to do this is as follows:
    ok. I also tried that, I mean a PL/SQL block... but
    Replace the data types as appropriate.
    v_Vars = v_Cursor.setinputsizes(p_Result = cx_Oracle.NUMBER)
    didn't know about this :)) thanks.
    ...
    If anyone has suggestions about how to improve this, fire away... :-)
    yes, put it in the documentation.
    Actually is there any documentation about cx_Oracle, past the README
    file?

    Now a simpler question:
    is it possible to specify the data source name directly not through
    the alias defined in tnsnames.ora. In perl DBD I can specify a
    connection string like cs = "dbi:Oracle:host=1.10.100.200;sid=TEST"?




    --
    ??????

    ???????? ?? ????...
    ...????????? ?? ????.
  • Anthony Tuininga at Sep 13, 2002 at 4:55 pm

    On Fri, 2002-09-13 at 10:22, ?????? ?. wrote:
    Oracle differentiates between functions and procedures so you must
    identify that you are calling a function, not a procedure. The DB API
    does not allow for this, so you must use an anonymous PL/SQL block and
    some non DB API code. The way to do this is as follows:
    ok. I also tried that, I mean a PL/SQL block... but
    Replace the data types as appropriate.
    v_Vars = v_Cursor.setinputsizes(p_Result = cx_Oracle.NUMBER)
    didn't know about this :)) thanks.
    You're welcome.
    ...
    If anyone has suggestions about how to improve this, fire away... :-)
    yes, put it in the documentation.
    Actually is there any documentation about cx_Oracle, past the README
    file?
    Bits and pieces in a variety of places. The assumption (to date) has
    been that the DB API is sufficient for almost everything and that you
    really shouldn't use extensions.... :-) However, over the couple of
    years that I have been using the DB API I have discovered a fair number
    of deficiencies with respect to Oracle, in particular when it comes to
    performance and quirks of Oracle. It hasn't been a problem for me since
    I wrote cx_Oracle, but it has been my desire to consolidate the DB API
    document with descriptions of the extensions used by cx_Oracle -- I hope
    to do something about this in the next month or so.
    Now a simpler question:
    is it possible to specify the data source name directly not through
    the alias defined in tnsnames.ora. In perl DBD I can specify a
    connection string like cs = "dbi:Oracle:host=1.10.100.200;sid=TEST"?
    cx_Oracle is passing the string directly to Oracle, so whatever it
    accepts, cx_Oracle will accept. Specifically, you can take the entire
    string that is in your tnsnames.ora file and pass it directly as in

    v_TNS (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=opus)(PORT21)))(CONNECT_DATA=(SIDÞV)))

    v_Connection = cx_Oracle.connect("user", "pw", v_TNS)

    and that works just fine. Does that answer your question?

    --
    Anthony Tuininga
    anthony at computronix.com

    Computronix
    Distinctive Software. Real People.
    Suite 200, 10216 - 124 Street NW
    Edmonton, AB, Canada T5N 4A3
    Phone: (780) 454-3700
    Fax: (780) 454-3838
    http://www.computronix.com
  • ґРЬшРЭ і. at Sep 13, 2002 at 5:25 pm

    Now a simpler question:
    is it possible to specify the data source name directly not through
    the alias defined in tnsnames.ora. In perl DBD I can specify a
    connection string like cs = "dbi:Oracle:host=1.10.100.200;sid=TEST"?
    cx_Oracle is passing the string directly to Oracle, so whatever it
    accepts, cx_Oracle will accept. Specifically, you can take the entire
    string that is in your tnsnames.ora file and pass it directly as in
    v_TNS > (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=opus)(PORT21)))(CONNECT_DATA=(SIDÞV)))
    v_Connection = cx_Oracle.connect("user", "pw", v_TNS)
    and that works just fine. Does that answer your question?
    Yes. Thanks. I would only suggest that you can add a function to
    cx_Oracle to make that big ugly string from 3 parameters.
    something like:

    def makeDSN(host,port,sid):
    return """(DESCRIPTION= (ADDRESS_LIST= (ADDRESS= (PROTOCOL=TCP)
    (HOST=%s) (PORT=%s))) (CONNECT_DATA=(SID=%s)))""" %
    (host,port,sid)





    --
    ??????

    Intel: where Quality is job number 0.9998782345!
  • Anthony Tuininga at Sep 13, 2002 at 6:07 pm

    On Fri, 2002-09-13 at 11:25, ?????? ?. wrote:
    Now a simpler question:
    is it possible to specify the data source name directly not through
    the alias defined in tnsnames.ora. In perl DBD I can specify a
    connection string like cs = "dbi:Oracle:host=1.10.100.200;sid=TEST"?
    cx_Oracle is passing the string directly to Oracle, so whatever it
    accepts, cx_Oracle will accept. Specifically, you can take the entire
    string that is in your tnsnames.ora file and pass it directly as in
    v_TNS > > (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=opus)(PORT21)))(CONNECT_DATA=(SIDÞV)))
    v_Connection = cx_Oracle.connect("user", "pw", v_TNS)
    and that works just fine. Does that answer your question?
    Yes. Thanks. I would only suggest that you can add a function to
    cx_Oracle to make that big ugly string from 3 parameters.
    something like:

    def makeDSN(host,port,sid):
    return """(DESCRIPTION= (ADDRESS_LIST= (ADDRESS= (PROTOCOL=TCP)
    (HOST=%s) (PORT=%s))) (CONNECT_DATA=(SID=%s)))""" %
    (host,port,sid)
    Sounds reasonable. I'll put it on my list of things to do.




    --
    ??????

    Intel: where Quality is job number 0.9998782345!

    --
    http://mail.python.org/mailman/listinfo/python-list
    --
    Anthony Tuininga
    anthony at computronix.com

    Computronix
    Distinctive Software. Real People.
    Suite 200, 10216 - 124 Street NW
    Edmonton, AB, Canada T5N 4A3
    Phone: (780) 454-3700
    Fax: (780) 454-3838
    http://www.computronix.com

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppython-list @
categoriespython
postedSep 13, '02 at 2:37p
activeSep 13, '02 at 6:07p
posts6
users2
websitepython.org

People

Translate

site design / logo © 2022 Grokbase