The Oracle module allows a CLISP program to act as client to an Oracle database server. The module includes full SQL support, transactions (including auto-commit), support for most Oracle data types (LONG, BLOB, CLOB, RAW, etc.), automatic conversion between Oracle and Common Lisp data types, database connection caching and retry, concurrent connections to multiple databases, proper handling of Oracle errors, and more.
The module can be used to build sophisticated Oracle database applications in Common Lisp.
When this module is present, *FEATURES*
contains the
symbol :ORACLE
.
Access to Oracle is via these functions and macros in
package “ORACLE”.
When any Oracle function fails, the general Lisp function
ERROR
is called, with the condition string set to
include the Oracle error number, the Oracle message text,
and other context of the error (e.g., the text and parse location of a
SQL query).
(ORACLE:CONNECT
user
password
server
&OPTIONAL
schema
auto-commit
prefetch-buffer-bytes
long-len
truncate-ok
)
Connect to an Oracle database. All subsequent operations will affect
this database until the next call to ORACLE:CONNECT
. A
single program can access different Oracle schemas concurrently by
repeated calls to ORACLE:CONNECT
. Database connections
are cached and re-used: if you call ORACLE:CONNECT
again
with the same user
,
schema
, and
server
, the previous Oracle connection will
be re-used. ORACLE:CONNECT
may not be called inside
WITH-TRANSACTION
.
Returns: T
if a cached connection was re-used, NIL
if a new
connection was created (and cached).
The meaning of the arguments is as follows:
Arguments for ORACLE:CONNECT
user
password
NIL
if
user
has no password (!).
server
schema
NIL
).
If NIL
, same as user. This allows you to log on with one user's
id/password but see the database as if you were some other user.
auto-commit
T
). Set this to NIL
if you intend to do transactions
and call COMMIT
explicitly. However,
WITH-TRANSACTION
is probably easier.
prefetch-buffer-bytes
long-len
truncate-ok
(below). Setting long-len
to zero and
truncate-ok
to NIL
will disable long
fetching entirely. If long-len
is NIL
or negative, defaults to 500k bytes.truncate-ok
long-len
bytes on fetch; otherwise, fetches
of LONG columns exceeding long-len
bytes
will raise an error. Default: NIL
.(ORACLE:DISCONNECT)
ORACLE:CONNECT
is called again. The
connection is closed and removed from the connection cache. Does
nothing if there is no connection. DISCONNECT
may not be called inside WITH-TRANSACTION
.
Returns NIL
.
(ORACLE:RUN-SQL
sql
&OPTIONAL
params
is-select
)
Execute a SQL statement. Must be ORACLE:CONNECT
ed
to a database. Returns the number of rows affected by the SQL operation,
for non-SELECT statements, zero for SELECT statements. For
destructive database operations (INSERT, UPDATE, DELETE), the results
are committed to the database immediately if
auto-commit
when establishing the current
connection; see ORACLE:CONNECT
. The meaning of the
arguments is as follows:
Arguments for RUN-SQL
sql
sql
statement may contain
Oracle "named parameters," e.g. ":myparam" whose values will
be substituted from the parameters given
in params
.
params
params
.
The mapping may be passed as either (1) a hash table whose keys are
the named parameters or (2) a list of pairs, ((name value) (name
value) ...). Parameter values passed from Lisp are converted to the
appropriate Oracle data types (see FETCH
).
is-select
(ORACLE:DO-ROWS
vars
&BODY
body
)
Macro which loops over a SQL SELECT result,
evaluating, for each row in the result, the forms in body
,
binding symbols given in vars
to
corresponding database columns in the SELECT result. The
argument vars
must be a non-empty list of
symbols matching a subset of the columns of an active SELECT query.
If a SELECT column is an Oracle expression such as
SUBSTR(mycol, 1, 10)
, it is recommended to use a
column alias, e.g., SELECT SUBSTR(mycol, 1, 10) AS
myvar
, in which case the column alias will be used as the
symbol bound to the column value.
As DO-ROWS
expands into a DO*
loop, it may be terminated prematurely, before all rows are fetched,
by using RETURN
anywhere in body
.
It is allowed to call ORACLE:CONNECT
in the
body
of the loop, but only to switch the connection to a database
other than the one that was used to do the SELECT. This is useful
for reading from one database while writing to another.
In vars
, instead of a single
symbol, a pair (bound-var
"column-name"
) may be specified, which
will cause values from the SELECTed column or alias ,
column-name
, to be bound to Lisp variable,
bound-var
. This is for unusual cases
where a Lisp variable cannot be created with the same name as the
column (e.g., a column named "T"), or when it is inconvenient or
impossible to alias the column with SELECT ... AS
.
(ORACLE:FETCH
&OPTIONAL
result-type
)
Fetch a single row of data. Returns a row of values
corresponding to the columns of an active SELECT statment. The row
data is returned in one of three different forms, depending on the
value of the symbol result-type
:
Return values for FETCH
ARRAY
ARRAY
with the
same number of columns as in the SELECT statement, in the same
order. This is the default.PAIRS
((column, value)
...)
is be returned. The number and order of pairs is
the same as the columns in the SELECT statement.
HASH
HASH-TABLE
whose keys are the column names
and whose values are the column values in the row. The SELECT
columns must be unique and be valid Lisp
symbols to use this option. If you are SELECTing an expression, you
probably want to use a column alias: SELECT <expr> AS
some_alias ...
The following data type conversions are done between Oracle datatypes and Common Lisp data types:
Oracle type | Converts to/from Common Lisp type |
---|---|
Numeric (NUMBER, INTEGER, FLOAT) | The appropriate Common Lisp numeric type (FIXNUM , BIGNUM ,
FLOAT ) |
String (CHAR, VARCHAR, VARCHAR2) | A Common Lisp STRING . Note that CHAR will be padded out to its
full, fixed length as defined in Oracle; VARCHAR will be a
string of variable length. Also note that Oracle has no
"zero-length string" value - it returns the SQL special value
NULL which is converted to NIL (see below). |
DATE | A string of the form "YYYY-MM-DD HH:MM:SS" where HH is
24-hour form. If you want dates formatted differently, convert
them to strings in Oracle using SELECT
TO_CHAR(mydate, ' ; the result will then be returned as a string,
formatted as per template . |
RAW, LONG RAW | A hexadecimal string, with two hex digits for each byte of Oracle data. Note that this means the Lisp string will be twice the size, in bytes, as the Oracle data. |
"Large" types (LONG, BLOB, CLOB) | A Lisp string of (arbitrary, possibly binary) data. Note
that truncation may occur; see the ORACLE:CONNECT
parameters long-len
and truncate-ok . |
NULL | The Common Lisp value NIL |
(ORACLE:FETCH-ALL
&OPTIONAL
max-rows
result-type
item-type
)
Fetch some or all the rows from a query and return
result as a sequence of sequences. Arguments are all optional:
max-rows
limits the result to
that numbers of rows;
result-type
is the type of
sequence of the rows, either
'ARRAY
(the default) or
'LIST
;
item-type
is the type of
sequence of the column values for each row, either
'ARRAY
(the default) or
'LIST
.
Each row fetched always contains the full set of column values SELECTed.
FETCH-ALL
is often useful in conjunction with
MAP
or REDUCE
to iterate
over an entire SELECT result to construct a single Lisp value.
(ORACLE:PEEK &OPTIONAL
result-type
)
FETCH
, except does not advance to the next row.
Repeated calls to PEEK
will thus return the same
row of data. Returns NIL
if at EOF. If data is available, returns
row data just as FETCH
(see
FETCH
for data format and conversions done).
Optional argument result-type
is the type
of sequence of the column values for the returned row, either
ARRAY
(the default) or LIST
.
PEEK
is a useful look-ahead
for database reporting functions that may need to "break" on changes in
data to print headers, summaries, etc.
(ORACLE:COLUMNS)
Returns information on the columns of a SELECT
result, in the form of an array of SQLCOL structures, one for each
result column in the most recent SELECT statement. It is not
necessary to have called FETCH
before requesting
column information on the query, however the query must have been
compiled and executed with RUN-SQL
. Each SQLCOL
structure has these slots:
Slots of SQLCOL
SELECT
expr
AS
alias
, then
alias
will be returned as the column name.
T
if NULL
s allowed, NIL
if NULL
s are
not allowed.To access the values of the SQLCOL structures, use the standard
accessor functions, e.g., (ORACLE:SQLCOL-NAME (elt
(ORACLE:COLUMNS) 0))
(ORACLE:EOF)
(ORACLE:INSERT-ROW
table
values
)
table
.
Second argument values
is a map of
column names to values: either a hash table whose keys are the column
names, or a list of (name, value) pairs. Columns missing from the map
will be given the default Oracle value, or NULL
.
Returns the number of rows inserted (i.e., always 1).
(ORACLE:UPDATE-ROW
table
condition
vals
&OPTIONAL
params
)
table
. Second argument
condition
is a string expression for a WHERE
clause (without the "WHERE") which determines which rows are updated.
Third argument vals
is a map of columns to
be updated to their new values: a hash table whose keys are column
names, or list of (name, value) pairs. Optional
params
specifies values for named
parameters that may occur in condition
,
e.g., when the condition is a match on a primary key, e.g.: "pk_column
= :pk_val"
. Returns the number of rows updated.
(ORACLE:ROW-COUNT)
FETCH
ed (not
PEEK
ed) so far. For other statements (e.g.,
INSERT, UPDATE, DELETE), returns the number of rows affected by the
last operation (e.g., inserted, updated, deleted). Must be connected
to a database and have an active SQL statement.
(ORACLE:WITH-TRANSACTION
&BODY
body
)
body
atomically as a
database transaction, ensuring that either all the database operations
done in body
complete successfully, or none
of them do. If pending (un-committed) changes exist when this macro
is entered, they are rolled back (undone), so
that the database is affected only by the subsequent updates inside
body
. Nesting of
WITH-TRANSACTION
blocks is not allowed and will
raise an error. There is no effect on the status of
auto-commit
given in
ORACLE:CONNECT
; it resumes its previous state when the
macro exits. The value of the WITH-TRANSACTION
expression is that of the last form in body
.
(ORACLE:COMMIT)
auto-commit
parameter to
ORACLE:CONNECT
must not have been set to use this
function, nor can it be called inside a
WITH-TRANSACTION
block. Always returns NIL.
(ORACLE:ROLLBACK)
auto-commit
parameter to
ORACLE:CONNECT
must not have been set to use this
function, nor can it be called inside a
WITH-TRANSACTION
block. Always returns NIL.
(ORACLE:AUTO-COMMIT)
auto-commit
initially
given to ORACLE:CONNECT
for the current connection.
With auto-commit
enabled, modifications to
the database are committed (made permanent) after each destructive SQL
operation made with calls to RUN-SQL
,
INSERT-ROW
, UPDATE_ROW
, etc.
With auto-commit
disabled, transactional
integrity is under the programmer's control and is managed either by
(1) explicitly calling COMMIT
or
ROLLBACK
to commit or undo the pending
operations, or (2) wrapping code blocks with database operations
inside the WITH-TRANSACTION
macro.
AUTO-COMMIT
returns the previous status of
auto-commit
.
AUTO-COMMIT
may not be called inside
WITH-TRANSACTION
.
Below is a simple example script which uses Oracle's demo database
schema, SCOTT
.
(setf server "orcl") ; ; Change this to your server's SID
(oracle:connect "scott" "tiger" server)
(oracle:run-sql "SELECT deptno, dname, loc FROM dept ORDER BY DNAME")
(oracle:do-rows (deptno dname loc)
(format t "Dept. no is '~A', " deptno)
(format t "Dept. name is '~A', " dname)
(format t "Dept. loc is '~A'~%" loc))
(oracle:update-row "dept" "dname = :acctval" '(("dname" "NEWACCT")) '(("acctval" "ACCOUNTING")))
(oracle:run-sql "SELECT deptno, dname, loc FROM dept ORDER BY DNAME")
(oracle:do-rows (deptno dname loc)
(format t "Dept. no is '~A', " deptno)
(format t "Dept. name is '~A', " dname)
(format t "Dept. loc is '~A'~%" loc))
(oracle:update-row "dept" "dname = :acctval" '(("dname" "ACCOUNTING")) '(("acctval" "NEWACCT")))
Obviously, a working Oracle environment is required. It is
recommended that you first be able to log on and use the Oracle
SQL*Plus application to test your environment
before attempting Oracle access via the CLISP module.
At a minimum you will need to set environment variables
ORACLE_HOME
to the Oracle base directory and
LD_LIBRARY_PATH
to include
$
and possibly other
directories.ORACLE_HOME
/lib
The module uses the Oracle Call Interface (OCI)
C library. To build the module you will need the Oracle
OCI headers and link libraries; as a quick check, make sure
you have the file oci.h
somewhere
under ORACLE_HOME
, probably
in $
.ORACLE_HOME
/rdbms/demo/oci.h
To build the module into CLISP, configure with
./configure ... --with-module=oracle ....
The full linking set will contain the module,
so you will need to use the -K
option to use it.
You can test that you really have the Oracle-enabled CLISP by
evaluating (
.DESCRIBE
'oracle:connect)
It may be necessary to edit file
modules/oracle/Makefile
prior to running ./configure.
These notes document CLISP version 2.49 | Last modified: 2010-07-07 |