33.9. Oracle Interface
Prev | Chapter 33. Extensions Implemented as Modules | Next

## 33.9. 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`

  `user`
    Oracle user ID
  `password`
    Password for user, or `NIL` if `user` has no password (!).
  `server`
    Oracle server ID (SID).
  `schema`
    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.
  `auto-commit`
    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.
  `prefetch-buffer-bytes`
    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.
  `long-len`
    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.
  `truncate-ok`
    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`.
`(ORACLE:DISCONNECT)`
  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: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`
    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`.
  `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`).
  `is-select`
    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`.

`(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`
    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.
  `PAIRS`
    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.
  `HASH`
    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 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, '`template`') AS 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`)`
  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.
`(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`

  NAME
    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.
  TYPE
    Oracle data type (VARCHAR, NUMBER, DATE, ...)
  SIZE
    Oracle data length (useful mostly for character types)
  SCALE
    For numeric (NUMBER) types, number of digits to right of decimal; NIL for FLOAT
  PRECISION
    For numeric types, total number of significant digits (decimal digits for NUMBER, bits for FLOAT)
  NULL_OK
    `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)`
  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.
`(ORACLE:ROW-COUNT)`
  For SELECT statements, returns the number of rows `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`)`
  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`.
`(ORACLE:COMMIT)`
  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.
`(ORACLE:ROLLBACK)`
  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.
`(ORACLE:AUTO-COMMIT)`
  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)`.

### Note

It may be necessary to edit file `modules/oracle/Makefile` prior to running ./configure.

| These notes document CLISP version 2.45 | Last modified: 2008-05-14

Enter your search termsSubmit search form
Webclisp.cons.orgclisp.podval.orgwww.lisp.org

Prev | Up | Next
33.8. PostgreSQL Database Access | Home | 33.10. LibSVM Interface