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 oracledb
import logging
from connect import connect


def get_order_count(salesman_id: int, year: int) -> int:
    try:
        with connect() 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_var = cursor.var(oracledb.NUMBER)

                logging.info(f"Calling stored procedure 'get_order_count' with salesman_id={salesman_id}, year={year}")
                # Call the stored procedure. The list order should match the procedure's parameter order.
                cursor.callproc('get_order_count', [salesman_id, year, order_count_var])

                # Get the value from the OUT parameter
                order_count = order_count_var.getvalue()
                return order_count

    except oracledb.Error as e:
        error_message = f"Oracle database error while getting order count for salesman_id {salesman_id}, year {year}: {e}"
        logging.error(error_message)
        return None
    except Exception as e:
        # Catch any other unexpected errors
        error_message = f"An unexpected error occurred while getting order count for salesman_id {salesman_id}, year {year}: {e}"
        logging.critical(error_message) # Use critical for unexpected, severe errors
        return None


if __name__ == '__main__':
    
    order_count = get_order_count(54, 2017)
    if order_count is not None:
        print(f"The number of orders for salesman 54 in 2017 is: {order_count}")
    else:
        print("Failed to retrieve order count for salesman 54 in 2017.")

    
    orders_invalid = get_order_count(99999, 2023)
    if orders_invalid is not None:
        print(f"The number of orders for salesman 99999 in 2023 is: {orders_invalid}")
    else:
        print("Failed to retrieve order count for salesman 99999 in 2023 (as expected or due to error).")Code language: Python (python)

In this example:

First, connect to the Oracle Database by calling the oracledb.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)

Since 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, oracledb 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.