Oracle – How to Automate CSV Exports of Queries in PL/SQL Developer

automationcsvexportoracle

I'm no pro in Oracle or PL/SQL Developer (I mostly use and admin mySQL and MS SQL Server).

I'm not an admin of any sort, but have read access to many tables in Oracle via PL/SQL Developer.

I need the ability to schedule an automatic (say, daily) export of a few tables (well, queries) in CSV format. Whether it's to my local computer, network, email — anything.

Does anyone know how this might be achieved?

Best Answer

One option (on MS Windows) is to

  • create SQL file which spools data into a file
  • create BAT script which calls that SQL script
  • schedule a daily job in Task Scheduler

Prerequisite is to install SQLPlus, if you don't have it already. PL/SQL Developer is a GUI tool; SQLPlus is a command-line tool. It can be installed via Oracle Client software, downloadable from Oracle Technology Network's Download section.

SQL file, based on Scott's schema, might look like this:

set linesize 100
set pagesize 100
set colsep ';'

spool dept.txt
select * from dept;
spool off

spool emp.txt
select * from emp;
spool off;

exit

BAT file:

sqlplus -s -l scott/tiger@xe @daily.sql

Task Scheduler: create a job which will run the BAT script at desired time; make it run in a directory you have access to (such as C:\Temp or similar).

Resulting DEPT.TXT file looks like this:

    DEPTNO;DNAME         ;LOC                                                                       
----------;--------------;-------------                                                             
        10;ACCOUNTING    ;NEW YORK                                                                  
        20;RESEARCH      ;DALLAS                                                                    
        30;SALES         ;CHICAGO                                                                   
        40;OPERATIONS    ;BOSTON

List of SET commands in the SQL file is really simple - there are numerous options you might want to research. For example, this is a longer list with more options (including column formatting):

set termout off 
set trimspool on 
set echo off 
set verify off 
set autoprint off 
set serveroutput off 
set arraysize 1000 
set pagesize 0
set linesize 100
set long 10000
set numwidth 10
set feedback off
set colsep ';'
col empno format 99999
col ename format a10
col sal format 999G990

You'll have to try these (and, possibly some more) to find a set which suits your needs.

Related Question