Wednesday, May 30

Run system commands from Oracle with PL/SQL

I friend of mine asked if it was possible to show the exact Linux kernel version on an Oracle server without actually having shell access to the server.
He had full access to Oracle with sysdba/dba roles etc, but not SSH.
I've seen some versions of executing system commands from Java but never really liked the idea of invoking Java for something simple like that.

One way I thought of would be to use dbms_scheduler to execute a job with an executable job_typ. The first problem was to find a way to actually return the standard output from the execution to Oracle.
Ok, so my 'hack' here is a stored procedure (entirely in PL/SQL) that creates a job with dbms_scheduler; calling /bin/sh as the executable and hands it a temporary script to execute. In the script I have a simple redirect to a temporary spool file and then the procedure simply reads and outputs the content of the file. It's a bit of a hack but at least it gets the job done and doesn't use Java.
I haven't drilled down on what kind of permissions you need to actually use the procedure but I suspect it's quite a lot.
The temporary spool file handling in my example is quite poor, but works. :)
A word of warning as usual when using PL/SQL, this code example is a proof of concept. It needs *loads' of error catching etc. in order to be production ready, use with caution.

Example of use:
oracle@htpc:~$ rsqlplus hlinden/password as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed May 30 21:55:06 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> set serveroutput on
SQL> @system_run

Procedure created.

SQL> exec system_run('ls -l /home/oracle/bin');
total 12
-rwxr-xr-x 1 oracle dba 797 Nov 5 2006 backup_controlfile.sh


PL/SQL procedure successfully completed.

SQL> exec system_run('uname -a');
Linux htpc 2.6.20-15-generic #2 SMP Sun Apr 15 06:17:24 UTC 2007 x86_64 GNU/Linux

PL/SQL procedure successfully completed.

SQL>
And here is the procedure code:
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) := '/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;
/

8 comments:

CarlosAL said...

Cool.

A little bit tricky, but cool.

Cheers.

Carlos.

Soujanya said...

Very interesting.It will be great if you can think of something that will allow us to run the sql script files inside the procedure.
Execute command has 32k limit and cant use "@/../test.sql" in the procedure.

Pls tell us if you have a similar trick..

RD said...

I tried the procedure, which compiled without any error. But I get runtime error:
Procedure created.

SQL> show error
No errors.
SQL> exec system_run('uname -a');
BEGIN system_run('uname -a'); END;

*
ERROR at line 1:
ORA-29280: invalid directory path
ORA-06512: at "SYS.UTL_FILE", line 33
ORA-06512: at "SYS.UTL_FILE", line 436
ORA-06512: at "SYS.SYSTEM_RUN", line 11
ORA-06512: at line 1

Usn said...

Cool hack, but is there a way to adjust the OS user that stuff runs with? I'd need "oracle" to execute some RMAN script using the oracle OS user permission universe.

Regards
Usn

Anonymous said...

Good Afternoon.
The process work with 10g Oracle version, maybe ould change the porcess to be used in 9i version ? I see yhat DBMS_SCHEDULER dont appear in 9i, and another document explain that 9I use DBMS_JOB.

Grothaal said...

Cool code, but I have some problems running it due to permissions. It creates a ".sh" script on the fly, but the script isnt "executable" (permission "x" on Linux". How do you make it executable?

ORA-27486: privil├ęgios insuficientes (insuficient privileges)
ORA-06512: em "SYS.DBMS_ISCHED", line 411
ORA-06512: em "SYS.DBMS_ISCHED", line 452
ORA-06512: em "SYS.DBMS_SCHEDULER", line 1082
ORA-06512: em ".P_SYSTEM_RUN", line 20
ORA-06512: em line 2

Hampus said...

I execute /bin/sh and just give a script with the command in as an argument to sh. I don't execute the script itself as an executable.

christi parks said...

Hello all,I am new and I would like to ask that what are the benefits of sql training, what all topics should be covered and it is kinda bothering me ... and has anyone studies from this course wiziq.com/course/125-comprehensive-introduction-to-sql of SQL tutorial online?? or tell me any other guidance...
would really appreciate help... and Also i would like to thank for all the information you are providing on sql.