IDC资讯中心
在oracle 8i中,往往会出现要在存储过程中运行操作系统命令的情况.一般来说,利用oracle enterprise manager设定作业时可以达到这个目的.但是由于oem在设定作业缺乏灵活性,设定的作业的参数是固定的.在实际应用当中往往需要在sql语句当中运行需要随时运行操作系统命令.oracle 8i没有直接运行os命令的语句,我们可以利用dbms_pipe程序包实现这一要求.dbms_pipe通过创建管道,可以让至少两个进程进行通信.oracle的管道与操作系统的管道在概念上有相同的地方,但是在实现机制不同.下面介绍实现具体步骤:1 创建一个程序包,姑且起名叫daemon,sql语句如下:/*创建daemon程序包*/create or replace package body daemon as/*execute_system是实现运行os命令的函数*/function execute_system(command varchar2,timeout number default 10)return number isstatus number;result varchar2(20);command_code number;pipe_name varchar2(30);beginpipe_name := dbms_pipe.unique_session_name;dbms_pipe.pack_message(system);dbms_pipe.pack_message(pipe_name);dbms_pipe.pack_message(command);/*向daemon管道发送表示命令的字符*/status := 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;/*获取返回结果*/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);dbms_output.put_line(system command executed. result = ||command_code);return command_code;end execute_system;/*stop是让daemon停止*/procedure stop(timeout number default 10) 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;通过sql*plus运行以上语句,将为当前用户创建daemon程序包.2 创建在os上运行的守护进程,监听由上面的daemon程序包发来的要求执行os命令的语句.以下pro*c的代码,必须由pro*c先进行预编译.#include #include exec sql include sqlca;exec sql begin declare section;char *uid = “scott/tiger”;/*在这个地方改为你自己访问的用户,密码,服务名*/int status;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);printf(“daemon error while connecting:\n”);printf(“%.*s\n”, msg_length, msg_buffer);printf(“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);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 sqlerror do sql_error();printf(“daemon waiting…\n”);while (1) {exec sql executebegin/*接收deamon发来的字符*/:status := dbms_pipe.receive_message(daemon);if :status = 0 then/*取出字符*/dbms_pipe.unpack_message(:command);end if;end;end-exec;if (status == 0){command.arr[command.len] = \0;/*如果是stop,该进程就退出*/if (!strcmp((char *) command.arr, “stop”)){printf(“daemon exiting.\n”);break;}else if (!strcmp((char *) command.arr, “system”)){exec sql executebegindbms_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);/*运行os命令*/status = system(value.arr);exec sql executebegindbms_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);}以上代码起名为daemon.pc,用proc预编译:proc iname=daemon.pc userid=用户名/密码@服务名 sqlcheck=semantics得到daemon.c,在用c进行编译,注意在nt上要把orasql8.lib加上,否则编译通过,连接没法通过.3 在服务器上运行daemon.exe
4 在sqlplus运行测试语句:sql> variable rv numbersql> execute :rv := daemon.execute_system(ls -la);pl/sql 过程已成功完成。sql> execute :rv := daemon.execute_system(dir);pl/sql 过程已成功完成。sql>dbms_pipe的用法见oracle的文档.
