How to execute a stored procedure with a return value in Oracle

Stored procedures in Oracle are database objects that contain a collection of pre-compiled SQL and PL/SQL (Procedural Language/Structured Query Language) code. They are stored within the Oracle database and can be executed on demand.

Install the cx_Oracle module

Ensure that you have the cx_Oracle module installed in your Python environment. You can install it using pip:

pip install cx_Oracle

Import the required modules

Import the cx_Oracle module and any other necessary modules in your Python script:

import cx_Oracle

Establish a connection to the Oracle database

Create a connection object to establish a connection to the Oracle database. We’ll need to provide the necessary connection details, such as the host, port, service name, username, and password:

connection = cx_Oracle.connect(
    user="your_username",
    password="your_password",
    dsn="your_host:your_port/service_name"
)

Create a cursor object


Create a cursor object using the connection. The cursor allows you to execute SQL statements and retrieve results:

cursor = connection.cursor()

Define and execute the stored procedure

Define a variable to store the return value of the stored procedure. Then, execute the stored procedure using the cursor and fetch the return value:

return_value = cursor.var(cx_Oracle.NUMBER)
cursor.callproc("our_stored_procedure_name", [return_value])

Replace our_stored_procedure_name with the actual name of the stored procedure being used. If the stored procedure accepts any input parameters, pass them as a list in the second argument of callproc().

Retrieve and use the return value

Retrieve the return value from the return_value variable and use it as needed:

result = return_value.getvalue()
print("Return Value:", result)

Close the cursor and connection

After executing the stored procedure and retrieving the return value, remember to close the cursor and connection to release database resources:

cursor.close()
connection.close()

You’ve now executed an Oracle-stored procedure with a return value using the cx_Oracle module in Python. Make sure to customize the code according to your specific database and stored procedure requirements.

Unlock your potential: Oracle series, all in one place!

To continue your exploration of Oracle, check out our series of Answers below:

  • How to execute an Oracle stored procedure with return value
    Learn how to execute Oracle stored procedures in Python using cx_Oracle by connecting, creating a cursor, calling the procedure, retrieving values, and closing resources, with customization for your database needs.

  • What are blockchain oracles?
    Learn how blockchain oracles bridge data between blockchains and the outside world, playing a crucial role in smart contracts. They come in centralized and decentralized forms, enhancing reliability.

  • How to disable Oracle triggers?
    Learn how to disable an Oracle trigger using ALTER TRIGGER trigger_name DISABLE; and re-enable it with ALTER TRIGGER trigger_name ENABLE;.

  • How to call Oracle stored procedure in Spring Boot?
    Learn how to create a Spring Boot project, configure an Oracle data source, define a repository with @Procedure, call a stored procedure, handle results, and implement error handling.

Free Resources

Copyright ©2025 Educative, Inc. All rights reserved