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;
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;
The output of the code is:
Calling a PL/SQL procedure in Python example
To execute a PL/SQL procedure, you use the
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
Note that the config.py is as follows:
username = 'OT' password = '<password>' dsn = 'localhost/pdborcl' port = 1512 encoding = 'UTF-8'
In this example:
First, connect to the Oracle Database by calling the
cx_Oracle.connect() method with the parameters provided by the
Second, create a new
Cursor object by calling the
Third, create a new variable that will hold the returned value of the
OUT parameter of the procedure:
order_count = cursor.var(int)
Fourth, call the procedure
get_order_count() using the
cursor.callproc('get_order_count', [salesman_id, year, order_count])
Finally, call the
Variable.getvalue() method to return the value of the variable.
Because we used the
with block, the
Connection objects were automatically released.
It is important to note that when you call the
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])
In this tutorial, you have learned how to use the
Cursor.callproc() method to call a PL/SQL procedure in Python.