How to connect an Oracle Database to Mathematica?

Question

I am trying to connect an Oracle database to Mathematica 8. Another question already says how it can be done in for a MySQL database but it does not work for me:

Needs["DatabaseLink"] AND conn = OpenSQLConnection[JDBC["MySQL(Connector/J)", 
"yourserver/yourdatabase"], "Username" -> "yourusername", "Password" -> "yourpassword"]

The following information is available for me on my desktop:

filepath = "C:\oracle\ora92\network\ADMIN\tnsnames.ora"; HOST; PORT; username; password;

conn = OpenSQLConnection[JDBC["MySQL(Connector/J)", HOST], "Username" -> username, "Password" -> password]

Error message: JDBC::error: 
    Communications link failure The last packet sent successfully to the server was 0 milliseconds ago. 
    The driver has not received any packets from the server. >>

Does anyone know how I can connect or continue?

Answer 1

Mathematica 8 does not come pre-equipped with an Oracle driver, a fact that can be verified by evaluating these expressions:

Needs["DatabaseLink`"]
JDBCDriverNames[]
(*
Out[2]= {Microsoft Access(ODBC),hsqldb,HSQL(Memory),HSQL(Server),
         HSQL(Server+TLS),HSQL(Standalone),HSQL(Webserver),HSQL(Webserver+TLS),
         jtds_sqlserver,jtds_sybase,mysql,MySQL(Connector/J),ODBC(DSN),odbc,
         PostgreSQL,Microsoft SQL Server(jTDS),Sybase(jTDS),HSQL 2.0.1}
*)

We will have to follow the instructions in the Mathematica documentation that describes how to install a new JDBC driver.

First, we will have to create a new resource directory in which to place the necessary JDBC driver JAR file:

$jarDirectory =
  CreateDirectory @
    FileNameJoin @
      {$UserBaseDirectory, "Applications", "Oracle", "Java"}

Next, we must select a JDBC driver to use. Visit the relevant Oracle page to find the correct JDBC driver for your database.

Chose a driver version that is compatible with Java 6, the version that Mathematica 8 uses internally. For this example, I chose to use the Oracle 11.2.0.2.0 driver for Java 6. Download the file and then move it into the resource directory just created:

SystemOpen[$jarDirectory]

Next, we will create a JDBC driver configuration file so that the new driver is registered with Mathematica:

$configDirectory =
  CreateDirectory @
    FileNameJoin @
      {$UserBaseDirectory, "Applications", "Oracle", "DatabaseResources"}

Export[
  FileNameJoin @ {$configDirectory, "Oracle.m"}
, JDBCDriver[
    "Name" -> "Oracle"
  , "Driver" -> "oracle.jdbc.driver.OracleDriver"
  , "Protocol" -> "jdbc:oracle:thin:@"
  , "Version" -> 1
  ]
, "Text"
]

The driver is now installed:

JDBCDriverNames[]
(*
Out[9]= {Oracle,Microsoft Access(ODBC),hsqldb,HSQL(Memory),HSQL(Server),
         HSQL(Server+TLS),HSQL(Standalone),HSQL(Webserver),HSQL(Webserver+TLS),
         jtds_sqlserver,jtds_sybase,mysql,MySQL(Connector/J),ODBC(DSN),odbc,
         PostgreSQL,Microsoft SQL Server(jTDS),Sybase(jTDS),HSQL 2.0.1}
*)

If the fates are smiling, we can now establish a connection and execute an SQL query:

$connection =
  OpenSQLConnection[
    JDBC["Oracle", "myserver:1521:mysid"]
  , "Username" -> "scott"
  , "Password" -> "tiger"
  ]

SQLExecute[$connection, "SELECT 'success!' FROM DUAL"]

... where myserver is the database server name, 1521 is the listener port number and mysid is the Oracle System ID (SID).

Oracle JDBC URLs come in many forms. For details, take a look at the Oracle FAQ.

Answer 2

I suspect that you are using the wrong JDBC driver - you should be using the Oracle JDBC driver, rather than MySQL one. When I was using DatabaseLink to connect to an Oracle database, I used this command:

OpenSQLConnection[
  JDBC[
   "oracle.jdbc.driver.OracleDriver", 
   "jdbc:oracle:thin:@server:port:dbname"
  ], 
  "Name" -> "dbname", 
  "Username" -> "YourUserName", 
  "Password" -> "YourPassword"
]

You should make sure to put the proper Oracle JDBC driver (corresponding to your Oracle db version) into a place where Mathematica can find it. This procedure is described in the documentation for the DatabaseLink, section JDBC Connections. You can test which JDBC drivers are visible to Mathematica by executing JDBCDrivers[]. Make sure that you install and use the correct driver corresponding to your DB version, b.t.w. - incorrect driver versions may result in very nasty and non-obvious bugs (this is unrelated to Mathematica).

Answer 3

My guess would be that you shouldn't use MySQL JBDC connections for Oracle. Although it is for Mathematica 5.2, here is an article that you perhaps can use as something to go from.

I've barely used Mathematica, and certainly not with a database, but from that page, it looks like you can do this:

OpenSQLConnection[JDBC["oracle","server.business.com:1999"],
    Username -> "you"]

Answer 4

Whilst the answer from WReach above is correct it may also be helpful to know that there are 2 additional lines that are useful - namely to make sure Jlink is loaded and the Java ClassPath is correct and pointing to your oracle jdbc6.jar file.

Needs["JLink`"]
AddToClassPath[
FileNameJoin[{$UserBaseDirectory, "Applications", "Oracle", 
"Java"}]];

or if using the answer verbatim just

Needs["JLink`"]
AddToClassPath[$jarDirectory];