calling Oracle functions

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

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