External procedures: copying files using kernel32.dll

From: glennh <glennh_at_kw.igs.net>
Date: Tue, 03 Nov 1998 23:39:25 -0500
Message-ID: <363FDA7D.64522B11@kw.igs.net>

I would very much like to be able to make an OS call to copy a file from within a stored procedure. I have an interface program which uses utl_file to write out a file. On NT, utl_file cannot write to a networked drive (tar 1751529, bug# 493693). Since I am using Oracle 8.0.4 on NT, I thought I would try a call to an external procedure. To avoid having a dependence on foreign files, I wanted to avoid compiling my own dll and use 'kernel32.dll' in the system directory since it has all the basic functions I need. In particular, it has 'CopyFileA' and 'CopyFileW' defined with function prototypes:

BOOL CopyFileW( LPCWSTR source, LPCWSTR dest, BOOL FailIfExists );

I can call this from a C program and it will copy the file identified by 'source' to 'dest'. Trying the following from PL/SQL results in successful execution, but no file copy:

create library gh_win_utils
  as 'c:\winnt\system32\kernel32.dll';

create or replace function gh_copy_file (

  p_src in varchar2,
  p_dst in varchar2,
  p_FailIfExist in boolean ) return boolean as external

  library gh_win_utils
  name "CopyFileW"
  language c
  calling standard pascal
  parameters (p_src STRING, p_dst STRING, p_FailIfExist int, return int);

declare
  x boolean;
begin
  x:=gh_copy_file('c:\temp\junk.txt', 'c:\temp\junk2.txt', FALSE); end;

I put an entry as follows in my listener.ora file:

SID_LIST_LISTENER =
  ( SID_LIST =

     ...
     (SID_DESC =


         (SID_NAME = extproc)


         (ORACLE_HOME=d:\orant)


         (PROGRAM=d:\orant\bin\extproc.exe)


      )

   )

I figure others must have done this before. If anyone has suggestions on how to get this to work, I would appreciate them. A lot of this is guess work for me, so I have been fiddling around quite a bit: what is the difference between CopyFileW and CopyFileA, do the parameter sizes match, do I have to worry about the backslash as an escape character, is there more to the listener process, should I run as a procedure or as a function, ...)

Thanks,
Glenn
glennh_at_kw.igs.net Received on Tue Nov 03 1998 - 22:39:25 CST