ToCDocOverviewCGDocRelNotesFAQIndexPermutedIndex
Allegro CL version 10.0
Unrevised from 9.0 to 10.0.
9.0 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 want to run in non-blocking mode if



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:

ANS type specifiers:

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

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

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

The following conversions are supported:

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



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

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

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:


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

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:



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.

]

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.

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


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

ToCDocOverviewCGDocRelNotesFAQIndexPermutedIndex
Allegro CL version 10.0
Unrevised from 9.0 to 10.0.
9.0 version