Oracle – Automate Export/Unload of Data

exportoracleoracle-sql-developer

Oracle SQL Developer has an option to export the contents of a query result to various formats (CSV/fixed width/Excel/XML). Is there any way to automate it?

If not, what free tools are available that will let me automate exports into the same formats that SQL Developer is capable of exporting to?

Best Answer

There are many ways to export data from Oracle and automate the functionality. Be sure to understand exactly what the data export is being used for, though. If it is for interop between systems, then export in a format your receiving system can understand. If it is for backup purposes, go for the exp/expdp (data pump) method because a database backup needs to store much more than simply data. (Better yet, just use RMAN. But I know many DBAs who also like to do full db exports on a regular basis as well.)

You can use a number of tools to accomplish this, TOAD being the one that springs to mind. It has a powerful data export tool that supports scheduling. There is a free version available, but I am uncertain if it has the scheduling functionality. Worth a try, though.

Alternatively, use the tools already at your disposal: SQL*PLUS, PL/SQL, and cron (or the Windows Scheduler if you run Windows). For a good example of how to write a PL/SQL routine that exports a table to a CSV file, see http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::p11_question_id:88212348059 . If you use a procedure, you can schedule it from Oracle's own job scheduler.

You can do similar things purely with SQL*PLUS as well (without getting into writing a procedure), but it isn't quite as flexible, but for what you want, it might be just what you need. SQL*PLUS is also easily scriptable so that you can then call it whenever you want via cron/Windows Scheduler.

SQL*PLUS works really well on its own to create good fixed-width reports, but it is possible to do HTML and CSV as well. XML will probably require a procedure of some sort, but I'm not an export here, so SQL*PLUS may not be perfect here (it will output to HTML, though, so that might be good enough). If exporting to Excel, remember that the current versions use XML as their file format, which makes things easy (in one way) and painful in other ways (like needing to know beforehand how many rows you're going to have in the output file).

Regardless, with a little bit of work and the combination of two or three tools, you should be able to export your data in any format you wish on any schedule you desire.

Hope that helps.

Related Solutions

Oracle – Automated Scripting of DDL

Well, if sqlplus is screwing your dbms_metadata.get_ddl output, why not select the output in a CLOB and write the CLOB to filesystem.

e.g.

DECLARE
    data CLOB;
    objType varchar2(30) := 'TABLE';
    objSchema varchar2(30) := 'SCOTT';
    objName varchar2(30) := 'EMP';
    fname varchar2(256) := objType || '_' || objSchema || '_' || objName || '.sql';
BEGIN
    SELECT dbms_metadata.get_ddl(objType,objName,objSchema) into data from dual;
    DBMS_XSLPROCESSOR.CLOB2FILE(data,'DATA_PUMP_DIR',fname);
END;
/

This should get you correct DDL, w/o the output getting messed up. The only thing is that the script will be created on the DB server and not on the client from where you invoke sqlplus.

The script gets saved in the directory pointed to by the 'DATA_PUPM_DIR' entry on the DB Server. i.e.

select directory_path from all_directories where directory_name like 'DATA_PUMP_DIR';

What's more you can add some sort of iteration over all the tables/indexes etc of a schema, and get a complete schema's DDL in no time. I do it all the time.

Can Oracle SQL developer export data as a set of Update commands

Not using a built in command, but...

If an UPDATE really can satisfy your requirements as your title implies, then no inserts should be necessary and the primary key must be in sync between the databases.

Your requirements don't allow remote updates, but if you can remotely query the database, then you can join the local table to the remote table to create the statements that will need to be run the remote system. Here is a demonstration:

drop table t1;
drop table t2;
create table t1 as (select level c1, level c2 from dual connect by level <=7);
create table t2 as (select * from t1);
update t2 set c2 = null where c1 in (3,6);
select * from t1;
select * from t2;

select 'UPDATE t2 SET c2 = ' || t1.c2 || ' WHERE c1=' || t1.c1 || ';' sql from t1 
join t2 on t1.c1 = t2.c1
where t1.c2 <> t2.c2 OR t2.c2 is null;

If you cannot query the remote database, you can update every row like this:

drop table t1;
create table t1 as (select level c1, level c2 from dual connect by level <=7);

select 'UPDATE t2 SET c2 = ' || t1.c2 || ' WHERE c1=' || t1.c1 || ';' sql from t1;

Best Answer

Related Solutions

Oracle – Automated Scripting of DDL

Can Oracle SQL developer export data as a set of Update commands

Related Question