How to execute Oracle Procedure from Python Script

Oracle procedures are a powerful way to encapsulate complex logic and functionality. They can be used to perform a variety of tasks, such as data manipulation, reporting, and business logic. In this blog post, we will show you how to execute an Oracle procedure from a Python script.

Prerequisites

To follow this tutorial, you will need the following:

• A working Python installation

• The cx_Oracle library

• An Oracle database

Getting Started

The first step is to import the cx_Oracle library. This library provides a Python interface to the Oracle database.

import cx_Oracle

Next, we need to connect to the Oracle database. We can do this using the cx_Oracle.connect() method.

connection = cx_Oracle.connect("user/password@localhost:1521/database")

The first argument to the connect() method is the user name, the second argument is the password, the third argument is the hostname, and the fourth argument is the database name.

Once we are connected to the database, we can create a cursor object. A cursor object is used to execute SQL statements and fetch results.

cursor = connection.cursor()

Now, we can create the procedure. The following code creates a procedure called MIGRATION_PROCEDURE.

procedure_sql = '''
create or replace PROCEDURE MIGRATION_PROCEDURE
AS
BEGIN
  -- Your SQL query goes here.
END;
'''

cursor.execute(procedure_sql)

Once the procedure is created, we can call it using the cursor.callproc() method.

cursor.callproc('MIGRATION_PROCEDURE')

The callproc() method takes the name of the procedure as its first argument.

To capture the output of the procedure, we can set variables to capture the output in string or integer format.

statusVar = cursor.var(cx_Oracle.NUMBER)
lineVar = cursor.var(cx_Oracle.STRING)

The var() method takes the type of the variable as its first argument and the name of the variable as its second argument.

Now, we can loop through the output of the procedure and print it to the console.

while True:
  cursor.callproc("dbms_output.get_line", (lineVar, statusVar))
  if statusVar.getvalue() != 0:
    break
  print (lineVar.getvalue())

The callproc() method will return a status code of 0 if it was successful, and a non-zero status code if it failed. The getvalue() method returns the value of the variable.

Finally, we need to close the cursor and connection.

cursor.close()
connection.close()

That's all there is to it! This is how you can execute an Oracle procedure from a Python script and print output to the console.
Here is the entire Python code:

import cx_Oracle

 # Connect to the Oracle database.
connection = cx_Oracle.connect("user/password@localhost:1521/database")

# Create a cursor object.
cursor = connection.cursor()

# Use "dbms_output.enable" instead of "set serveroutput on;" to print output in console
cursor.callproc("dbms_output.enable")

procedure_sql = '''
create or replace PROCEDURE MIGRATION_PROCEDURE
YOUR_SQL_QUERY...
'''

# create procedure
cursor.execute(procedure_sql)

# call procedure using callproc() method
cursor.callproc('MIGRATION_PROCEDURE')

# set variables to capture output in str or int format
statusVar = cursor.var(cx_Oracle.NUMBER)
lineVar = cursor.var(cx_Oracle.STRING)

while True:
  cursor.callproc("dbms_output.get_line", (lineVar, statusVar))
  if statusVar.getvalue() != 0:
    break
  print (lineVar.getvalue())


# Close the cursor and connection
cursor.close()
conn.close()

I hope this blog post was helpful. If you have any questions, please feel free to leave a comment below.