Re: writing text files from within stored procedure

In article <91lrua$97p$1_at_nnrp1.deja.com>,   Ed Stevens <ed.stevens_at_home.com> wrote:
> Subject: writing text files from within stored procedure
>
> Platform: Oracle 8.0.5 Enterprise; NT 4 sp6
>
> One of our developers is working on a PL/SQL procedure that, among
> other things, uses UTL_FILE to write to a text file. We discovered
 the
> utl_file_dir initialization parm, and everything works as expected as
> long as we specify a directory on the local to the box hosting the
> database. However, if we specify a directory on a drive that was
> mapped to another box, the procedure returns an INVALID OPERATION
 error.
>
> My working theory is that the Oracle server is running under NT userid
> SYSTEM and that this userid is absolutely forbidden to touch anything
> across the network. (We discovered this restriction on the SYSTEM
> userid on a similar problem of running REXX scripts from the NT
> Scheduler service.
>
> Is my working theory correct? If so, is there any good solution, or
> are we stuck with writing this file to a local drive, then copying to
> its final destination from outside the PL/SQL procedure?
>
> TIA
>
> Ed Stevens
>
> Sent via Deja.com
> http://www.deja.com/
>

Security

The PL/SQL file I/O feature is available for both client side and server side PL/SQL. The client implementation (text I/O) is subject to normal operating system file permission checking, and it does not need any additional security constraints. However, the server implementation might be running in a privileged mode, and will need additional security restrictions that limit the power of this feature.

Server Security

Server security for PL/SQL file I/O consists of a restriction on the directories that can be accessed. Accessible directories must be specified in the instance parameter initialization file (INIT.ORA). Specify the accessible directories for the UTL_FILE functions in the initialization file using the UTL_FILE_DIR parameter. For example:

UTL_FILE_DIR = <directory name>

If the initialization file for the instance contains the line

UTL_FILE_DIR = /usr/jsmith/my_app,

then the directory /usr/jsmith/my_app is accessible to the FOPEN function. Note that a directory named /usr/jsmith/My_App would not be accessible on case-sensitive operating systems.

The parameter specification UTL_FILE_DIR = * has a special meaning. This entry turns off directory access checking, and it makes any directory accessible to the UTL_FILE functions.

Note: The UTL_FILE package is similar to the client-side TEXT_ IO package currently provided by Oracle Procedure Builder.

Restrictions for a server implementation require some API differences between UTL_FILE and TEXT_IO. In PL/SQL file I/O, errors are returned to you using PL/SQL exceptions.

Note: The directory specification is different on different platforms.

--
Michael Krolewski
Rosetta Inpharmatics
mkrolewski_at_rii.com
              Usual disclaimers


Sent via Deja.com
http://www.deja.com/