Oracle - Automate Export/Unload of Data

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?

I don't know of a way to automate SQL Developer exports, no.

However, it's easy enough to generate CSV and/or fixed width files from PL/SQL (using the UTL_FILE package). Tom Kyte has an example of programs that generate CSV or fixed width files using either PL/SQL or Pro*C. Either can be relatively easily automated using your favorite scheduler.

XML outputs can be automated in much the same way depending on how much control you need over the XML that is actually generated. If you just need valid XML and don't care about the format of that XML, you can do something like this using the DBMS_XMLGEN package (this example is straight from the documentation).

DECLARE
  qryCtx DBMS_XMLGEN.ctxHandle;
  result CLOB;
BEGIN
  qryCtx := DBMS_XMLGEN.newContext('SELECT * FROM hr.employees');
  -- Set the row header to be EMPLOYEE
  DBMS_XMLGEN.setRowTag(qryCtx, 'EMPLOYEE');
  -- Get the result
  result := DBMS_XMLGEN.getXML(qryCtx);
  INSERT INTO temp_clob_tab VALUES(result);
  --Close context
  DBMS_XMLGEN.closeContext(qryCtx);
END;
/

You could then write the result CLOB to a file using UTL_FILE and, again, use your favorite scheduler to schedule it.

If you just need to generate a file that Excel can open, you probably just need to create a CSV or a tab-delimited file. Excel can open either type of file relatively easily though you do get the extra step of being prompted to accept the delimiter that it found (it generally detects the delimiter correctly).

Generating native Excel output is a bit more challenging. There are PL/SQL APIs for generating Excel files such as Jason Bennett's ExcelDoctypeUtils. But I've always gone with a Java stored procedure that used either the JExcelAPI or that Apache POI to generate the Excel file. This, obviously, requires a bit more work to implement. Once you have a stored procedure that can write an Excel file, as with the other options, you can automate calling that procedure using your favorite scheduler.

SQL Workbench has a pretty powerful command line export tool which is non-database specific