How to Call Oracle Stored Procedure in Python?

How to Call Oracle Stored Procedure in Python?

Written by
Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.

In this blog post, I am giving an example to call Oracle stored procedure in Python. Example include calling a stored procedure with IN-OUT parameters. I am using cx_Oracle library callproc function to call the Oracle procedure. To let you test this example in your system, I am also providing the source code of the Oracle database table and the stored procedure.

Download source Oracle tables used in this example from the following link Download Scott Schema script.

Oracle Stored Procedure Example with IN-OUT Parameters

The following Oracle procedure will take the two parameters, (1) i_empno as IN parameter employee number and (2) o_total_salary as OUT parameter to return the total salary of the employee.

CREATE OR REPLACE PROCEDURE get_total_sal (i_empno IN emp.empno%TYPE,
o_total_salary OUT NUMBER)
IS
CURSOR c_emp (p_empno emp.empno%TYPE)
IS
SELECT sal, comm
FROM emp
WHERE empno = p_empno;

v_sal NUMBER;
v_comm NUMBER;
BEGIN
OPEN c_emp (i_empno);

FETCH c_emp
INTO v_sal, v_comm;

CLOSE c_emp;

o_total_salary := (v_sal + NVL (v_comm, 0));
END get_total_sal;

Now we will call this procedure in Python using the cx_Oracle callproc function.

CX_Oracle callproc Syntax

cursor.callproc('procedure_name', [argument_1, argument_2, ...])

Call Oracle Stored Procedure in Python with IN-OUT Parameters Example

The following Python program will call the compute_sal procedure and will print the returned total salary on the screen.

import cx_Oracle

con = cx_Oracle.connect('scott/tiger@localhost/orcl')

cur = con.cursor()
n_empno = 7788
n_total_salary = cur.var(cx_Oracle.NUMBER)
cur.callproc('compute_sal', [n_empno, n_total_salary])

print ("Total salary for the employee: ", n_empno, "is: ", n_total_salary.getvalue())
cur.close()
con.close()

Output

Total salary for the employee:  7788 is:  3080.0

See also: