1

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?

2

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.

  • I'm familiar with Windows Task Scheduler and .bat files. What I'm sadly not familiar with is executing SQL scripts via the command line, including spool. I may try it. I'm not sure how I would pass on my access credentials. I have a username, password, and database name (4 letters) that I enter to query the Oracle database. There is no traditional connection string that I'm aware of -- not sure if the DB name is an alias over the network. The db in particular is locked down like a mofo. – user45867 Nov 9 '18 at 20:48
  • Basically, you'd just mimic what I posted. Instead of "scott/tiger@xe", you'd enter your credentials (username/password@database) - those you enter when connecting to the DB via PL/SQL Developer. – Littlefoot Nov 9 '18 at 20:55
  • @Littlefoot - you are missing the ever important setting SET MARKUP CSV ON that exists in newer versions of SQLPlus – Michael Kutz Nov 10 '18 at 14:52
  • Thank you, @Michael. As I've said, there are numerous set options, I've written only some of them. It is up to the OP to choose ones they are interested in. – Littlefoot Nov 10 '18 at 15:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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