Repository files navigation

This driver is based on OCI bindings developed for CLSQL.

This library provides Oracle interface to CL-DBI, so anyCL-DBI-complaint program should work. Please, note the followinglimitations.

Connection can be estabslished by providing appropriate database nametodbi:connect method. The value ofdatabase-name key argument is anORACLE connect string that might include hostname, listener portnumber, and oracle SID or service name. For example,

(defvar*connection*  (dbi:connect:oracle:database-name"127.0.0.1:1521/orcl":username"nobody":password"1234":encoding:utf-8))

Encoding parameter is used for conversion of character strings betweenOCI library and Lisp. :utf-8 is the default encoding. Possible valuesfor encoding parameter is the same as for CFFI string conversionfunctions. If :encoding keyword is not fiven, the library accessesNLS_LANG enironment variable. Note, that Oracle encoding namesappearing in NLS_LANG may not match the names used in CFFI.

The application makes an attempt to load Oracle OCI library during thefirst call todbi:connect. By default, the OCI library is searched inORACLE_HOME, ORACLE_HOME/lib, and ORACLE_HOME/bin directories, as wellas in the current directory. If ORACLE_HOME environment variable isnot set, then search paths can be provided by settingdbd.oracle:*foreign-library-search-paths* variable. The valueassigned to this variable should be a list of pathnames. For example,

  (let* ((dbd.oracle:*foreign-library-search-paths*'(#p"/opt/oracle/"))         (connection (dbi:connect:oracle:database-name"localhost/orcl":username"nobody":password"1234")))     (dbi:disconect connection))

You may need to create a symbolic link forlibclntsh.so pointing toan appropriate library, e.g.libclntsh.so.12.1.

Queries are evaluated using CL-DBI interface. Parameters bindings isnot guaranteed to work properly due to incompatible syntax forplaceholders definitions used in CL-DBI and Oracle queries. Oraclerequires parameter name to start with a colon, while CL-DBI uses thequestion mark? to define placeholders. Simple substitution ofalloccurrences of " ?" (a space followed by a question mark) byOracle-friendly column expressions is performed for the entire SQLexpression.

(defvar*connection*  (dbi:connect:mysql:database-name"test":username"nobody":password"1234"))(let* ((query (dbi:prepare*connection*"SELECT * FROM somewhere WHERE flag = ? OR updated_at > ?"))       (result (dbi:execute query (list0"2011-11-01"))))  (loop for row= (dbi:fetch result)     while row;; process "row".       ))

The effectve query would be"SELECT * FROM somewhere WHERE flag = :1 OR updated_at > :2".If a question mark appears inside a quoted string of the SQL query,then the effective query could not be identical to the originalone. The rule of thumb is to avoid using French punctuation style inliteral constants inside the main body of SQL queries (bound stringsare processed as is), and to put space before every placeholder. Automaticsubstitution of question marks may be disabled by settingformat-placeholders key parameter ofdbi:connect to NIL.

CLOB fields may be used in INSERT operations.

(let ((long-string (make-string22000:initial-element#\a))      (query (dbi:prepare"INSERT INTO tbl (clob_field) VALUES ( ?)")))  (dbi:execute query (list long-string)))

You can not SELECT the value back, as ORACLE strips CLOB values to apredefined limit, which is 4000 for modern versions of ORACLE.

The library supports single parse, multiple bind/executes loop for a statement.

(with-reusable-query (query connection"INSERT INTO tbl (k) VALUES ( ?)")  (loop for k from1 to100do (dbi:execute query (list k))))

This scheme may be more efficient when evaluating large number ofqueries. Macrowith-reusable-query evaluates its body withquerybeing prepared SQL expressions, and ensures that all resourcesallocated for the query will be released at the end.

The library was manually tested using Oracle 11g server under following client configurations:

• 64-bit Clozure CL 1.9 under Windows 7 using 64-bit version of Oracle instant client 12.1.0.1
• 64-bit SBCL 1.3.2 under Ubuntu 14.04 64-bit using Oracle instant client 12.1.0.1
• 32-bit LispWorks Personal Edition on Mac OS X 10.10 using 32-bit version of Oracle instant client 11.2.0.4

Automated testing uses SBCL under Linux, and Oracle XE version11.2.

Testig procedure requires that the databse contain a table

CREATETABLErealval (    rREAL,    nNUMBER,    nsNUMBER(*,5),    npsNUMBER(10,2),    iINTEGER);INSERT INTO realvalVALUES (17.17,17.17,17.17,17.17,17);

In order to run tests locally, you need to modify connection-relatedparameters intest/root.lisp, or modify your environment inaccordance with the default values:

(deftestsuite root ()  ((user-name"scott")   (password"tiger")   (connect-string (let ((host"127.0.0.1")                         (port"1521")                         (oracle-sid (or (dbd.oracle::getenv"ORACLE_SID")"orcl")))                     (formatnil"~A:~A/~A" host port oracle-sid))))  (:dynamic-variables   (dbd.oracle:*foreign-library-search-paths*'(#p"/opt/oracle/")))  ...)

Tests can be started using

(asdf:test-system"dbd-oracle")

Lisp Lesser GNU Public License