Re: utl_file

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Wed, 17 Dec 2003 06:53:33 +1100
Message-ID: <3fdf62be$0$18688$afc38c87@news.optusnet.com.au>

"Pete Finnigan" <plsql_at_petefinnigan.com> wrote in message news:xvqMjmAsZx3$Qxgp_at_peterfinnigan.demon.co.uk...
> Hi Peter
>
> From 9iR2 the utl_file_dir parameter in the init.ora file is obsolete.
> There is a metalink note describing this fact 196939.1 "Ext/Pub
> UTL_FILE_DIR init.ora parameter obsolete from 9.2" From 9iR2 you need to
> create a directory object and pass this to fopen in utl_file. Have a
> look at the package source or search tahiti for an example.
>
> kind regards
>
> Pete

This is a bizarre one, Pete. Have a look at my 9i Release 2 system:

SQL> select * from v$version;

BANNER

---

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production NLSRTL Version 9.2.0.1.0 - Production

SQL> show parameter latches
SQL> show parameter db_block_max
SQL> show parameter utl_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------------------
---
utl_file_dir                         string

Now, OK: I don't have a setting for utl_file_dir, but it at least exists as a parameter. Whereas things like ..._lru_latches and _max_dirty_target (which are also obsolete in 9i R2) simply don't get listed at all.

Moreover:

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------------------
---
spfile                               string
%ORACLE_HOME%\DATABASE\SPFILE%
                                                 ORACLE_SID%.ORA

SQL> alter system set utl_file_dir='C:\' scope=spfile;

SQL> connect sys/xxxxx_at_win92 as sysdba
Connected.

SQL> startup force
ORACLE instance started.

Total System Global Area 135338868 bytes

Fixed Size                   453492 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes

Database mounted.
Database opened.
SQL> show parameter utl_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------------------
---
utl_file_dir                         string      C:\

So the thing remains settable, and useable. Yet that's what the Metalink note you refer to says, which is rather odd.

Since I've been merrily setting utl_file_dir in class as part of my log miner demo (where the static dictionary file gets written to) for the best part of a year, and the demo has had no more than it's usual share of disasters thanks to my abysmal typing skills, I'd have to question whether this particular Metalink note is actually describing reality, or Oracle intentions.

Which is not to say that 'create directory' isn't a better way to go, for sure. But to say utl_file_dir is actually obsolete in 9iR2 is, I think, inaccurate.

Unless I'm missing something.

Regards
HJR Received on Tue Dec 16 2003 - 13:53:33 CST