Execute OS Command from a Procedure - Informix to Oracle Migration

In Informix you can use SYSTEM statement to execute a OS command from a SPL stored procedure.

In Oracle you can use DBMS_SCHEDULER.CREATE_JOB built-in package procedure to execute a OS command from a PL/SQL stored procedure.

SYSTEM Statement in Informix

You can use the SYSTEM statement in a stored procedure in Informix as follows:

Informix:

  -- Sample procedure to execute a command on Windows
  CREATE PROCEDURE log_message (message VARCHAR(70))
 
    DEFINE os_command VARCHAR(100);
 
    -- Output message to log file
    LET os_command = 'cmd.exe /c echo ' || message || ' >> c:\temp\data.log';
 
    SYSTEM os_command;
 
  END PROCEDURE;

Now you can execute this procedure that outputs a message to c:\temp\data.log file:

Informix

  -- Execute the stored proedure
  CALL log_message('Test message...');

DBMS_SCHEDULER.CREATE_JOB in Oracle

You can use DBMS_SCHEDULER package to create and run a job only once:

Oracle:

  -- Sample procedure to execute a command on Windows
  CREATE OR REPLACE PROCEDURE log_message (message VARCHAR2)
  AS
    os_command VARCHAR2(100);
  BEGIN  
    -- Output message to log file
    os_command := 'cmd.exe /c echo ' || message || ' >> c:\temp\data.log';
 
    DBMS_SCHEDULER.CREATE_JOB(job_name => 'log_message_job', job_type => 'EXECUTABLE', 
                job_action => os_command, enabled => TRUE); 
 END;
 /

Now you can execute this procedure the same as in Informix:

Oracle

  -- Execute the stored proedure
  CALL log_message('Test message...');

Note that the user executing this procedure must have CREATE JOB and GRANT CREATE EXTERNAL JOB privileges in Oracle.

You can grant the privileges by executing:

Oracle:

   -- Grant privileges to create jobs
   GRANT CREATE JOB TO scott;
   GRANT CREATE EXTERNAL JOB TO scott;

Also on a Windows system you have to run OracleJobScheduler service to enable job execution.

Database and SQL Migration Tools

About SQLines

SQLines offers services and tools to help you migrate databases and applications. For more information, please contact us at support@sqlines.com.

Written by Dmitry Tolpeko, dmtolpeko@sqlines.com - August 2013.

You could leave a comment if you were logged in.