Calling PL/SQL Procedures in Python

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

Setting up a PL/SQL procedure

The following statement creates a new procedure called get_order_count() that returns the number of sales orders by a salesman in a specific year.

CREATE OR REPLACE PROCEDURE get_order_count(
    salesman_code NUMBER, 
    year NUMBER,
    order_count OUT NUMBER)
IS     
BEGIN     
    SELECT 
        COUNT(*) INTO order_count  
    FROM orders 
    WHERE salesman_id = salesman_code AND
        EXTRACT(YEAR FROM order_date) = year;
EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line(sqlerrm);
END;        
Code language: SQL (Structured Query Language) (sql)

To test the procedure, you can use the following code using SQL*Plus or SQL Developer:

SET SERVEROUTPUT ON;

DECLARE
    l_order_count NUMBER;
BEGIN
    get_order_count(54,2017,l_order_count);
    dbms_output.put_line(l_order_count);
END;    
Code language: SQL (Structured Query Language) (sql)

The output of the code is:

3
Code language: SQL (Structured Query Language) (sql)

Calling a PL/SQL procedure in Python example

To execute a PL/SQL procedure, you use the Cursor.callproc() method.

The following code illustrates how to call the procedure get_order_count() and print out the number of orders of the salesman 54 in 2017:

import cx_Oracle
import config as cfg


def get_order_count(salesman_id, year):
    """
    Get order count by salesman and year
    :param salesman_id:
    :param year:
    :return: the number of orders by a salesman and year
    """
    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:
                # create a new variable to hold the value of the
                # OUT parameter
                order_count = cursor.var(int)
                # call the stored procedure
                cursor.callproc('get_order_count',
                                [salesman_id, year, order_count])
                return order_count.getvalue()
    except cx_Oracle.Error as error:
        print(error)


if __name__ == '__main__':
    orders = get_order_count(54, 2017)
    print(orders)  # 3
Code language: Python (python)

Note that the config.py is as follows:

username = 'OT'
password = '<password>'
dsn = 'localhost/pdborcl'
port = 1512
encoding = 'UTF-8'
Code language: Python (python)

In this example:

First, connect to the Oracle Database by calling the cx_Oracle.connect() method with the parameters provided by the config module.

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

Third, create a new variable that will hold the returned value of the OUT parameter of the procedure:

order_count = cursor.var(int)Code language: Python (python)

Fourth, call the procedure get_order_count() using the Cursor.callproc() method:

cursor.callproc('get_order_count', [salesman_id, year, order_count])Code language: Python (python)

Finally, call the Variable.getvalue() method to return the value of the variable.

return order_count.getvalue()Code language: Python (python)

Because we used the with block, the Cursor and Connection objects were automatically released.

It is important to note that when you call the Cursor.callproc() method, cx_Oracle actually generates the following anonymous block and then executes it:

cursor.execute("begin get_order_count(:1,:2,:3); end;", [salesman_id, year, order_count])
Code language: Python (python)

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