Perform PL/SQL Operations with cx_Oracle

Use Python for PL/SQL operations in Oracle Database.

By Blaine Carter

November/December 2018

After you’ve gotten the hang of performing basic create, retrieve, update, delete (CRUD) operations with the cx_Oracle driver for Python, you’re ready to start tapping into some of the real power of 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 article covers how to execute Oracle PL/SQL functions and procedures with Python and cx_Oracle. If you’re not already familiar with PL/SQL, you can get some help from Steven Feuerstein and Bryn Llewellyn (see “Additional Resources”).

Prerequisites

The following are required to run through the steps in this article with me:

• Python 3
• Oracle Database 12c or later
• Basic Oracle PL/SQL and SQL knowledge
• cx_Oracle

To run through the examples in this article, you’ll also 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 PL/SQL package. When you execute the drops, 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 Header

The boilerplate template header for this article assumes that cx_Oracle is installed. Here is the boilerplate code

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

# Your code here

and a description of what the code does.

1. Imports the cx_Oracle driver
2. Imports the OS module used to read the environment variable
3. Gets the connection string from the environment variable
4. Creates the connection object
5. Creates 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 this article. For each example in the article, replace the # Your code here line above with the code for that example.

Anonymous PL/SQL Block

Let’s 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)

When I run the above code in my Python template, named anon_plsql.py for this example,

python3 anon_plsql.py

the code is successful and does not return a response.

You can execute any data definition language (DDL) or data manipulation language (DML) statement this way, 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 this anonymous block, I create a procedure in the pet_manager PL/SQL package called reset_data.

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

cur.callproc('pet_manager.reset_data')

When I run the this code line in my Python template, named execute_procedure.py for this example,

python3 execute_procedure.py

the code is successful and does not return a response. When 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 pet_manager PL/SQL package that I can use to create a new pet in the LCS_PETS table. The add_pet procedure will accept the Python pet_name, owner_id, and pet_type variable values. 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;

On the Python side, I prefer to set my values with variables so that my code is easier to read. I create and set the pet_name, owner_id, and pet_type values. Next, I call the cursor.callproc method and add an array containing the values to pass in in the order in which 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])

When I run the above code in my Python template, named pass_parameters.py for this example,

python3 pass_parameters.py

the code is successful and does not return a response. Once again, if everything works, there will be no response—no value returned.

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 an add_pet function in my pet_manager PL/SQL package that creates a new pet in the LCS_PETS table, and 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;

1. Set the variables to use as arguments to the function.
2. Define a new_pet_id variable and assign it the value returned from callfunc.
3. Define the type of the data being returned—the second argument of the callfunc method does this. I set it to int. (cx_Oracle handles the NUMBER-to-int conversion.)
4. Pass in the array of values (just as I did when I used callproc).
5. Print the returned value for new_pet_id.

When I run the following code in my Python template, named execute_function.py for this example,

python3 execute_function.py

the code is successful and returns a 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)

4

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 pet_manager PL/SQL package that checks to see if the pet type I’m adding needs a license. The function returns the new ID, as before, in addition to a yes or no value 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 add a string variable called need_license. It can be defined with cursor.var(str). Then I add the new variable to the values array in the correct position. This works the same way when you’re using OUT parameters with the callproc method.

To get the value from need_license, I call its getvalue() function. When I run the following code in my Python template, named execute_function_out.py for this example,

python3 execute_function_out.py

the code is successful and returns values for new_pet_id and need_license.

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()))

New pet id: 5
License needed: yes

Accept Argument Values

So far I’ve hardcoded 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, I want to accept parameter values that can be passed into the Python code and then on to the PL/SQL functions. I modify the Python method to accept command-line arguments.

I need to import sys so that I 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()))

When I run the above code in my Python template, named accept_input.py for this example, and add a dog as input

python3 accept_input.py rover 2 dog

the code is successful and returns values for new_pet_id and need_license.

New pet id: 4
License needed: yes

Or if I run the same code and add a fish as input instead,

python3 accept_input.py roger 1 fish

the code is successful and returns values for new_pet_id and need_license.

New pet id: 4
License needed: no

PL/SQL Exceptions

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

It’s easy enough to test this. Make the same call as before to add a dog, but pass in a string for the second value (2x).

python3 accept_input.py rover 2x dog

It returns the following error:

Traceback (most recent call last):
  File "accept_input.py", line 22, in 
    [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 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.

Next Steps

DOWNLOAD cx_Oracle.

GET this article’s code examples from GitHub.

READ the “Perform Basic CRUD Operations with cx_Oracle” series:
Part 1.
Part 2.
Part 3.
Part 4.

Illustration by Wes Rowell