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

Question

The sample perl script that connects to the oracle database makes a simple SELECT query and spills the results into stdout in CSV format, would be great. Python or any other language available in a typical unix distribution is fine too.

Note that I start from scratch with nothing but a username / password for the remote Oracle database. Is there more than just having the right oracle compound library?

If there is a way to do this directly in mathematics, that would be ideal (apparently this should be possible with J / Link (mathematical integration of java thingy)).

Answer 1

perl - , ... "failmessage" .

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 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();

, :

• sid = XE - oracle, . oracle, "XE", .
• DBD:: Oracle oracle . , .

Answer 2

- , sqlplus...

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

Answer 3

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 4

dreeves, DatabaseLink . , , - JDBC. MySQL:

Mathematica:

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

, SQLSelect . , . SQLSelect, . (, SQLColumn [ "Name" ] == "joeuser" ).

, Oracle, - , JDBC. , - JDBC [ "oracle", "hostname/dbname" ].

Answer 5

Mathematica DatabaseLink, , Oracle. " oracle" ...

Answer 6

Oracle Application Express. , . CSV ( Excel) . ( ).

, .. : http://apex.oracle.com

URL-, Oracle.

Answer 7

PERL, , . Oracle. : -

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 that this DDL statement assumes that you have already created a directory called "SOME_DIRECTORY_NAME". Then you can issue DML commands to get data to or from this table, and after committing, all the data is good and accurate in your my_file.csv file. After that, do your PERL magic to get the file wherever you want it.