{"id":3050,"date":"2019-11-24T04:37:35","date_gmt":"2019-11-24T11:37:35","guid":{"rendered":"https:\/\/oracletutorial.com\/?page_id=3050"},"modified":"2025-05-31T20:19:41","modified_gmt":"2025-06-01T03:19:41","slug":"calling-a-plsql-procedure-in-python","status":"publish","type":"page","link":"https:\/\/www.oracletutorial.com\/python-oracle\/calling-a-plsql-procedure-in-python\/","title":{"rendered":"Calling PL\/SQL Procedures in Python"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the Cursor.callproc()<\/code> to call a PL\/SQL procedure from a Python program.<\/p>\n\n\n\n

Setting up a PL\/SQL procedure #<\/a><\/h2>\n\n\n\n

The following statement creates a new procedure<\/a> called get_order_count()<\/code> that returns the number of sales orders by a salesman in a specific year.<\/p>\n\n\n

CREATE<\/span> OR<\/span> REPLACE<\/span> PROCEDURE<\/span> get_order_count(\n    salesman_code NUMBER<\/span>, \n    year<\/span> NUMBER<\/span>,\n    order_count OUT<\/span> NUMBER<\/span>)\nIS<\/span>     \nBEGIN<\/span>     \n    SELECT<\/span> \n        COUNT<\/span>(*) INTO<\/span> order_count  \n    FROM<\/span> orders \n    WHERE<\/span> salesman_id = salesman_code AND<\/span>\n        EXTRACT<\/span>(YEAR<\/span> FROM<\/span> order_date) = year<\/span>;\nEXCEPTION\n    WHEN OTHERS THEN\n        dbms_output.put_line(sqlerrm);\nEND<\/span>;        \n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
To test the procedure, you can use the following code using SQL*Plus or SQL Developer:<\/p>\n\n\n
SET<\/span> SERVEROUTPUT ON<\/span>;\n\nDECLARE<\/span>\n    l_order_count NUMBER<\/span>;\nBEGIN<\/span>\n    get_order_count(54<\/span>,2017<\/span>,l_order_count);\n    dbms_output.put_line(l_order_count);\nEND<\/span>;    \n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The output of the code is:<\/p>\n\n\n
3\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Calling a PL\/SQL procedure in Python example #<\/a><\/h2>\n\n\n\n
To execute a PL\/SQL procedure, you use the Cursor.callproc()<\/code> method.<\/p>\n\n\n\n
The following code illustrates how to call the procedure get_order_count()<\/code> and print out the number of orders of the salesman 54 in 2017:<\/p>\n\n\n
import<\/span> oracledb\nimport<\/span> logging\nfrom<\/span> connect import<\/span> connect\n\n\ndef<\/span> get_order_count<\/span>(salesman_id: int, year: int)<\/span> -> int:<\/span>\n    try<\/span>:\n        with<\/span> connect() as<\/span> connection:\n            # Create a new cursor<\/span>\n            with<\/span> connection.cursor() as<\/span> cursor:\n                # Create a new variable to hold the value of the OUT parameter<\/span>\n                order_count_var = cursor.var(oracledb.NUMBER)\n\n                logging.info(f\"Calling stored procedure 'get_order_count' with salesman_id={salesman_id}<\/span>, year={year}<\/span>\"<\/span>)\n                # Call the stored procedure. The list order should match the procedure's parameter order.<\/span>\n                cursor.callproc('get_order_count'<\/span>, [salesman_id, year, order_count_var])\n\n                # Get the value from the OUT parameter<\/span>\n                order_count = order_count_var.getvalue()\n                return<\/span> order_count\n\n    except<\/span> oracledb.Error as<\/span> e:\n        error_message = f\"Oracle database error while getting order count for salesman_id {salesman_id}<\/span>, year {year}<\/span>: {e}<\/span>\"<\/span>\n        logging.error(error_message)\n        return<\/span> None<\/span>\n    except<\/span> Exception as<\/span> e:\n        # Catch any other unexpected errors<\/span>\n        error_message = f\"An unexpected error occurred while getting order count for salesman_id {salesman_id}<\/span>, year {year}<\/span>: {e}<\/span>\"<\/span>\n        logging.critical(error_message) # Use critical for unexpected, severe errors<\/span>\n        return<\/span> None<\/span>\n\n\nif<\/span> __name__ == '__main__'<\/span>:\n    \n    order_count = get_order_count(54<\/span>, 2017<\/span>)\n    if<\/span> order_count is<\/span> not<\/span> None<\/span>:\n        print(f\"The number of orders for salesman 54 in 2017 is: {order_count}<\/span>\"<\/span>)\n    else<\/span>:\n        print(\"Failed to retrieve order count for salesman 54 in 2017.\"<\/span>)\n\n    \n    orders_invalid = get_order_count(99999<\/span>, 2023<\/span>)\n    if<\/span> orders_invalid is<\/span> not<\/span> None<\/span>:\n        print(f\"The number of orders for salesman 99999 in 2023 is: {orders_invalid}<\/span>\"<\/span>)\n    else<\/span>:\n        print(\"Failed to retrieve order count for salesman 99999 in 2023 (as expected or due to error).\"<\/span>)<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
In this example:<\/p>\n\n\n\n
Second, create a new Cursor<\/code> object by calling the Connection.cursor()<\/code> method.<\/p>\n\n\n\n
Third, create a new variable that will hold the returned value of the OUT<\/code> parameter of the procedure:<\/p>\n\n\n
order_count = cursor.var(int)<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Fourth, call the procedure get_order_count()<\/code> using the Cursor.callproc()<\/code> method:<\/p>\n\n\n
cursor.callproc('get_order_count'<\/span>, [salesman_id, year, order_count])<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, call the Variable.getvalue()<\/code> method to return the value of the variable.<\/p>\n\n\n
return<\/span> order_count.getvalue()<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Since we used the with<\/code> block, the Cursor<\/code> and Connection<\/code> objects were automatically released.<\/p>\n\n\n\n
cursor.execute(\"begin get_order_count(:1,:2,:3); end;\"<\/span>, [salesman_id, year, order_count])\n<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
In this tutorial, you have learned how to use the Cursor.callproc()<\/code> method to call a PL\/SQL procedure in Python.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful?<\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t
It is important to note that when you call the Cursor.callproc()<\/code> method, oracledb actually generates the following anonymous block<\/a> and then executes it:<\/p>\n\n\n
First, connect to the Oracle Database<\/a> by calling the oracledb.connect()<\/code> method with the parameters provided by the config<\/code> module.<\/p>\n\n\n\n