CODEWITHSUNDEEP
oracle-databasecsvcronsqlplus
KinsDotNet
KinsDotNet

Reputation: 1560

How can I automate exporting of an Oracle table/view?

I have a table in Oracle which I need to export as a file, xls, csv or otherwise. I am currently attempting to do so using sqlplus to export the view to csv in a shell script which is setup as a cronjob.

This seems to be really inelegant as the separators, line size and pagesize seem to create a lot of errors in the data.

Is there a better way to accomplish this than sqlplus?

Upvotes: 1

Views: 3216

Answers (3)

svaor
svaor

Reputation: 2245

You can try use this approach in SQL Plus:

test@ORADEV> set feed off markup html on spool on 
test@ORADEV> spool 'c:\test.xls' 
<br>
test@ORADEV&gt; select 1 from dual; -- or any other query/queries
...
test@ORADEV&gt; spool off 
<br>
test@ORADEV&gt; set markup html off spool off 
<br>
test@ORADEV>

Or you can code your own tool in Java. It's a pretty simple. :)

DbUnitExport.java:

import org.dbunit.database.DatabaseConfig;
import org.dbunit.database.DatabaseConnection;
import org.dbunit.database.IDatabaseConnection;
import org.dbunit.dataset.IDataSet;
import org.dbunit.dataset.excel.XlsDataSet;
import org.dbunit.ext.oracle.OracleDataTypeFactory;

import java.io.BufferedOutputStream;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;

public class DbUnitExport {
    public static void main(String[] args) throws Exception {
        if (args.length != 5 && args.length != 6) {
            System.err.println("java -jar export.jar host port sid user password [filename.xls]");
            System.exit(1);
        }

        final Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@" + args[0] + ":" + args[1] + "/" + args[2], args[3], args[4]);
        final IDatabaseConnection databaseConnection = new DatabaseConnection(connection, args[3]);
        final DatabaseConfig config = databaseConnection.getConfig();
        config.setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, new OracleDataTypeFactory());

        final IDataSet fullDataSet = databaseConnection.createDataSet();
        final String fileName = args.length == 6 ? args[5] : "full.xls";
        XlsDataSet.write(fullDataSet, new BufferedOutputStream(new FileOutputStream(fileName)));
    }
}

I could publish ready compiled tool but I can't share Oracle driver due to limitations of Oracle licensing policy...

Upvotes: 0

David Aldridge
David Aldridge

Reputation: 52376

I'm not sure that the SQL*Plus configuration elements should be causing problems with the output -- if they are, then open a second question on it.

One method you can explore is using a DBMS_Scheduler-driven PL/SQL program to write the data out with UTL_FILE. If you consider the export process to be a part of your database functionality then that would be a good route to go.

I'd also want to know what you want to do with that data? Exported data on its own is just using disk space.

Are you importing it into another system? If so, it would be more robust to connect to the database from that system and pull the data using SQL, as writing data to disk is always fraught with issues concerning date formats, the presence of CR LF in the data, number formats, etc.

Upvotes: 1

ozborn
ozborn

Reputation: 1070

There is no single right answer to this question - it depends on a number of other factors. Are you very familiar are you with sqlplus? Is this table always going to be in Oracle?

I think if the answer is yes to both questions your approach is fine, you can customize your table output to a great extent with SQLPLUS including messing with line size and page size.

However, if the data may move out of Oracle you may want to try something more generic. Like a simple JDBC program (if you like java) or something similar in your favorite scripting language that will allow you to structure easily the data into the format you need for your dump file.

Alternatively, if there is a favorite ETL tool in your group you may want to be using that.

The short answer is it depends - which is probably why someone down-voted your question.

Upvotes: 1

Related Questions