Die Welt ist meine Vorstellung

Connect to Oracle DB from Clojure

15 April 2020

I need to analyze data from Oracle database in Clojure, and come across some error. Here is the solutions to the problems.

1 No suitable driver found

The first problem is no oracle database driver: 

Syntax error (SQLException) compiling at (C:\cygwin64\tmp\form-init16410964815710271710.clj:1:88).
No suitable driver found for jdbc:oracle:thin:@xxx.xxx.xxx.xxx:1521

The solution:

1.1 download Oracle jdbc driver

Download "ojdbc8.jar" from:

https://www.oracle.com/database/technologies/jdbc-ucp-122-downloads.html

1.2 add lein-localrepo plugin to leiningen profiles

In ~/.lein/profiles.clj, add: 

{:user
 {
  :plugins [[lein-localrepo "0.5.4"]]
  }
 }

1.3 install oracledriver

$ lein localrepo install ~/downloads/ojdbc8.jar oracle.jdbc/oracledriver "12.1.0.1"

1.4 add dependencies in project.clj

:dependencies [[org.clojure/clojure "1.10.1"]
               [org.clojure/java.jdbc "0.7.11"]
               [oracle.jdbc/oracledriver "12.1.0.1"]] <--

2 No service found

Syntax error (NetException) compiling at (C:\cygwin64\tmp\form-init628084108377043642.clj:1:86).
Listener refused the connection with the following error:
ORA-12504, TNS:listener was not given the SID in CONNECT_DATA

You need to find the SERVICE_NAME from tnsnames.ora: 

TEST =
   (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = *<validhost>*)(PORT = *<validport>*))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = *<servicenamefromDB>*)
    )
)

defin the db-spec as: 

(def db-spec {:classname "oracle.jdbc.OracleDriver"
              :subprotocol "oracle"
              :subname "@*<validhost>*:*<validport>*/*<servicenamefromDB>*"
              :user "username"
              :password "password"})