An example perl script that connects for an oracle database, does an easy Choose query, and spits the outcomes to stdout in CSV format could be great. Python or other language obtainable in an average unix distribution could be fine too.

Observe that I am beginning on your own without a penny but a username/password for any remote Oracle database. Can there be more for this than simply getting the best oracle connection library?

If there's a method to do that directly in mathematica, that would be amazing (most probably it ought to be possible with J/Link (mathematica's java integration thingy)).

What about simple things like 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;

Here's 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)

In perl you could do this something similar to this, departing out all of the my local variable terms 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 individuals requested in comments:

  • sid=XE may be the oracle service id, that's such as the title of the database. Should you install the free version of oracle, it defaults to "XE", however, you can alter it.
  • Setting up DBD::Oracle needs the oracle client libraries in your system. Setting up which will also set all of the necessary atmosphere variables.

As dreeves states, DatabaseLink makes this trivial. The part I'm not sure may be the particulars from the JDBC declaration. But here's how things search for MySQL:

Then from inside Mathematica:

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

You can obviously assign the SQLSelect to some variable first and look at it. It will likely be a listing of lists holding the table data. You are able to pass conditions to SQLSelect, begin to see the documentation for your (e.g. SQLColumn["Title"]=="joeuser").

The only real factor Oracle-specific this is how you are making the bond, within the JDBC expression. It's most likely something similar to JDBC["oracle", "hostname/dbname"].

Mathematica includes a package "DatabaseLink" built-in which should get this to easy but you have to look for a driver for Oracle. Setting up the "oracle client libraries" must do that...

Get Oracle Application Express. It is a browser-based tool which comes free using the database. It enables you to definitely rapidly click together reviews and specify CSV (or Stand out) as output format. (You may also utilize it to construct complete programs).

You discover a lot of documentation, demos etc. here: http://apex.oracle.com

You may also download the tool only at that URL, or sign up for a totally free workspace and adjust the tool with an Oracle server.