FreeLists / oracle-l / RE: utl_file_dir

["Christian Antognini" <Christian.Antognini@xxxxxxxxxxxx>]

Hi John

First of all thank you for your reply.

> I have a long list of UTL_FILE_DIR lines in my Apps 11i instance,
> one
> for each directory. There are 22 lines and the total number of
> characters used is around 600 characters. However, 'show parameter
> utl_file_dir' in SQL*Plus shows only the first 512 bytes.

In my case, 10.2, "show parameter" shows an empty string. Probably for
that reason I was mislead. But, as you wrote, the directory is used at
runtime... Mhmm... Really strange behavior... 

Below a simple test.

Regards,
Chris


1) add some lines up to the limit of 512 bytes

SQL> create pfile='/tmp/a.ora' from spfile;

$ for i in `ls -1 /dev | head -102`
> do
>   echo "utl_file_dir=/ab" >> /tmp/a.ora
> done

SQL> startup force pfile=/tmp/a.ora

SQL> select length(value) from v$parameter where name = 'utl_file_dir';

LENGTH(VALUE)
-------------
          508

2) add one more line, i.e. go beyond the limit (here it is interesting
to see that "show parameter" shows an empty string and that in
v$parameter the value is cut)

$ echo "utl_file_dir=/tmp" >> /tmp/a.ora

SQL> select length(value) from v$parameter where name = 'utl_file_dir';

LENGTH(VALUE)
-------------
          512

SQL> select substr(value,500) from v$parameter where name =
'utl_file_dir';

SUBSTR(VALUE,
-------------
 /ab, /ab, /t 

SQL> show parameter utl_file_dir

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

3) in any case the last directory can be used

SQL> declare
  2    l_exists boolean;
  3    l_length number;
  4    l_bs binary_integer;
  5  begin
  6    utl_file.fgetattr('/tmp','a.ora',l_exists,l_length,l_bs);
  7    if l_exists
  8    then
  9      dbms_output.put_line(l_length);
 10      dbms_output.put_line(l_bs);
 11    end if;
 12  end;
 13  /
2955
4096

$ ls -l /tmp/a.ora
-rw-r--r--  1 oracle oinstall 2955 2006-10-03 09:41 /tmp/a.ora




--
http://www.freelists.org/webpage/oracle-l