Tom
------------------------------------------------------------------------
# oramodule.doc
#
# Copyright 1994 Thomas J. Culiton
#
# Permission to use, copy, modify, and distribute this software and its
# documentation for any purpose and without fee is hereby granted, provided
# that the above copyright notice appear in all copies.
# Thomas J. Culliton makes no representations about the suitability of
# this software for any purpose. It is provided "as is" without express
# or implied warranty. By use of this software the user agrees to
# indemnify and hold harmless Thomas J. Culliton from any claims or
# liability for loss arising out of such use.
The quick cheat sheet on the Python Oracle module
To get started:
import oracle
Functions:
con = oracle.newconnection("uid/pswd") - returns a new connection
con = oracle.logon("nobody/nothing") - ditto
oldmax = oracle.maxlong(10000) - set the max long size
The string passed to the newconnection or logon method should be a valid
Oracle logon specifier with user id, password and possibly server name,
the value returned is a connection object. You should be able to have
multiple connections to multiple databases. The maxlong method takes an
(long) int size to set a limit on fetching of Oracle long fields, it
returns the old limit. A negative value is ignored but still returns
the current value. (OK, so it's hacky...)
You should always set the maxlong value to something reasonable. With
the 16 bit limit under v6 and a 32 bit address space this isn't a big
problem, but the 32 bit limit of v7 is. I'm tempted to set a smallish
default like 4k and force you to increase it just to avoid the problems
that this will almost certainly cause otherwise.
Classes:
The connection class methods
con.logoff() - break the connection
cur = con.opencursor() - returns a new cursor
con.commit() - confirm changes
con.rollback() - remove changes
con.autocommit("on") - "on" or "off"
res = con.code() - get Oracle result code
The opencursor method returns a new cursor object associated with the
connection. The autocommit method takes a string value of "on" or "off"
and sets the transaction handling accordingly. The initial value is
"off".
The cursor class methods
cur.close() - close the cursor
cur.execsql("select * from mytable where x is 50")
tup = cur.fetch() - get the next record
tt = cur.columns() - get the record description
tt = cur.describe() - ditto
cur.cancel() - cancel the pending actions
cur.execpl() - Stubbed out...
cnt = cur.rows() - returns the number of rows processed
res = cur.code() - get Oracle result code
The execsql method takes a string which is a valid SQL statement or
query. The fetch() method returns a tuple of the field values. Null
fields are set to the Python special value None. If no more data is
available, fetch returns None. The columns or describe method returns a
tuple of 7-tuples containing the column description data. The rows
and code methods both return an int.
In order the column description 7-tuples members are:
name - string
type code - int
display size - int
internal size - int
precision - int
scale - int
nullok - int
Most Oracle types are encoded as strings, except numbers which use the
appropriate Python numeric type (int, long int, or float). Dates may
suffer from Oracles default 2 digit year display format as the year 2000
approaches, but you can always fix the select statement to use a better
format.
Exceptions:
In general if any of the Oracle functions or class methods can't do what
you ask for some reason they throw an exception, usually oracle.error.
Given Python's easy exception handling this is probably OK. Other
common possibilities include MemoryError when you run out of space, and
ValueError or TypeError when you botch the arguments to a method or
function.
To Do List:
- (DONE) Return None from fetch on EOD rather than throwing an
exception.
- (DONE) Add a "code" method to the cursor and connection classes to
return the Oracle result code. Mostly useful in exception handling...
- Multi-fetch. I don't want to hassle with buffering and fudging rows.
Instead you'll have to specify a number of records to fetch for the
cursor (with some arbitrary max) before doing execsql, and then fetch
will return a list or tuple of tuples.
- Make newconnection and opencursor more "constructor like"?
- Implement or remove the stub execpl.
- Use variables (getatter/setattr) for access to maxlong, code, rows and
possibly others?
Bugs:
- (FIXED) Apparently there is no clean way using the Oracle V6 OCI to get the
precision, scale, and nullok information for the columns. The macros
inherited from the TCL bindings to make the V6 calls look like the V7
calls quietly drop these variables. It isn't even obvious if you can
do a SQL query to get the information. This leaves us with a nasty
bug. Under V6 all numeric types are treated as python long ints
because we use this information internally and it's WRONG. I don't
know what to do about this... Look for a sneaky way to get the info?
Have the database convert the numbers to strings and then try to parse
them? Give up on V6 since it's obsolete? ???
Under V6 we ask Oracle to export all numbers as strings and then we
parse them back into real Python numbers. The only glitch is that the
type depends on the value. For example 47.0 which is a float becomes
47 which is an int. This is mostly because of the way Oracle formats
them. You can always use explicit type coercion if it matters. Of
course the last three values in each column description tuple are
still bogus under V6.
- The module is sensitive to the order of some operations. If you
logoff of a connection before closing the associated cursors they may
cause a problem when they do close. One user got a segmentation fault
when he exited the interpreter after logging off of a connection and
not closing it's cursors first. This is just a guess at the moment.
------------------------------------------------------------------------------
GENERAL COMMENTS
Binding - This module does not support binding to Python variables. You
must generate the SQL statements that you want to use as strings, like
this:
sql = "select * from employee where sal_grade = %d" % grade
Once you have done a select, each fetch returns a tuple, at which point
you can do an assignment like this:
rm_num, bldng, occupant = cur.fetch()
This helps keep the module simple, and in practice it works quite well.
There are all sorts of hairy issues here related to the nature of Python
variables and dangling pointer references that we're better off without.
Buffering - I've thought about doing some kind of buffering on fetches,
or allowing the user to specify how many records to fetch at once, and
decided not to bother on several occasions. Fetching one record at a
time is simple and we haven't seen a problem with speed. (As a test we
selected, fetched, and discarded the cross product of a large table with
many entries. It happened fast enough that we decide it wasn't even an
issue.)
LONG's - ALWAYS SET MAXLONG TO SOMETHING REASONABLE!!! Please make sure
that you take care of this. If you don't, especially with Oracle 7,
you'll spend a lot of time wondering why you're getting a MemoryError
when you execsql a select statement which includes an Oracle long field.
I've thought about setting a low default like 4k and forcing you to
increase it, but that would cause a much less obvious problem with
truncation.
Oracle 6 - The Oracle 6 OCI doesn't provide enough information about
numeric columns to let us select the right Python type to return. If
someone knows a clever way to get the scale and precision I'd like to
hear it. Lacking that the module asks Oracle to export the value as a
string and parses it back into an "appropriate" numeric type. Since
Python is pretty good about mixing numeric types this doesn't usually
cause a problem. When the precise type of a numeric column matters, you
may have to coerce it. Also note that the last three values in the
column description 7-tuple do not contain useful data under Oracle 6.
Miscellaneous - The method names probably aren't ideal.
Many Thanks - To Guido for Python, Jack Jansen for Modulator, Tom
Poindexter for oratcl, and Eric Newton for suggesting and testing.
These folks deserve most of the credit (although none of the blame) for
what you see here. Any place I say "we" above you can assume that I'm
trying to implicate poor Eric. ;-)