Oracle Apps Notes

A collection of my random notes, primarily on Oracle Apps

An interesting observation about the filehandle in UTL_FILE

While debugging a program, I had a suspicion that the filehandle in the UTL_FILE package was pointing correctly to a file even though the location of the file had changed after it was opened. So, I decided to test it out by myself.

I created a directory,’TEST_DIR’ and a small procedure which creates a file, ‘test.txt’ in that directory. After the file is created the procedure waits for 45 seconds and then writes a line to the file before closing it. (The reason for the 45 second delay is explained later)


SQL> create or replace directory test_dir as '/home/oracle/mydir';

Directory created

SQL>
SQL> create or replace procedure utlfile_test as
 2 l_filehandle UTL_FILE.file_type;
 3 begin
 4 l_filehandle := utl_file.fopen('TEST_DIR', 'test.txt', 'W', 32767);
 5 dbms_lock.sleep(45);
 6 utl_file.put_line(l_filehandle, 'This is a test');
 7 utl_file.fclose(l_filehandle);
 8 end;
 9 /

Procedure created

SQL>

Next I executed the procedure which opens the file, gets a handle for it and waits for 45 seconds.


SQL> exec utlfile_test;

While the procedure waited for 45 seconds, I logged on to the server and did the following


[oracle@myapps ~]$ pwd
/home/oracle
[oracle@myapps ~]$ ls
APPSORA.env mbox mydir VIS_myapps.env
[oracle@myapps ~]$
[oracle@myapps ~]$ cd mydir/
[oracle@myapps mydir]$ ls
test.txt
[oracle@myapps mydir]$ mv test.txt /home/oracle/
[oracle@myapps mydir]$
[oracle@myapps mydir]$ ls
[oracle@myapps mydir]$
[oracle@myapps mydir]$ cd ..
[oracle@myapps ~]$
[oracle@myapps ~]$ ls
APPSORA.env mbox mydir test.txt VIS_myapps.env
[oracle@myapps ~]$
[oracle@myapps ~]$ cat test.txt
[oracle@myapps ~]$

To summarize my actions, I moved the file from the location where it was created to a different location. I also checked the contents of the file in the new location and noted that it was empty.

I waited for the procedure to complete, which it did successfully.


SQL> exec utlfile_test;

PL/SQL procedure successfully completed

SQL>

And then I checked the contents of the file in the new location . The procedure had written a line to it even though its location had changed! What bonding, what love šŸ™‚


[oracle@myapps ~]$ pwd
/home/oracle
[oracle@myapps ~]$
[oracle@myapps ~]$ ls
APPSORA.env mbox mydir test.txt VIS_myapps.env
[oracle@myapps ~]$
[oracle@myapps ~]$
[oracle@myapps ~]$ cat test.txt
This is a test
[oracle@myapps ~]$

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: