Getting data from an oracle database as a CSV file (or any other custom text format).

Question

A sample perl script that connects to an oracle database, does a simple SELECT query, and spits the results to stdout in CSV format would be great. Python or any other language available in a typical unix distribution would be fine too.

Note that I'm starting from scratch with nothing but a username/password for a remote Oracle database. Is there more to this than just having the right oracle connection library?

If there's a way to do this directly in mathematica, that would be ideal (presumably it should be possible with J/Link (mathematica's java integration thingy)).

Answer 1

In perl you could do something like this, leaving out all the my local variable declarations and ... or die "failmessage" error handling for brevity.

use DBI; 
use DBD::Oracle;

$dbh = DBI->connect( "dbi:Oracle:host=127.0.0.1;sid=XE", "username", "password" );

# some settings that you usually want for oracle 10
$dbh->{LongReadLen} = 65535;
$dbh->{PrintError} = 0;  

$sth = $dbh->prepare("SELECT * FROM PEOPLE");

$sth->execute();

# one example for error handling just to show how it's done in principle
if ( $dbh->err() ) { die $dbh->errstr(); }

# you can also do other types of fetchrow, see perldoc DBI 
while ( $arrayref = $sth->fetchrow_arrayref ) {
    print join ";", @$arrayref;
    print "\n";
}

$dbh->disconnect();

Two notes, because people asked in comments:

• sid=XE is the oracle service id, that is like the name of your database. If you install the free version of oracle, it defaults to "XE", but you can change it.
• Installing DBD::Oracle needs the oracle client libraries on your system. Installing that will also set all the necessary environment variables.

Answer 2

Here is an implementation in Python:

import cx_Oracle, csv

orcl = cx_Oracle.connect('ohd/john@ohddb')
curs = orcl.cursor()

csv_file_dest = "C:\\test.csv"

output = csv.writer(open(csv_file_dest,'wb'))

sql = "select * from parameter"

curs.execute(sql)

headers_printed = False
for row_data in curs:        
    if not headers_printed:
        cols = []
        for col in curs.description:
            cols.append(col[0])
        output.writerow(cols)
        headers_printed = True

    output.writerow(row_data)

Answer 3

Mathematica has a package "DatabaseLink" built in that should make this easy but you need to find a driver for Oracle. Installing the "oracle client libraries" should do that...

Answer 4

Get Oracle Application Express. It's a browser-based tool that comes free with the database. It allows you to quickly click together reports and specify CSV (or Excel) as output format. (You can also use it to build complete applications).

You find tons of documentation, demos etc. here: http://apex.oracle.com

You can also download the tool at this URL, or you can register for a free workspace and play around with the tool on an Oracle server.

Answer 5

I'm not a PERL programmer, but here's a little extra feature you might want to investigate. Have a look at the concept of external tables in Oracle. You create a table with a definition of something similar to the following:-

CREATE TABLE MY_TABLE
(
  COL1    NUMBER(2),
  COL2  VARCHAR2(20 BYTE)
)
ORGANIZATION EXTERNAL
  (  TYPE ORACLE_LOADER
     DEFAULT DIRECTORY SOME_DIRECTORY_NAME
     ACCESS PARAMETERS 
       ( FIELDS TERMINATED BY ','
         MISSING FIELD VALUES ARE NULL
       )
     LOCATION (SOME_DIRECTORY_NAME:'my_file.csv')
  )
REJECT LIMIT UNLIMITED;

Note this DDL statement assumes you have a directory already created called "SOME_DIRECTORY_NAME". You can then issue DML commands to get data into or out of this table, and once the commit has been done, the data is all nice and neat in your file my_file.csv. After that, do your PERL magic to get the file wherever you want it to be.

Answer 6

How about something as simple as creating the file from sqlplus...

set echo off heading off feedback off colsep ,;
spool file.csv;
select owner, table_name
from all_tables;
spool off;

Answer 7

As dreeves says, DatabaseLink makes this trivial. The part I don't know is the details of the JDBC declaration. But here's how things look for MySQL:

Then from within Mathematica:

Needs["DatabaseLink`"]
conn = OpenSQLConnection[JDBC["mysql","hostname/dbname"], Username->"user", Password->"secret"]
Export["file.csv", SQLSelect[conn, "MyTable"]]

You could of course assign the SQLSelect to a variable first and examine it. It will be a list of lists holding the table data. You can pass conditions to SQLSelect, see the documentation for that (e.g. SQLColumn["Name"]=="joeuser").

The only thing Oracle-specific here is how you make the connection, in the JDBC expression. It is probably something like JDBC["oracle", "hostname/dbname"].

Answer 8

Hello,

Please help me. When I try to see the reports, applying Filter option. I can see the proper output. But as soon as I download the filtered report in CSV format. The Downloaded report has no effect of filter. Please help me out of this.

Raj