Someone said to me the other day, “PL/SQL’s OK, but it’s not much of a shell”. I didn’t really investigate quite what he meant by this, but he’s a wizard at the Unix command line and probably feels that anything I can do with PL/SQL loops, conditional branching, variable assignment and so on, he can accomplish perfectly well in a Bash or Korn shell.

He’s probably right: at the command line, you could type

chmod 777 /name/of/shellscript.sh

…to make it executable. The equivalent in PL/SQL is just a tad messier:

 begin
  dbms_scheduler.create_job
      (job_name=> MYJOB,
       job_type=>'EXECUTABLE',
       job_action=>'/bin/chmod',
       number_of_arguments => 2,
       start_date=> systimestamp,
       enabled=>false);
    
      dbms_scheduler.set_job_argument_value
      (job_name=> MYJOB,
       argument_position=> 1,
       argument_value => '777');
    
      dbms_scheduler.set_job_argument_value
      (job_name=>MYJOB,
       argument_position=> 2,
       argument_value => 'name/of/shellscript.sh');
       
      dbms_scheduler.enable(MYJOB); 
end;

Still, it achieves the same thing… provided that you make sure these sorts of external jobs are run with sufficient privileges (by default, in 10gR2 and above they run as the user ‘nobody’, but by editing ORACLE_HOME/rdbms/admin/externaljob.ora you can make them run as someone with the power to actually do something).

A lot of gotchas and workarounds, to be sure… but it’s still a shell of sorts!