1. 以数据库管理员权限登录,建立必要的用户名(暂定为cdx)和全局同义名:create user cdx identified by "123"; grant connect to cdx; grant create library to cdx; grant create procedure to cdx; create public synonym cdxpack for cdx.cdxpack;alter user cdx default tablespace users temporary tablespace temp; alter user cdx quota unlimited on users; 2. 建立必要的日志表纪录(根据需要,这一步也可以不要)用于当向表中增加纪录时, 调用触发器进行根据合法性检查,根据结果(是否为真)判断是否调用外部命令。 create table cdx.cmdrunlog ( usr_name varchar2(50), run_date date, cmd_str varchar2(50), para_str varchar2(50), run_rtecode number(10) ); / 3. 编辑文件extproc.c,保存在:e:\extproc目录#include <stdio.h> #include <stdlib.h> #include <time.h>int RunSysCmd(char * str,char * parastr) { char CmdStr[300];if ( str == NULL ) return 1; if (parastr != NULL) sprintf(CmdStr,"%s %s",str,parastr); else sprintf(CmdStr,"%s",str); return system(CmdStr); }3. 运行命令 cl -I. /LD -Zi extproc.c /link msvcrt.lib /nod:libcmt /DLL 生成动态库文件extproc.dll,注意它的存放路径。 4. 运行命令 sqlplus cdx/123 登陆后,输入如下脚本,创建自定义的进程库: CREATE OR REPLACE LIBRARY cdxlib IS 'e:\extproc\extproc.dll'; / show errors;CREATE OR REPLACE PACKAGE cdxpack IS FUNCTION RunSysCmd(str IN CHAR,parastr IN CHAR) RETURN BINARY_INTEGER; end cdxpack; / show errors;CREATE OR REPLACE PACKAGE BODY cdxpack IS FUNCTION RunSysCmd(str IN CHAR,parastr IN CHAR) RETURN BINARY_INTEGER IS EXTERNAL NAME "RunSysCmd" LIBRARY cdxlib PARAMETERS ( str BY REFERENCE, parastr BY REFERENCE); end cdxpack; / show errors;5. 编辑一个文本文件测试上面操作的正确性,文件名为testpack.sql:declare str1 CHAR(50) :='dir'; parastr1 char(50) :='e:\ >e:\extproc\pack.txt'; nRetVal binary_integer; begin nRetVal := cdxpack.RunSysCmd(str1,parastr1); dbms_output.put_line('cdxpack.RunSysCmd:'|| nRetVal); end; /exit; /
grant connect to cdx;
grant create library to cdx;
grant create procedure to cdx;
create public synonym cdxpack for cdx.cdxpack;alter user cdx default tablespace users temporary tablespace temp;
alter user cdx quota unlimited on users;
2. 建立必要的日志表纪录(根据需要,这一步也可以不要)用于当向表中增加纪录时,
调用触发器进行根据合法性检查,根据结果(是否为真)判断是否调用外部命令。
create table cdx.cmdrunlog
(
usr_name varchar2(50),
run_date date,
cmd_str varchar2(50),
para_str varchar2(50),
run_rtecode number(10)
);
/
3. 编辑文件extproc.c,保存在:e:\extproc目录#include <stdio.h>
#include <stdlib.h>
#include <time.h>int RunSysCmd(char * str,char * parastr)
{
char CmdStr[300];if ( str == NULL )
return 1;
if (parastr != NULL)
sprintf(CmdStr,"%s %s",str,parastr);
else
sprintf(CmdStr,"%s",str);
return system(CmdStr);
}3. 运行命令 cl -I. /LD -Zi extproc.c /link msvcrt.lib /nod:libcmt /DLL
生成动态库文件extproc.dll,注意它的存放路径。
4. 运行命令
sqlplus cdx/123
登陆后,输入如下脚本,创建自定义的进程库:
CREATE OR REPLACE LIBRARY cdxlib IS 'e:\extproc\extproc.dll';
/
show errors;CREATE OR REPLACE PACKAGE cdxpack IS
FUNCTION RunSysCmd(str IN CHAR,parastr IN CHAR) RETURN BINARY_INTEGER;
end cdxpack;
/
show errors;CREATE OR REPLACE PACKAGE BODY cdxpack IS
FUNCTION RunSysCmd(str IN CHAR,parastr IN CHAR)
RETURN BINARY_INTEGER
IS EXTERNAL
NAME "RunSysCmd"
LIBRARY cdxlib
PARAMETERS (
str BY REFERENCE,
parastr BY REFERENCE);
end cdxpack;
/
show errors;5. 编辑一个文本文件测试上面操作的正确性,文件名为testpack.sql:declare
str1 CHAR(50) :='dir';
parastr1 char(50) :='e:\ >e:\extproc\pack.txt';
nRetVal binary_integer;
begin
nRetVal := cdxpack.RunSysCmd(str1,parastr1);
dbms_output.put_line('cdxpack.RunSysCmd:'|| nRetVal);
end;
/exit;
/
生成动态库文件extproc.dll,注意它的存放路径。