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.

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, but 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.

CREATE TABLE lcs_people (
 id NUMBER GENERATED BY DEFAULT AS identity,
 name VARCHAR2(20),
 age NUMBER,
 notes VARCHAR2(100)
)
/

ALTER TABLE LCS_PEOPLE
ADD CONSTRAINT PK_LCS_PEOPLE PRIMARY KEY ("ID")
/

CREATE TABLE LCS_PETS (
 id NUMBER GENERATED BY DEFAULT AS IDENTITY,
 name VARCHAR2(20),
 owner NUMBER,
 type VARCHAR2(100)
)
/

ALTER TABLE LCS_PETS ADD CONSTRAINT PK_LCS_PETS PRIMARY KEY ("ID")
/

ALTER TABLE LCS_PETS ADD CONSTRAINT FK_LCS_PETS_OWNER FOREIGN KEY ("OWNER") REFERENCES "LCS_PEOPLE" ("ID")
/

INSERT INTO lcs_people (name, age, notes)
 VALUES ('Bob', 35, 'I like dogs')
/

INSERT INTO lcs_people (name, age, notes)
 VALUES ('Kim', 27, 'I like birds')
/

INSERT INTO lcs_pets (name, owner, type)
 VALUES ('Duke', 1, 'dog')
/

INSERT INTO lcs_pets (name, owner, type)
 VALUES ('Pepe', 2, 'bird')
/

COMMIT
/

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

CREATE OR REPLACE PACKAGE pet_manager AS
    PROCEDURE reset_data;

    PROCEDURE add_pet (
        name_p       IN VARCHAR2,
        owner_id_p   IN NUMBER,
        pet_type_p   IN VARCHAR2
    );

    FUNCTION add_pet (
        name_p       IN VARCHAR2,
        owner_id_p   IN NUMBER,
        pet_type_p   IN VARCHAR2
    ) RETURN NUMBER;

    FUNCTION add_pet (
        name_p           IN VARCHAR2,
        owner_id_p       IN NUMBER,
        pet_type_p       IN VARCHAR2,
        need_license_p   OUT VARCHAR2
    ) RETURN NUMBER;

END;
/

CREATE OR REPLACE PACKAGE BODY pet_manager IS

  /*
   * Reset the example data
   */

   PROCEDURE reset_data
      AS
   BEGIN
      DELETE FROM lcs_pets;

      EXECUTE IMMEDIATE ('alter table lcs_pets modify id generated BY DEFAULT as identity (START WITH 3)');

      DELETE FROM lcs_people;

      EXECUTE IMMEDIATE ('alter table lcs_people modify id generated BY DEFAULT as identity (START WITH 3)');

      INSERT INTO lcs_people (id, name, age, notes)
      VALUES (1, 'Bob', 35, 'I like dogs');

      INSERT INTO lcs_people (id, name, age, notes)
      VALUES (2, 'Kim', 27, 'I like birds');

      INSERT INTO lcs_pets (id, name, owner, type)
      VALUES (1, 'Duke', 1, 'dog');

      INSERT INTO lcs_pets (id, name, owner, type)
      VALUES (2, 'Pepe', 2, 'bird');

      COMMIT;
   END reset_data;

  /*
   * Add a new Pet
   */

   PROCEDURE add_pet (
      name_p       IN VARCHAR2,
      owner_id_p   IN NUMBER,
      pet_type_p   IN VARCHAR2
   )
      IS
   BEGIN
      INSERT INTO lcs_pets (
         name,
         owner,
         type
      ) VALUES (
         name_p,
         owner_id_p,
         lower(pet_type_p)
      );

      COMMIT;
   END add_pet;

  /*
   * Add a new Pet return new id
   */

   FUNCTION add_pet (
      name_p       IN VARCHAR2,
      owner_id_p   IN NUMBER,
      pet_type_p   IN VARCHAR2
   ) RETURN NUMBER IS
      new_pet_id   NUMBER;
   BEGIN
      INSERT INTO lcs_pets (
         name,
         owner,
         type
      ) VALUES (
         name_p,
         owner_id_p,
         lower(pet_type_p)
      ) RETURNING id INTO new_pet_id;

      COMMIT;
      RETURN new_pet_id;
   END add_pet;

  /*
   * Add a new Pet return new id and if it needs a license.
   */

   FUNCTION add_pet (
      name_p           IN VARCHAR2,
      owner_id_p       IN NUMBER,
      pet_type_p       IN VARCHAR2,
      need_license_p   OUT VARCHAR2
   ) RETURN NUMBER IS
      new_pet_id   NUMBER;
   BEGIN
      INSERT INTO lcs_pets (
         name,
         owner,
         type
      ) VALUES (
         name_p,
         owner_id_p,
         lower(pet_type_p)
      ) RETURNING id INTO new_pet_id;

      IF lower(pet_type_p) IN ('dog', 'cat') THEN
         need_license_p := 'yes';
      ELSE
         need_license_p := 'no';
      END IF;

      COMMIT;
      RETURN new_pet_id;
   END add_pet;

END 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.

drop table lcs_pets
/

drop table lcs_people
/

drop package pet_manager
/

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.

import cx_Oracle
import os
connectString = os.getenv('db_connect')
con = cx_Oracle.connect(connectString)
cur = con.cursor()

# Your code here

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.

# reset data
statement = """
   BEGIN
      DELETE FROM lcs_pets;

      EXECUTE IMMEDIATE ('alter table lcs_pets modify id generated BY DEFAULT as identity (START WITH 3)');

      DELETE FROM lcs_people;

      EXECUTE IMMEDIATE ('alter table lcs_people modify id generated BY DEFAULT as identity (START WITH 3)');

      INSERT INTO lcs_people (id, name, age, notes)
      VALUES (1, 'Bob', 35, 'I like dogs');

      INSERT INTO lcs_people (id, name, age, notes)
      VALUES (2, 'Kim', 27, 'I like birds');

      INSERT INTO lcs_pets (id, name, owner, type)
      VALUES (1, 'Duke', 1, 'dog');

      INSERT INTO lcs_pets (id, name, owner, type)
      VALUES (2, 'Pepe', 2, 'bird');

      COMMIT;
    END;"""

cur.execute(statement)

 Example: python3 anon_plsql.py

 Example:

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.

cur.callproc('pet_manager.reset_data')

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

 Example: python3 execute_procedure.py

 Example:

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.

  /*
   * Add a new Pet
   */

    PROCEDURE add_pet (
        name_p       IN VARCHAR2,
        owner_id_p   IN NUMBER,
        pet_type_p   IN VARCHAR2
    )
        IS
    BEGIN
        INSERT INTO lcs_pets (
            name,
            owner,
            type
        ) VALUES (
            name_p,
            owner_id_p,
            lower(pet_type_p)
        );

        COMMIT;
    END add_pet;

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.

pet_name = 'Roger'
owner_id = 1
pet_type = 'fish'

# add a new pet
cur.callproc('pet_manager.add_pet',
      [pet_name, owner_id, pet_type])

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

 Example: python3 pass_parameters.py

 Example:

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.

/*
   * Add a new Pet return new id
   */

    FUNCTION add_pet (
        name_p       IN VARCHAR2,
        owner_id_p   IN NUMBER,
        pet_type_p   IN VARCHAR2
    ) RETURN NUMBER IS
        new_pet_id   NUMBER;
    BEGIN
        INSERT INTO lcs_pets (
            name,
            owner,
            type
        ) VALUES (
            name_p,
            owner_id_p,
            lower(pet_type_p)
        ) RETURNING id INTO new_pet_id;

        COMMIT;
        RETURN new_pet_id;
    END add_pet;

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.

pet_name = 'Roger'
owner_id = 1
pet_type = 'fish'

# add a new pet
new_pet_id = cur.callfunc('pet_manager.add_pet',
      int,
      [pet_name, owner_id, pet_type])

print (new_pet_id)

 Example: python3 execute_function.py
4

 Example:

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.

/*
   * Add a new Pet return new id and if it needs a license.
   */

   FUNCTION add_pet (
      name_p           IN VARCHAR2,
      owner_id_p       IN NUMBER,
      pet_type_p       IN VARCHAR2,
      need_license_p   OUT VARCHAR2
   ) RETURN NUMBER IS
      new_pet_id   NUMBER;
   BEGIN
      INSERT INTO lcs_pets (
         name,
         owner,
         type
      ) VALUES (
         name_p,
         owner_id_p,
         lower(pet_type_p)
      ) RETURNING id INTO new_pet_id;

      IF lower(pet_type_p) IN ('dog', 'cat') THEN
         need_license_p := 'yes';
      ELSE
         need_license_p := 'no';
      END IF;

      COMMIT;
      RETURN new_pet_id;
   END add_pet;

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.

pet_name = 'Roger'
owner_id = 1
pet_type = 'dog'
need_license = cur.var(str)

# add a new pet
new_pet_id = cur.callfunc('pet_manager.add_pet',
      int,
      [pet_name, owner_id, pet_type, need_license])

print ("New pet id: {}\nLicense needed: {}".format(new_pet_id, need_license.getvalue()))

 Example: python3 execute_function_out.py
New pet id: 5
License needed: yes

 Example:

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.

import cx_Oracle
import os
import sys

connectString = os.getenv('db_connect')
con = cx_Oracle.connect(connectString)
cur = con.cursor()

pet_name = sys.argv[1]
owner_id = sys.argv[2]
pet_type = sys.argv[3]
need_license = cur.var(str)

# add a new pet
new_pet_id = cur.callfunc('pet_manager.add_pet',
      int,
      [pet_name, owner_id, pet_type, need_license])

print ("New pet id: {}\nLicense needed: {}".format(new_pet_id, need_license.getvalue()))

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

 Example: python3 accept_input.py rover 2 dog
New pet id: 4
License needed: yes

Adding a fish, I get:

 Example: python3 accept_input.py roger 1 fish
New pet id: 4
License needed: no

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.

 Example: python3 accept_input.py rover 2x dog
Traceback (most recent call last):
  File "accept_input.py", line 22, in <module>
    [pet_name, owner_id, pet_type, need_license])
cx_Oracle.DatabaseError: ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 1

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

• cx_Oracle Documentation
• https://www.python.org/about/gettingstarted/
• Steven Feuerstein’s Blog
• Bryn Llewellyn’s Blog
• Oracle DevGym
• Oracle LiveSQL
• If you have any problems using cx_Oracle, you can get help here.