ToC | DocOverview | CGDoc | RelNotes | FAQ | Index | PermutedIndex
| Allegro CL version 9.0 Unrevised from 8.2 to 9.0. 8.2 version

# Oracle Interface: Allegro Oracle Direct Connect

This document contains the following sections:

1.0 Oracle Interface introduction
1.1 Prerequisites and implementation details
2.0 Connections to Oracle
2.1 Blocking and non-blocking modes
3.0 The Oracle datatypes
4.0 Cursors
5.0 The Oracle interface API
5.1 API for connecting and disconnecting
5.2 Excuting DML and DDL
5.3 One step execution of an sql statement
5.4 Step by step execution of a sql statement
5.5 The Convert Argument
5.5.1 The syntax of the convert argument
5.5.2 The convert argument values
5.6 Binding parameters
5.7 Multiple statement handles
5.8 Statement handle parameters
5.9 Setting flags
5.10 Obtaining schema information
5.11 Managing cursors
6.0 Examples using the Oracle interface
7.0 Index for Oracle Interface

## 1.0 Oracle Interface introduction

This interface connects Allegro Common Lisp to Oracle (version 9 or higher) using the Oracle C Interface (OCI) libraries from Oracle.

The API of the interface is similar to the Allegro ODBC interface (see aodbc.htm) and the Allegro MySQL Direct interface (see mysql.htm) but it differs where necessary in order to better match Oracle.

## 1.1 Prerequisites and implementation details

### Oracle requirements

In order to use this interface you must have the Oracle Client Libraries installed on your machine and your dynamic loader shell environment variable must include the Oracle Client Library directory.

If you have got an oracle server on your machine and if you can run the sqlplus program then you should be ready to run the Oracle Allegro CL interface. If you don't have the Oracle Client Libraries installed on your machine then go to the download page on www.oracle.com and download the InstantClient libraries. If you're on UNIX then after installing the Instant Client libraries you will want to create a symbolic link from libclntsh.so to the version of libclntsh.so that is included with the libraires (that symbolic link should have been included in the install but wasn't for some reason). On HPUX machines the name of the library is libclntsh.sl instead of libclntsh.so.

### Package and module information

Functions, variables and classes in this interface are exported from the `dbi.oracle` package.

The module name for the Oracle Interface is `:oracle`. To load the module, evaluate

    (require :oracle)

## 2.0 Connections to Oracle

The connect function establishes a connect to a database. A connection is a single channel through which sql commands can be sent and values returned. It is the user program's responsibility to ensure that only one command at a time is sent through a connection. If you want to issue more than one command at a time to the database in different threads then you should create multiple connections.

## 2.1 Blocking and non-blocking modes

A connection is in either blocking or non-blocking mode. You can change the mode of a connection at any time.

In blocking mode Lisp cannot do any processing while waiting for a response from the server. When the response is ready Lisp will resume processing immediately.

In non-blocking mode Lisp will allow other programs to run if it finds that the oracle server does not have an answer immediately. When the Oracle server does respond it may take a little while for Lisp to check on that response.

You want to run in blocking mode if

- You are executing a large number of statements and wish to maximize performance.
- The application is only running the database access code and should the database be slow in responding there's nothing else that the application can do while waiting for the database.

You want to run in non-blocking mode if

- Your application has other active lisp threads that can do useful work while waiting for the database to respond.
- You have more than one connection to the database and there is a possibility that actions on each connection could be modifying the same object. In this case Lisp can freeze if in locking mode if Oracle blocks one connection while waiting for a lock.

## 3.0 The Oracle datatypes

Oracle stores all numbers effectively in character format. That is, a number is stored as a sequence of decimal characters with a decimal point. If the decimal point is at the end, the number is an integer. The maximum number of decimal digits is 38. The biggest integer that can be stored on a 32-bit machine in binary format is just barely 10 decimal digits. Therefore if you're going to be storing or retrieving with very large numbers it is best to use the string representation of numbers

There are four collections of datatypes involved using this interface

> #### 1. The data types used in sql DDL statements to define the columns

> For Oracle there are a set of data types that closely match the internal oracle data types. Also supported are data types defined by standard SQL.

> #### 2. Internal Oracle data types

> These describe the data as it is actually stored in the database.

> #### 3. External Oracle data types.

> These are types in a programming language. In the case of the OCI interface written in C these are C data types. User code in C using the OCI always works in with these external data types and the OCI does the job of converting data between the Internal and External types.

> #### 4. Lisp data types

> The set of lisp data types which store the data from the Oracle database and which can be stored in the Oracle database.

Let us consider the Internal Oracle data types and how those relate to the other 3 types of data types.

### Numeric

There is only one Internal data type for storing numbers in Oracle and that's called simply: Number. A number is stored in binary coded decimal format (equivalent to a character string of decimal numbers) and also includes the location of the decimal point and a sign.

The Precision of a Number is the number of decimal digits. It can range from 1 to 38.

The Scale of a number is the number of the decimal digits that are to the right of the decimal point. The Scale can range from -84 to 127.

In SQL DDL statements you can specify this Internal data type with any of these types:

- number - holds a number with any Precision and Scale in the valid range
- number(p,s) - holds a number with a max precision of p and a scale of exactly s. This is a fixed point number.
- number(p) - the same as number(p,0) which is an integer of a maximum precision.
- float - same as number
- float(b) - number with a maximum number of *binary* digits of b (mult b by 0.30103 to get the number of decimal digits).

### ANS type specifiers:

- numeric(p,s) - same as number(p,s)
- decimal(p,s) - same as number(p.s)
- integer - same as number(38)
- int - same as number(38)
- smallint - same as number(38)
- double precision - same as float(126)
- real - same as number

When information about a column is returned by Oracle the description contains typecode == 2 [see table 3-1 in the OCI programmer's ref] and

Precision | Scale | Description
0 | -127 | for number (any kind of number)
9 | 0 | for number(9) == number(9,0) - 9 digit integer
100 | -127 | float(100) floating point 100 binary digits
38 | 0 | integer or smallint

Thus: scale of -127 indicates a floating point number where precision is the binary precision. A precision of 0 means unspecified so it can vary up to the maximum precision.

Otherwise scale of not -127 means it is a fixed point value with precision being the decimal precision. A scale of 0 means integer.

These are the numeric External data types (as best as we can determine):

- SQLT_NUM - (ORANET TYPE) oracle numeric. This is a char[21] which holds precisely the oracle numeric object in 21 bytes.
- SQLT_INT - (ORANET TYPE) integer. This is a
  - signed char - 1 byte
  - signed short - 2 bytes
  - signed int - 2 bytes or 4 bytes
  - signed long - 4 bytes

  The number of bytes is specified as an additional parameter when SQLT_INT is given as the external type. Note that on a 64 bit machine you can specify 8 bytes as the length but still you can only get a 32 bit value (it is unclear if it zero fills the upper 4 bytes or sign extends or what but it is clear that you cannot get Oracle to give you a 64 bit integer).

- SQLT_FLT - (ORANET TYPE) Floating point number. This is a float or double. We believe that you can distinguish between float and double by an extra parameter giving a size of 4 or 8 bytes.
- SQLT_VNU - NUM with preceding length byte. A char[22] (the same as SQL_NUM except it begins with a length byte showing how many of the following 21 bytes contain real data.
- SQLT_UIN - unsigned integer. We think that this is like SQL_INT except that the values are unsigned rather than signed integer values.

In lisp the data types we will use to represent numbers are:

- fixnum (signed 30 bit on 32-bit machine, signed 61-bit on 64 bit machine)
- bignum: integer of effectively any length
- double: double precision floating point number
- single: single precision floating point number

And the following, using the def-foreign-type facility to create locations for storing raw data:

- integer box - 32 bit signed value
- unsigned integer box - 32 bit unsigned value
- long integer box - 32 bit signed value (32-bit machine); 64 bit signed value (64-bit machine)
- long unsigned integer box - 32 bit unsigned value (32-bit machine); 64 bit unsigned value (64-bit machine)

The following conversions are supported:

- :int - fit into a C int (which is 4 bytes even on 64-bit machines).
- :bigint - any size integer, will read a string and do the conversion ourselves
- :double - will read as double float
- :float, :single - will read as single float
- :string - will return the number as if printed. For floating point values with a large number of significant digits this is the only way to read the number and not lose information.

A conversion code of `t` will examine the scale and precision and will do either

- :int - scale of 0, if precision will fit
- :bigint - scale of 0, otherwise
- :double - scale not zero

## 4.0 Cursors

Oracle has the ability to return a collection of rows in a variable whose type is known as a cursor. This is sometimes also known as a result set and a variable which points to a cursor is known as a ref cursor. A cursor is in some ways similar to a prepared statement that has been executed: with both you can fetch rows of data one after another.

A cursor is an internal oracle datatype whose External representation is identified using SQLT_RSET. There is no native Lisp datatype for cursors. Instead, a cursor is an opaque object which you can operate on using functions provided by this API. The cursor functions are fetch-from-cursor, free-cursor, and cancel-cursor. `:cursor` can be the value of the type argument to bind-parameter. See also parameter-value. Example 5 is Section 6.0 Examples using the Oracle interface deals with ref cursors.

Having a cursor open, means that you have reserved some state in the Oracle database. As such, it is very important to free the cursor when you are done with it. Otherwise you introduce what is known as a cursor leak. If you are retreiving values from a cursor and decide to stop before all the data is retreived, you must call cancel-cursor.

## 5.0 The Oracle interface API

Functions, variables and classes in this interface are exported from the dbi.oracle package.

### *db*

### Variable

#### Package: dbi.oracle

This special variable holds the default database connection object used in most of the oracle API functions. The connect function returns a database connection object and also sets `*db*` to the connection object.

### *null-value*

### Variable

#### Package: dbi.oracle

The value of this variable is returned by the sql function (and related functions) to indicate a null value in a column. Its initial value is :null-value. A value should be something which could not be otherwise returned as a column value (thus nil, for example, is an inappropriate value).

## 5.1 API for connecting and disconnecting

### connect

### Function

#### Package: dbi.oracle

Arguments: &key database user password non-blocking autocommit

Create a connection to the given database using user-name/password authentication. If database is a string then a TCP/IP connection will be made to the server. If database is `nil` then a connection to the Oracle default database will be made. (The Oracle system has the notion of a default database. If you use the Oracle sqlplus program to access a database and you do not specify a database then it connects to the default database.)

If non-blocking is true (it defaults to true) then the connection will be set to non-blocking mode. If specified `nil`, the connection will be set to blocking mode. See Section 2.1 Blocking and non-blocking modes for information on choosing between blocking and non-blocking modes. set-db-flags can be used to change the blocking/non-blocking modes of a database.

If autocommit is true then a commit will be done after every successful sql call.

The database connection object will be returned and the variable `*db*` will be set to that connection as well. The value of `*db*` is the default value for functions in this interface that take a db keyword argument.

### disconnect

### Function

#### Package: dbi.oracle

Arguments: &key db

This function commits any changes to the database and closes the connection to the database.

db defaults to the value of `*db*`.

## 5.2 Excuting DML and DDL

Executing an sql statement involves these steps:

1. prepare the statement; the sql statement is sent to the Oracle database server to be parsed
2. bind lisp objects for all the parameters in the sql statement
3. store values into each of the lisp objects that are bound to the parameters
4. execute the statement; Oracle executes the sql statement in the database
5. If the sql statement is a select statement then Oracle will be returning rows of data so go to step 6. Otherwise there is no more work to be done for this statement. Either go to step 10 to finish or if you wish to execute the same statement with different parameter values then go to step 3.
6. reserve space to store the data from a single row
7. fetch the next row into the the reserved space. If there are no more rows then either go to step 10 to finish or go to step 3 to run the same statement with different parameter values.
8. convert the data returned by Oracle into Lisp objects
9. go to step 7
10. finished

These steps can be run individually if your program needs control at each step, or you can use the sql function to run through all the steps.

We mentioned the 'parameterized' sql statement above. In Oracle a parameter is denoted by a name preceded by a colon. This sql statement has two parameters, foo and bar:

        select * from mytab where i < :foo and b < :bar

## 5.3 One step execution of an sql statement

### sql

### Function

#### Package: dbi.oracle

Arguments: statement &key db names retrieve convert

Execute the sql statement and return any values sent from the database server as a result of the statement.

db defaults the value of `*db*`; names defaults to `t`; retrieve defaults to `t`; convert defaults to `nil`.

If the statement is a sql select statement and retrieve is true then after the statement is executed the rows that are returned by the select are returned by the sql function. The first value returned by sql is a list of the rows, each row being a list of the column values. The second value returned is a list of the column names (as long as the keyword argument names is true).

The convert argument determines how values are converted after being retrieved from the database. See Section 5.5 The Convert Argument.

If the statement has any sql parameters in it then you cannot use this sql function, instead you must use prepare-sql, bind-parameter and run-prepared-sql.

If statement is a select statement and retrieve is `nil` then you can call fetch-next-row and get-row-data to retrieve the results.

## 5.4 Step by step execution of a sql statement

### prepare-sql

### Function

#### Package: dbi.oracle

Arguments: statement &key db statement-handle

This function passes the sql statement to Oracle for parsing in preparation for executing the statement.

db defaults to the value of `*db*`.

statement-handle can be one of

- `nil`, meaning use the statement handle associated with this database connection, allocating one if necessary.
- a db-statement object, meaning use the statement handle in this object. The value of db passed to prepare-sql will not be used as the statement handle is already associated with a particular database object.
- `:new`, meaning create a new statement handle for the database connection, do the prepare-sql action, and then return a db-statement object that holds this new statement handle.

This function must be used if the statement has any parameters (which are symbols beginning with a colon) as in

          "select from foo where i < :maxval"

This function can also be used if there are no parameters.

At this point you would call bind-parameter and (setf ff:fslot-value) to bind parameters and store values in the parameters. This is covered in the Section 5.6 Binding parameters.

### run-prepared-sql

### Function

#### Package: dbi.oracle

Arguments: &key db statement-handle names retrieve convert

This function executes the last prepared sql statement using the current value of all bound parameters.

db defaults to the value of `*db*`. names defaults to `t`. retrieve defaults to `t`. convert defaults to `nil`. statement-handle defaults to `nil`.

statement handle can be one of

- nil - use the statement handle associated with this db connection
- a db-statement object - use the statement handle in the object

This function returns the same two values returned by the sql function.

### run-prepared-sql

### Function

#### Package: dbi.oracle

Arguments: statement-handle

This function returns the db object associated with the given statement handle.

### fetch-next-row

### Function

#### Package: dbi.oracle

Arguments: &key db

This function retrieves the next row from the oracle server. It returns true if successful and returns `nil` if there are no more rows. db defaults to the value of `*db*`.

The values retrieved are stored in objects not accessible to user code. The program must call get-row-data to convert the values to lisp objects.

### get-row-data

### Function

#### Package: dbi.oracle

Arguments: &key db

After a call to fetch-next-row you call this function to convert the fetched data to a list of column values.

db defaults to the value of `*db*`.

### get-column-value

### Function

#### Package: dbi.oracle

Arguments: &key db

After a call to fetch-next-row returns `t` you can use this function to retrieve the value of a specific column. Column numbers are 0-based. This is intended to be used in place of get-row-data if you want to avoid consing up a list of all the column values. You can use result-columns-count after a call to fetch-next-row in order to determine how many columns are in the result set.

### result-columns-count

### Function

#### Package: dbi.oracle

Arguments: &key db

After a call to fetch-next-row this function will return the number of columns in the result set. Every row in the result set has the same number of columns.

### db-statement-db

### Function

#### Package: dbi.oracle

Arguments: &key statement-handle

Returns the database object associated with the given statement handle.

## 5.5 The Convert Argument

The values returned by the database are converted into appropriate lisp values as directed by the convert keyword argument to sql and run-prepared-sql.

The convert argument provides an argument descriptor for each column in the result set.

## 5.5.1 The syntax of the convert argument

convert can be a list that's at least as long as the number of columns, e.g. `(t nil :int nil nil t)`. or convert can be an atom or a list that is shorter than the number of columns in which case the missing values at the end are computed as follows:

> If convert is a list then the cdr of the last cons in the list is repeated as much as necessary to fill out the convert list. Thus a convert list of

>     (a b . c)    [ notice the period between the b and c]

> is the same as (a b c c c c c c c c c c c c ...). As a consequence of this (a b) is the same as (a b . nil) and thus denotes the convert list (a b nil nil nil nil nil nil nil ....).

> If the convert argument is an atom then this denotes a convert list consisting of just that atom. Thus a convert argument of `:int` is the same as (:int :int :int :int ....) and a convert argument of `nil` denotes (nil nil nil nil nil nil ...)

## 5.5.2 The convert argument values

As mentioned above, the first column's database value is converted to a lisp object based on the value of the first item in the convert list. The second column is converted based on the second item in the convert list, and so on.

The possible values for the convert specification are:

- `nil` - return the values from the database as a string if they are textual or an unsigned-byte 8 array if they are not.
- `t` - return the appropriate lisp type based on the internal type of the column. For numeric columns this returns either an integer (fixnum or bignum) or a double float, depending on the precision and scale values for the column.
- `:int` - return a fixnum or bignum after having converted the database internal value into a 4 byte signed integer. If the database value is too large to be converted into a 4 byte signed integer than an error will be signalled.
- `:bigint` - return a fixnum or bignum by having lisp build the number based on its string representation. This is slower than the :int case but this case can handle the full range of integers that can be stored in an Oracle database
- `:double` - return a lisp double-float object. This may result in a loss of information as oracle can store data with more precision than a double float can represent.
- :single - return a lisp single-float object. This may result in a loss of information as oracle can store data with more precision than a single float can represent.
- `:float` - this is the same as `:single`.
- `:string` - a lisp string less than 4096 characters in size. It is permitted to return numeric columns as string and in fact this is a good idea if you're not going to do numeric operations on the values and you want to preserve their precision.
- `:usb8` - a vector of element-type `(unsigned-byte 8)`.
- `:ut` - universal time. For columns of type DATE this returns the date as a lisp universal time. The date object in oracle doesn't contain timezone information but under the default Oracle configuration the dates are stored with the local time (rather than GMT) thus when the lisp universal time is returned the time in the date object is interpreted as a local time, not a GMT.

## 5.6 Binding parameters

Note: A simpler method of setting statement parameters is described in the Section 5.8 Statement handle parameters section below.

Binding a parameter causes a region of memory to be associated with a parameter in an sql statement. When the sql statement is executed (with run-prepared-sql) whatever value is in the memory object is then used as the value of that parameter.

The Lisp memory objects bound to parameters are Foreign Type objects (see ftype.htm for a full description of foreign types).

### bind-parameter

### Function

#### Package: dbi.oracle

Arguments: location existing-obj type width &key db statement-handle

This function binds a Foreign Type object to the parameter at the given location and returns that object.

location is an integer or a string. Use 1 for the first parameter mentioned in the sql statement, and 2 for the second and so on. You can also specify the parameter by the names. Do not include the colon preceding the name when specifying the name to bind-parameter.

existing-obj is an existing Foreign Type object or `nil`. If you pass `nil` then an appropriate object will be created. If you pass an object as the value then you must be sure that the object has the correct type (and generally that means passing in an object that was returned by a previous call to bind-parameter that you know is no longer in use).

type is one of

- `:int` - for a 32-bit signed integer
- `:char` - for a sequence of 8-bit bytes with no nulls.
- `:byte` - for a sequence of octets that can have nulls. This can only be used for columns declared to have type 'long'.
- `:float` - for a double float value.
- `:double` - also for a double float value.
- `:ut` - a lisp universal time.
- `:cursor` - for a ref cursor parameter

width is the number of bytes if the type is given as :char, otherwise width is ignored. If type is `:char` and width is `nil` then width is assumed to be 4096.

db defaults to the value of `*db*`.

Once you call bind-parameter and have an object returned you will want to save a reference to that object somewhere so that for the lifetime of the binding that object is not garbage collected. You cannot just bind the parameter, set the value and then lose a pointer to the object.

To set the value you use (setf ff:fslot-value).

For an `:int` type object obj:

         (setf (ff:fslot-value obj :data) 324534)

For a `:char` type object obj:

         (setf (ff:fslot-value obj :data) "this is a string")

For a `:float` or `:double` object obj you must make sure that the value stored is a double-float value:

         (setf (ff:fslot-value obj :data) 1.2d0)

or for val being an integer or float:

         (setf (ff:fslot-value obj :data) (float val 1.0d0))

For a universal time object you have to use this function to store all of the fields:

         (store-universal-time obj  universal-time)

For a ref cursor parameters, it is recommended that you use the simpler method described below for fetching the cursor from the parameter after executing the query. See Section 5.8 Statement handle parameters.

A parameter object contains two values: the first is a specific type of value determined by how the parameter was allocated, and the second is a flag that says whether this parameter's value is 'null' or not.

### set-parameter-null

### Function

#### Package: dbi.oracle

Arguments: obj

The set-parameter-null function says that the parameter represents the null value regardless of whatever value has been stored in the parameter with setf of fslot-value.

### unset-parameter-null

### Function

#### Package: dbi.oracle

Arguments: obj

This function clears the flag in the parameter that says that it has a null value. After this function is called the value of the parameter is the last value that was stored in it with setf of fslot-value.

After set-parameter-null is called on a parameter object the only way to make the parameter represent a non null value is to call unset-parameter-null. calling setf of fslot-value will not change the null flag inside a parameter object.

## 5.7 Multiple statement handles

The connect function creates a single connection to the database. You can use the mutiple statement handle feature to prepare in advance multiple sql statements, however you can only execute one statement at a time.

That is you can do this:

    prepare the sql for execution

       (setq h1 (prepare-sql "sql stmt 1" :statement-handle :new))
       (setq h2 (prepare-sql "sql stmt 2" :statement-handle :new))
       (setq h3 (prepare-sql "sql stmt 3" :statement-handle :new))

    and then bind any parameters

       (setq p1-1 (bind-parameter 1 nil :int nil :statement-handle h1))
       (setq p2-1 (bind-parameter 1 nil :int nil :statement-handle h2))
       (setq p3-1 (bind-parameter 1 nil :int nil :statement-handle h3))

    and then set the parameters

       (setf (ff:fslot-value p1-1 :data) 10)
       (setf (ff:fslot-value p2-1 :data) 20)
       (setf (ff:fslot-value p3-1 :data) 30)

    and then run sql statements

       (run-prepared-sql :statement-handle h1)
       (run-prepared-sql :statement-handle h2)
       (run-prepared-sql :statement-handle h3)

However if you specify `:retrieve nil` to run-prepared-sql then you must retrieve all the rows that you want to retrieve before calling run-prepared-sql again, since when run-prepared-sql is called it will end the retrieval of the previous query.

## 5.8 Statement handle parameters

The mechanism described here for setting statement parameters can be used instead of bind-parameter. If you use the calls given in this section then do not use bind-parameter calls for the same statement handle.

Above we've shown how to create a statement handle object, e.g.

        (setq h (prepare-sql "insert into foo(i,j) values(:ipar, :jpar)"
                             :statement-handle :new))

The above statement has two parameters, denoted either by their positions: 1 and 2 or by their names "ipar" and "jpar".

In order to specify values for the parameters before calling run-prepared-sql you can do

         (set-parameter-value h "ipar" 23)  ; set param named "ipar"
         (set-parameter-value h  2 "smith") ; set second parameter (i.e. "jpar")

then you can do

         (run-prepared-sql :statement-handle h)

to do the sql insert statement.

### set-parameter-value

### Function

#### Package: dbi.oracle

Arguments: statement-handle param value &key type

This function stores the given value as the value for the given parameter.

statement-handle is an object returned by prepare-sql.

param is a string naming a parameter or a positive integer specifying the position of the parameter. You must be consistent -- for a given parameter always use either a number or a name. You can also supply a symbol as an argument in which case the symbol's name will be used. Since Oracle's parameters appear to be Lisp keyword symbols in the sql it is nice to use Lisp keywords to represent parameters in the Lisp code as well.

value is the value to store in the parameter. If the value is `nil` or the value of `*null-value*` then a null will be stored in this column.

type is one of the bind parameter types and need only be specified if value could be interpreted as being one of many types.

You'll need to specify a type in the following cases:

- value is a lisp universal time and you want it stored in oracle as a date object. In this case specify a type of `:ut`.
- value is a lisp string containing nulls and you want it treated as an oracle LONG object. In this case specify a type of `:byte`.

### parameter-value

### Function

#### Package: dbi.oracle

Arguments: statement-handle param

This function returns the last value stored with set-parameter-value.

statement-handle is an object returned by prepare-sql

param is a string naming a parameter or a positive integer specifying the position of the parameter. You must be consistent -- for a given parameter always use either a number or a name.

For out parameters and function result parameters, it is often necessary to call set-parameter-value to set the correct type, even when no value is being set. This is especially important for cursor parameters, where the type must be set. In cases where no value is being set and only the type is important, use `nil` as the value. See Example 5 in Section 6.0 Examples using the Oracle interface.

## 5.9 Setting flags

To set flags affecting the operation of the interface use set-db-flags.

### set-db-flags

### Function

#### Package: dbi.oracle

Arguments: &key db non-blocking autocommit

The db argument defaults to the value of `*db*`.

If the non-blocking argument is provided then it sets the interface into non-blocking mode if the value is true and blocking mode if `nil`.

If the autocommit is provided then if true it causes a commit to done after every successful execution of an sql statement (either via the sql function or the run-prepared-sql function).

You can set any number of flag values in one call.

If you specify exactly one flag value (as well as possibly a value for db) then the return value from this function is the previous value of the flag.

## 5.10 Obtaining schema information

In order to find information on tables you retrieve data from system tables and views.

- "select * from all_tables": finds names and other info from all tables in the database (a big list).
- "select * from user_tables": finds names and other info in user-defined tables.
- "select * from user_tab_columns where table_name='MYTAB'": finds information about the columns of table MYTAB (case is important and you must use upper). You can use the view all_tab_columns to retrieve information about non-user-defined tables.

]

## 5.11 Managing cursors

See Section 4.0 Cursors.

### fetch-from-cursor

### Function

#### Package: dbi.oracle

Arguments: cursor &key names retrieve convert

Similar to run-prepared-sql but for a cursor instead of a statement, this function takes a cursor object and returns rows of data when retrieve is set to true. Otherwise, it prepares the cursor so that fetch-next-row and get-row-data will return rows from this cursor. It is only possible to fetch data from one statement or cursor at a time. See sql for a discussion of the keyword arguments.

- names defaults to `t`.
- retrieve defaults to `t`
- convert defaults to `nil`

This function returns the same two values returned by the sql function.

### free-cursor

### Function

#### Package: dbi.oracle

Arguments: cursor

Once an application has fetched the data from a cursor using fetch-from-cursor, it is important to call free-cursor. A cursor object holds state in the database server and this function allows that state to be freed. Not freeing a cursor results in what is known as a cursor leak.

### cancel-cursor

### Function

#### Package: dbi.oracle

Arguments: &key (db *db*)

If you wish to quit retrieving values from a cursor before you have read all the values, you must call this function. It assumes that a cursor is being read and it is that cursor that is cancelled.

## 6.0 Examples using the Oracle interface

In all examples shown here we assume but do not show that we have loaded in the oracle interface and that we have caused the user package to use the `dbi.oracle` package.

### Example 1

This example shows connecting, simple operations and disconnecting. Note how the use of `*db*` as the default allows one to interactively access the database with minimal typing.

    ;; we connect to the default database with user name "jkf" and
    ;; password "jkf".  This assigns the variable *db* to
    ;; the database object that's returned.
    ;;
    cl-user(3): (connect :user "jkf" :password "jkf")
    ; Foreign loading libclntsh.so.
    #<db @ #x71aafb12>

    ;; we drop the table "samp" if it already exists so that we
    ;; can redefine it (the ignored error indicates it did not exist).
    cl-user(4): (ignore-errors (sql "drop table samp"))
    nil
    #<simple-error @ #x71dbf262>

    ;; we create the table
    cl-user(5): (sql "create table samp(i int, j int)")
    nil

    ;; we add some rows to the table
    cl-user(6): (sql "insert into samp values(1,10)")
    nil
    cl-user(7): (sql "insert into samp values(2,20)")
    nil
    cl-user(8): (sql "insert into samp values(3,30)")
    nil

    ;; we test to see if those rows are in the table
    cl-user(9): (sql "select * from samp")
    (("1" "10") ("2" "20") ("3" "30"))
    ("I" "J")

    ;; we commit our changes.  In this case the commit wasn't
    ;; necessary since the disconnect we do next also does a commit.
    cl-user(10): (commit)
    0

    ;; we disconnect.
    cl-user(11): (disconnect)
    t

### Example 2

Shows fetching one row at a time. You can use this technique if you have a huge result set and do not want to create a big list of all the rows.

    cl-user(2): (connect :user "jkf" :password "jkf")
    ; Foreign loading libclntsh.so.
    #<db @ #x71db7b8a>

    ;; specify that we don't want the sql function to retrieve the rows
    cl-user(3): (sql "select * from samp" :retrieve nil)
    nil

    ;; now call fetch-next-row until it returns nil
    cl-user(4): (fetch-next-row)
    t

    ;; and after each successful fetch call get-row-data to get the row
    cl-user(5): (get-row-data)
    ("1" "10")
    cl-user(6): (fetch-next-row)
    t
    cl-user(7): (get-row-data)
    ("300" "20")
    cl-user(8): (fetch-next-row)
    t
    cl-user(9): (get-row-data)
    ("3" "30")

    ;; this time fetch-next-row returns nil - no more rows to read
    cl-user(10): (fetch-next-row)
    nil
    cl-user(11): (disconnect)
    t
    cl-user(12):

### Example 3

Shows binding of parameters. We build a table by binding parameters and rerunning a parameterized sql statement.

    cl-user(2): (connect :user "jkf" :password "jkf")
    ; Foreign loading libclntsh.so.
    #<db @ #x71dc298a>
    cl-user(3): (ignore-errors (sql "drop table squares"))
    nil
    #<simple-error @ #x71de5bea>
    cl-user(4): (sql "create table squares (val int, square int)")
    nil

    ;; this sql statement has two parameters
    cl-user(5): (prepare-sql "insert into squares values (:val, :sq)")
    "insert into squares values (:val, :sq)"

    ;; we bind each parameter.  The first we bind by order in the sql
    ;; statement the second we bind by name

    cl-user(6): (setq vv (bind-parameter 1 nil :int nil))
    #<foreign object of class oracle-int-param>

    cl-user(7): (setq sqsq (bind-parameter "sq" nil :int nil))
    #<foreign object of class oracle-int-param>

    ;; and now insert 1000 rows
    cl-user(8): (dotimes (i 1000)
    	      (setf (ff:fslot-value vv :data) i)
    	      (setf (ff:fslot-value sqsq :data) (* i i))
    	      (run-prepared-sql))
    nil

    ;; we use the table to find the value of 34 squared.
    cl-user(9): (sql "select square from squares where val = 34")
    (("1156"))
    ("SQUARE")
    cl-user(10): (disconnect)
    t
    cl-user(11):

### Example 4

Shows transactions and multiple connections open at once.

    ;; open two connections to oracle
    cl-user(2): (setq db-one (connect :user "jkf" "jkf"))
    ; Foreign loading libclntsh.so.
    #<db @ #x71dcd38a>
    cl-user(3): (setq db-two (connect :user "jkf" "jkf"))
    #<db @ #x71dcda72>

    ;; using the db-one connection create a table called 'concur'
    ;; and adds a row
    cl-user(4): (sql "create table concur(i int)" :db db-one)
    nil
    cl-user(5): (sql "insert into concur values(1)" :db db-one)
    nil

    ;; view the table using the db-two connection  The table is present
    ;; even before the commit, but there are no rows in it.
    cl-user(6): (sql "select * from concur" :db db-two)
    nil
    ("I")

    ;; however viewed from db-one connection we see the rows
    cl-user(7): (sql "select * from concur" :db db-one)
    (("1"))
    ("I")

    ;; commit the changes on the db-one connection
    cl-user(8): (commit :db db-one)
    0

    ;; and now the row is seen on the db-two connection
    cl-user(9): (sql "select * from concur" :db db-two)
    (("1"))
    ("I")

    cl-user(10): (disconnect :db db-one)
    t
    cl-user(11): (disconnect :db db-two)
    t
    cl-user(12):

### Example 5: ref cursors

First create the following stored procedure in oracle using the scott/tiger standard test account. You can do this easily using sqlplus.

Then create or replace procedure get_emps(o_cur out sys_refcursor) as

    begin
       open o_cur for select ename, empno from emp order by ename;
    end;
    /

Now in Lisp...

    CL-USER(1): (require :oracle)
    T

    CL-USER(2) (in-package :dbi.oracle)
    #<The DBI.ORACLE package>
    DBI.ORACLE(3): (connect :database "oracle" :user "scott" :password "tiger")
    ; Foreign loading libclntsh.so.
    #<Oracle db connection to oracle @ #x71718be2>

    DBI.ORACLE(4): (setq stmt (prepare-sql "begin get_emps(:cur); end;"
                                           :statement-handle :new))
    #<statement for oracle db tickdb @ #x71741e92>

    DBI.ORACLE(5): (set-parameter-value stmt "cur" nil :type :cursor)
    NIL

    DBI.ORACLE(6): (run-prepared-sql :statement-handle stmt)
    NIL

    DBI.ORACLE(7): (setq cur (parameter-value stmt "cur"))
    #<statement for oracle db tickdb @ #x7178726a>

    DBI.ORACLE(8): (fetch-from-cursor cur)
    (("ADAMS" "7876") ("ALLEN" "7499") ("BLAKE" "7698") ("CLARK" "7782")
    ("FORD" "7902") ("JAMES" "7900") ("JONES" "7566") ("KING" "7839")
    ("MARTIN" "7654")
     ("MILLER" "7934") ("SCOTT" "7788") ("SMITH" "7369") ("TURNER" "7844")
    ("WARD" "7521"))
    ("ENAME" "EMPNO")

    DBI.ORACLE(9): (free-cursor cur)
    NIL

## 7.0 Index for Oracle Interface

- bind-parameter
- connect
- `*db*`
- db-statement-db
- disconnect
- fetch-from-cursor
- fetch-next-row
- free-cursor
- get-column-value
- get-row-data
- `*null-value*`
- parameter-value
- prepare-sql
- result-columns-count
- run-prepared-sql
- set-db-flags
- set-parameter-null
- set-parameter-value
- sql
- unset-parameter-null

Copyright (c) 1998-2019, Franz Inc. Oakland, CA., USA. All rights reserved.
This page was not revised from the 8.2 page.
Created 2012.5.30.

ToC | DocOverview | CGDoc | RelNotes | FAQ | Index | PermutedIndex
| Allegro CL version 9.0 Unrevised from 8.2 to 9.0. 8.2 version