Calling PL/SQL Stored Functions in Python

Summary: in this tutorial, you will learn how to use the Cursor.callfunc() to call a PL/SQL stored function from a Python program.

Setting up a PL/SQL function

The following statement creates a new stored function called get_revenue() that returns the sales revenue by a salesman in a specific year.

CREATE OR REPLACE FUNCTION get_revenue( salesman_code 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_code AND EXTRACT(YEAR FROM order_date) = YEAR; RETURN l_revenue; END;
Code language: SQL (Structured Query Language) (sql)

This anonymous block tests the stored function get_revenue() that get the sales revenue of the salesman id 54 in the year 2017:

SET SERVEROUTPUT ON; DECLARE l_revenue NUMBER; BEGIN l_revenue := get_revenue(54, 2017); dbms_output.put_line(l_revenue); END;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

1160350.79
Code language: Python (python)

Calling a PL/SQL stored function example

To execute a PL/SQL stored function, you use the Cursor.callproc() method. The following code illustrates how to call the stored function get_revenue() and display the revenue of salesman 54 in the year 2017:

import cx_Oracle import config as cfg def get_revenue(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_revenue', float, [salesman_id, year]) except cx_Oracle.Error as error: print(error) return revenue if __name__ == '__main__': sales_revenue = get_revenue(54, 2017) print(sales_revenue) # 1160350.79
Code language: Python (python)

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'
Code language: Python (python)

Second, create a new Cursor object using the Connection.cursor() method.

Third, call the stored function get_revenue() using the Cursor.callfunc() method:

cursor.callfunc('get_revenue', float, [salesman_id, year])
Code language: Python (python)

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_revenue() function with the salesman 54 and the year 2017:

sales_revenue = get_revenue(54, 2017) print(sales_revenue) # 1160350.79
Code language: Python (python)

In this tutorial, you have learned how to use the Cursor.callfunc() method to call a PL/SQL stored function in Python.

Was this tutorial helpful?