CODEWITHSUNDEEP
oracle-databasejobs
sndeli
sndeli

Reputation: 101

Create jobs to run on a schedule that exports a CSV

I'm really new to oracle db, can anyone help or suggest what should I do, I need to generate a csv file daily, I have created a script like this but don't know how I can create a jobs using this script.

SET markup csv on
SET VERIFY OFF
set feedback off
set termout off
set echo off
Column ReportDate New_Value XRptDate
Select To_char(sysdate-1,'yyyymmdd') ReportDat From Dual;

Spool D:\test\REPORTSYS_HDR_&xRptDate..csv
SELECT * FROM REPORTSYS_HDR_VW;
Spool Off

Upvotes: 0

Views: 551

Answers (2)

Roberto Hernandez
Roberto Hernandez

Reputation: 8518

One option you might want to use is DBMS_SCHEDULER. If you are using 12c onwards, you might use the new option EXTERNAL_SCRIPT. You have also an option for creating jobs of type SQL_SCRIPT.

Let me show you an example of EXTERNAL_SCRIPT.

I am taking two assumptions because you did not specify clearly

  • Your database is running on Windows.
  • You have access to the database with a user with the necessary privileges to create or manage objects in DBMS_SCHEDULER and DBMS_CREDENTIAL.

**

Steps to create an External Job

**

1.Create the Credential for the Operating system

begin
DBMS_CREDENTIAL.create_credential(
    credential_name => 'NAME_WIN_CREDENTIAL',
    username        => 'youruser',
    password        => 'password',
    windows_domain  => 'yourwindowsdomain');
end;
/

2.Create a JOB to use the credential of type EXTERNAL_SCRIPT

declare
v_job     varchar2(80) := 'YOUR_JOB_NAME';
v_counter pls_integer;
BEGIN
  select count(*) into v_counter from dba_scheduler_jobs where job_name = v_job;
  if v_counter > 0 
  then 
      dbms_scheduler.drop_job ( job_name => v_job , force => true );
  end if;
  DBMS_SCHEDULER.create_job
  (
    job_name             => v_job,
    job_type             => 'EXTERNAL_SCRIPT',
    job_action           => 'D:\test\REPORTSYS_HDR.cmd',
    credential_name      => 'NAME_WIN_CREDENTIAL',
    start_date           => '24-JUL-20 08.15.00 AM Europe/Warsaw',
    repeat_interval      => 'FREQ=DAILY;' -- change the frequency to your needs,
    enabled              =>  TRUE,
    auto_drop            =>  FALSE -- avoid the job to be dropped after finish 
  );
END;
/

3.Include your SQL script into a new cmd program called D:\test\REPORTSYS_HDR.cmd , that can be triggered by the new job.

%ORACLE_HOME%\bin\sqlplus -S youruser/yourpassword@yourtns @D:\test\REPORTSYS_HDR.sql

Upvotes: 2

Anton Tokmakov
Anton Tokmakov

Reputation: 700

You can read about creation jobs in documentation about package DBMS_SCHEDULER. You need read about procedure DBMS_SCHEDULER.CREATE_JOB, I think you can find what you need there.

Upvotes: 0

Related Questions