This article continues the Using Python to Access Oracle, Part 1 and Using Python to Access Oracle, Part 2. Like those articles, this article assumes that you have basic skills writing Python programs. It also assumes you read the earlier articles, installed and configured the cx_Oracle library, and worked through the other coding examples.

This article covers

  • How you write a transaction against two tables
  • How you write a transaction using a PL/SQL stored procedure
  • How you write a transaction using a PL/SQL stored function

The last article shows you how to embed INSERT, UPDATE, and DELETE statements in Python programs. All embedded statements are ACID compliant. ACID is an acronym that qualifies the steps necessary to guarantee a transaction’s outcome.

The ACID acronym stands for four words that guarantee behavior: atomic, consistent, isolated, and durable. Atomic means either the whole statement or nothing happens. Consistent means a statement can run sequentially or in parallel. (The database choses how the statement runs.) Durable means the changes are permanent once the user commits them.

As most developers know, transactions typically don’t involve a single INSERT, UPDATE, or DELETE statement. Transactions frequently, if not almost always, involve two or more statements and two or more tables. The collection of two or more statements are always independent. That means a transaction against two or more tables isn’t ACID compliant.

SQL provides a solution to the lack of ACID compliance when you work with multiple tables. That solution adds three transaction control language (TCL) commands to the SQL dialect. The three TCL commands are SAVEPOINT, COMMIT, and ROLLBACK. A SAVEPOINT sets a point in time. A COMMIT writes the change permanently to the database. A ROLLBACK undoes transactions to the beginning of a session, the last data definition language command, or to a SAVEPOINT set in the current thread of execution.

The cx_Oracle library doesn’t support a SAVEPOINT. The cx_Oracle cursor object supports a begin() function, which substitutes for a SAVEPOINT in Python.

This article shows you how to write a transaction against two or more tables in Python. Writing transactions against two or more tables is more complex than writing a single INSERT, UPDATE, or DELETE statement.  It’s more complex because individual INSERT, UPDATE, and DELETE statements are ACID compliant but transactions across two or more tables are not.

The first part of the article shows you how to write two INSERT statements against two tables. The second part shows you to write a PL/SQL procedure that manages the transaction across two tables and how you call and run a PL/SQL stored procedure. The third part shows you how to write a PL/SQL function that manages the same transactions as the procedure.

The difference between a procedure and a function is significant. Unfortunately, you may not see the difference until you know why the behavior of a procedure differs from a function. You can run a procedure or function one of two ways. One way runs the stored program in the same transaction scope and the other runs the stored program in a separate transaction scope.

You designate the scope of a PL/SQL stored programs when you write them. A stored program runs in the active or current scope by default. A stored program runs in a separate scope when you include a precompiler instruction that designates it as an autonomous transaction. However, autonomous procedures and functions differ in one key behavior. An autonomous procedure sends no signal whether or not it runs successfully, whereas an autonomous function must return a value when it completes or fails.

An additional complication of stored functions is you can write them to suppress failure. Fortunately, you typically return an error code when you write a function to suppress an error. The article uses an inline function and an autonomous function. Both functions return a zero when successful and an error code when they fail.

How you write a transaction against two tables

This example code could become large because INSERT statements are naturally wordy, or verbose. To simplify the solution, you can use the create_billing.sql script to create the bill and detail tables. These two tables have a minimum number of columns to ensure the embedded INSERT statements are as small as possible.

The script creates the following bill table:

 

 Name                            Null?    Type
 ------------------------------- -------- ----------------------------
 BILL_ID                                  NUMBER
 BILL_NUMBER                              VARCHAR2(20)
 BILL_TEXT                                VARCHAR2(20)

and it creates the following detail table:

 Name                            Null?    Type
 ------------------------------- -------- ----------------------------
 DETAIL_ID                                NUMBER
 BILL_ID                                  NUMBER
 DETAIL_NUMBER                            VARCHAR2(20)
 DETAIL_TEXT                              VARCHAR2(20)

 

The following insertsToTables.py program writes a record to the bill table and then to the detail table.  The program writes to the two tables in the scope of a transaction. If the insert into the bill table succeeds and then the insert into the detail table fails, the transaction scope undoes the insert into both the bill and detail tables.

# ----------------------------------------------------------------------
#  Program Name:  insertsToTables.py
#  Author Name:   Michael McLaughlin
#  Date:          26-Dec-2016
# ----------------------------------------------------------------------

# Import the Oracle library.
import cx_Oracle
import re
import sys

# Declare variables.
sBillNumber = '2016-001'
sBillText = 'Invoice'
sDetailNumber = '01'
sDetailText = 'Mileage'

try:
  # Create a connection.
  db = cx_Oracle.connect("student/student@xe")

  # Set a starting transaction point.
  db.begin()

  # Create a cursor.
  cursor = db.cursor()

  # Execute an INSERT statement.
  stmt = "INSERT"            + "\n" + \
         "INTO   bill"       + "\n" + \
         "( bill_id"         + "\n" + \
         ", bill_number"     + "\n" + \
         ", bill_text )"     + "\n" + \
         "VALUES"            + "\n" + \
         "( bill_s.nextval"  + "\n" + \
         ", :bBillNumber"    + "\n" + \
         ", :bBillText )"

  # Parse the statement by replacing line returns with a single
  # whitespace, replacing multiple whitespaces with single spaces.
  stmt = re.sub('\s+',' ',stmt.replace('\n',' ').replace('\r',''))

  # Declare a dynamic statement.
  cursor.execute(stmt, bBillNumber = sBillNumber         \
                     , bBillText = sBillText )

  # Create a cursor.
  cursor = db.cursor()

  # Execute an INSERT statement.
  stmt = "INSERT"              + "\n" + \
         "INTO   detail"       + "\n" + \
         "( detail_id"         + "\n" + \
         ", detail_number"     + "\n" + \
         ", detail_text )"     + "\n" + \
         "VALUES"              + "\n" + \
         "( detail_s.nextval"  + "\n" + \
         ", bill_s.currval"    + "\n" + \
         ", :bDetailNumber"    + "\n" + \
         ", :bDetailText )"

  # Parse the statement by replacing line returns with a single
  # whitespace, replacing multiple whitespaces with single spaces.
  stmt = re.sub('\s+',' ',stmt.replace('\n',' ').replace('\r',''))

  # Declare a dynamic statement.
  cursor.execute(stmt, bDetailNumber = sDetailNumber         \
                     , bDetailText = sDetailText )

  # Commit the inserted value.
  db.commit()
 

except cx_Oracle.DatabaseError, e:
  error, = e.args
  # Rollback the transaction.
  db.rollback()
  print >> sys.stderr, "Oracle-Error-Code:", error.code
  print >> sys.stderr, "Oracle-Error-Message:", error.message
 
finally:
  # Close cursor and connection.
  cursor.close()
  db.close()

The insertsToTables.py program begins a transaction, commits the inserts when both are successful, and rollbacks one insert statement or both insert statements when an error occurs. The begin() function starts the transaction context, which acts like a SAVEPOINT. The insertsToTables.py program keeps things as simple as possible by reusing the same cursor variable.

You can run the insertsToTables.py program and then query the bill and detail tables to see if the Python program works:

COL bill_id        FORMAT 9999    HEADING "Bill|ID #"
COL bill_number    FORMAT A10     HEADING "Bill|Number"
COL bill_text      FORMAT A10     HEADING "Bill Text"
COL detail_id      FORMAT 999999  HEADING "Detail|ID #"
COL detail_number  FORMAT A10     HEADING "Detail|Number"
COL detail_text    FORMAT A10     HEADING "Detail Text"
SELECT b.bill_id
,      b.bill_number
,      b.bill_text
,      d.bill_id
,      d.detail_id
,      d.detail_number
,      d.detail_text
FROM   bill b JOIN detail d
ON     b.bill_id = d.bill_id;

It should return the following result:

 Bill  Bill                  Bill  Detail  Detail     Detail
 ID #  Number     Bill Text  ID #  ID #    Number     Text
 ----- ---------- ---------- ----- ------- ---------- ----------
     1 2016-001   Invoice        1       1 01         Mileage

The begin(), commit(), and rollback() functions are members of the Connection object in the cx_Oracle library. You can test a scenario where the transaction is rolled back by changing the value of the sDetailText variable to string longer than 20 characters.

A failure test case with a long string raises the following error:

Oracle-Error-Code: 12899
Oracle-Error-Message: ORA-12899: value too large for column "STUDENT"."DETAIL"."DETAIL_TEXT" (actual: 38, maximum: 20)

As the scope of transactions increase, the complexity of your Python grows. You can actually shorten the profile of your Python by writing stored procedures or functions. The next two sections show you how to call stored procedures and functions from Python.

How you write a transaction using a PL/SQL stored procedure

This section refactors the transaction control and the two insert statements into a PL/SQL stored procedure. The logic transfers from the prior Python program into the insert_bill_detail procedure.

The logic of the refactored create_insert_bill_detail procedure follows:

SQL> CREATE PROCEDURE insert_bill_detail
  2  ( pv_bill_number   VARCHAR2
  3  , pv_bill_text     VARCHAR2
  4  , pv_detail_number  VARCHAR2
  5  , pv_detail_text   VARCHAR2 ) IS
  6
  7  BEGIN
  8    /* Declare a save point. */
  9    SAVEPOINT all_or_nothing;
 10

 11    /* Insert into the bill table. */
 12    INSERT
 13    INTO   bill
 14    ( bill_id
 15    , bill_number
 16    , bill_text )
 17    VALUES
 18    ( bill_s.nextval
 19    , pv_bill_number
 20    , pv_bill_text );
 21
 22    /* Insert into the detail table. */
 23    INSERT
 24    INTO   detail
 25    ( detail_id
 26    , bill_id
 27    , detail_number
 28    , detail_text )
 29    VALUES
 30    ( detail_s.nextval
 31    , bill_s.currval
 32    , pv_detail_number
 33    , pv_detail_text );
 34
 35    /* Commit both insert statements. */
 36    COMMIT;
 37  EXCEPTION

 38    WHEN OTHERS THEN
 39   ROLLBACK;
 40  END;

 41  /

The insertProcedure.py calls the procedure insert_bill_detail procedure. The call mechanics differ quite a bit from binding variables into statement strings, like you do for INSERT, UPDATE, and DELETE statements. You should find the approach much simpler to use.

The following insertsTransactionProcedure.py program shows you how to call a stored procedure:

# Import the Oracle library.
import cx_Oracle
import re
import sys

# Declare variables.
sBillNumber = '2016-002'
sBillText = 'Invoice'
sDetailNumber = '01'
sDetailText = 'Mileage'

try:
  # Create a connection.
  db = cx_Oracle.connect("student/student@xe")

  # Create a cursor.
  cursor = db.cursor()

  # Call a stored procedure.
  cursor.callproc( 'insert_bill_detail' \
                 , ( sBillNumber        \
                   , sBillText          \
                   , sDetailNumber      \
                   , sDetailText   ))

except cx_Oracle.DatabaseError, e:
  error, = e.args
  print >> sys.stderr, "Oracle-Error-Code:", error.code
  print >> sys.stderr, "Oracle-Error-Message:", error.message
 
finally:
  # Close cursor and connection.
  cursor.close()
  db.close()

The callproc() function takes a string literal for the stored procedure name and a list of parameters that match the positional order of the procedure’s parameters. The call to the insert_bill_detail stored procedure is more straightforward than binding values into INSERT, UPDATE, and DELETE statements.

The foregoing call uses a list of actual values. That’s because there are only four parameters. You would want to submit a sequence to the callproc() function when there are more values to pass to the procedure.

You can verify that the call to the insert_bill_detail procedure found in the insertsTransactionProcedure.py program works with the following query:

COL bill_id        FORMAT 9999    HEADING "Bill|ID #"
COL bill_number    FORMAT A10     HEADING "Bill|Number"
COL bill_text      FORMAT A10     HEADING "Bill Text"
COL detail_id      FORMAT 999999  HEADING "Detail|ID #"
COL detail_number  FORMAT A10     HEADING "Detail|Number"
COL detail_text    FORMAT A10     HEADING "Detail Text"
SELECT b.bill_id
,      b.bill_number
,      b.bill_text
,      d.bill_id
,      d.detail_id
,      d.detail_number
,      d.detail_text
FROM   bill b JOIN detail d
ON     b.bill_id = d.bill_id;

It should return the following result when you’ve performed all of the test cases in this article:

 Bill  Bill                  Bill  Detail  Detail     Detail
 ID #  Number     Bill Text  ID #  ID #    Number     Text
 ----- ---------- ---------- ----- ------- ---------- ----------
    1 2016-001    Invoice    1     1 01       Mileage
    2 2016-002    Invoice    2     2 01       Mileage

As the parameter list of stored procedures grows, passing individual values could become tedious to code. You have the ability of simply passing a position specific sequence of values to the callproc() function, like the example in the insertsTransactionProcedure2.py program:

# Import the Oracle library.
import cx_Oracle
import re
import sys

# Declare variables.
sBillNumber = '2016-003'
sBillText = 'Invoice'
sDetailNumber = '01'
sDetailText = 'Mileage'

# Create a sequence for a procedure call.
param = (sBillNumber, sBillText, sDetailNumber, sDetailText)

try:
  # Create a connection.
  db = cx_Oracle.connect("student/student@xe")

  # Create a cursor.
  cursor = db.cursor()

  # Call a stored procedure.
  cursor.callproc( 'insert_bill_detail', param)

except cx_Oracle.DatabaseError, e:
  error, = e.args
  print >> sys.stderr, "Oracle-Error-Code:", error.code
  print >> sys.stderr, "Oracle-Error-Message:", error.message
 
finally:
  # Close cursor and connection.
  cursor.close()
  db.close()

The param sequence holds the list of parameters in the right order. You pass the param sequence as the second parameter to the callproc() function. You probably agree this is the simpler way to call a procedure. It may even be the best practice.

Assuming you’ve run all three tests, the diagnostic query at the end of the create_billing.sql script should display the following:

 Bill Bill                   Bill  Detail Detail
 ID # Number     Bill Text   ID #    ID # Number     Detail Tex
----- ---------- ---------- ----- ------- ---------- ----------
    1 2016-001   Invoice        1       1 01         Mileage
    2 2016-002   Invoice        2       2 01         Mileage
    3 2016-003   Invoice        3       3 01         Mileage

This section has shown you how to call a stored procedure and insert data in two related tables. It has shown you how to embed transaction control language inside your Python program and how to shift that control to a stored procedure.

How you write a transaction using a PL/SQL stored function

This section also refactors the transaction control and the two insert statements. However, it refactors the logic into a PL/SQL stored function. The logic from the insert_bill_detail procedure transfers directly into the insert_bill_detail_func function.

The insert_bill_detail_func function returns a 0 when there isn’t an error and a positive number when there is an error. The insert_bill_detail_func returns a 1 for a program logic error and the positive number of any ORA- error code.

The following is the insert_bill_detail_func function:

SQL> CREATE FUNCTION insert_bill_detail_func
  2  ( pv_bill_number   VARCHAR2
  3  , pv_bill_text     VARCHAR2
  4  , pv_detail_number  VARCHAR2
  5  , pv_detail_text   VARCHAR2 ) RETURN NUMBER IS
  6 
  7    /* Decalre a return variable. */
  8    lv_return  NUMBER := 0;
  9 

 10  BEGIN
 11    /* Declare a save point. */
 12    SAVEPOINT all_or_nothing;
 13 
 14    /* Insert into the bill table. */
 15    INSERT
 16    INTO bill
 17    ( bill_id
 18    , bill_number
 19    , bill_text )
 20    VALUES
 21    ( bill_s.nextval
 22    , pv_bill_number
 23    , pv_bill_text );
 24 
 25    /* Insert into the detail table. */
 26    INSERT
 27    INTO detail
 28    ( detail_id
 29    , bill_id
 30    , detail_number
 31    , detail_text )
 32    VALUES
 33    ( detail_s.nextval
 34    , bill_s.currval
 35    , pv_detail_number
 36    , pv_detail_text );
 37  
 38    /* Commit both insert statements. */
 39    COMMIT;
 40 
 41    /* Return the default return value. */
 42    RETURN lv_return;
 43  EXCEPTION
 44    WHEN OTHERS THEN
 45   lv_return := ABS(SQLCODE);
 46   ROLLBACK;
 47   RETURN lv_return;
 48  END;

 49  /

The insert_bill_detail_func function returns a number. Line 8 declares the default value when the function is successful. Lines 45 through 47 sets the value to a positive number, rolls back any partial successful insert, and returns the error code value.

The following insertsTransactionFunction.py program calls the stored function and manages the return value. This example uses a sequence for the list of parameter values to the function, like the last procedure example in the prior section.

# Import the Oracle library.
import cx_Oracle
import re
import sys

# Declare variables.
sBillNumber = '2016-005'
sBillText = 'Invoice'
sDetailNumber = '01'
sDetailText = 'Mileage'

# Hold return value.
fRetVal = 0

# Create a sequence for a procedure call.
param = (sBillNumber, sBillText, sDetailNumber, sDetailText)

try:
  # Create a connection.
  db = cx_Oracle.connect("student/student@xe")

  # Create a cursor.
  cursor = db.cursor()

  # Call a stored procedure.
  fRetVal = cursor.callfunc( 'insert_bill_detail_func', cx_Oracle.NUMBER, param)

  # Check for successful function call or failure number.
  if fRetVal == 0:
    print "Success"
  else:
    print "Failure [" + str(int(fRetVal)) + "]"

except cx_Oracle.DatabaseError, e:
  error, = e.args
  print >> sys.stderr, "Oracle-Error-Code:", error.code
  print >> sys.stderr, "Oracle-Error-Message:", error.message
 
finally:
  # Close cursor and connection.
  cursor.close()
  db.close()

The insertsTransactionFunction.py adds a fRetVal return variable and initializes it’s value as an integer. The call to the callfunc() function includes a new twist, the data type of the return value. The cx_Oracle object defines the valid Oracle data types. In this case, the function is returning a number. The cx_Oracle.NUMBER data type maps to a double in Python, and that’s why it’s later cast as an integer before printing it as a string.

Assuming you’ve run all four tests, the diagnostic query at the end of the create_billing.sql script should display the following:

 Bill Bill                   Bill  Detail Detail
 ID # Number     Bill Text   ID #    ID # Number     Detail Tex
----- ---------- ---------- ----- ------- ---------- ----------
    1 2016-001   Invoice        1       1 01         Mileage
    2 2016-002   Invoice        2       2 01         Mileage
    3 2016-003   Invoice        3       3 01         Mileage
    4 2016-004   Invoice        4       4 01         Mileage

This section has shown you how to call a stored function and manage a successful or unsuccessful return value. It also showed introduced you to the type matching between the Oracle database and cx_Oracle library.

This article should have shown you how to effectively work with transactions and stored procedures and functions. You can find the scripts on the github.com server.