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;
/