Scheduling a query to run on a schedule that exports a CSV in SQL Developer

Question

Good morning,

I am rather new to SQL and I've scoured the internet to try to find a solution to my issue to no avail. I've tried creating procedures, jobs, programs, credentials and schedules through the SQL Developer interface and modifying them as instructed by every article I could find on the subject and I can't seem to get this working.

I'd like to run the following SQL Script every 30 minutes from 0600 to 1700 Mon-Friday, so that it exports a CSV file every 30 minutes.

When I execute the script in SQL developer, it queries the database and saves the file just as intended, but no matter how many times I've tried to get it working on a schedule I can't seem to get t right.

Thanks in advance for the help!

SPOOL C:\Users\X\Documents\SQL\Uploads\X.CSV
SET SQLFORMAT CSV


    SELECT

    NAME_OF_PERSON

    FROM DATABASE;

Answer 1

In versions lower than 12c, Oracle's DBMS_JOB and/or DBMS_SCHEDULER will schedule execution of a stored procedure. It can create a file, but you'll have to use UTL_FILE package to do that, not SPOOL.

As you're on Oracle 12c, its DBMS_SCHEDULER now offers a new job type - SQL_SCRIPT, which lets you schedule a .SQL script. It means that code you posted should be stored as a file. I can't create an example on my 11gXE, but here's a link to ORACLE-BASE site: https://oracle-base.com/articles/12c/scheduler-enhancements-12cr1 and script copied from it which shows how to do that:

CONN test/test@pdb1

-- Create a job with a SQL*Plus script defined in-line,
-- including an explicit connect.
SET SERVEROUTPUT ON
DECLARE
  l_job_name VARCHAR2(30);
  l_script   VARCHAR2(32767);
BEGIN
  l_job_name := DBMS_SCHEDULER.generate_job_name;
  DBMS_OUTPUT.put_line('JOB_NAME=' || l_job_name);

  -- Notice the explicit database connection in the script.
  l_script := 'CONN test/test@pdb1
SPOOL /tmp/test.lst
SELECT SYSDATE, USER FROM dual;
SPOOL OFF';

  DBMS_SCHEDULER.create_job(
    job_name        => l_job_name,
    job_type        => 'SQL_SCRIPT',
    job_action      => l_script,
    credential_name => 'oracle_ol6_121',
    enabled         => TRUE
  );
END;
/

Alternatively, you could use your operating system's scheduling program (Task Scheduler on MS Windows) and tell it to run a .BAT script which would establish SQL*Plus connection and run a .SQL script which contains SPOOL command and that SELECT statement.

Forgot to say: I wouldn't involve SQL Developer into that.