"In the first place i want to congratulate you on this wonderful game, but the real reason that i'm writing is because i don't agree with the answer to the 11 June quiz in which you asked: Which of the following choices correctly names a schema-level program or packaged subprogram provided by Oracle that you can use to determine if a file exists?
"In my opinion you can use UTL_FILE.IS_OPEN to determine if the file exists or not (i know that you can not use it by itself but in fact in one situation that you have already tried to open a file you can use it to check the existence).
If you use it in conjunction with utl_file.fopen you can find out if the file exists or not...
DECLARE
vInHandle utl_file.file_type;
BEGIN
vInHandle := utl_file.fopen('ORALOAD', 'test.txt', 'R');
IF utl_file.is_open(vInHandle) THEN
dbms_output.put_line('The File exists');
Else
dbms_output.put_line('The File dosen't exists');
END IF;
END fopen;
Miguel, we salute your creativity! All too often, developers stick to the documented and "intended" purpose of a program and don't explore other ways that it can be applied to solve a problem.
So, by all means, please keep exploring and pushing boundaries.
In this case, however, we must disagree. The IS_OPEN subprogram of UTL_FILE will only tell you if a file is open or not (and even then, so far as we know, this function only checks the value of a field in the UTL_FILE.FILE_TYPE record; it doesn't actually check file status). If the file doesn't exist, it will not be opened and so you never even get to call IS_OPEN.
Hi Steven,
ReplyDeleteAfter further research and remembering using it in the past. I did find it in one of your books the "Oracle Built-in Packages by O'Relly" on page 415 the example for fileexist.sf.
It was referred to as "Testing for files's existence". It was a clear example of file's existence that I used in the past. I guess there is a better way to determine file existence now, but at one time it might have been the only way to determine file existence.
So, it might not be as creative as you might think, since it was an example in your book.
Thanks, I still learn something from this exercise right or wrong.
Kai Liu
Kai,
ReplyDeleteFascinating! It's always a strange and uncomfortable feeling to be confronted with something I wrote long ago (1997 or thereabouts), especially when it contradicts what I am saying today.
You are absolutely right. I straight out recommend using IS_OPEN to answer the question "Does a file exist??
You are also right about the lack of alternatives; at that time, UTL_FILE.FGETATTR did not exist. So I suppose I was being very "creative."
Sadly, I was not correct, even then, so creative doesn't count for much!
I stand by my original blog posting in this thread. The function in my book assumes that if the attempt to open the file fails for any reason, it should be interpreted as "does not exist."
Does that make sense to anyone out there? It does not to me.
So perhaps it is now time to invoke my favorite piece of advance (I believe I heard it first from Tom Kyte):
"Don't believe anything I say. Instead, prove that what I say is right or wrong."
Especially if you are reading something I wrote more than ten years ago!
The bottom line, however, is that I am more than a little embarrassed to have my previous writings cause anyone to lose points in their PL/SQL Challenge ranking.
Steven,
ReplyDeleteWell, creative or not I still have code out there using IS_OPEN to check file existence.
The main point is you can teach an old dog a new trick. So, I will keep this in my future development and to do it the right way going forward. I'm constantly promoting being a better PL/SQL Developer to others.
Thanks for PL/SQL Challenge site and keep up the good work.
Hi Steven,
ReplyDeleteYou are right!!
But, we can use UTL_FILE.IS_OPEN for checking the file exists.
As per your question, the implicit meaning is that which is useful for your requirement.
So, that UTL_FILE.IS_OPEN also valid.
If your intension is NOT to choose the option UTL_FILE.IS_OPEN then you would have to write the question in another format like "which of the following schema-level packaged program(s) for checking the file exists?"
Thanks,
Sailaja
SP, my argument is that you CANNOT use IS_OPEN to check reliably for file existence. It will not work. It is not a matter of implicit meaning. That function tells you if the file is open or not. If you can open it, it exists - so in that sense, you could argue that FOPEN can be used to check for file existence, but not IS_OPEN.
ReplyDeleteSF