当前页面位置: » 丰搜网 » 文档中心 » 详细内容
在oracle运行操作系统命令
在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 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);
/*向daemon管道发送表示命令的字符*/
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;
/*获取返回结果*/
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是让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 then
raise_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;
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 sqlerror do sql_error();
printf("daemon waiting...\n");
while (1) {
exec sql execute
begin
/*接收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,该进程就退出*/
