Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers 2

up vote 6 down vote accepted

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.

share|improve this answer
add comment
/** **/
DECLARE
   lfFilelog UTL_FILE.FILE_TYPE;

   lspath varchar2(50) := 'c:\';
   lsfile varchar2(50) := 'file';
BEGIN
   lfFilelog := UTL_FILE.FOPEN(lspath,lsfile||'.txt','w');

   FOR r IN (SELECT FROM table) LOOP
      UTL_FILE.PUT_LINE(lfFilelog ,r.row);
   END LOOP;

   UTL_FILE.FCLOSE_ALL;
EXCEPTION
   WHEN UTL_FILE.INVALID_OPERATION THEN
      UTL_FILE.PUT_LINE(lfFilelog,SQLERRM);
      UTL_FILE.PUT_LINE(lfFilelog,' Invalid File open Operation');
      UTL_FILE.FCLOSE_ALL;
      RAISE_APPLICATION_ERROR(-20051,'Invalid File open Operation');

   WHEN UTL_FILE.INVALID_FILEHANDLE THEN
      UTL_FILE.PUT_LINE(lfFilelog,SQLERRM);
      UTL_FILE.PUT_LINE(lfFilelog,' Invalid File Name');
      UTL_FILE.FCLOSE_ALL;
      RAISE_APPLICATION_ERROR(-20052,'Invalid File Name');

   WHEN UTL_FILE.READ_ERROR THEN
      UTL_FILE.PUT_LINE(lfFilelog,SQLERRM);
      UTL_FILE.PUT_LINE(lfFilelog,' Read Error');
      UTL_FILE.FCLOSE_ALL;
      RAISE_APPLICATION_ERROR(-20053,'Read Error');

   WHEN OTHERS THEN
      UTL_FILE.PUT_LINE(lfFilelog,SQLERRM);
      UTL_FILE.FCLOSE_ALL;
      DBMS_OUTPUT.PUT_LINE(SQLERRM);
      ROLLBACK;
END;
share|improve this answer
1  
Just want to make it clear that "r.row" sintax defined in above statement UTL_FILE.PUT_LINE(lfFilelog ,r.row); last time I check, is not supported in oracle 8i,9i,10g and 11.2.0.1 –  croca Mar 21 at 21:04
1  
-1 SELECT FROM table is not a valid sql statement. r.row only works if the select list of the query contains a filed named row. In most cases you want to exportmore than one column. It does not make sense to catch an error caused by opening or writing to a file and try to write the error message in the same file. This will raise another exception and the file_close, the dbms_output_put_line and the raise_error will not be executed. –  miracle173 Mar 21 at 21:37
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.