Calling PL/SQL Procedures in Python

admin

Setting up a PL/SQL procedureThe following statement creates a new procedure called get_order_count() that returns the number of sales orders by a salesman in a specific year.

1. CREATE OR REPLACE PROCEDURE get_order_count(
   
2.     salesman_code NUMBER,
   
3.     year NUMBER,
   
4.     order_count OUT NUMBER)
   
5. IS     
   
6. BEGIN     
   
7.     SELECT
   
8.         COUNT(*) INTO order_count  
   
9.     FROM orders
   
10.     WHERE salesman_id = salesman_code AND
    
11.         EXTRACT(YEAR FROM order_date) = year;
    
12. EXCEPTION
    
13.     WHEN OTHERS THEN
    
14.         dbms_output.put_line(sqlerrm);
    
15. END;        

複製代碼

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

1. SET SERVEROUTPUT ON;
   
2. 
   
3. DECLARE
   
4.     l_order_count NUMBER;
   
5. BEGIN
   
6.     get_order_count(54,2017,l_order_count);
   
7.     dbms_output.put_line(l_order_count);
   
8. END;   

複製代碼

The output of the code is:

1. 3

複製代碼

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:

1. import cx_Oracle
   
2. import config as cfg
   
3. 
   
4. 
   
5. def get_order_count(salesman_id, year):
   
6.     """
   
7.     Get order count by salesman and year
   
8.     :param salesman_id:
   
9.     :param year:
   
10.     :return: the number of orders by a salesman and year
    
11.     """
    
12.     try:
    
13.         # create a connection to the Oracle Database
    
14.         with cx_Oracle.connect(cfg.username,
    
15.                             cfg.password,
    
16.                             cfg.dsn,
    
17.                             encoding=cfg.encoding) as connection:
    
18.             # create a new cursor
    
19.             with connection.cursor() as cursor:
    
20.                 # create a new variable to hold the value of the
    
21.                 # OUT parameter
    
22.                 order_count = cursor.var(int)
    
23.                 # call the stored procedure
    
24.                 cursor.callproc('get_order_count',
    
25.                                 [salesman_id, year, order_count])
    
26.                 return order_count.getvalue()
    
27.     except cx_Oracle.Error as error:
    
28.         print(error)
    
29. 
    
30. 
    
31. if __name__ == '__main__':
    
32.     orders = get_order_count(54, 2017)
    
33.     print(orders)  # 3

複製代碼

Note that the config.py is as follows:

1. username = 'OT'
   
2. password = '<password>'
   
3. dsn = 'localhost/pdborcl'
   
4. port = 1512
   
5. 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 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:

1. order_count = cursor.var(int)

複製代碼

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

1. cursor.callproc('get_order_count', [salesman_id, year, order_count])
   

複製代碼

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

1. return order_count.getvalue()

複製代碼

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:

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

複製代碼