Run the operating system command in Oracle
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 enables at least two processes to communicate at least two processes. Oracle's pipelines are conceptually identical, but in realization, the implementation specific steps are implemented:
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 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); / char * represents conduit daemon sends command * / status: = DBMS_PIPE.SEND_MESSAGE ( 'daemon', timeout); IF status <> 0 THEN RAISE_APPLICATION_ERROR (-20010, 'Execute_system: Error while sending Status =.' || status) ;
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 return results * / DBMS_PIPE. Unpack_message (result); if result <> 'DONE' THEN RAISE_APPLICATION_ERROR (-20012, 'Execute_System: DONE NOT receivated.'); Endiff;
DBMS_PIPE.UNPACK_MESSAGE (command_code); DBMS_OUTPUT.PUT_LINE ( 'System command executed result =.' || command_code); RETURN command_code; END execute_system; / * stop daemon is 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) Endness; End Daemon; Run the above statement through SQL * Plus, 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 the user, password, service name * / int status; varchand [20]; varchar value [2000]; 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 SQLERROR Do Connect_ERROR (); Exec SQL Connect: UID; Printf (" Daemon Connected./N ");
EXEC SQL WHENEVER SQLEC DO SQL_ERROR (); Printf ("daemon waiting ... / n"); While (1) {EXEC SQL EXECUTE BEGIN / * Receives 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 exitation./n"); Break;} Else if (! ") 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 ("DAEMON Error While Responding to System Command."); Printf ("status:% d / n", status;}} else {printf ("daemon error: invalid command '% s' received ./N ",", "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'); PL / SQL process has been successfully completed. SQL> EXECUTE: RV: = daemon.execute_system ('DIR'); PL / SQL process has been successfully completed. SQL> DBMS_PIPE Usage See Oracle's documentation.
