[Published on: Oct 10, 2009]
I decided to prepare a small booklet where I can write down relevant (relevant for me, being an Oracle DBA, not a full time developer) examples that will help me recollect basic cx_Oracle code patterns when I need them. This is my first sidekick booklet and I’m not even sure if I’ll stick with this form of publishing that certainly demands more time than short notes I’m usually writing in Forums section.
Unfortunately, I do not code in python as much as I would like nowadays, so you should take this as a fair warning: python newbie aboard!

It was not on my agenda to write down a comprehensive cx_Oracle tutorial by any means. The main objective is to keep my notes about cx_Oracle module ready for the time when I’ll need them — and this is only a couple of times per year, thus cx_Oracle details can easily slip from my mind.

My primary source of information was: cx_Oracle documentation, articles on OTN, recently released book “Python Programming with Oracle Database” by Ray Terrill and (of course) Google.

My working environment consists of the following:

• Windows XP SP3, 32-bit
• Python 2.6.4
• cx_Oracle 5.0.2
• Oracle 10.2.0.4 EE

I don’t work with older Oracle versions (older than 10g R2), that’s why I really don’t care if some code snippets doesn’t work on them. If you happen to be stuck on older Oracle release and you find that something doesn’t work as described here, then you’ll have to find the workaround by yourself. [Of course, if you find some wrong information that was published on this site, you’re (as always) welcome to contact us.]

Table of contents

• Part 1 – Connecting to Oracle
• Part 2 – Hello World!
• Part 3 – Cursors
  • Part 3.1 – Parse
  • Part 3.2 – Execute
  • Part 3.3 – Fetch
  • Part 3.4 – PL/SQL
• Part 4 – Exceptions
• Part 5 – LOB’s
• Part 6 – Miscellaneous
  • Part 6.1 – DCN
  • Part 6.2 – Session Pool
  • Part 6.3 – dbms_output

---

Part 1 – Connecting to Oracle

How to retrieve some basic information about environment?

# ====================================================
# PART 1 - connecting to Oracle with cx_Oracle
#          Let code speak for itself!
# ====================================================
import sys
import getpass
import platform
import cx_Oracle

# -----------------------------------------------------
# Display versions of python, cx_Oracle module
# and Oracle client being used...
# -----------------------------------------------------
print ("Python version: " + platform.python_version())
print ("cx_Oracle version: " + cx_Oracle.version)
print ("Oracle client: " + str(cx_Oracle.clientversion()).replace(', ','.'))

connection = cx_Oracle.connect("alesk/dbaportal")
print ("Oracle DB version: " + connection.version)
print ("Oracle client encoding: " + connection.encoding)
connection.close()

How to connect to database?

# Basic connection using Oracle tns alias (specified in
# in tnsnames.ora, ldap or in obsolete Oracle Names server)
connection = cx_Oracle.connect("alesk/dbaportal@tns")

# By asking user for the password (suitable for
# interactive command line usage)...
pwd = getpass.getpass()
connection = cx_Oracle.connect("alesk",pwd,"tns")

# Passing connection string on python command line
# for example:
# cmd> python script.py alesk/dbaportal@tns
connection = cx_Oracle.connect(sys.argv[1])

# Using Easy Connect syntax and service name
# (ora10 in example below)...
connection = cx_Oracle.connect('alesk','dbaportal','localhost:1521/ora10')

# or passing connection parameter with single string
connection = cx_Oracle.connect('alesk/dbaportal@localhost:1521/ora10')

# cx_Oracle has also a method for DSN construction...
tns_name = cx_Oracle.makedsn('localhost','1521','ora10')
connection = cx_Oracle.connect('alesk','dbaportal',tns_name)

# Let's see how can we connect as SYSDBA (local and remote):
connection = cx_Oracle.connect('/', mode=cx_Oracle.SYSDBA)
connection = cx_Oracle.connect('alesk/dpaportal@tns', mode=cx_Oracle.SYSDBA)

# or SYSOPER
connection = cx_Oracle.connect('/', mode=cx_Oracle.SYSOPER)

We should always close the connection explicitly when we’re done!

connection.close()

If you’re using cx_Oracle then you should be aware of three important connection attributes that will help
you write DBA friendly code: clientinfo, module and action. We all know (righ?) how important is code instrumentation, Oracle supports instrumentation with package DBMS_APPLICATION_INFO and cx_Oracle conveniently provides API for the same.

connection = cx_Oracle.connect('alesk','dbaportal','tns')
connection.clientinfo = 'python 2.6.4 @ home'
connection.module = 'cx_Oracle demo'
connection.action = 'BatchJob#1'

# At this point module/action is not yet visible in v$session view

SQL> select username, module, action
     from v$session where username='ALESK';

USERNAME   MODULE               ACTION
---------- -------------------- --------------------
ALESK      python.exe

# Let's execute some query....

cursor = connection.cursor()
cursor.execute('select sysdate from dual')

# and see if module/action is now visible in v$session...

SQL> select username, module, action
     from v$session where username='ALESK';

USERNAME   MODULE               ACTION
---------- -------------------- --------------------
ALESK      cx_Oracle demo       BatchJob#1

# Usually, we should keep the module attribute unchanged
# for the duration of python script being executed,
# changing only the action part according to
# application logic.
connection.action = 'BatchJob#2'

Other notable attributes and methods of connect object

# Autocommit -- read/write attribute.
# By default autocommit is off (0).
connection.autocommit

# Current_schema -- read/write attribute set's current
# schema (similar to ALTER SESSION SET CURRENT_SCHEMA = scott;)
>>> connection.current_schema
''
>>> connection.current_schema = 'scott'
>>> connection.current_schema
'scott'

# password atrribute and changepassword method
>>> connection.password
'dbaportal'
>>> connection.changepassword('dbaportal','dbanewpwd')
>>> connection.password
'dbanewpwd'

# Ping (>=10g R2) -- check if connection to the server is still alive.
# For example you could use ping() in a code such as this:
try:
    connection.ping()
except cx_Oracle.DatabaseError, exception:
    error, = exception.args
    print ("Database connection error: ", error.code, error.offset, error.message)
else:
    print "Connection is alive!"

# Cancel long running transaction
connection.cancel()

# commit transaction
connection.commit()

# rollback transaction
connection.rollback()

We should move password out of the main script and instead use Oracle Wallet that we wrote about. On the other hand if all you want to achieve is to prevent casual, over the shoulder password peaking, then moving password to separate module makes sense. For example:

# ----------------------------------------------------
# login.py -- one way to prevent casual password peaking
# over the shoulder. Nothing more than (in)security by obscurity.
# If you want better password protection you should configure
# and use Oracle Wallet. Login.py is not by any means
# a secure password store and anyone that can read the login.py
# can easily retrieve the password!
# ----------------------------------------------------
from base64 import b64decode as d

D = {'alesk':'WkdKaGNHOXlkR0Zz','scott':'ZEdsblpYST0='}

class getpwd(object):
    def __init__(self, username):
        self.username = username
        if D.has_key(self.username) and D[self.username] != None:
            try:
                self.pwd = d(d(D[self.username]))
            except:
                self.pwd = None
        else:
            self.pwd = None
#
# end login.py
#

…with login.py module in place we can remove passwords from our main script. Depending on your skills, you can always change/enhance the logic behind the login.py, knowing that all “consuming” scripts will inherit the change…for example:

#
# Login_demo.py
#
import login
import cx_Oracle

username = 'alesk'
pwd = login.getpwd(username).pwd
database = 'tns'

connection = cx_Oracle.connect(username, pwd, database)

connection.close()

I must say that I’m not happy with the login.py and I’m looking for a better (secure) alternative on Windows, specifically I’m looking in Microsoft DPAPI direction. Most likely I’ll investigate .NET class library System.Security.Cryptography.ProtectedData with IronPython for a start, but this is another story for another time.

End of Part1
GoTo Table of contents

---

Part 2 – Hello World!

Let’s see some basic, Hello World kind of examples to illustrate better the cx_Oracle usage.

#
# Hello World!
#
import cx_Oracle

username = 'alesk'
pwd = 'dbaportal'
database = 'tns'

connection = cx_Oracle.connect(username, pwd, database)

cursor = connection.cursor()
query = "select banner from v$version"

cursor.execute(query)

rows = cursor.fetchall()

for row in rows:
    print row[0]

connection.close()
#
# End
#

One would argue that I could write the above “cx_Oracle – Hello World!” script with less number of lines of the code. True. But this would not be pythonic nor sane thing to do…so don’t be tempted to code like this:

# Hello World!!
import cx_Oracle
for row in cx_Oracle.connect('alesk/dbaportal@tns').cursor().execute("select banner from v$version").fetchall(): print row[0]
# End of dense Hello World!

in both cases the result will be something like this:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production


…and some more examples with DDL and DML statements…

#
# Yet another "Hello World", kind of ;-)
#
import cx_Oracle

connection = cx_Oracle.connect('alesk/dbaportal@tns')

cursor = connection.cursor()

# preparing and executing some DDL statements
create_table = """
 CREATE TABLE dummy (
  id    integer not null,
  name  varchar2(20))"""

add_primary_key = """
 ALTER TABLE dummy ADD CONSTRAINT dummy_pk
  PRIMARY KEY (id)"""

cursor.execute(create_table)
cursor.execute(add_primary_key)

# executing some DML statemenst...
# inserting row using position based bind variables
cursor.execute("""
   INSERT INTO dummy (id, name) VALUES (:1, :2)""",(1,'AlesK'))

# ...and another example using named bind variables
cursor.execute("""
   INSERT INTO dummy (id, name) VALUES (:id, :name)""",
               {'id':2, 'name':'AlesK'})

# commit
connection.commit()

# note that tuple with the single value needs trailing comma
# in python! That's why we wrote ('AlesK',)
cursor.execute("""
  select id, name from dummy where name = :1""", ('AlesK',))

result = cursor.fetchall()

for row in result:
    print("Id=" + str(row[0]) + " Name=" + row[1])

# close cursor and connection
cursor.close()
connection.close()
#
# End
#

End of Part 2
Goto Table of Contents

---

Part 3 – Cursors

In our code we can create arbitrary number of cursor objects with the connection method cursor(). For example:

cursor1 = connection.cursor()
cursor2 = connection.cursor()
...

However, for simple scripts one cursor that is reused with different statements will most likely be more than enough. Cursor objects has many methods and properties. In Part 3 I’ll cover the most important ones.

Cursor objects are at the heart of cx_Oracle module — it’s where the actual work is done. That’s why I decided to break this section in subsections, each covering code pattern related to a particular cursor object method or attribute.

For complete list of cursor attributes and methods see cx_Oracle cursor doumentaion. Most commonly used cursor attributes are listed here:

# Default arraysize is 50 and we can change that...
>>> cursor.arraysize
50
>>> cursor.arraysize = 100
>>> cursor.araysize
100

# Number of rows affected by last SQL statement...
cursor.rowcount

# Bind variables used at last execution...
cursor.bindvars

# Display the last SQL statement...
>>> cursor.statement
"select to_char(sysdate,'dd.mm.yyyy hh24:mi:ss') from dual"

# Display column description of last SQLstatement. Attribute is list of tuples with 7 items
# describing each column:
# (column name, column type, display size, internal size, precision, scale, nullable)
>>> cursor.description
[("TO_CHAR(SYSDATE,'DD.MM.YYYYHH24:MI:SS')", <type 'cx_Oracle.STRING'>, 19, 19, 0, 0, 1)]

#
# cursor.numbersAsStrings
# is convenient attribute if we want to treat retrieved numbers as string
#

# assume ID is defined as NUMBER and NAME as VARCHAR2
sql = "select id, name from dummy"

# Numbers as numbers (default)
cursor = connection.cursor()
cursor.numbersAsStrings = False
cursor.execute(sql)
result = cursor.fetchall()
cursor.close()

for row in result:
    print row

# This will result in ID's to be returned as numbers:
# (1, 'AlesK')
# (2, 'AlesK')

# Numbers as strings
cursor = connection.cursor()
cursor.numbersAsStrings = True
cursor.execute(sql)
result = cursor.fetchall()
cursor.close()

for row in result:
    print row

# And this snippet will result in ID's as strings:
# ('1', 'AlesK')
# ('2', 'AlesK')

Other attributes not mentioned above that you might be interested in: bindarraysize, arrayvar, fetchvars, inputtypehandler, outputtypehanlder, rowfactory.

End of Part 3
Goto Table of contents

---

Part 3.1 – Parse

Cursor method for explicitly parsing SQL statements is in my opinion rarely used, usually we let execute method take care of parsing. Nevertheless we should be aware of this possibility. Let’s see some example:

#
# Explicitly parsing the statement
#
import cx_Oracle

# Parsing can be done explicitly or implicitly by
# execute method of cursor object.

connection = cx_Oracle.connect('alesk/dbaportal@tns')

cursor = connection.cursor()

query = "select sysdate from dual"

# Explicitly parsing the statement
cursor.parse(query)
# Execute already parsed query...
result = cursor.execute(None).fetchone()

print result[0]

# In all our remaining examples we do not parse explicitly
# because execute method does this for us implicitly.
# Parsing is shown here solely as a reference.

When would parse method come handy? One such example might be when collecting meta data about some queries if the hole purpose is to “document” queries without actually executing them…[I know, I don’t sound very convincing :-), but I had to come with some excuse for cursors parse method]:

# Explicitly parsing the query to get metadata about
# columns involved in the query with cursor read only
# attribute description.
#
import cx_Oracle

username = 'alesk'
pwd = 'dbaportal'
database = 'tns'

connection = cx_Oracle.connect(username, pwd, database)

cursor = connection.cursor()
query = 'select id, name from dummy'

cursor.parse(query)

query_description = cursor.description

# plain query description
for column in query_description:
    print column

# or a more verbose description
for column in query_description:
    print ('Column Name: ' + column[0])
    print ('Column Type: ' + str(column[1]))
    print ('Display Size: ' + str(column[2]))
    print ('Internal Size: ' + str(column[3]))
    print ('Precision : ' + str(column[4]))
    print ('Scale : ' + str(column[5]))
    print ('Nullable : ' + str(column[6]))
    print ('---------------------------------------')

cursor.close()
#
# End
#

And here is how plain description would look like:

... for column in query_description:
...    print column
...
('ID', , 39, 22, 38, 0, 0)
('NAME', , 20, 20, 0, 0, 1)

and here is verbose one:

>>> for column in query_description:
...    print ('Column Name: ' + column[0])
...    print ('Column Type: ' + str(column[1]))
...    print ('Display Size: ' + str(column[2]))
...    print ('Internal Size: ' + str(column[3]))
...    print ('Precision : ' + str(column[4]))
...    print ('Scale : ' + str(column[5]))
...    print ('Nullable : ' + str(column[6]))
...    print ('---------------------------------------')
...
Column Name: ID
Column Type:
Display Size: 39
Internal Size: 22
Precision : 38
Scale : 0
Nullable : 0
---------------------------------------
Column Name: NAME
Column Type:
Display Size: 20
Internal Size: 20
Precision : 0
Scale : 0
Nullable : 1
---------------------------------------

End of Part 3.1
Goto Table of contents

---

Part 3.2 – Execute

cx_Oracle cursor object has two methods for executing SQL statements, execute() and executemany(). I’ll cover both methods with code examples.

Execute – with and without bind variables

#
# Execute examples (with and without bind variables).
#
import cx_Oracle

connection = cx_Oracle.connect('alesk/dbaportal@tns')
cursor = connection.cursor()
#
# simple select (fetching is needed only for select's)
#
sql = cursor.execute("select to_char(sysdate,'DD.MM.YYYY') from dual")
result = cursor.fetchone()
print result
#
# some DDL
#
ddl_1 = "CREATE TABLE codebook (id NUMBER(2), name VARCHAR2(20))"
ddl_2 = "ALTER TABLE codebook ADD CONSTRAINT codebook_pk PRIMARY KEY(id)"
cursor.execute(ddl_1)
cursor.execute(ddl_2)
#
# and some more DDL...
#
ddl_3 = "DROP TABLE codebook PURGE"
cursor.execute(ddl_3)
#
# creating stored procedure...(not something I would recommend
# to do from python script, it's just a demo)
#
cr_proc = """
   CREATE OR REPLACE PROCEDURE myproc
   IS
   BEGIN
     -- This is completely uselesss procedure.
     null;
   END;
   """
cursor.execute(cr_proc)
#
# Execute with positional bind parameters, passing variables with a list
#
cursor.execute('select sal from emp where ename = :1', ['KING'])
#
# and passing bind variables with a tuple
#
cursor.execute('select sal from emp where ename = :1', ('KING',))
#
# Execute with named bind parameters using dictionary
#
sql = 'select sal from emp where sal > :sal and deptno = :deptno'
cursor.execute(sql, { 'sal':1000, 'deptno':30 })

result = cursor.fetchall()
for row in result:
    print row

Executemany – Case 1
cx_Oracle.cursor.executemany() method allows execution of many insert statements at once. Bulk inserts can considerably improve performance.

#
# Bulk insert example with executemany.
# In this script I'm using init.ora parameters
# as my input for bulk insert.
#
import cx_Oracle

connection = cx_Oracle.Connection('alesk/dbaportal@tns')
cursor = connection.cursor()

cr_table = """
 CREATE TABLE init_ora (
   pname varchar2(60) not null,
   pvalue varchar2(2000) not null)
 """

cursor.execute(cr_table)

# prepare list with ini.ora parameters
L = []

file_init_ora = open('INITora10.ora','r')

# fill list (L) with tuples (pname, pvalue)
for line in file_init_ora.readlines():
    pair = line.split('=')
    L.append((pair[0],pair[1].rstrip()))

file_init_ora.close()

# Prepare insert statement
cursor.prepare("""
 INSERT INTO init_ora (pname, pvalue) VALUES (:1, :2)""")

# executemany by passing list (L) with tuples (pname, pvalue)
cursor.executemany(None, L)

# Number of inserted rows...
print cursor.rowcount

# commit
connection.commit()

# count the number of inserted rows
cnt = cursor.execute("select count(*) from init_ora")
print cursor.fetchone()

# -------------------------------------------------------------------
# or we can skip prepare statement and use somehow
# different approach, preparing local cache (bind array)
# -------------------------------------------------------------------

# set max sizes, column pname = varchar2(60),
# column pvalue = varchar2(2000)
cursor.setinputsizes(60,2000)

# set bindarray size (default is 1)
cursor.bindarraysize=10

cursor.executemany("""INSERT INTO init_ora (pname,pvalue)
                   VALUES (:1,:2)""", L)

connection.commit()

cursor.close()
connection.close()
#
# End
#

Executemany – Case 2
More common example of loading data into Oracle database from comma separated file (csv):

#
# Loading emp.csv data with cx_Oracle in "bulk" mode
#
# Sample data from emp.csv:
# 7369;SMITH;CLERK;7902;17.12.1980;800;;20
# 7499;ALLEN;SALESMAN;7698;20.02.1981;1600;300;30
# 7521;WARD;SALESMAN;7698;22.02.1981;1250;500;30
# 7566;JONES;MANAGER;7839;02.04.1981;2975;;20
# ...
# Position of fields in csv files equals position
# of columns in emp table.
import cx_Oracle
import csv

connection = cx_Oracle.connect('alesk/dbaportal@tns')
cursor = connection.cursor()

insert = """
 INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
   VALUES (:1, :2, :3, :4, to_date(:5,'DD.MM.YYYY'), :6, :7, :8)"""

# Initialize list that will serve as a container for bind values
L = []

reader = csv.reader(open('emp.csv'), delimiter=';')

for row in reader:
    L.append(tuple(row))

# prepare insert statement
cursor.prepare(insert)

# execute insert with executemany
cursor.executemany(None, L)

# report number of inserted rows
print 'Inserted: ' + str(cursor.rowcount) + ' rows.'

# commit
connection.commit()

# close cursor and connection
cursor.close()
connection.close()
#
# End
#

End of Part 3.2
Goto Table of contents

---

Part 3.3 – Fetch

We can retrieve rows from the select statement with cursor iterator or “explicitly” with one of the three methods for fetching data from Oracle (ok, there is also a fourth method, fetchraw() that is not covered here).

Using cursor iterator:

#
# Fetching data with builtin cursor iterator
#
import cx_Oracle

connection = cx_Oracle.connect('alesk/dbaportal@tns')
cursor = connection.cursor()
# we can change arraysize from default (50) to something
# else, such as 100 or 150, imho larger values are not as
# beneficial as you might think.
cursor.arraysize = 100

cursor.execute('select * from emp')

for row in cursor:
    print row

Using fetchone():

#
# Fetching row one by one with fetchone() method.
#

# if you know that your query will return exactly one row
# using fetchone() method makes sense - in a way we're
# telling to the poor soul, who got the privilege to maintain your code,
# something about the "business" rule: we expect to process exactly one row.

cursor.execute("select to_char(sysdate,'DD.MM.YYYY') from dual")
row = cursor.fetchone()
print row

# With the following pattern using fetcone() you can in a way mimick
# cursor iterator. It's up to your preference to pick the pattern you like.
# Imho, cursor iterator is more concise code pattern.

cursor.execute("select empno, ename, job from emp")
row = cursor.fetchone()

while row:
    print row
    row = cursor.fetchone()

Using fetchmany(n):

#
# Fecthmany(n) - if not specified, then n = arraysize.
#
cursor.execute("select empno, ename, job from emp")

# Case 1 - for some reason we want to display only the first five rows
for row in cursor.fetchmany(5):
    print row

# Case 2 - if you're web developer trying to break the result
# set on separate pages (so called pagination), you might
# want to execute fetchmany several times, perhaps until you
# reach the end of resultset.

cursor.execute("select empno, ename from emp")

P = cursor.fetchmany(5)
i = 1
while P:
    print "Page " + str(i), P
    P = cursor.fetchmany(5)
    i = i + 1

Using fetchall():

#
# Fetchall() - be very carefull using this method,
# because all rows will be fetched and stored in the list,
# needless to say that your client machine must have enough
# memory to keep the list in.
# It makes sense to retrieve with fetchall() data from
# small tables, such as codebooks and various dictionary
# views.
#
cursor.execute("select * from emp")
result = cursor.fetchall()

for row in result:
    print row

End of Part 3.3
Goto Table of contents

---

Part 3.4 – PL/SQL

In this section I’ll cover:

• executing PL/SQL procedures and functions with methods callproc and callfunc
• calling procedure without a method
• calling a procedure and function that returns REFCURSOR

At the bottom of this section you’ll find PL/SQL code used in examples.

Calling procedure with callproc()

#
# callproc(name, parameters)
#
import cx_Oracle

connection = cx_Oracle.connect('alesk/dbaportal@tns')
cursor = connection.cursor()

# Raise salary with procedure emp_pkg.raise_sal.
# Procedure accepts employee name and raise percentage
# and reports (returns) number of updated rows.
out_parameter = cursor.var(cx_Oracle.STRING)

# Let's raise poor CEO salary for 10%
execute_proc = cursor.callproc('emp_pkg.raise_sal',[ 'KING', 10, out_parameter ])

# print returned value
print out_parameter.getvalue()

Calling function with callfunc():

#
# callfunc(name, returnType, parameters)
#
import cx_Oracle

connection = cx_Oracle.connect('alesk/dbaportal@tns')
cursor = connection.cursor()

# function returns NUMBER
return_value = cursor.var(cx_Oracle.NUMBER)

# calling function to retrieve max. commision for department 30
execute_func  = cursor.callfunc('emp_pkg.max_commission', return_value, [30])

# print the result
if return_value.getvalue() is not None:
    print "Max commission in department: " + str(return_value.getvalue())
else:
    print "Department doesn't employ people with commission."

Inline PL/SQL procedure call (without callproc):

#
# Inline PL/SQL procedure call (without using callproc)
#
import cx_Oracle

connection = cx_Oracle.connect('alesk/dbaportal@tns')
cursor = connection.cursor()

# calling procedure emp_pkg.raise_sal

out_parameter = cursor.var(cx_Oracle.STRING)

# we can enclose procedure call between BEGIN and END
# The BEGIN/END is not required but recommended for clarity.
plsql = "BEGIN emp_pkg.raise_sal(:ename, :percent, :out); END;"
execute_proc = cursor.execute(plsql, ('KING', 10, out_parameter))

print out_parameter.getvalue()

Calling PL/SQL procedure that returns REFCURSOR:

#
# Calling PL/SQL *procedure* that returns refcursor
#
import cx_Oracle
import datetime

connection = cx_Oracle.connect('alesk/dbaportal@tns')

# we need first cursor for callproc
cursor = connection.cursor()

# and second one for refcursor OUT parameter from PL/SQL proc
ref_cursor = connection.cursor()

# get list of employees hired after 31.12.1979
# I used datetime module to prepare input parameter
# hire_date, since it's of DATETIME type.

hire_date = cursor.var(cx_Oracle.DATETIME)
hire_date.setvalue(0, datetime.date(1979,12,31))

execute_proc = cursor.callproc('emp_pkg.list_emp',[hire_date, ref_cursor])

for row in ref_cursor:
    print row

Calling PL/SQL function that returns REFCURSOR:

#
# Calling PL/SQL *function* that returns refcursor with
# callfunc(func, returnType, parameters)...
#
import cx_Oracle

connection = cx_Oracle.connect('alesk/dbaportal@tns')

cursor = connection.cursor()

# PL/SQL function list_all has no parameters!
result = cursor.callfunc('emp_pkg.list_all',returnType=cx_Oracle.CURSOR)

for row in result:
    print row

And here is PL/SQL code used in above examples…

---

-- -----------------------------------------------
-- Demo PL/SQL package EMP_PKG.
-- -----------------------------------------------
create or replace package emp_pkg
is
  procedure raise_sal(p_ename in varchar2, p_raise number, status out varchar2);
  function  max_commission(p_deptno in number) return number;
  type t_refcursor is ref cursor;
  procedure list_emp (p_hiredate in date, emplist out t_refcursor);
  function list_all return t_refcursor;
end emp_pkg;
/

create or replace package body emp_pkg
is
-- --------------------------------------------------------
-- PROCEDURE RAISE_SAL
-- --------------------------------------------------------
procedure raise_sal (p_ename in varchar2, p_raise number, status out varchar2)
as
begin
 update emp set sal = sal + round(sal * p_raise/100) where ename=upper(p_ename);
 status := '# of rows updated: '||SQL%ROWCOUNT;
 commit;
 return;
end raise_sal;
-- -------------------------------------------------------
-- FUNCTION MAX_COMMISSION
-- -------------------------------------------------------
function max_commission (p_deptno in number) return number
as
 max_comm NUMBER;
begin
 -- calculate maximum commission per salary (%) for given department
 select max( round((comm/sal)*100) ) into max_comm
   from emp where comm > 0 and deptno = p_deptno;
 return max_comm;
end max_commission;
-- -------------------------------------------------------
-- PROCEDURE LIST_EMP (demo for refcursor and dbms_output)
-- -------------------------------------------------------
procedure list_emp (p_hiredate in date, emplist out t_refcursor)
is
 min_hiredate date := to_date('01.01.1979','DD.MM.YYYY');
begin
 if p_hiredate >= min_hiredate then
    open emplist
      for 'select * from emp where hiredate >= :pdate' using p_hiredate;
 else
    dbms_output.put_line('Input date '||to_char(p_hiredate,'DD.MM.YYY')||
      'is older than '||to_char(min_hiredate,'DD.MM.YYYY'));
 end if;
end list_emp;
-- ------------------------------------------------------
-- FUNCTION LIST_ALL (demo for refcursor with callfunc)
-- ------------------------------------------------------
function list_all return t_refcursor
is
 listall t_refcursor;
begin
    open listall
      for 'select * from emp';
    return listall;
end list_all;
--
-- END
--
end emp_pkg;
/

End of Part 3.4
Goto Table of contents

---

Part 4 – Exceptions

Exceptions – when things go wrong. According to cx_Oracle documentation API supports the following exceptions:

• cx_Oracle.Warning — defined by DB API 2, but not actually used by cx_Oracle
• cx_Oracle.Error — base class for all other cx_Oracle exceptions.
  1. cx_Oracle.InterfaceError — subclass of cx_Oracle.Error class, it covers errors at interface level rather than database itself (for example if you close cursor and then try to execute something with closed cursor object, you’ll get InterfaceError) .
  2. cx_Oracle.Database — subclass of cx_Oracle.Error class, it’s raised for database related errors.
     1. cx_Oracle.DataError – subclass of cx_Oracle.Database, raised for data processing errors, for example division by zero.
     2. cx_Oracle.OperationalError – subclass of cx_Oracle.Database, raised for database operational errors that are outside programmers control.
     3. cx_Oracle.IntegrityError – subclass of cx_Oracel.Database, raised for data integrity errors (for example violating primary key constraint).
     4. cx_Oracle.InternalError – subclass of cx_Oracle.Database, raised for Oracle internal errors, I would expect ORA-3113, ORA-600 or ORA-7445 to be covered with this exception, but honestly didn’t take the time to prove with the test.
     5. cx_Oracle.ProgrammingError – subclass of cx_Oracle.Database, syntax error, referencing non-existing objects etc.
     6. cx_Oracle.NotSupportedError – subclass of cx_Oracle.Database, non existing part of the API has been called

cx_Oracle._Error argument is returned in a tuple from exception object with the attributes: code, offset, message and context .

Some examples:

#
# Exceptions
#
import cx_Oracle

connection = cx_Oracle.connect('alesk/dbaportal@tns')
cursor = connection.cursor()

# Typicaly I'll be using general cx_Oracle.DatabaseError
# to catch database related error (likely close to 99% of the time)

try:
    # Trying to insert duplicate records
    cursor.execute('insert into emp select * from emp')
except cx_Oracle.DatabaseError, exception:
    error, = exception
    print "Oracle error code: ", error.code
    print "Oracle error message: ", error.message

# ...thought, for example when you know it's likely you'll
# hit data integrity error, you might very well choose to use more
# specific (narrow scoped) exception, cx_Oracle.IntegrityError

try:
    # Trying to insert duplicate records
    cursor.execute('insert into emp select * from emp')
except cx_Oracle.IntegrityError, exception:
    error, = exception
    print "Oracle error code: ", error.code
    print "Oracle error message: ", error.message

In both cases you’ll get error message such as:

Oracle error code: 1
Oracle error message: ORA-00001: unique constraint (ALESK.EMP_PK) violated


#
# Of course we can use several exceptions and react according
# to the nature of the error
#

try:
    # Try to insert row
    sql = "insert into emp(empno,ename) values (:1,:2)"
    cursor.execute(sql,[9000,'ALESK'])
    connection.commit()
except cx_Oracle.IntegrityError:
    print "Sorry, this employee number is already taken."
except cx_Oracle.InternaError, exception:
    error, = exception
    # Do something, perhaps ask DBA to check alert.log...
    # SendAlertToDBA("Check Alert log", error.message)
except cx_Oracle.Database, exception:
    error, = exception
    # You might choose to alert developer in case some
    # general database error occurs...
    # SendAlertToDevTeam("Check app.", error.message)
else:
    print "Record inserted!"

End of Part 4
Goto Table of contents

---

Part 5 – LOB’s

Handling LOB’s (BLOB, CLOB/NCLOB, BFILE)
In this section I’ll cover basic code patterns for handling LOB’s, CLOB, BLOB and BFILE, as well as working with legacy (deprecated) LONG and LONG RAW types. For truly large objects (which imho, doesn’t belong in relational database), that are gigabyte(s) in size, you’ll likely have to look somewhere else for memory friendly code, that is reading and/or writing LOBS in chunks (start with cx_Oracle documentation, section about LOB’s).
Example for deprecated LONG/LONG RAW data type is here solely because Oracle dictionary itself still has tables with this deprecated type.

DDL for tables used in LOB examples

--
-- Table EMP_RESUME used in LOB examples
--
create table emp_resume (
 empno      number(4) not null,
 pdfname    varchar2(100),
 pdf        BLOB,
 memo       CLOB,
 picture    BFILE);

alter table emp_resume add constraint emp_resume_pk
 primary key (empno);

Writing to tables with BLOB & CLOB columns:

---

#
# LOB Example 1 - loading data into table with BLOB & CLOB
#
import cx_Oracle

connection = cx_Oracle.connect('alesk/dbaportal@tns')
cursor = connection.cursor()

# King's resume is in King.pdf (11 MB) file and memo about him in
# King.txt (88 KB).

f_pdf  = open('King.pdf','rb')
f_memo = open('King.txt','r')

pdf  = f_pdf.read()
memo = f_memo.read()

# prepare memory for operation parameters
cursor.setinputsizes(pdf=cx_Oracle.BLOB, memo=cx_Oracle.CLOB)

insert = """insert into emp_resume (empno, pdfname, pdf, memo)
   values (:empno, :pdfname, :pdf, :memo)"""

try:
    cursor.execute(insert,{'empno':7839, 'pdfname':f_pdf.name,
                           'pdf':pdf, 'memo':memo})
    connection.commit()
except cx_Oracle.DatabaseError, exception:
    error, = exception
    print "Oracle error: ", error.message
else:
    print "Row inserted."
finally:
    # Housekeeping...
    f_pdf.close()
    f_memo.close()
    cursor.close()
    connection.close()

Reading data from table with BLOB & CLOB columns:

---

#
# LOB Example 2 - retrieving data from table with BLOB & CLOB
#
import cx_Oracle

connection = cx_Oracle.connect('alesk/dbaportal@tns')
cursor = connection.cursor()

select = """select empno, pdfname, pdf, memo
            from emp_resume"""

try:
    cursor.execute(select)
    # the safest method for fetching rows with LOB columns
    # is with cursor iterator. Do no use fetchall().
    # See official documentation in LOB section for the
    # explanation!
    for row in cursor:
        empno = str(row[0])
        pdfname = row[1]
        pdf = row[2].read()
        memo = row[3].read()
        # now we can dump documents to the file system...
        f_pdf  = open(empno + '_' + pdfname, 'wb')
        f_memo = open(empno + '_memo.txt', 'wb')
        f_pdf.write(pdf)
        f_memo.write(memo)
        f_pdf.close()
        f_memo.close()
except cx_Oracle.DatabaseError, exception:
    error, = exception
    print "Oracle error: ", error.message
else:
    print "Data processed."
finally:
    # Housekeeping...
    cursor.close()
    connection.close()

Reading and writing to table with BFILE column type:

--
-- DDL for BFILE example. Note that table emp_resume used
-- in BFILE example already has BFILE column and that
-- picture King.jpg already exist in directory!
--
create or replace directory emp_photo
as 'D:\ORADATA\PHOTOS\EMP';
grant read on directory emp_photo to alesk;

---

# --------------------------------------------------
# Handling BFILE's...
# --------------------------------------------------
import cx_Oracle

connection = cx_Oracle.connect('alesk/dbaportal@tns')
cursor = connection.cursor()

#
# check if King.jpg exists in EMP_PHOTO directory...
#
sql = """
 select decode(DBMS_LOB.FILEEXISTS( BFILENAME('EMP_PHOTO','King.jpg') ),
        1,'Yes','No') Fileexists from dual"""

# ok, I admit, a spaghetti line we should usually avoid ;-)
print "Does file exists? ", cursor.execute(sql).fetchone()[0]

#
# and here is native, cx_Oracle method to check
# if file actually exists with fileexists() method
#
sql = """
 select empno, picture from emp_resume where picture is not null"""

cursor.execute(sql)

for row in cursor:
    if row[1].fileexists():
        print "File for emp#" + str(row[0]) + " exists!"
    else:
        print "File for emp#" + str(row[0]) + " doesn't exists!"

#
# Update King PICTURE in table EMP_RESUME
#
king_empno = 7839
photo_dir = "EMP_PHOTO"
photo = "King.jpg"

update = """
 update emp_resume set picture = BFILENAME(:bdir, :bfile)
  where empno = :empno"""

try:
    cursor.execute(update,{'bdir':photo_dir,'bfile':photo,
                           'empno':king_empno})
    connection.commit()
except cx_Oracle.DatabaseError, exception:
    error, = exception
    print "Oracle error: ", error.message
    connection.rollback()
else:
    print "Update succedeed!"

#
# How to "download" bfile content...
#
import cx_Oracle
import Image

connection = cx_Oracle.connect('alesk/dbaportal@tns')
cursor = connection.cursor()

# Let's first see how can we retrieve BFILE locator
# with cx_Oracle...in PL/SQL we could retrieve
# file locator information for BFILE with code such as this:
"""
DECLARE
 lob_locator BFILE;
 dir_name    VARCHAR2(30);
 file_name   VARCHAR2(200);
BEGIN
 SELECT picture INTO lob_locator FROM
    emp_resume where empno = 7839;

 DBMS_LOB.filegetname(lob_locator, dir_name, file_name);
 DBMS_OUTPUT.put_line('Dir: '||dir_name||' File: '||file_name);
END;
/
"""
# It's much easier in cx_Oracle to obtain file locator,
# thanks to LOB method getfilename().
# Let's see how can we "dump" all photos from table EMP_RESUME
# to local file system, keeping original file names from BFILE locator

cursor.execute("select picture from emp_resume where picture is not null")

for row in cursor:
    # retrieve bfile locator
    bdir, bfilename = row[0].getfilename()
    try:
        f_picture = open(bfilename,'wb')
        f_picture.write(row[0].read())
        f_picture.close()
    except IOError:
        print "IO  error..."

# ----------------------------------------------------------
# Finally we could show the picture,
# for example with PIL library:
# pythonware.com/products/pil/
# ----------------------------------------------------------
Image.Open('King.jpg').show()

DDL for tables used in LONG/LONG RAW examples

---

--
-- Table EMP_RESUME_LEGACY used in LONG/LONG RAW
--
create table emp_resume_legacy_pdf (
 empno      number(4) not null,
 pdfname    varchar2(100),
 pdf        LONG RAW);

alter table emp_resume_legacy_pdf add
 constraint emp_resume_legacy_pdf_pk primary key(empno);

create table emp_resume_legacy_memo (
 empno      number(4) not null,
 memo       LONG);

alter table emp_resume_legacy_memo add
 constraint emp_resume_legacy_memo_pk primary key (empno);

Writing to table with LONG & LONG RAW column:

---

#
# Writing LONG / LONG RAW
#
import cx_Oracle

connection = cx_Oracle.connect('alesk/dbaportal@tns')
cursor = connection.cursor()

# ---------------------------------------
# Loading data...
# ---------------------------------------
f_pdf  = open('King.pdf','rb')
f_memo = open('King.txt','rb')

pdf  = f_pdf.read()
memo = f_memo.read()

# Note that Oracle allows only one LONG column per table, that's
# why I create two tables and need to execute insert twice

insert_pdf = """insert into emp_resume_legacy_pdf (empno, pdfname, pdf)
    values(:empno, :pdfname, :pdf)"""

insert_memo = """insert into emp_resume_legacy_memo (empno, memo)
    values(:empno, :memo)"""

try:
    # prepare memory for operation parameter (LONG RAW)
    # and execute insert
    cursor.setinputsizes(pdf=cx_Oracle.LONG_BINARY)
    cursor.execute(insert_pdf,{'empno':7839,'pdfname':f_pdf.name,
                                'pdf':pdf})

    # prepare memory for operation parameter (LONG)
    # and execute insert
    cursor.setinputsizes(memo=cx_Oracle.LONG_STRING)
    cursor.execute(insert_memo,{'empno':7839,'memo':memo})

    # we want both inserts to succeed,
    # otherwise rollback transaction within exeception block
    connection.commit()

except cx_Oracle.DatabaseError, exception:
    error, = exception
    print "Oracle error: ", error.message
    # Rollback
    connection.rollback()
finally:
    f_pdf.close()
    f_memo.close()
    cursor.close()
    connection.close()

Reading from table with LONG/LONG RAW columns:

---

#
# Reading LONG / LONG RAW
#
import cx_Oracle

connection = cx_Oracle.connect('alesk/dbaportal@tns')
cursor1 = connection.cursor()
cursor2 = connection.cursor()

# ------------------------------------
# Downloading data to file system...
# ------------------------------------

select_pdf = """select empno, pdfname, pdf
    from emp_resume_legacy_pdf"""

select_memo = """select empno, memo from
    emp_resume_legacy_memo"""

# In my test case max. expected size for single
# pdf document stored in LONG RAW is less
# than 12MB, still python process memory jumped
# over 100MB while fetching single 11MB pdf
# file from LONG RAW.
# Needless to say that I don't have a clue why.
# Also, if I set setoutputsize to precisely 16MB
# (or more), I got ORA-01062: unable to allocate
# memory for define buffer.
# Perhaps my understanding of parameter
# serveroutputsize is plain wrong!?
# Fortunately for me LONG and LONG RAWS that
# I'm dealing with are much, much smaller, at
# worse in range of couple of kilobytes.

cursor1.arraysize = 1
cursor1.setoutputsize(12000000)

# max. expected size for single memo is 256KB
# cursor2.setinputsizes(memo = cx_Oracle.LONG_STRING)

cursor2.arraysize = 1
cursor2.setoutputsize(262144)

try:
    cursor1.execute(select_pdf)
    for row in cursor1:
        empno = str(row[0])
        pdfname = row[1]
        pdf = row[2]
        f_pdf = open(empno + '_' + pdfname,'wb')
        f_pdf.write(pdf)
        f_pdf.close()

    cursor2.execute(select_memo)
    for row in cursor2:
        empno = str(row[0])
        memo = row[1]
        f_memo = open(empno + '_memo.txt', 'wb')
        f_memo.write(memo)
        f_memo.close()
except cx_Oracle.DatabaseError, exception:
    error, = exception
    print "Oracle error: ", error.message
else:
    print "Data downloaded!"
finally:
    cursor1.close()
    cursor2.close()
    connection.close()

End of Part 5
Goto Table of contents

Part 6 – Miscellaneous

In this section I’ll try to cover various topics that were skipped so far. It’s very likely that this particular section is the one, that will see most of the changes over time. Topics covered here are arguably not the most important ones, nevertheless they deserve to be covered with concise examples.

End of part 6
Goto Table of contents

---

Part 6.1 – DCN

Oracle introduced Database Change Notification (DCN) feature in 10g R2 but truly enhanced it in 11g. This page is more or less a derivative work based on two examples published on OTN:

• Python cx_Oracle 5.0 New Features Overview, by Anthony Tuininga
• Using Python With Oracle Database 11g
• Tim Hall article is a good starting point if you’re new to DCN

I found Anthony Tuininga article (btw. he is author of cx_Oracle) especially good introduction to DCN with python. Otherwise, my experience with Oracle DCN is of limited use right now, I’m still learning and experimenting. DDL code used in this example is published at the bottom of this page.
What is the use of DCN? DCN can be used for example to refresh semi-static tables (such as code books) on middle-tier, saving back and forth trips for every (web) client request.

---

#
# DCN - Database Change Notification example
# is mainly based on two articles:
#
# Python cx_Oracle 5.0 New Features Overview
# -Anthony Tuininga, OTN April 2009
#
# Using Python With Oracle Database 11g
# -Oracle by example OTN article, author unknown.
#
import cx_Oracle

def ShowOperation(operation):
    # This function is not as elegant as the one Anthony
    # Tuininga used in his OTN article, but I prefer
    # dictionary in such situations.
    # Dictionary below keeps all possible operations as well
    # as their combinations derived from cx_Oracle.OPCODE_????
    # constants and my own experimentation.
    operations = {0:'All operations',
                  1:'All rows',
                  2:'Insert',
                  3:'Insert - rowids=False',
                  4:'Update',
                  5:'Insert - rowids=False',
                  8:'Delete',
                  9:'Delete - rowids=False',
                  16:'Alter table',
                  32:'Drop table',
                  6:'Insert, Update',
                  10:'Insert, Delete',
                  11:'Insert, Delete - rowids=False',
                  12:'Update, Delete',
                  13:'Update, Delete - rowids=False',
                  14:'Insert, Update, Delete',
                  15:'Insert, Update, Delete - rowids=False',
                  17:'Alter table - all rows',
                  33:'Drop table - all rows'}
    if operations.has_key(operation):
        return operations[operation]
    else:
        return "Unknown operation:" + str(operation)

def DCN_callback(message):
    """
    This function serves as DCN callback handler that
    in this example only prints information from the
    message received from database.
    Message has three attributes: dbname, type
    and tables. Type atrribute is always
    cx_Oracle.EVENT_OBJCHANGE and thus skipped in this
    example. Needless to say that handler could perform
    various things, refreshing locally cached data for
    example.
    """
    print "Callback from database: ", message.dbname
    print "  Tables:"
    print "---------------------------------------------"
    for table in message.tables:
        print "  Name: ", table.name
        print "  Operation(s): ", ShowOperation(table.operation)
        if table.operation & cx_Oracle.OPCODE_DROP:
            print "  [DROP TABLE]"
        if table.operation & cx_Oracle.OPCODE_ALTER:
            print "  [ALTER TABLE]"
        if table.rows is None or table.operation & cx_Oracle.OPCODE_ALLROWS:
            print "  Rows: all rows"
        else:
            print "  Rows:"
            for row in table.rows:
                if row.operation & cx_Oracle.OPCODE_INSERT:
                    print "  [INSERT] rowid=", row.rowid
                if row.operation & cx_Oracle.OPCODE_DELETE:
                    print "  [DELETE] rowid=", row.rowid
                if row.operation & cx_Oracle.OPCODE_UPDATE:
                    print "  [UPDATE] rowid=", row.rowid
        print "_________________________________________"

#
# Note the parameter events=True, with this we turn
# DCN on.
#
connection = cx_Oracle.connect('alesk/dbaportal@tns', events=True)

sql ="""
        select i.order#, i.customer#, p.description, i.amount
        from products p, order_items i
        where p.product# = i.product#
    """

# Note that you can turn rowids off with rowids=False. This is
# actually recommended, unless you're really prepared to handle
# every rowid you'll get back.
# If you turn rowids=False, you'll get different messages,
# such as Insert - rowids=False.
subscribeAll = connection.subscribe(callback=DCN_callback, rowids=True)
subscribeAll.registerquery(sql)

"""
#
# You can subscribe individual operations, such as DML.
#
subscribeOnDML = connection.subscribe(callback=DCN_callback,
                    operations = cx_Oracle.OPCODE_INSERT |
                                 cx_Oracle.OPCODE_DELETE |
                                 cx_Oracle.OPCODE_UPDATE,
                    rowids = True)
subscribeOnDML.registerquery(sql)

#
# ... I tried to subscribe to DDL events, but it didn't work!??
#
subscribeOnDDL = connection.subscribe(callback=DCN_callback,
                    operations = cx_Oracle.OPCODE_DROP   |
                                 cx_Oracle.OPCODE_ALTER)
subscribeOnDDL.registerquery(sql)
"""
raw_input("Press ENTER for exit....\n")

How do you run above example?

Open two command prompts and position them side by side:
- in the first command prompt window you will run above python script with command such as:
cmd> python dcn.py
- in the second command prompt window run sqlplus and connect to schema (in my case alesk@tns),
then run some DML statements against tables that are registered for DCN monitoring (in my case
tables PRODUCTS and ORDER_ITEMS. See picture 1 for an example. When you'll commit the data
the database will execute callback to python application and you should see something like picture 2.
Remember, DCN works only with Oracle10g R2 or higher.


Picture 1 – sqlplus session

Picture 2 – python app, after we commit changes in sqlplus.

DDL for tables used in DCN example:

---

-- ----------------------------
-- Demo tables for DCN
-- Database Change Notification
-- introduced in 10gR2
-- ----------------------------

-- this is slowly changing
-- dimension in our warehouse
create table products (
 product#       number(5) NOT NULL,
 description    varchar2(100)
);

alter table products
 add constraint products_pk primary key(product#);

insert into products values (1,'Acme Soil');
insert into products values (2,'Acme Hammer');
insert into products values (3,'Acme Firs Aid');

commit;

create table order_items (
 order#         number(5) NOT NULL,
 customer#      number(5) NOT NULL,
 product#       number(5) NOT NULL,
 amount         number(5)
);

alter table order_items add
 constraint order_items_pk primary key(order#,customer#,product#);

alter table order_items add
 constraint order_items_product_fk
 foreign key (product#) references products;

insert into order_items values(1,1,1,100);
insert into order_items values(1,1,2,350);
commit;

# select statement used for DCN query registration
select i.order#, i.customer#, p.description, i.amount
from products p, order_items i
where p.product# = i.product#;

End of Part 6.1
Goto Table of contents

---

Part 6.2 – Session Pool

OCI Session pooling was introduced in Oracle9i R2. Session pool is especially useful for web based applications where number of stateless sessions are acquired for short period of time. Opening session for each request in such situations would be too costly. cx_Oracle supports session pool with SessionPool object. This page serves only as an introduction to cx_Oracle SessionPool. I couldn’t find much info on the web, especially about some nuances that I found (skewed timeout for the pool, some parameters are scarcely documented and not entirely clear to me).

While experimenting with SessionPool in python observe the  sessions from
another command prompt with SQL*Plus, for example:

column username format a10
column program format a15
column module format a20
column action format a20

select sid, serial#, username, program, module, action from v$session;

#
# Using cx_Oracle SessionPool object
#
import cx_Oracle

# Let's create session pool with
# five initial sessions (min=5),
# limit maximum session to 10,
# increment # of sessions by 1,
# connectiontype = [not documented?]
# threaded = False (by default,
# search cx_Oracle docs for OCI_THREADED)
# getmode = [cx_Oracle.SPOOL_ATTRVAL_NOWAIT |
#           cx_Oracle.SPOOL_ATTRVAL_WAIT   |
#           cx_Oracle.SPOOL_ATTRVAL_FORCEGET]
# homogeneous = True (according to cx_Oracle
# docs, if pool is not homogeneous then different
# authentication can be used for each connection
# "pulled" from the pool)

pool = cx_Oracle.SessionPool(
    user='alesk',
    password='dbaportal',
    dsn='tns',
    min=5,
    max=10,
    increment=1,
    connectiontype=cx_Oracle.Connection,
    threaded=False,
    getmode=cx_Oracle.SPOOL_ATTRVAL_NOWAIT,
    homogeneous=True)

#
# Print some facts about the pool
#
def InfoAboutPool(cx_pool):
    print "Oracle OCI name of the pool: " + cx_pool.name
    print "Number of sessions acquired from the pool: " + str(cx_pool.busy)
    print "Number of sessions currently opened: " + str(cx_pool.opened)
    print "Number seconds after which idle session will be terminated: " + str(cx_pool.timeout)

# above command will print something like:
# >>> InfoAboutPool(pool)
# Oracle OCI name of the pool: OCI:SP:ZnicHt42Qf+alpWCBA2EAD
# Number of sessions acquired from the pool: 0
# Number of sessions currently opened: 5

#
# acquire some connections
#
connection1 = pool.acquire()
connection2 = pool.acquire()
connection3 = pool.acquire()
...
...
connection10 = pool.acquire()

# If you try at this point to acquire session# 11,
# you'll receive:
# cx_Oracle.DatabaseError: ORA-24418: Cannot open further sessions.

# At this point you'll see 10 sessions related to python
# in v$session view.

#
# Releasing session back to the pool is easy as well
#
pool.release(connection1)
pool.release(connection2)
...
...
pool.release(connection10)

# Even after you release all ten sessions back to the pool,
# Oracle still maintaines ten database sessions.
# If you want to terminate idle sessions after some elapsed
# time, then set pool.timeout read/write parameter
# to the number of seconds after which idle sessions will
# be terminated (from what I see during my test I guess
# the target for the optimum number is somewhere
# between min and max!?).
# By default timeout is zero, meaning idle session
# termination is turned off.
# During my experiments I noticed that true timeout can vary,
# perhaps it's code optimization, either
# in OCI or cx_Oracle.

pool.timeout = 50

#
# Let's see some practical example...
#
connection = pool.acquire()

# In the following example I showed how session can be
# dropped from the pool in case your session has been killed (or you might
# try to catch other fatal errors such as ORA-3113, ORA-7445, ORA-600).
# In Else section I'm releasing session back to the pool in ordinary fashion.

try:
    # Make sure you intrument your code with clientinfo,
    # module and action attributes - this is especially
    # important if you're using SessionPool.
    connection.clientinfo = 'python 2.6.4 - win32'
    connection.module = 'cx_Oracle SessionPool demo'
    connection.action = 'Get sysdate'
    cursor = connection.cursor()
    cursor.execute('select sysdate from dual')
    sysdate = cursor.fetchone()
    print sysdate[0]
except cx_Oracle.DatabaseError, exception:
    error, = exception
    # chekc if session was killed (ORA-00028)
    session_killed = 28
    if error.code == session_killed:
        #
        # drop session from the pool in case
        # your session has been killed!
        # Otherwise pool.busy and pool.opened
        # will report wrong counters.
        #
        pool.drop(connection)
        print "Session droped from the pool..."
else:
    # if you're done with procession you can return session
    # back to the pool
    cursor.close()
    pool.release(connection)
    print "Session released back to the pool..."

End of Part 6.2
Goto Table of contents

Part 6.3 – dbms_output

It’s fairly common that we need to capture some dbms_output from PL/SQL stored routines. Below is a simple code pattern showing how to handle output from dbms_output in python script.

#
# 6.3. How to handle output from dbms_output
#
import cx_Oracle

connection = cx_Oracle.connect('alesk/dbaportal@tns')

cursor = connection.cursor()

def DbmsOutputGetLine():
    """
    Retrieving dbms_output result line by line
    with dbms_output.get_line method.
    """
    line = cursor.var(cx_Oracle.STRING)
    status = cursor.var(cx_Oracle.NUMBER)
    while True:
        cursor.callproc("dbms_output.get_line",(line,status))
        if status.getvalue() != 0:
            break
        else:
            print line.getvalue()

def DbmsOutputGetLines(n):
    """
    Retrieving dbms_output result with 'array' function
    dbms_output.get_lines.
    """
    lines = cursor.arrayvar(cx_Oracle.STRING, 50)
    numlines = cursor.var(cx_Oracle.NUMBER)
    numlines.setvalue(0,n)  # fetch 'n' lines at a time
    while True:
        cursor.callproc("dbms_output.get_lines",(lines, numlines))
        num_of_lines = int(numlines.getvalue())
        if num_of_lines != 0:
            # dbms_output.get_lines method will set N+1 line
            # as NULL resulting in python printing None as the
            # last line. That's why we need to slice the
            # the list with [:num_of_lines] up to the reported
            # num_of_lines parameter.
            for line in lines.getvalue()[:num_of_lines]:
                print line
        else:
            break

# Enable dbms_output
cursor.callproc("dbms_output.enable")

# Calling some procedure that returns dbms_output...
cursor.callproc("Hello1")

# retrieving dbms_output result, line by line...
DbmsOutputGetLine()

# Calling another procedure that returns dbms_output...
cursor.callproc("Hello2")

# retrieving dbms_output result in batches (20 lines at
# the time in below example)
DbmsOutputGetLines(20)

# Some house keeping...
cursor.callproc("dbms_output.disable")
cursor.close()
connection.close()
#
# End
#

create or replace procedure hello1 as
begin
 dbms_output.put_line('--------------------');
 dbms_output.put_line('Output from Hello1.  ');
 dbms_output.put_line('--------------------');
end;
/

create or replace procedure hello2 as
begin
 dbms_output.put_line('--------------------------');
 for line in 1..lines
 loop
   dbms_output.put_line('Line '||line||' output from Hello2.');
 end loop;
 dbms_output.put_line('--------------------------');
end;
/

End of Part 6.3
Goto Table of contents