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

mw-hostingAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DavidSenior Oracle Database AdministratorCommented:
edster has a good source -- I would have mentioned the option to shell out of the SQL client session.
0
mw-hostingAuthor Commented:
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

0
mw-hostingAuthor Commented:
I needed to grant OWNER permission to use DBMS_LOCK
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.