Execute PL/SQL calls with Python and cx_Oracle

After you’ve got the hang of performing Basic CRUD operations with cx_Oracle you’re ready to start tapping into some of the real power of the Oracle Database.

Why use PL/SQL?

Python is an excellent language for most things you want your application to do, but when you’re processing data it just goes faster if you do the work where the data is.

This post will cover how to execute Oracle PL/SQL functions and procedures using Python and cx_Oracle.  I’m assuming you’re already familiar with PL/SQL if not, you can get some help from Steven Feuerstein and Bryn Llewellyn.  (Additional resources at the end.)

Prerequisites

  • Python 3
  • Oracle Database version 12+
  • Basic Oracle PL/SQL and SQL knowledge.

Setup

If you’d like to follow along with the examples you’ll need to create the following objects in a database schema that is safe to experiment in.  Make sure you have permissions to create the following objects.

To keep everything clean, I’ll be putting my PL/SQL code into a package called pet_manager.

Cleanup

To clean up the database when you are finished with the series, you need to drop the two tables and the package.  Please make sure you’re connected to the correct schema where you created the tables.

Boilerplate template

The template we will be using is:

  1. Install cx_Oracle.
  2. Import the cx_Oracle driver.
  3. Import os module used to read the environment variable.
  4. Get the connection string from the environment variable.
  5. Create the connection object.
  6. Create the cursor object.
I will include this code section with all Python examples and use the connection object “con” and the cursor object “cur” throughout the series.

For each exercise, replace the “# Your code here” line with your code.

Anonymous PL/SQL Block

I’m going to start off with the most basic process and simply execute an anonymous block of PL/SQL code to reset the database tables.

You can execute any DDL or DML statement like this, but if you’re going to run PL/SQL it’s usually best to compile it to the database.

Execute a PL/SQL Procedure

Using the code from the anonymous block I created a procedure in the PL/SQL package called reset_data.

To call this procedure from Python we use the cursor.callproc method and pass in the package.procedure name to execute.

Assuming everything works, there will not be any response.  So this works as a ‘fire and forget’ way to call database procedures.

Pass Parameters

I have a procedure in my PL/SQL package that we can use to create a new pet in the lcs_pets table.  It accepts the pet_name, owner_id and pet_type.  Using these values it will insert a new entry into the lcs_pets table.

Now on the Python side.

I prefer to set my values with variables so that my code is easier to read, so I’ll create and set pet_name, owner_id and pet_type.

Next, I’ll call the cursor.callproc method and add an array containing the values to pass in the order they are defined in the database.

If everything works there will not be any response.

You can also use keyword parameters.  This also makes your code easy to read and also makes it so you don’t need to worry about the order of the parameters.

Once again, if everything works there will not be any response.

Get PL/SQL Function Return Values

When a row is added to the lcs_pets table a new id is automatically generated.  Having this id can be useful so I created a function in my PL/SQL package that will create a new pet in the lcs_pets table, just like in the previous function, but it will return the new id.

Using Python to call a function in the database and get the return value I’ll use the cursor.callfunc method.

  1. I set the variables that I’ll use as arguments to the function.
  2. Define a new_pet_id variable and assign it the value returned from callfunc.
  3. The second argument of the callfunc method is used to define the type of the data being returned.  I’ll set it to int.  (cx_Oracle will handle the NUMBER to int conversion.)
  4. I pass in the array of values just like I did when I used callproc.
  5. Print the returned value for new_pet_id.

Out Parameters

Out parameters can be very handy when you need to pass back more than one piece of information.  I have an add_pet function in the PL/SQL package that will check to see if the pet type you’re adding needs a license or not.  The function will return the new id like before, and a ‘yes’ or ‘no’ through the out parameter.

To work with the out parameter in Python I’ll add a string variable called ‘need_license’.  It can be defined using ‘cursor.var(str)‘. Then we just add the new variable to the values array in the correct position.  This works the same when using out parameters with the callproc method.

To get the value from ‘need_license’ we call it’s getvalue() function.

Accept Argument Values

So far I’ve hard-coded the variable values in the Python code and the methods are fairly simple, so there’s a low chance of errors.  But, for most methods, we want to accept parameter values that can be passed into the Python code then on to the PL/SQL functions.  I’ll modify the Python method to accept command line arguments.

We need to import sys so that we can use sys.argv[] to grab the command line arguments and assign them to the variables.

If I run this to add a dog, I get:

Adding a fish, I get:

PL/SQL Exceptions

Now that I’m accepting outside argument values, the odds that I’ll eventually get errors with the above code is almost a certainty.  If an error happens in the Python code you can handle it as you normally would.  But, what if there’s an error thrown by the PL/SQL code?

It’s easy enough to test this.  Make the same call as before but pass in a string for the second value.

I would recommend that you handle errors as close to where they happen as you can.  In this example, you could catch the error in the PL/SQL function and either handle it or raise it.  If you don’t handle it in PL/SQL it will be passed back to cx_Oracle which will throw a cx_Oracle.DatabaseError.  At that point, you can handle it as you would when any other Error is thrown in your Python application.

Additional Resources

4 thoughts on “Execute PL/SQL calls with Python and cx_Oracle”

  1. Interesting post but in my case, all I wanted was getting the ID of a row that has been added as the result of an INSERT.
    Fortunately, doing this is a lot simpler than having to write a PL/SQL function.
    Of course, none of the blog posts I’ve seen mentions it, as Oracle does a great job at over complicating things and not explaining them to anyone when there is a simpler/better way. After all, that is expected from a company that bases its business on selling support.

    Anyway, enough ranting, here it goes:

    insert_id = cursor.var(cx_Oracle.NATIVE_INT) # define the return value
    cursor.execute(”’INSERT INTO foo(hcp_id, record_id) VALUES(:1,:2) RETURNING id INTO :3”’, (hcp_id, row, insert_id))
    connexion.commit()
    return insert_id.getvalue()[0] # retrieve the return value of the INSERT

    where id has been defined as INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, in this example.

    1. I appreciate the input.

      As with almost everything, there are many ways to do things, this post focuses on using PL/SQL with Python. It is part a series on how to get started using Python with Oracle. On the top of the site you should see a Python menu with links to the other posts.

      For your scenario, I believe you are looking for the INSERT (CRUD) USING CX_ORACLE post, specifically the “Returning data after an insert” section.

  2. In my scenario, I’m calling a stored proc which accepts a list of varchar and returns a lit of attributes (varchar, integer, date).
    I’m getting the below error:

    TypeError: an integer is required (got type type)

Leave a Reply