Re: Accessing files copied by UTL_FILE.FCOPY

On Sep 29, 11:14 am, DA Morgan <damor..._at_psoug.org> wrote:
> Claus wrote:
> > Hi folks,
>
> > I'm running into a strange (at least to me) problem. I have
> > implemented a small document management system into an existing
> > Oracle10g database application. DMS requires copying files from one
> > folder to another. Physical files (originals as well as copies) are
> > located on a mapped resource. This drive is mapped by group policy so
> > SYSTEM user which oracle service is running on "sees" that resource.
> > Thus, copying files with utl_file.fcopy is no problem.
> > But I cannot access any copied file with it's application. An MSExcel
> > file won't open at all. Error message says, network drive wasn't
> > available or file was write protected. A pdf files is opened by Adobe
> > Reader but showing empty pages (in correct number, by the way).
> > Network drive is available and, as far as I can see, there is no lock
> > on any file. Original files open without problems.
>
> > Do you have any ideas on where to to closer examination?
>
> > Regards
> > Claus.
>
> No idea where to look but a lot of questions:
>
> 1. What operating system?
> 2. How are drives mapped?
> 3. Is the file size being changed by the copying?
> 4. Why are you doing this at all rather than storing the docs as a BLOB?
>
> I have no problem, with the code here:http://www.psoug.org/reference/securefiles.html
> (scroll down to .mpg)
> storing docs and restoring them on demand in perfectly usable form.
> Similar code is available in all currently supported versions.
> --
> Daniel A. Morgan
> Oracle Ace Director & Instructor
> University of Washington
> damor..._at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>
> - Show quoted text -

Claus, I found the following in the 10gR2 Oracle PL/SQL Packages and Types Reference:

>>

With the UTL_FILE package, PL/SQL programs can read and write operating system text files. UTL_FILE provides a restricted version of operating system stream file I/O.
<<

An Excel file is not an OS text file so I think using it might be the cause of your file corruption.

I have not used it but if you are on 10g+ look up the dbms_file_transfer package which while added to Oracle to support moving files into and out of ASM states it can copy from OS directory to OS directory.
>>

provides procedures to copy a binary file within a database or to transfer a binary file between databases <<

I have not used this routine so I do not know if it will work for you but thought I would mention it.

Depending on your application, your Oracle version, and your platform you might just want to use OS commands to copy your files.

HTH -- Mark D Powell -- Received on Mon Sep 29 2008 - 11:38:38 CDT