I want to create a export table job, but I can't understand why its not working.

my table is Department

create table department (id number, name varchar2(200));

I want to export a csv file for per day at 9:00 pm. I need to create it.

I only know: 0. create a directory

1. create a PROCEDURE
2. create a DBMS_SCHEDULER.CREATE_PROGRAM
3. create a DBMS_SCHEDULER.CREATE_SCHEDULE
4. create a DBMS_SCHEDULER.CREATE_JOB
5. excute the job

thanks

CodePudding user response：

Yes, you can use DBMS_SCHEDULER by creating in such a way

DECLARE
  v_job_name VARCHAR2(32) := 'jb_exp_emp_data';
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(job_name        => v_job_name,
                            job_type        => 'STORED_PROCEDURE',
                            job_action      => 'exp_emp_data',
                            start_date      => TO_DATE('11-12-2021 21:00:10',
                                                       'DD-MM-YYYY HH24:MI:SS'),
                            repeat_interval => 'FREQ=DAILY; BYHOUR=21;',
                            auto_drop       => false,
                            comments        => 'Exports the content of the department table every day at 9:00PM o''clock ');

  DBMS_SCHEDULER.ENABLE(v_job_name);
END;
/

that starts at the time defined by the start_date parameter, then repeats on every upcoming days at 9pm in the future.

CodePudding user response：

I followed the steps below and it was successful ...

1. Create a directory (path of export file):

    CREATE OR REPLACE DIRECTORY CSVDIR AS 'D:\';

2. Create a procedure:

Create Or Replace Procedure exp_emp_data Is
    
    today varchar2(200);
    fileName varchar2(200);
    
    n_file     utl_file.file_type;
    v_string   Varchar2(4000);
  Cursor c_emp Is
    Select
        id, name
    From
        department;
        
Begin

    select to_char(sysdate,'yyyymmdd','nls_calendar=persian') into today from dual;
    
    fileName := 'empdata' || today || '.csv';

    n_file := utl_file.fopen('CSVDIR', fileName, 'w', 4000);
    v_string := 'ID, Name';
    utl_file.put_line(n_file, v_string);
    -- open the cursor and concatenate fields using comma
    For cur In c_emp Loop
        v_string := cur.id
                    || ','
                    || cur.name;
        
        -- write each row
        utl_file.put_line(n_file, v_string);
    End Loop;
    -- close the file
    utl_file.fclose(n_file);
Exception
    When Others Then
        -- on error, close the file if open
        If utl_file.is_open(n_file) Then
            utl_file.fclose(n_file);
        End If;
End;
/

-------- Test
Begin
    exp_emp_data;
End;
/

3. Create a program:

    BEGIN
    DBMS_SCHEDULER.CREATE_PROGRAM (
     program_name      => 'PROG_EXPORT_TABLE',
     program_action     => 'exp_emp_data',
     program_type      => 'STORED_PROCEDURE');
    END; 
    /

4. Create a job:

     BEGIN
     DBMS_SCHEDULER.CREATE_JOB (
     job_name           =>  'JOB_EXPORT_TABLE',
     job_type           =>  'STORED_PROCEDURE',
     job_action         =>  'PROG_EXPORT_TABLE',
     start_date         =>  '16-nov-2021 11:50:00 pm',
     repeat_interval    =>  'FREQ=DAILY;BYHOUR=23;BYMINUTE=59',
     enabled            =>  true
    );
     END;
     /

5. And enabled it:

    exec dbms_scheduler.enable('JOB_EXPORT_TABLE');