1

I use DBMS_SCHEDULER.PUT_FILE to write file to a specified remote host.

Is there a procedure for deleting the created files?

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
enfix
  • 6,680
  • 12
  • 55
  • 80

2 Answers2

1

Unfortunately, as far as I know, there is no an equivalent in DBMS_SCHEDULER for removing a file in a remote host using the scheduler agent.

PUT_FILE Procedure

This procedure saves a file to the operating system file system of a specified remote host or of the local computer. It differs from the equivalent UTL_FILE procedure in that it uses a credential and can save files to a remote host that has only a Scheduler agent (and not an Oracle Database) installed.

However, you can overcome this limitation by creating a remote job that runs a utl_file.fremove or a shell script installed on the remote host to delete the file pass as parameter in the remote host

Example using EXTERNAL_SCRIPT type for remote job

create or replace procedure my_delete_job ( pname in varchar2 , pfiletodelete in varchar2 )
is
v_job pls_integer;
begin 
    select count(*) into v_job from dba_scheduler where job_name = upper(pname);
    if v_job = 1 
    then 
        dbms_scheduler.drop_job ( job_name => upper(pname) , force => true );
    end if;
    -- UNIX
    DBMS_SCHEDULER.create_job(
        job_name             => 'MY_SH_DELETE',
        job_type             => 'EXTERNAL_SCRIPT',
        job_action           => '/path/my_delete_script.sh '||pfiletodelete||' ',
        auto_drop            => FALSE,
        enabled              => FALSE);
     DBMS_SCHEDULER.set_attribute('MY_SH_DELETE', 'credential_name', 'MY_OS_REMOTE_CRED');
     DBMS_SCHEDULER.set_attribute('MY_SH_DELETE', 'destination', 'host.domain:scheduleragentport');
     DBMS_SCHEDULER.enable('MY_SH_DELETE');
END;
/

exec my_delete_job ( 'MY_DELETE_JOB' , '/home/user/file.txt' );

Keep in mind that remote jobs are normally detached, so you need to consider a way to handle exceptions.

Of course, you can instead create a procedure in the remote database to use the UTL_FILE package. However, the advantage of using external scripts is that you can run remote jobs in hosts which don't have an Oracle database installed.

This article is a good example on how to handle exceptions on remote jobs.

Remote Jobs

Roberto Hernandez
  • 8,231
  • 3
  • 14
  • 43
0

Yes, but not with DBMS_SCHEDULER. Use UTL_FILE.FREMOVE instead.

This procedure deletes a disk file, assuming that you have sufficient privileges.

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • 1
    there is a `put_file` procedure inside `dbms_scheduler` for the remote scheduler agent , since 11g. I think the question refers to this – Roberto Hernandez Sep 17 '21 at 10:27
  • yes, I write to remote scheduler agent. With UTL_FILE how can I manage file on the scheduler? – enfix Sep 17 '21 at 10:36