![[CLISP home]](clisp.png){kind=small} | ![[Support CLISP]](http://images.sourceforge.net/images/project-support.jpg){kind=small} |
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
userpasswordNIL if
user has no password (!).
serverschemaNIL).
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-commitT). Set this to NIL if you intend to do transactions
and call COMMIT explicitly. However,
WITH-TRANSACTION is probably easier.
prefetch-buffer-byteslong-lentruncate-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-oklong-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:CONNECTed
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
sqlsql statement may contain
Oracle "named parameters," e.g. ":myparam" whose values will
be substituted from the parameters given
in params.
paramsparams.
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
ARRAYARRAY 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.
HASHHASH-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 NULLs allowed, NIL if NULLs 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)FETCHed (not
PEEKed) 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 |
| |