RE: utl_file_dir

From: Christian Antognini <Christian.Antognini_at_trivadis.com>
Date: Tue, 3 Oct 2006 09:51:19 +0200
Message-ID: <F2C9CCA71510B442AF71446CAE8AEBAF615CA7@MSXVS04.trivadis.com>

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

Received on Tue Oct 03 2006 - 02:51:19 CDT