Using the %ROWTYPE attribute with python-oracledb – Christopher Jones

When using Python to interact with Oracle Database PL/SQL stored procedures, you may encounter PL/SQL code that uses the %ROWTYPE attribute. This is used to declare a record that represents either a full or partial row of a database table or view. This blog post shows how to use %ROWTYPE in python-oracledb.

Photo by Mike Petrucci on Unsplash

Let’s start with a PL/SQL function that returns a row of the sample schema LOCATIONS table using LOCATIONS%ROWTYPE as the return type. You might create this in Python like:

import getpass
import oracledb

un = ‘cj’
cs = ‘localhost/orclpdb1’
pw = getpass.getpass(f’Enter password for {un}@{cs}: ‘)
connection = oracledb.connect(user=un, password=pw, dsn=cs)

with connection.cursor() as cursor:
cursor.execute(“””
create or replace function TestFuncOUT return locations%rowtype as
p locations%rowtype;
begin
select * into p from locations where rownum < 2;
return p;
end;”””)
if cursor.warning:
print(cursor.warning)

The %ROWTYPE attribute defines a record, so you can use python-oracledb’s gettype() object functionality to get the record “shape”, and pass this as the expected return type to callfunc() when you invoke TestFuncOUT:

rt = connection.gettype(“LOCATIONS%ROWTYPE”)
r = cursor.callfunc(“TESTFUNCOUT”, rt)

The python-oracledb type of r is oracledb.DbObject. You can manipulate and view it using that class’s methods. A handy convenience function that prints an instance is dump_object() defined in samples/object_dump.py. Calling it on the return value from TestFuncOUT:

dump_object(r)

gives:

{
LOCATION_ID: 1000
STREET_ADDRESS: ‘1297 Via Cola di Rie’
POSTAL_CODE: ‘00989’
CITY: ‘Roma’
STATE_PROVINCE: None
COUNTRY_ID: ‘IT’
}

which is the row returned from the query.

If you need to construct a similar object directly in python-oracledb to pass into the database, you can use newobject() and set any desired fields. For example:

rt = connection.gettype(“LOCATIONS%ROWTYPE”)
r = rt.newobject()
r.CITY = ‘Roma’

Passing a value from Python into a PL/SQL block that expects a %ROWTYPE parameter or bind variable is straightforward. Let’s create a PL/SQL procedure that has an IN parameter of type %ROWTYPE:

cursor.execute(“””
create or replace procedure TestProcIN(p in locations%rowtype, city out varchar2) as
begin
city := p.city;
end;”””)
if cursor.warning:
print(cursor.warning)

Now you can call callproc() passing the variable r from the previous callfunc() or newobject() examples in the appropriate parameter position, for example:

c = cursor.var(oracledb.DB_TYPE_VARCHAR)
cursor.callproc(“TESTPROCIN”, [r, c])
print(c.getvalue())

This prints:

Roma

Conclusion

PL/SQL %ROWTYPE attribute values map to python-oracledb’s DbObject instances, allowing easy manipulation and interaction between Python and Oracle Database.

Installing or Upgrading python-oracledb

You can install or upgrade python-oracledb by running:

python -m pip install oracledb –upgrade

The pip options –proxy and –user may be useful in some environments. See python-oracledb Installation for details.

Python-oracledb References

Home page: oracle.github.io/python-oracledb/index.html

Installation instructions:

Truncated by Planet PHP, read more at the original (another 1322 bytes)