| Allegro CL version 9.0 Unrevised from 8.2 to 9.0. 8.2 version | ||||||||
This document contains the following sections:
1.0 Oracle Interface introductionThis 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.
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.
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)
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.
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
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.
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:
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):
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).
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
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.
Functions, variables and classes in this interface are exported from the dbi.oracle package.
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.
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).
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.
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*.
Executing an sql statement involves these steps:
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
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.
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.
: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.
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.
Arguments: statement-handle
This function returns the db object associated with the given statement handle.
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.
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*.
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.
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.
Arguments: &key statement-handle
Returns the database object associated with the given statement handle.
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.
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
:intis the same as (:int :int :int :int ....) and a convert argument ofnildenotes (nil nil nil nil nil nil ...)
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.
: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.
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).
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.
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.
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.
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.
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.
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:
:ut.
:byte.
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.
To set flags affecting the operation of the interface use set-db-flags.
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.
In order to find information on tables you retrieve data from system tables and views.
See Section 4.0 Cursors.
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.
t.
t
nil
This function returns the same two values returned by the sql function.
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.
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.
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.
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
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):
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):
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):
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
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.
| Allegro CL version 9.0 Unrevised from 8.2 to 9.0. 8.2 version | ||||||||