calling Oracle functions
Дамјан Г.
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?
Anthony Tuininga
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... :-)
> --
> http://mail.python.org/mailman/listinfo/python-list
--
Anthony Tuininga
ant...@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
Дамјан Г.
> 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
>
> > 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)(PORT=1521)))(CONNECT_DATA=(SID=DEV)))
v_Connection = cx_Oracle.connect("user", "pw", v_TNS)
and that works just fine. Does that answer your question?
Дамјан Г.
>> 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)(PORT=1521)))(CONNECT_DATA=(SID=DEV)))
> 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
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