Use PLSQL Stored Procedure in Python
Brief about the Blog:
In this, I will give an example on how to use the Cursor.callfunc() to call a PL/SQL stored function from a Python program.
Creation of Function
This function is wrote for to get the profit in current year to a sales team
CREATE OR REPLACE FUNCTION get_profit(
salesman_id NUMBER,
Year NUMBER)
RETURN NUMBER
IS
l_revenue NUMBER;
BEGIN
SELECT
SUM(quantity*unit_price)
INTO l_revenue
FROM
orders
INNER JOIN
order_items USING (order_id)
WHERE
salesman_id = salesman_id AND
EXTRACT(YEAR FROM order_date) = YEAR;
RETURN l_revenue;
END;
Below Anonymous block will be helpful for to test the Function,
SET SERVEROUTPUT ON;
DECLARE
l_revenue NUMBER;
BEGIN
l_revenue := get_profit(12, 2020);
dbms_output.put_line(l_revenue);
END;
————————————
Output:
2500320
To execute a PL/SQL stored function, you use the Cursor.callproc() method.
The following code illustrates how to call the stored function get_profit() and display the revenue of salesman 12 in the year 2020:
import cx_Oracle
import config as cfg
def get_profit(salesman_id, year):
“””
Get revenue by salesman in a specific year
:param salesman_id:
:param year:
:return: the revenue
“””
revenue = None
try:
# create a connection to the Oracle Database
with cx_Oracle.connect(cfg.username,
cfg.password,
cfg.dsn,
encoding=cfg.encoding) as connection:
# create a new cursor
with connection.cursor() as cursor:
# call the function
revenue = cursor.callfunc(‘get_profit,
float,
[salesman_id, year])
except cx_Oracle.Error as error:
print(error)
return revenue
if __name__ == ‘__main__’:
sales_revenue = get_profit(12, 2020)
print(sales_revenue) # 2500320
In this example:
First, establish a connection to the Oracle Database by calling the cx_Oracle.connect() method with the parameters provided by the config module:
username = ‘OT’
password = ‘<password>’
dsn = ‘localhost/pdborcl’
port = 1512
encoding = ‘UTF-8’
Second, create a new Cursor object using the Connection.cursor() method.
Third, call the stored function get_profit() using the Cursor.callfunc() method:
cursor.callfunc(‘get_profit’, float, [salesman_id, year])
In the Cursor.callfunc() method: the first argument is the stored function’s name, the second argument is the type of the returned value, and the third argument is a list of arguments passed to the stored function.
The following code tests the get_profit() function with the salesman 12 and the year 2020:
sales_revenue = get_profit(12, 2020)
print(sales_revenue) # 2500320
In this tutorial, you have learned how to use the Cursor.callfunc() method to call a PL/SQL stored function in Python.
