asked on

Have a Job execute a bash script at the end

I have the following job logging data to a file (thanks to you), and I wanted it to also run a BASH script at the end. I don't know how to do that, linux admin, not a dba.

DECLARE
   v_file   UTL_FILE.file_type;
BEGIN
   v_file :=
      UTL_FILE.fopen ('MONITOR_DIR',
                      'refresh.log',
                      'a',
                      32767);

   UTL_FILE.put_line (v_file,
                      'Start :' || TO_CHAR (SYSDATE, 'hh24:mi:ss'));

   BEGIN
      dbms_refresh.refresh('"OWNER"."CON"');
   EXCEPTION
      WHEN OTHERS
      THEN
         UTL_FILE.put_line (v_file, 'Error: ' || SQLERRM);
   END;

   UTL_FILE.put_line (v_file,
                      'END :' || TO_CHAR (SYSDATE, 'hh24:mi:ss'));
   UTL_FILE.fclose (v_file);
END;

Open in new window

ASKER CERTIFIED SOLUTION

edster9999

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

David VanZandt

edster has a good source -- I would have mentioned the option to shell out of the SQL client session.

mw-hosting

ASKER

I tried to run the code (attached) and get the following errors:

LINE/COL ERROR
-------- -----------------------------------------------------------------
36/3 PL/SQL: Statement ignored
36/3 PLS-00201: identifier 'DBMS_LOCK' must be declared

CREATE OR REPLACE PROCEDURE OWNER.system_run(cmd IN varchar2)
IS
  script_file   varchar2(40) := 'my-temp-script.sh';
  script_data   varchar2(4000);
  MyFile        utl_file.file_type;
  d             varchar2(4000);
  dump_file     varchar2(40) := '/tmp/my-temp-file.dat';
  dump_type     utl_file.file_type;
BEGIN
  -- Open file
  MyFile := utl_file.fopen('TMP',script_file,'w');
  -- Write data to file
  script_data := '#!/bin/bash' || chr(10) || cmd||'>'||dump_file;
  utl_file.put_line(MyFile, script_data, FALSE);
  -- Close file
  utl_file.fflush(MyFile);
  utl_file.fclose(MyFile);
  -- Purge old logs, no fun anyway
  dbms_scheduler.purge_log(JOB_NAME=>'TEST');
  -- Execute script
  -- The job is created as disabled as
  -- we execute it manually and will
  -- drop itself once executed.
  dbms_scheduler.create_job(
    job_name   => 'TEST',
    job_type   => 'EXECUTABLE',
    job_action => '/bin/bash',
    number_of_arguments => 1,
    start_date => SYSTIMESTAMP,
    enabled    => FALSE);
  dbms_scheduler.set_job_argument_value('TEST', 1, '/tmp/'||script_file);
  dbms_scheduler.enable('TEST');
  -- Wait for the job to be executed
  -- usually done within 1 second but
  -- I set it to 2 just in case.
  dbms_lock.sleep(2);
  -- Open the output file and
  -- print the result.
  dump_type := utl_file.fopen('TMP',dump_file,'r');
  loop
    begin
      utl_file.get_line(dump_type,d);
      dbms_output.put_line(d);
    exception
      when others then
        exit;
    end;
  end loop;
  utl_file.fclose(dump_type);
  -- Clean up our temp files
  utl_file.fremove('TMP', script_file);
  utl_file.fremove('TMP', dump_file);
END;
/

Open in new window

mw-hosting

ASKER

I needed to grant OWNER permission to use DBMS_LOCK