RE: utl_file_dir
- From: "Christian Antognini" <Christian.Antognini@xxxxxxxxxxxx>
- To: "John Kanagaraj" <john.kanagaraj@xxxxxxxxx>
- Date: Tue, 3 Oct 2006 09:51:19 +0200
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
