<p>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.  </p>

<p>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?</p>

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

[{'answer_id': 65568, 'author': 'Jumpy', 'author_id': 9416, 'author_profile': 'https://Stackoverflow.com/users/9416', 'pm_score': 3, 'selected': True, 'text': '<p>In perl you could do something like this, leaving out all the my local variable declarations and ... or die "failmessage" error handling for brevity.</p>\n\n<pre><code>use DBI; \nuse DBD::Oracle;\n\n$dbh = DBI-&gt;connect( "dbi:Oracle:host=127.0.0.1;sid=XE", "username", "password" );\n\n# some settings that you usually want for oracle 10\n$dbh-&gt;{LongReadLen} = 65535;\n$dbh-&gt;{PrintError} = 0;     \n\n$sth = $dbh-&gt;prepare("SELECT * FROM PEOPLE");\n\n$sth-&gt;execute();\n\n# one example for error handling just to show how it\'s done in principle\nif ( $dbh-&gt;err() ) { die $dbh-&gt;errstr(); }\n\n# you can also do other types of fetchrow, see perldoc DBI \nwhile ( $arrayref = $sth-&gt;fetchrow_arrayref ) {\n    print join ";", @$arrayref;\n    print "\\n";\n}\n\n$dbh-&gt;disconnect();\n</code></pre>\n\n<p>Two notes, because people asked in comments:</p>\n\n<ul>\n<li>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. </li>\n<li>Installing DBD::Oracle needs the oracle client libraries on your system. Installing that will also set all the necessary environment variables.</li>\n</ul>\n'}, {'answer_id': 65689, 'author': 'Galuvian', 'author_id': 1566663, 'author_profile': 'https://Stackoverflow.com/users/1566663', 'pm_score': 2, 'selected': False, 'text': '<p>Here is an implementation in Python:</p>\n\n<pre><code>import cx_Oracle, csv\n\norcl = cx_Oracle.connect(\'ohd/john@ohddb\')\ncurs = orcl.cursor()\n\ncsv_file_dest = "C:\\\\test.csv"\n\noutput = csv.writer(open(csv_file_dest,\'wb\'))\n\nsql = "select * from parameter"\n\ncurs.execute(sql)\n\nheaders_printed = False\nfor row_data in curs:        \n    if not headers_printed:\n        cols = []\n        for col in curs.description:\n            cols.append(col[0])\n        output.writerow(cols)\n        headers_printed = True\n\n    output.writerow(row_data)\n</code></pre>\n'}, {'answer_id': 65713, 'author': 'dreeves', 'author_id': 4234, 'author_profile': 'https://Stackoverflow.com/users/4234', 'pm_score': 0, 'selected': False, 'text': '<p>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...</p>\n'}, {'answer_id': 66028, 'author': 'Sten Vesterli', 'author_id': 9363, 'author_profile': 'https://Stackoverflow.com/users/9363', 'pm_score': 0, 'selected': False, 'text': '<p>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). </p>\n\n<p>You find tons of documentation, demos etc. here: \n<a href="http://apex.oracle.com" rel="nofollow noreferrer">http://apex.oracle.com</a></p>\n\n<p>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. </p>\n'}, {'answer_id': 84764, 'author': 'Mike McAllister', 'author_id': 16247, 'author_profile': 'https://Stackoverflow.com/users/16247', 'pm_score': 0, 'selected': False, 'text': '<p>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:-</p>\n\n<blockquote>\n<pre><code>CREATE TABLE MY_TABLE\n(\n  COL1    NUMBER(2),\n  COL2  VARCHAR2(20 BYTE)\n)\nORGANIZATION EXTERNAL\n  (  TYPE ORACLE_LOADER\n     DEFAULT DIRECTORY SOME_DIRECTORY_NAME\n     ACCESS PARAMETERS \n       ( FIELDS TERMINATED BY \',\'\n         MISSING FIELD VALUES ARE NULL\n       )\n     LOCATION (SOME_DIRECTORY_NAME:\'my_file.csv\')\n  )\nREJECT LIMIT UNLIMITED;\n</code></pre>\n</blockquote>\n\n<p>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.</p>\n'}, {'answer_id': 110013, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 3, 'selected': False, 'text': '<p>How about something as simple as creating the file from sqlplus...</p>\n\n<pre><code>set echo off heading off feedback off colsep ,;\nspool file.csv;\nselect owner, table_name\nfrom all_tables;\nspool off;\n</code></pre>\n'}, {'answer_id': 603665, 'author': 'jfklein', 'author_id': 72919, 'author_profile': 'https://Stackoverflow.com/users/72919', 'pm_score': 2, 'selected': False, 'text': '<p>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:</p>\n\n<p>Then from within Mathematica:</p>\n\n<pre><code>Needs["DatabaseLink`"]\nconn = OpenSQLConnection[JDBC["mysql","hostname/dbname"], Username-&gt;"user", Password-&gt;"secret"]\nExport["file.csv", SQLSelect[conn, "MyTable"]]\n</code></pre>\n\n<p>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").</p>\n\n<p>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"].</p>\n'}]
['https://Stackoverflow.com/questions/65447', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/4234/']