clsql-oracle #106

Open
quicklisp opened this Issue Feb 3, 2011 · 7 comments

Projects

None yet

5 participants

@quicklisp
Owner

Update build environment to include oracle support so clsql-oracle builds.

@canweriotnow

Any activity on this? 😄

@quicklisp
Owner

I have an oracle environment set up, but I can't seem to get clsql to work with it. I wonder if anyone else has ever had success with it, and if so, how they did it.

@JesterSks

Ok, for those who come upon this issue I'm going to leave what I have done to get clsql-oracle to work:

  1. Make sure you have ORACLE_HOME set (mine wasn't)
  2. Move or copy the clsql (it will be named something like clsql-20130420-git) directory from ~/quicklisp/dists/quicklisp/software to ~/quicklisp/local-projects and rename it to clsql. I moved the directory but you might be able to get away with copying it because I think packages in local-projects have priority over the ones in software.
  3. Load up your common-lisp and now you can run (ql:quickload "clsql-oracle") almost.
  4. When I did this I received an error about c--stmt-string being unbound if you get such a message you need to change c--stmt-string to c-stmt-string in oracle-sql.lisp:517. I have sent a message to the clsql mailing list about this.

This is totally a hack

It removes clsql from the part of quicklisp that automatically manages packages (if that wasn't obvious). Please keep this in mind.

@josrr
josrr commented May 13, 2015

If you are using the oracle instantclient 12.1 in Linux maybe you need to change the names of the libraries in oracle-loader.lisp:24 to (list "libclntsh" "libociei").

@la-ci
la-ci commented Nov 16, 2015

Redhat/Centos setup

I was playing with oracle lately and found out that all you need is to put path to libclntsh into /etc/ld.conf.d/oracle.conf

my setup was following(redhat,centos - as root): downloaded from oracle

oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64.rpm
oracle-instantclient12.1-devel-12.1.0.2.0-1.x86_64.rpm

install via

rpm -ivh oracle*.rpm

create file /etc/ld.so.conf.d/oracle.conf:

echo /usr/lib/oracle/12.1/client64/lib >/etc/ld.so.conf.d/oracle.conf

then execute ldconfig

ldconfig

now as clsql-oracle is not in quicklisp, I downloaded and extracted clsql-6.6.2
then

(require "asdf")
(push #P"/opt/jeff/clsql-6.6.2/" asdf:*central-registry*)
(asdf:load-system :clsql-oracle)
(defparameter *some-db* (connect '("127.0.0.1:1521/db1" "SOME_USER_RO" "*******") :database-type :oracle))

and voila, it works

as all what was needed is just oracle environment setup, I believe clsql-oracle should be back in quicklisp

@quicklisp
Owner

That didn't work for me on my debian system - I installed instant-client for x86-64, but clsql fails with

  Couldn't load foreign libraries "libclntsh", "oci". (searched *FOREIGN-LIBRARY-SEARCH-PATHS*: NIL)

libclntsh.so shows up in ldconfig -v output, but Lisp loading fails. I'm not sure if it's a problem with my OS setup, or what.

@la-ci
la-ci commented Nov 18, 2015

Debian setup

I gave it a try, installed debian 8.2 server, downloaded zip files and created symlinks as noted in http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html

Approach 1 - use zip files from oracle

with zip files i did following

mkdir -p /opt/oracle/
cd /opt/oracle/
unzip /root/instantclient-basic-linux.x64-12.1.0.2.0.zip
unzip /root/instantclient-sdk-linux.x64-12.1.0.2.0.zip
cd /opt/oracle/instantclient_12_1
ln -s libclntsh.so.12.1 libclntsh.so
ln -s libocci.so.12.1 libocci.so
export LD_LIBRARY_PATH=/opt/oracle/instantclient_12_1:$LD_LIBRARY_PATH
echo /opt/oracle/instantclient_12_1 > /etc/ld.son.conf.d/oracle.conf
ldconfig
ldconfig -v

first it did not work
so i grab sqlplus package and installed it

cd /opt/oracle/
unzip /root/instantclient-sqlplus-linux.x64-12.1.0.2.0.zip
cd /opt/oracle/instantclient_12_1
./sqlplus scott/tiger@DB

worked
retried with ccl
worked
seems working now

so i decided to have a better approach, removed /opt/oracle dir and /etc/ld.so.conf.d/oracle.conf files
and verified i got error message back. i actually did good

root@debian:/opt/jeff# rlwrap /opt/ccl/lx86cl64
Welcome to Clozure Common Lisp Version 1.11-r16635  (LinuxX8664)!

CCL is developed and maintained by Clozure Associates. For more information
about CCL visit http://ccl.clozure.com.  To enquire about Clozure's Common Lisp
consulting services e-mail info@clozure.com or visit http://www.clozure.com.

? (load "jeff.lisp")
> Error: Couldn't load foreign libraries "libclntsh", "oci". (searched *FOREIGN-LIBRARY-SEARCH-PATHS*:NIL)
> While executing: FIND-AND-LOAD-FOREIGN-LIBRARY, in process listener(1).
> Type :POP to abort, :R for a list of available restarts.
> Type :? for other options.
1 > q
?

Approach 2 - use rpm on debian

so let us take better approach: rpm and alien

apt-get install libaio1
apt-get install alien
cd /root
root@debian:~# ls -l *.rpm
-rw-r--r-- 1 root root 62587782 Nov 18 05:07 oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64.rpm
-rw-r--r-- 1 root root   634803 Nov 18 05:07 oracle-instantclient12.1-devel-12.1.0.2.0-1.x86_64.rpm
root@debian:~#

now let us create deb packages from rpm

alien oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64.rpm
alien oracle-instantclient12.1-devel-12.1.0.2.0-1.x86_64.rpm
root@debian:~# ls -l *.deb
-rw-r--r-- 1 root root 40986452 Nov 18 05:34 oracle-instantclient12.1-basic_12.1.0.2.0-2_amd64.deb
-rw-r--r-- 1 root root   557770 Nov 18 05:37 oracle-instantclient12.1-devel_12.1.0.2.0-2_amd64.deb
root@debian:~# 

and install now

dpkg -i oracle-instantclient12.1-basic_12.1.0.2.0-2_amd64.deb
dpkg -i oracle-instantclient12.1-devel_12.1.0.2.0-2_amd64.deb
echo /usr/lib/oracle/12.1/client64/lib/ > /etc/ld.so.conf.d/oracle.conf
ldconfig
ldconfig -v

and again things are working

root@debian:/opt/jeff# rlwrap /opt/ccl/lx86cl64
Welcome to Clozure Common Lisp Version 1.11-r16635  (LinuxX8664)!

CCL is developed and maintained by Clozure Associates. For more information
about CCL visit http://ccl.clozure.com.  To enquire about Clozure's Common Lisp
consulting services e-mail info@clozure.com or visit http://www.clozure.com.

? (require "asdf")
"asdf"
NIL
? (push #P"/opt/jeff/clsql-6.6.2/" asdf:*central-registry*)
(#P"/opt/jeff/clsql-6.6.2/" #P"/root/quicklisp/quicklisp/")
? (asdf:load-system :clsql-oracle)
T
? (defparameter *some-db* (clsql-sys:connect '("10.0.0.5:1521/DB1" "SOME_USER" "******") :database-type :oracle))
*SOME-DB*
? (clsql-user:connected-databases)
(#<ORACLE-DATABASE 10.0.0.5:1521/DB1/SOME_USER OPEN #x30200112AF0D>)
?

please let me know if any of this works for you.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment