Search notes:

Oracle: Creating a scheduler-program to call a PowerShell script

This examples demonstrates how a scheduler program and job can be created to execute a PowerShell script with Oracle.

The PowerShell script to invoke:

write-host "pwd = $pwd"

$dir = split-path $myInvocation.myCommand.path
(get-date).toString() | out-file $dir/ps.log -append

Create the program …

begin

   dbms_scheduler.create_program (
      program_name        => 'tq84_prg_run_powershell_script',
      program_type        => 'executable',
      program_action      => 'C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe',
      number_of_arguments => 3
   );

   dbms_scheduler.define_program_argument (
      program_name        => 'tq84_prg_run_powershell_script',
      argument_position   =>  1,
      argument_type       => 'varchar2',
      default_value       => '-noProfile'
   );

   dbms_scheduler.define_program_argument (
      program_name        => 'tq84_prg_run_powershell_script',
      argument_position   =>  2,
      argument_type       => 'varchar2',
      default_value       => '-f'
   );

   dbms_scheduler.define_program_argument (
      program_name        => 'tq84_prg_run_powershell_script',
      argument_position   =>  3,
      argument_type       => 'varchar2',
      default_value       => 'C:/Users/Rene/scheduler/script.ps1'
   );

end;
/

… the necessary credentials …

begin
--
-- TODO: dbms_scheduler.create_credential is deprecated
--       in favor of dbms_credential.create_credential
--
   dbms_scheduler.create_credential(
      credential_name       => 'RENES_CREDENTIALS',
      username              => 'rene',
      password              => '…'
   );
end;
/

… and the job:

begin
   dbms_scheduler.create_job (
      job_name            => 'tq84_job_run_powershell_script',
      program_name        => 'tq84_prg_run_powershell_script',
      repeat_interval     => 'freq=secondly;interval=10',
      start_date          =>  systimestamp + interval  '5' second,
      end_date            =>  systimestamp + interval '10' minute,
      credential_name     => 'RENES_CREDENTIALS',
      auto_drop           =>  true
   );
end;
/

begin
   dbms_scheduler.enable('
      tq84_prg_run_powershell_script,
      tq84_job_run_powershell_script
   ');
end;
/

Let some time pass, then query the log:

select * from user_scheduler_job_log          where job_name = 'TQ84_JOB_RUN_POWERSHELL_SCRIPT' order by log_date;
select * from user_scheduler_job_run_details  where job_name = 'TQ84_JOB_RUN_POWERSHELL_SCRIPT';

Cleaning up:

begin
   dbms_scheduler.drop_job('tq84_job_run_powershell_script');
end;
/

begin
   dbms_scheduler.drop_program('tq84_prg_run_powershell_script');
end;
/

begin
   dbms_scheduler.drop_credential('RENES_CREDENTIALS');
end;
/

Index