Call Oracle Stored Function in Python

When we query data from a database in Python, we usually use a package, pyodbc, to do it. Recently, I have to call a stored function of Oracle in Python. I have done call a stored procedure of Microsoft SQL Server before, so it makes sense for me to use the same package to do the same thing.

However, I found the problem is very different because there are not only input parameters but also output parameters of the stored function of Oracle. The stored function looks like the following.

FUNCTION Oracle_Stored_Function(
parameter_a number,
parameter_b number,
parameter_c out varchar2,
parameter_d out varchar2
) RETURN NUMBER

Pyodbc can call the function if the function only has input parameters; otherwise, if it has output parameters it can’t support it. The details are in the link.

Luckily, I found another solution for calling a stored function in Oracle. Instead of using pyodbc, I can use cx_Oracle, another nice package to interact with Oracle database. The following is the code, you can change it to fulfil your situation.

import cx_Oracle
conn = cx_Oracle.connect(user = "your use name", password = "your password",dsn = "your dsn", encoding = "UTF-8")
my_cursor = conn.cursor()

parameter_a = your_value_2
parameter_b = your_value_2

parameter_c = my_cursor.var(str)
parameter_d = my_cursor.var(str)

my_cursor.callfunc("Oracle_Stored_Function", int, 
[parameter_a, parameter_b, parameter_c, parameter_d]
)
parameter_c_output = parameter_c.getvalue()
parameter_d_output = parameter_d.getvalue()

my_cursor.close()

The parameter_a and parameter_b are the input. And you can get the output parameter value after you call the stored function in Oracle. By using the .getvalue(), you can retrieve the value.

You can set up your dsn in the control panel.

Now, we can call a stored function in Oracle in Python. For more details, such as calling a stored procedure. You can check the link.