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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 |
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.
|
1 2 3 4 5 6 7 8 |
drop table lcs_pets / drop table lcs_people / drop package pet_manager / |
Boilerplate template
The template we will be using is:
- Install cx_Oracle.
- Import the cx_Oracle driver.
- Import os module used to read the environment variable.
- Get the connection string from the environment variable.
- Create the connection object.
- Create the cursor object.
|
1 2 3 4 5 6 7 |
import cx_Oracle import os connectString = os.getenv('db_connect') con = cx_Oracle.connect(connectString) cur = con.cursor() # Your code here |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
# 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) |
|
1 2 3 |
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.
|
1 |
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.
|
1 2 3 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
/* * 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.
|
1 2 3 4 5 6 7 |
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]) |
If everything works there will not be any response.
|
1 2 3 |
Example: python3 pass_parameters.py Example: |
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.
|
1 2 3 |
# add a new pet cur.callproc('pet_manager.add_pet', keywordParameters={"pet_type_p": "fish", "name_p": "Roger", "owner_id_p": 1}) |
Once again, if everything works there will not be any response.
|
1 2 3 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
/* * 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.
- I set the variables that I’ll use as arguments to the function.
- Define a new_pet_id variable and assign it the value returned from callfunc.
- 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.)
- I pass in the array of values just like I did when I used callproc.
- Print the returned value for new_pet_id.
|
1 2 3 4 5 6 7 8 9 10 |
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) |
|
1 2 3 4 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
/* * 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.
|
1 2 3 4 5 6 7 8 9 10 11 |
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())) |
|
1 2 3 4 5 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
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:
|
1 2 3 |
Example: python3 accept_input.py rover 2 dog New pet id: 4 License needed: yes |
Adding a fish, I get:
|
1 2 3 |
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.
|
1 2 3 4 5 6 |
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.
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.
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.
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)
Make sure you’re returning integers into the integer attribute.