In Oracle 8i, it is often necessary to run the operating system command during the stored procedure. In general, this can be achieved when using Oracle Enterprise Manager to set the job. However, because OEMs lack flexibility, set The parameters of the job are fixed. In practical applications, it is often necessary to run the operating system command at any time in the SQL statement. Oracle 8i does not run the os command, we can use the DBMS_PIPE package to implement this requirement.
DBMS_PIPE allows at least two processes to communicate. Oracle's pipelines are conceptually identical to the pipeline of the operating system, but in different mechanisms.
The following describes the specific steps:
1 Create a package, just name the daemon, the SQL statement is as follows:
/ * Create a DAEMON package * /
Create or Replace Package Body Daemon As
/ * EXECUTE_SYSTEM is a function that implements the run 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);
/ * Send characters 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 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 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 THENRAISE_APPLICATION_ERROR (-20030,
'stop: error while sending. status =' || status);
END IF;
End stop;
End daemon;
Run the above statement through SQL * Plus, you will create a DAEMON package for the current user.
2 Create a daemon running on the OS, listens to the statement of the DAEMON package from the above DAEMON package. The following Pro * c code must be pre-prepared first by Pro * C.
#include
#include
EXEC SQL include Sqlca;
EXEC SQL Begin Declare Section;
Char * uid = "scott / tiger"; / * Change users, password, service name * / in this place
Int status;
VARCHAR Command [20];
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;
Exec SQL WHENEVER SQLERROR Continue;
SQLGLM (msg_buffer, & buffer_size, & msg_length);
Printf ("Daemon Error While Connecting: / N");
Printf ("%. * s / n", msg_length, msg_buffer;
Printf ("daemon quitting./n");
Exit (1);
}
Void
SQL_ERROR ()
{
Char msg_buffer [512];
INT MSG_LENGTH;
Int buffer_size = 512;
Exec SQL WHENEVER SQLERROR Continue;
SQLGLM (msg_buffer, & buffer_size, & msg_length);
Printf ("Daemon Error While Executing: / N");
Printf ("%. * s / n", msg_length, msg_buffer;
Printf ("Daemon Continuing./N);
}
Main ()
{
EXEC SQL WHENEVER SQLEC DO Connect_ERROR ();
EXEC SQL Connect: UID;
Printf ("Daemon Connected./N");
EXEC SQL WHENEVER SQLEC SQL_ERROR ();
Printf ("daemon waiting ... / n");
While (1) {
EXEC SQL EXECUTE
Begin
/ * Receive the character sent by DEAMON * /
: status: = dbms_pipe.receive_message ('daemon');
IF: status = 0 THEN
/ * Take the character * /
DBMS_PIPE.UNPACK_MESSAGE (: Command);
END IF;
END;
End-exec;
IF (status == 0)
{
Command.arr [command.len] = '/ 0';
/ * If it is STOP, the process exits * /
IF (! Strcmp ((char *) Command.arr, "stop")) {
Printf ("Daemon EXITING./N);
Break;
}
Else if (! Strcmp ((char *) Command.arr, "System"))
{
EXEC SQL EXECUTE
Begin
DBMS_PIPE.UNPACK_MESSAGE (: return_name);
DBMS_PIPE.UNPACK_MESSAGE (: Value);
END;
End-exec;
Value.arr [Value.len] = '/ 0';
Printf ("Will Execute System Command '% S' / N", Value.arr);
/ * Run OS command * /
Status = system (Value.arr);
EXEC SQL EXECUTE
Begin
DBMS_PIPE.PACK_MESSAGE ('DONE');
DBMS_PIPE.PACK_MESSAGE (: status);
: status: = dbms_pipe.send_message (: return_name);
END;
End-exec;
IF (status)
{
PRINTF
"" Daemon Error While Responding to System Command. ");
Printf ("status:% d / n", status;
}
}
Else
{
PRINTF
("Daemon error: invalid command '% s' received./n",
Command.arr);
}
}
Else
{
Printf ("Daemon Error While Waiting for Signal.");
Printf ("status =% d / n", status);
}
}
EXEC SQL Commit Work Release;
exit (0);
}
The above code is named Daemon.PC, pre-compiled with PROC:
Proc iname = daemon.pc userid = username / password @ service name SQLCHECK = Semantics
Get Daemon.c, compile with C, pay attention to add ORASQL8.LIB on NT, otherwise compiling, the connection cannot be passed.
3 Run daemon.exe on the server
4 Run the test statement in SQLPLUS:
SQL> Variable RV Number
SQL> EXECUTE: RV: = daemon.execute_system ('ls -la');
The PL / SQL process has been successfully completed.
SQL> EXECUTE: RV: = daemon.execute_system ('DIR');
The PL / SQL process has been successfully completed.
SQL>
DBMS_PIPE is used to see Oracle's documentation.
