Accessing files copied by UTL_FILE.FCOPY

On Sep 29, 11:14 am, DA Morgan <damor....DeleteThis@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....DeleteThis@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 --
 >> Stay informed about: Accessing files copied by UTL_FILE.FCOPY 

Thanks to both of you. Your answers were extremely helpful. Seems, I
have to rewrite a bit... :-/

Regards
Claus.
 >> Stay informed about: Accessing files copied by UTL_FILE.FCOPY 

Well, just as an addition: the dbms_file_transfer package isn't that
helpful in my case. The documents managed in my DMS addon are related
to objects in a facility management application. Physical file
structure is a bit complex, too. Copying won't be done between always
the same source and destination folder, thus I had to rewrite
directory objects for every file copy action.
Because - and it's getting not easier - copy action is required by a
trigger based event, I cannot rewrite directory objects. Accept from
wrapping them in an autonomous transaction which I do not really want
to do. Addionally the copy method of this function seems to rely on a
very specific file size (in multiples of 256 bit or so) and errors out
when not meeting this requirement. Died.

I tried triggering a scheduled job using DBMS_SCHEDULER package, but
this again has to be called in an autonomous transaction (due to
trigger based file action). When trying out, I found that calling
neither the OS method copy nor a batch file doing the same thing can
be supplied as a job action. Instead I have to call the batch file as
a parameter for cmd.exe. But then, I cannot pass parameters to the
batch file. At least, _I_ didn't manage to pass source and destination
path to the batch file.

Obviously, I will have to store files as blob in database directly
using a special tablespace.

Thank again for your help.
Claus.
 >> Stay informed about: Accessing files copied by UTL_FILE.FCOPY 

Mark D Powell wrote:

> 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

It works well but there is one caveat. Oracle dropped it from 11g so
it is an orphan usable in only a single version of the database.

I have used UTL_FILE with many different file types and can not
think of any issues I have encountered that remind me of the OP's
description.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan DeleteThis @x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
 >> Stay informed about: Accessing files copied by UTL_FILE.FCOPY 

On Sep 30, 10:43 am, DA Morgan <damor... RemoveThis @psoug.org> wrote:
> Mark D Powell wrote:
> > 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
>
> It works well but there is one caveat. Oracle dropped it from 11g so
> it is an orphan usable in only a single version of the database.
>
> I have used UTL_FILE with many different file types and can not
> think of any issues I have encountered that remind me of the OP's
> description.
> --
> Daniel A. Morgan
> Oracle Ace Director & Instructor
> University of Washington
> damor... RemoveThis @x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org

Daniel, I do not have 11g but the package entry does appear in my 11g
PL/SQL Packages and Types manual so are you sure it was dropped.

Claus, here are some options

use external procedure feature to execute program that runs OS or
application product commands to do the file copying

use the dbms_scheduler ability (10g+) to run OS shell scripts that
will handle the file manipulation

use the dbms_alert package to signal from a trigger that a waiting
program is to take action and process a file.

HTH -- Mark D Powell --
 >> Stay informed about: Accessing files copied by UTL_FILE.FCOPY 

Mark D Powell wrote:
> On Sep 30, 10:43 am, DA Morgan <damor....RemoveThis@psoug.org> wrote:
>> Mark D Powell wrote:
>>> 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
>> It works well but there is one caveat. Oracle dropped it from 11g so
>> it is an orphan usable in only a single version of the database.
>>
>> I have used UTL_FILE with many different file types and can not
>> think of any issues I have encountered that remind me of the OP's
>> description.
>> --
>> Daniel A. Morgan
>> Oracle Ace Director & Instructor
>> University of Washington
>> damor....RemoveThis@x.washington.edu (replace x with u to respond)
>> Puget Sound Oracle Users Groupwww.psoug.org
>
> Daniel, I do not have 11g but the package entry does appear in my 11g
> PL/SQL Packages and Types manual so are you sure it was dropped.
>
> Claus, here are some options
>
> use external procedure feature to execute program that runs OS or
> application product commands to do the file copying
>
> use the dbms_scheduler ability (10g+) to run OS shell scripts that
> will handle the file manipulation
>
> use the dbms_alert package to signal from a trigger that a waiting
> program is to take action and process a file.
>
> HTH -- Mark D Powell --

You are correct. I was thinking about the 10gR2 package
DBMS_SCHEMA_COPY.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan.RemoveThis@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
 >> Stay informed about: Accessing files copied by UTL_FILE.FCOPY 

On 30 Sep., 22:29, Mark D Powell <Mark.Pow....RemoveThis@eds.com> wrote:

> use the dbms_scheduler ability (10g+) to run OS shell scripts that
> will handle the file manipulation

Hi Mark,

I managed file ops to be handled by dbms_scheduler package. It's a bit
tricky, because one has to be very precises on how to wrap os commands
in batch files and how to call them via cmd.exe. Most examples I could
find turned out to have errors...

So, many thanks to you and Daniel
Claus.
 >> Stay informed about: Accessing files copied by UTL_FILE.FCOPY