Running operating system commands on Oracle

Oracle in Oracle 8i, there is often a situation in which you want to run operating system commands in a stored procedure. Typically, you can do this by using Oracle Enterprise Manager to set up jobs. However, due to the lack of flexibility of the OEM in setting up the job, The parameters of the set are fixed. In practical applications, it is often necessary to run operating system commands at any time in the SQL statement. Oracle 8i does not directly run the OS command statement, we can use the Dbms_pipe package to achieve this requirement. Dbms_pipe by creating pipelines, You can allow at least two processes to communicate. The pipeline of Oracle has the same conceptual location as the operating system, but the implementation mechanism is different. The following steps are implemented: 1 Create a package, and then name the Daemon,sql statement as follows:/* Create daemon Package */create OR REPLACE PACKAGE Body Daemon As/*execute_system is the function that implements the OS command */function execute_system (command varchar2,timeout number DEFAULT 10 ) return number Isstatus Number;result VARCHAR2 (a); Command_code number;pipe_name VARCHAR2 (30); Beginpipe_name: = Dbms_pipe. Unique_session_name;dbms_pipe. Pack_message (' SYSTEM ');D bms_pipe. Pack_message (pipe_name);D bms_pipe. Pack_message (command);//Send the character */status representing the command to the daemon pipe: = Dbms_pipe. Send_message (' daemon ', timeout); IF status <> 0 thenraise_application_error ( -20010, ' execute_system:error while Sending. Status = ' | | status); End If;status: = Dbms_pipe. Receive_message (pipe_name, timeout); IF status <> 0 thenraise_application_ERROR ( -20011, ' execute_system:error while receiving. Status = ' | | status); End if;/* Gets the return result */dbms_pipe. Unpack_message (result); IF result <> "Done" Thenraise_application_error ( -20012, ' Execute_system:done not Received. '); End If;dbms_pipe. Unpack_message (Command_code);D bms_output. Put_Line (' System command executed. Result = ' | | Command_code); return command_code; End Execute_system;/*stop is to let daemon stop */procedure stops (timeout number DEFAULT) Isstatus number; Begindbms_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; Running the above statement through Sql*plus will create the daemon package for the current user. 2 creates a daemon running on the OS and listens for statements from the above daemon package that require execution of the OS command. The following pro*c code must be pro* by C to precompile first. #include #include EXEC SQL include SQLCA; EXEC SQL BEGIN DECLARE Section;char *uid = "Scott/tiger";/* change to your own user, password, service name */int status in this place; VARCHAR COMMAND[20]; VARCHAR value[2000]; VARCHAR return_name[30]; EXEC SQL End DEclare Section;voidconnect_error () {char msg_buffer[512];int msg_length;int buffer_size = 512; EXEC SQL whenever SQLERROR continue;sqlglm (Msg_buffer, &buffer_size, &msg_length);p rintf ("Daemon Error while connecting:\n ");p rintf ("%.*s\n ", Msg_length, Msg_buffer);p rintf (" Daemon quitting.\n "); exit (1);} Voidsql_error () {char msg_buffer[512];int msg_length;int buffer_size = 512; EXEC SQL whenever SQLERROR continue;sqlglm (Msg_buffer, &buffer_size, &msg_length);p rintf ("Daemon Error while executing:\n ");p rintf ("%.*s\n ", Msg_length, Msg_buffer);p rintf (" Daemon continuing.\n "); Main () {EXEC SQL whenever SQLERROR do Connect_error (); EXEC SQL connect:uid;printf ("Daemon connected.\n"); exec SQL whenever SQLERROR do Sql_error ();p rintf ("Daemon waiting...\n"), while (1) {EXEC SQL executebegin/* receives characters from Deamon * /:status: = Dbms_pipe. Receive_message (' daemon '); if:status = 0 then/* fetch character */dbms_pipe. Unpack_message (: command); End IF; End; End-exec;if (Status = = 0) {Command.arr[command.len] = ' yes ';/* If it is a stop, theThe process exits */if (!strcmp (char *) Command.arr, "STOP") {printf ("Daemon exiting.\n"); ELSE IF (!strcmp (char *) Command.arr, "SYSTEM") {EXEC SQL executebegindbms_pipe. Unpack_message (: return_name);D bms_pipe. Unpack_message (: value); End; End-exec;value.arr[value.len] = ' yes ';p rintf (' would execute system command '%s ', value.arr);/* Run OS command */status = System ( Value.arr); EXEC SQL Executebegindbms_pipe. Pack_message (' Done ');D bms_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 named DAEMON.PC, with proc precompiled: Proc iname=daemon.pc userid= username/password @ service name Sqlcheck=semantics get daemon.c, in C to compile, Note on NT to add Orasql8.lib, or compile through, the connection can not pass. 3 running on the server Daemon.exe

4 Run test statements in sqlplus:sql> variable RV numbersql> execute:rv: = DAEMON. Execute_system (' Ls-la ');P the L/SQL process has completed successfully. sql> EXECUTE:RV: = DAEMON. Execute_system (' dir ');P L/sql process has completed successfully. The use of Sql>dbms_pipe is seen in Oracle's documentation.