33.9. Oracle Interface

33.9.1. Functions and Macros in package ORACLE
33.9.2. Oracle Example
33.9.3. Oracle Configuration
33.9.4. Building the Oracle Interface

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.

33.9.1. Functions and Macros in package 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

Oracle user ID
Password for user, or NIL if user has no password (!).
Oracle server ID (SID).
Oracle default schema (default: 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.
Flag: whether to commit after every operation (default: T). Set this to NIL if you intend to do transactions and call COMMIT explicitly. However, WITH-TRANSACTION is probably easier.
Number of bytes to cache from SQL SELECT fetches (default: 64 Kbytes) If you are very short of memory, or have a slow connection to Oracle, you can reduce this to 10k or so. Alternatively, if you have a fast connection to Oracle and regularly do large queries, you can increase throughput by increasing this value.
Number of bytes to fetch for "long" (LONG, [BC]LOB) types. Long data that exceeds this size will raise an error, or be truncated depending on the value of 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.
Flag: if set, allow truncation of LONG columns to long-len bytes on fetch; otherwise, fetches of LONG columns exceeding long-len bytes will raise an error. Default: NIL.
Disconnect from the database currently connected. No more calls can be made until 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

Text of SQL statement, as a string. The sql statement may contain Oracle "named parameters," e.g. ":myparam" whose values will be substituted from the parameters given in params.
A mapping of the names of the bind-parameters in the query to their values. The set of named parameters in the query must match exactly the keys mapped by 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).
Flag: whether the statement is a SELECT query. You usually do not need to set this as it is detected by default based on the SQL text. However, there are situations, such as when a SELECT query begins with comment, that you need to specify it explicitly.
(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.


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

Values will be returned in an ARRAY with the same number of columns as in the SELECT statement, in the same order. This is the default.
A list of pairs, ((column, value) ...) is be returned. The number and order of pairs is the same as the columns in the SELECT statement.
A 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 typeConverts 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).
DATEA 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, 'template') AS mydate; the result will then be returned as a string, formatted as per template.
RAW, LONG RAWA 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.
NULLThe 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.

Peek at next row of data (without fetching it). Returns a row a la 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.

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

The Oracle column name or the expression selected. If the query used a column alias, SELECT expr AS alias, then alias will be returned as the column name.
Oracle data type (VARCHAR, NUMBER, DATE, ...)
Oracle data length (useful mostly for character types)
For numeric (NUMBER) types, number of digits to right of decimal; NIL for FLOAT
For numeric types, total number of significant digits (decimal digits for NUMBER, bits for FLOAT)
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))

Returns EOF status. A SELECT query cursor is considered at EOF if the next FETCH would return no data. Must be connected to a database, and have an active SELECT statement.
(ORACLE:INSERT-ROW table values)
Inserts a single row into 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)
Updates rows in 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.
For SELECT statements, returns the number of rows 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.
Evaluates the forms in 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.
Commits (makes permanent) any pending changes to the database. The 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.
Rolls back (undoes and abandons) any pending changes to the database. The 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.
Toggles the state of 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.

33.9.2. Oracle Example

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")))

33.9.3. Oracle Configuration

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 $ORACLE_HOME/lib and possibly other directories.

33.9.4. Building the Oracle Interface

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.49Last modified: 2010-07-07