Question: Is there a way to check to see if a system file exists in PL/SQL?
Answer:
In Oracle9i Database Release 2 and above, you can use the UTL_FILE.FGETATTR procedure to determine whether or not a file exists. Here is an example of using this program:
SQL> DECLARE
2 l_fexists BOOLEAN;
3 l_file_length PLS_INTEGER;
4 l_block_size PLS_INTEGER;
5 BEGIN
6 -- This file exists....
7 UTL_FILE.fgetattr ( LOCATION => 'TEMP'
8 , filename => 'temp.sql'
9 , fexists => l_fexists
10 , file_length => l_file_length
11 , block_size => l_block_size
12 );
13
14 IF l_fexists
15 THEN
16 DBMS_OUTPUT.put_line ( 'File exists!' );
17 ELSE
18 DBMS_OUTPUT.put_line ( 'File does not exist!' );
19 END IF;
20
21 -- Non-existent file
22 -- This file exists....
23 UTL_FILE.fgetattr ( LOCATION => 'TEMP'
24 , filename => 'nosuchfile.sql'
25 , fexists => l_fexists
26 , file_length => l_file_length
27 , block_size => l_block_size
28 );
29
30 IF l_fexists
31 THEN
32 DBMS_OUTPUT.put_line ( 'File exists!' );
33 ELSE
34 DBMS_OUTPUT.put_line ( 'File does not exist!' );
35 END IF;
36 END;
37 /
File exists!
File does not exist!
In other words, UTL_FILE.FGETATTR returns three pieces of information about a file: whether or not it exists; the length of the file; its block size. Call it, deposit the three values in their own variables and then examine whichever is of interest to you.
That's easy enough, isn't it? Unfortunately, we are not quite done because prior to Oracle Database 10g Release 2, there was a bug (2174036) in this built-in program that would cause the fexists value to be set to NULL when the file didn't exist. In fact, fexists is set to NULL and both file_length and block_size are set to 0.
Consequently, if you are not yet on Oracle Database 10g Release 2, you should take the following approach to determining with this built-in if a file exists:
DECLARE
l_fexists BOOLEAN;
l_file_length PLS_INTEGER;
l_block_size PLS_INTEGER;
BEGIN
-- This file exists....
UTL_FILE.fgetattr ( LOCATION => 'TEMP'
, filename => 'nosuchfile.sql'
, fexists => l_fexists
, file_length => l_file_length
, block_size => l_block_size
);
IF l_fexists is null and l_file_length = 0 and l_block_size = 0
THEN
DBMS_OUTPUT.put_line ( 'File does not exist!' );
ELSE
DBMS_OUTPUT.put_line ( 'File exists!' );
END IF;
END;
/
Having said all that, let's circle back to theme of this column: best practices.
As Oracle will admit, it has some bugs in its code base. And you are quite likely to run into at least one or two as you work with Oracle technology. So the question then should be: how do I deal with these bugs and the resulting workarounds I will write?
Here are my suggestions:
1. Whenever you need to code a workaround, place all the code into its own, separate subprogram. In other words, hide the workaround and instead simply call a program that does whatever it is you need to do.
2. Include a comment about the bug that you are working around, including the Oracle bug number and all other relevant information.
3. Also include in-program documentation on how to upgrade the code when the bug is fixed.
4. Finally, create a document to hold information about the workarounds going into your code. It can be as simple as a word processing document containing a table with these columns:
Date - the date this entry was added to the table
Program Name - the name of the program containing the workaround
Bug - the bug identifier information
Description - a description of the problem and workaround
I will demonstrate with bug 4547551: Suppose I am building an application in an Oracle 9i Database Release 2 environment. I need to check to see if a file exists, and I have discovered this bug. Here are the steps I will take:
1. First, create a separate function to hide the workaround code, including an explanatory header:
CREATE OR REPLACE FUNCTION fexists (
dir_in IN VARCHAR2
, file_in IN VARCHAR2
)
RETURN BOOLEAN
/*
Name of program: fexists
Summary: Uses UTL_FILE.FGETATTR to determine if a file exists.
Includes workaround for bug #4547551:
UTL_FILE fexists return NULL for a non-existent file
while file_length and block_size are both set to 0.
Author: Steven Feuerstein
*/
IS
BEGIN
RETURN NULL;
END fexists;
/
2. Add the logic for the workaround:
IS
l_fexists BOOLEAN;
l_file_length PLS_INTEGER;
l_block_size PLS_INTEGER;
BEGIN
UTL_FILE.fgetattr ( LOCATION => dir_in
, filename => file_in
, fexists => l_fexists
, file_length => l_file_length
, block_size => l_block_size
);
IF l_fexists IS NULL AND l_file_length = 0 AND l_block_size = 0
THEN
RETURN FALSE;
ELSE
RETURN TRUE;
END IF;
END fexists;
/
3. Add comments to explain what is going on and what should be done when the bug is fixed:
CREATE OR REPLACE FUNCTION fexists (
dir_in IN VARCHAR2
, file_in IN VARCHAR2
)
RETURN BOOLEAN
/*
Name of program: fexists
Summary: Uses UTL_FILE.FGETATTR to determine if a file exists.
Includes workaround for bug #4547551:
UTL_FILE fexists return NULL for a non-existent file
while file_length and block_size are both set to 0.
Author: Steven Feuerstein
*/
IS
l_fexists BOOLEAN;
l_file_length PLS_INTEGER;
l_block_size PLS_INTEGER;
BEGIN
UTL_FILE.fgetattr ( LOCATION => dir_in
, filename => file_in
, fexists => l_fexists
, file_length => l_file_length
, block_size => l_block_size
);
-- When bug is fixed...
-- REMOVE FROM HERE
IF l_fexists IS NULL AND l_file_length = 0 AND l_block_size = 0
THEN
RETURN FALSE;
ELSE
RETURN TRUE;
END IF;
-- TO HERE
/* Then uncomment the following line of code: */
--RETURN l_fexists;
END fexists;
/
With this program in place, I can, first of all, check to see if a file exists much more easily than if I were calling UTL_FILE.FGETATTR:
BEGIN
IF fexists ('TEMP', 'indicator_file.txt')
THEN
-- Proceed with program.
...
And the fact that there is a bug, a workaround, any complexity whatsoever, is hidden from the eyes and fingers of the user of this handy utility.
So I use this function, and so do a dozen other members of my team. Two years from now, we upgrade to Oracle Database 10g Release 2. I open up my workarounds document, identify bugs that have been fixed, and then go to each of the programs to remove the workarounds.
When I get to fexists, I breathe an enormous sigh of relief. I certainly don't remember writing this program and don't remember the problem. That's OK; I don't have to remember a thing. The program reminds me, and tells me exactly what to do!
So I refactor the code to remove the workaround, recompile, run my regression test to verify that the program still works (what? You don't have regression tests for all your programs? May I suggest you check out a tool I am building called Qute, www-unit-test.com?), and check it in for use by my entire team.
And off we go, with barely a ripple. Please notice that because I hid the workaround behind a function header, I was able to remove the workaround without having to change any of the application code that uses this function.
If you don't take this approach and instead expose the workaround directly in your application code, it will be almost impossible to go back later and remove the workaround.
You might be saying to yourself: "So what? The code will still work as is..." Sadly, that is not always the case. If Oracle fixes a bug, the workaround might well be rendered invalid along the way.
No comments:
Post a Comment