Run bat file From Oracle database

[mustafa76]

عثرت على هذا الشرح والكود في احد المواقع ولكن لم اقم بتجريبه

Run Executable From Oracle

This is a small utility that can run an executable from PL/SQL on NT40 (>=Oracle80) or Win95 (>=Personel Oracle80). If your Oracle Server is not >=80, don't worry, dowload Personal Oracle from Oracle, install it to a normal win95 machine and then use RPC (Remote Procedure Call) from your server over a db link.

If you are on Unix and want to use something like that, please see Thomas Kyte (Guru of the Oracle Gurus). He has a solution for this on Unix using pipes. You can find and get the source from comp.databases.oracle.server newsgroup (or www.dejanews.com on Web).


create user mail identified by mail;
grant connect,resource,create library,create public synonym to mail;

CREATE OR REPLACE LIBRARY demolib IS 'c:\winnt\system32\kernel32.dll';

create or replace
function run(lpCmdLine IN varchar2, nCmdShow IN binary_integer) return binary_integer
AS EXTERNAL
NAME "WinExec"
LIBRARY demolib
CALLING STANDARD PASCAL;

create sequence lock#;
grant execute on dbms_lock to mail;

P.S. Here we have a 7.2 production and a 8.0 where-shell-function-is database, when i use RPC from 7.2 like this;
execute shell@data('dir'); -- it gives an error, but when i use values instead of defaults, like this;
execute shell@data('dir',true,false,100); -- it executes succesfully.
In my opinion, this can because of default boolean datatypes.

create or replace procedure
shell (what varchar2,llock boolean default true,show boolean default false,wait number default 100) IS
pfile utl_file.file_type;
d binary_integer;
dummy varchar2(20);
ok boolean:=false;
ploop number:=0;
plock# number;
ndumm number;
begin
if llock then
select lock#.nextval into plock# from dual;
loop
select count(*)
into ndumm
from v$session
where to_number(substr(client_info,instr(client_info,'-')+1))<plock# and
client_info is not null;

pfile:=utl_file.fopen('c:\mail\','shell.ok','r');
utl_file.get_line(pfile,dummy);
utl_file.fclose(pfile);

if substr(dummy,1,1)='O' and ndumm=0 then
pfile:=utl_file.fopen('c:\mail\','shell.ok','w');
utl_file.put_line(pfile,to_char(plock#));
utl_file.fclose(pfile);

ok:=true;
else
ploop:=ploop+1;
if ploop > wait then
raise_application_error(-20000,'Try again later');
else
dbms_application_info.set_client_info('Waiting for ' || to_char(ploop) || ' secs - ' || to_char(plock#));
dbms_lock.sleep(ploop);
dbms_application_info.set_client_info(null);
end if;
end if;

exit when ok;
end loop;
pfile:=utl_file.fopen('c:\mail\','shell.ok','r');
utl_file.get_line(pfile,dummy);
utl_file.fclose(pfile);
if plock#<>to_number(dummy) then
raise_application_error(-20999,'Conflict ! Try again');
end if;
end if;
if show then
d:=1;
else
d:=0;
end if;
-- pfile:=utl_file.fopen('c:\mail\','shell.bat','w'); for Win95
pfile:=utl_file.fopen('c:\mail\','shell.bat','w'); --for NT

utl_file.put_line(pfile,what);
utl_file.put_line(pfile,'echo O> c:\mail\shell.ok');
utl_file.put_line(pfile,'echo ' || what || '(' || to_char(sysdate,'dd/mm/yyyy hh24:mi:ss') || ')' || '>> c:\mail\shell.log');
utl_file.put_line(pfile,'exit');
utl_file.fclose(pfile);

-- d:=run('c:\mail\shell.bat',d); for Win95
d:=run('c:\mail\shell.cmd',d); --for WinNT
end;
/
create public synonym shell for mail.shell;
grant all on shell to public;



http://www.geocities.com/ohakano/htmls/utilities.htm