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.

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...');

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.

• Informix to Oracle Migration Tools and Reference
• Online SQL Conversion Tool

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.