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
