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.

Was this tutorial helpful?