Re: copy file to a remote server

From: Andy Klock <andy_at_oracledepot.com>
Date: Fri, 1 Oct 2010 09:27:01 -0400
Message-ID: <AANLkTimsa1RkBaXa-kSfRhSDLCtEd-F=h5Kuu+t5xhEQ_at_mail.gmail.com>



Back in my utl_file_dir/Windows days I remember having issues with accessing mapped drives (typically resolved with lots of rebooting). You may be better off with Oracle Directories.

Or another approach is to convert the file into a clob and then having a program convert it to a file. To give Cary Millsap a plug, his Mr. Trace SQL Developer pluggin works under this premise to deliver developers server side trace information, giving them the tools to think for themselves.

To start convert your file into a clob:

--uncompiled pseudo-code
 create or replace function getmyfile (p_directory varchar2, p_filename varchar2) return clob as

   c clob;
   b bfile := bfilename(p_directory,p_filename);  begin

    dbms_lob.open(b,dbms_lob.file_readonly);
    dbms_lob.createtemporary(c,true);
    dbms_lob.loadfromfile(c,b,dbms_lob.lobmaxsize);
    dbms_lob.close(b);

    return c;

end getmyfile;
 /

Then you could use a program to dump that clob to a file someplace (that's not restricted to an Oracle directory). I would use Java whereas Jared would probably use Perl.

Another option could be to call an Oracle scheduled job that kicks off a bat script or xcopy directly to do this file copy.

On Thu, Sep 30, 2010 at 5:21 PM, Eugene Pipko <epipko_at_unionbay.com> wrote:

> They are not directories.
>
> I set utl_file_dir = * on the source box.
>
> I mapped the drive to the destination box.
>
> When I run UTL_FILE.FCOPY (vSource_dir,vSource_file,vDest_dir,vDest_file); I
> get invalid_operation.
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Oct 01 2010 - 08:27:01 CDT

Original text of this message