Last active
December 26, 2015 16:49
-
-
Save pshapoval/7182614 to your computer and use it in GitHub Desktop.
Oracle PL-SQL procedure for running OS script
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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