How can I run operating system commands in Oracle correctly?

We all know that in the Oracle 8i database, we often find that Oracle runs operating system commands. in general, Oracle Enterprise Manager is used to set jobs to achieve what we want. however, due to the lack of flexibility in the setup of OEM jobs, the parameters of the set jobs are fixed.

In practical applications, it is often necessary to run the operating system commands in Oracle at any time in SQL statements. Oracle 8i does not directly run the OS command statement. We can use the DBMS_PIPE package to implement this requirement.

DBMS_PIPE allows at least two processes to communicate with each other by creating pipelines. The pipelines of Oracle and the operating system have the same concept, but the implementation mechanism is different.
The following describes the implementation steps:

1. Create a package named DAEMON. The SQL statement is as follows:

Create a daemon package

 
 

  
  
CREATE OR REPLACE PACKAGE BODY daemon AS  
 
 

Execute_system is a function used to run OS commands.

 
 

  
  
FUNCTION execute_system(command VARCHAR2,   
  
  
timeout NUMBER DEFAULT 10)   
  
  
RETURN NUMBER IS   
  
  
status NUMBER;   
  
  
result VARCHAR2(20);   
  
  
command_code NUMBER;   
  
  
pipe_name VARCHAR2(30);   
  
  
BEGIN   
  
  
pipe_name := DBMS_PIPE.UNIQUE_SESSION_NAME;   
  
  
DBMS_PIPE.PACK_MESSAGE(''SYSTEM'');   
  
  
DBMS_PIPE.PACK_MESSAGE(pipe_name);   
  
  
DBMS_PIPE.PACK_MESSAGE(command);   
 
 

The character used to send a command to the daemon pipeline.

 
 

  
  
status := DBMS_PIPE.SEND_MESSAGE(''daemon'', timeout);   
  
  
IF status <> 0 THEN   
  
  
RAISE_APPLICATION_ERROR(-20010,   
  
  
''Execute_system: Error while sending. Status = '' || status);   
  
  
END IF;   
  
  
status := DBMS_PIPE.RECEIVE_MESSAGE(pipe_name, timeout);   
  
  
IF status <> 0 THEN   
  
  
RAISE_APPLICATION_ERROR(-20011,   
  
  
''Execute_system: Error while receiving.   
  
  
Status = '' || status);   
  
  
END IF;   
 
 

Get returned results

 
 

  
  
DBMS_PIPE.UNPACK_MESSAGE (result );
  
  
IF result <> ''done' THEN
  
  
RAISE_APPLICATION_ERROR (-20012,
  
  
''Execute _ system: Done not supported ed .'');
  
  
End if;
  
  
 
  
  
DBMS_PIPE.UNPACK_MESSAGE (command_code );
  
  
DBMS_OUTPUT.PUT_LINE (''system command executed. result = ''|
  
  
Command_code );
  
  
RETURN command_code;
  
  
END execute_system;
  
  
Stop is to stop daemon */
  
  
PROCEDURE stop (timeout number default 10) IS
  
  
Status NUMBER;
  
  
BEGIN
  
  
DBMS_PIPE.PACK_MESSAGE (''stop '');
  
  
Status: = DBMS_PIPE.SEND_MESSAGE (''daemon', timeout );
  
  
IF status <> 0 THEN
  
  
RAISE_APPLICATION_ERROR (-20030,
  
  
''Stop: error while sending. status = ''| status );
  
  
End if;
  
  
END stop;
  
  
END daemon;
 
 

Run the preceding statement using SQL * Plus to create a daemon package for the current user. The preceding content introduces how to create a package for running operating system commands in Oracle. I hope you will get some benefits.