How to connect an Oracle Database to Mathematica?

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.

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).

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"]