oracle-databaseplsqloracle12cdbms-scheduler

how to create a export table job in oracle 12c


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


Solution

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

    1. Create a directory (path of export file):
        CREATE OR REPLACE DIRECTORY CSVDIR AS 'D:\';
    
    1. 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;
    /
    
    1. Create a program:
        BEGIN
        DBMS_SCHEDULER.CREATE_PROGRAM (
         program_name      => 'PROG_EXPORT_TABLE',
         program_action     => 'exp_emp_data',
         program_type      => 'STORED_PROCEDURE');
        END; 
        /
    
    1. 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;
         /
    
    1. And enabled it:
        exec dbms_scheduler.enable('JOB_EXPORT_TABLE');