How do Oracle directly run the OS command of
- 2020-05-09 19:31:12
- OfStack
In Oracle 8i, it is common to run operating system commands in a stored procedure. 1 generally speaking, this can be achieved when setting jobs with Oracle Enterprise Manager. However, due to the lack of flexibility of OEM in setting jobs, the parameters of the set jobs are fixed. In practice you often need to run in SQL statements and you need to run operating system commands at all times. Oracle 8i does not directly run the OS command statement, we can use the DBMS_PIPE package to implement this 1 requirement.
DBMS_PIPE allows at least two processes to communicate by creating a pipe. The Oracle pipeline has the same concept as the operating system pipeline, but different implementation mechanisms.
Here's how to do it:
1. Create a package and call it DAEMON. The SQL statement is as follows:
/* create daemon package */
CREATE OR REPLACE PACKAGE daemon AS
/*execute_system is a function that implements the os command */
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);
/* sends the character representing the command to the daemon pipe */
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 the return result */
DBMS_PIPE. UNPACK_MESSAGE (result);
IF result
<
>
'done' THEN
RAISE_APPLICATION_ERROR (-20012,
'Execute_system: Done not received.');
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 for daemon to stop */
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;
Running the above statement through Sql*Plus creates the daemon package for the current user.
2. Create a daemon running on OS to listen for statements from the daemon package above that request the os command to be executed. The following code for Pro*C must be precompiled by pro*c.
# include
# include
EXEC SQL INCLUDE SQLCA;
EXEC SQL BEGIN DECLARE SECTION;
char * uid = "scott/tiger"; /* in this place change to the user, password, and service name you have access to */
int status;
VARCHAR command;
VARCHAR value [2000].
VARCHAR return_name [30].
EXEC SQL END DECLARE SECTION;
void
connect_error ()
{
char msg_buffer [512].
int msg_length;
int buffer_size = 512;