Skip to content

Instantly share code, notes, and snippets.

@pshapoval
Last active December 26, 2015 16:49
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save pshapoval/7182614 to your computer and use it in GitHub Desktop.
Save pshapoval/7182614 to your computer and use it in GitHub Desktop.
Oracle PL-SQL procedure for running OS script
CREATE DIRECTORY TMP AS '/tmp/';
create or replace
PROCEDURE 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) := '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||'>'||'/tmp/'||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_RUN');
-- 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_RUN',
job_type => 'EXECUTABLE',
job_action => '/bin/bash',
number_of_arguments => 1,
start_date => SYSTIMESTAMP,
enabled => FALSE);
dbms_scheduler.set_job_argument_value('TEST_RUN', 1, '/tmp/'||script_file);
dbms_scheduler.enable('TEST_RUN');
-- 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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment