Home » SQL & PL/SQL » SQL & PL/SQL » Directory Name with UTL_FILE.FOPEN() (Oracle, 12.2, Unix)
Directory Name with UTL_FILE.FOPEN() [message #686042] Fri, 03 June 2022 18:46 Go to next message
Duane
Messages: 508
Registered: December 2002
Senior Member
I'm just trying to wrap my head around the proper way to use an Oracle Directory with UTL_FILE.FOPEN().

I use the following code:


DECLARE 

F1 UTL_FILE.FILE_TYPE; 

BEGIN 

F1 := UTL_FILE.FOPEN('MYDIR','MYFILE','R'); 
.
.
.
.
UTL_FILE.FCLOSE(F1);

END;

Another person is using this method:


DECLARE 

DIRECTORY_NAME varchar2(100);

DIRECTORY_NAME := LOOKUP('MYDIR')  -- this is a function that looks up MYDIR from the Oracle Directory Table and returns the Path (/var/spool/hr).

F1 UTL_FILE.FILE_TYPE; 

BEGIN 

F1 := UTL_FILE.FOPEN(DIRECTORY_NAME,'MYFILE','R'); 
.
.
.
.
UTL_FILE.FCLOSE(F1);

END;

Both methods work but the other person wants to use his LOOKUP Function to return the Path versus just using the Oracle Directory Name. We're on 12.2 and going to 19c. I feel, we should be using the actual Oracle Directory Name and not a Path when we go to 19c.

Will his method continue to work on 19c? I know mine will since it's the actual Oracle Directory Name.

[Updated on: Fri, 03 June 2022 18:47]

Report message to a moderator

Re: Directory Name with UTL_FILE.FOPEN() [message #686043 is a reply to message #686042] Sat, 04 June 2022 00:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68341
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

No, it won't work as UTL_FILE_DIR parameter is desupported since 18c.
See Database Reference, 18.1 Desupported Features section.
Note it was already deprecated in your release.
See Database Reference, Deprecated Features section.
Re: Directory Name with UTL_FILE.FOPEN() [message #686044 is a reply to message #686043] Sat, 04 June 2022 07:36 Go to previous messageGo to next message
Duane
Messages: 508
Registered: December 2002
Senior Member
Just so we're clear. I don't believe he is using the UTL_FILE_DIR in any of the LOOKUP code.

He's just doing this:

create or replace function LOOKUP (DirectoryName varchar2) return varchar2

Path varchar2(100);

begin
  select Path
    into Path 
    from all_directories
      where directory_name = DirectoryName;

  return Path

end;

I might not have all of the columns/tables names correct but he is returning the PATH from ALL_DIRECTIORES.

He's using that PATH in the FOPEN statement.


DIRECTORY_NAME varchar2(100);

DIRECTORY_NAME := LOOKUP('MYDIR')  -- this is a function that looks up MYDIR from the Oracle Directory Table and returns the Path (/var/spool/hr).

F1 UTL_FILE.FILE_TYPE; 

F1 := UTL_FILE.FOPEN(DIRECTORY_NAME,'MYFILE','R'); 

Bottom line:

My Statement = F1 := UTL_FILE.FOPEN('MYDIR','MYFILE','R');

His Statement = F1 := UTL_FILE.FOPEN('/var/spool/hr','MYFILE','R'); -- After the LOOKUP function is called to return the PATH of MYDIR
Re: Directory Name with UTL_FILE.FOPEN() [message #686045 is a reply to message #686044] Sat, 04 June 2022 07:40 Go to previous messageGo to next message
Duane
Messages: 508
Registered: December 2002
Senior Member
Maybe the better question is "How is he using the UTL_FILE_DIR parameter"? He's just calling a simple function to return the PATH. Not sure how the UTL_FILE_DIR parameter is being used at that point. Just trying to better understand this.
Re: Directory Name with UTL_FILE.FOPEN() [message #686046 is a reply to message #686045] Sat, 04 June 2022 09:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68341
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

UTL_FILE uses/used UTL_FILE_DIR if you specify an OS directory instead of an Oracle one.

Re: Directory Name with UTL_FILE.FOPEN() [message #686047 is a reply to message #686046] Sat, 04 June 2022 09:39 Go to previous messageGo to next message
Duane
Messages: 508
Registered: December 2002
Senior Member
Michel Cadot wrote on Sat, 04 June 2022 14:20

UTL_FILE uses/used UTL_FILE_DIR if you specify an OS directory instead of an Oracle one.

What I understand from you and, he's only returning a PATH (/var/spool/hr) from the all_directories table, it would appear to me that his version will continue to work on 19c.

The all_directories table has an entry that is MYDIR with a Path of /var/spool/hr. That's an Oracle Directory. I don't believe any OS directory has been defined. The DBA has only been creating Oracle Directories going forward. Any Oracle Directory using his method has worked so far.
Re: Directory Name with UTL_FILE.FOPEN() [message #686048 is a reply to message #686047] Sat, 04 June 2022 09:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68341
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You don't understand what I mean.
I don't talk about you LOOKUP function I don't know the code and I don't care.
I say that (with DIRECTORY_NAME something like /var/spool/hr):
UTL_FILE.FOPEN(DIRECTORY_NAME,'MYFILE','R');
uses/used UTL_FILE_DIR which then won't work in 19c as UTL_FILE_DIR no more exists in this version.

UTL_FILE_DIR was used to know which OS directories is accessible to UTL_FILE.

Re: Directory Name with UTL_FILE.FOPEN() [message #686049 is a reply to message #686048] Sat, 04 June 2022 11:23 Go to previous messageGo to next message
Duane
Messages: 508
Registered: December 2002
Senior Member
Ok, I think I follow you.

Oracle Directory

Name Path

MYDIR /var/spool/hr


12.2 Works because of UTL_FILE_DIR

UTL_FILE.FOPEN('MYDIR','MYFILE','R');
UTL_FILE.FOPEN('/var/spool/hr','MYFILE','R');

19c Won't work because no UTL_FILE_DIR

UTL_FILE.FOPEN('/var/spool/hr','MYFILE','R');

19c Works

UTL_FILE.FOPEN('MYDIR','MYFILE','R');


Anyone have 19c to confirm? Take any Oracle Directory you have and do UTL_FILE.FOPEN('Oracle Directory','MYFILE','R'); UTL_FILE.FOPEN('Oracle Directory Path','MYFILE','R'); Only one works, both work?

Re: Directory Name with UTL_FILE.FOPEN() [message #686050 is a reply to message #686049] Sat, 04 June 2022 11:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68341
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You don't trust Oracle documentation?
Anyway:
MIKJ3DB1> select directory_path from all_directories where directory_name='MY_DIR';
DIRECTORY_PATH
------------------------------------------------------------------------------------
D:\Oracle\Save

MIKJ3DB1> declare F1 UTL_FILE.FILE_TYPE;
  2  begin
  3    F1 := UTL_FILE.FOPEN('MY_DIR','T.TXT','R');
  4    UTL_FILE.FCLOSE(F1);
  5    F1 := UTL_FILE.FOPEN('D:\Oracle\Save','T.TXT','R');
  6  end;
  7  /
declare F1 UTL_FILE.FILE_TYPE;
*
ERROR at line 1:
ORA-29280: invalid directory object
ORA-06512: at "SYS.UTL_FILE", line 41
ORA-06512: at "SYS.UTL_FILE", line 478
ORA-06512: at line 5

MIKJ3DB1> @v

Oracle version: 19.13.0.0.211019 EE - JVM v1.8.0_201 - timezone files v32
Re: Directory Name with UTL_FILE.FOPEN() [message #686051 is a reply to message #686042] Sat, 04 June 2022 12:13 Go to previous messageGo to next message
Duane
Messages: 508
Registered: December 2002
Senior Member
It's not that I don't trust the Oracle Documentation but I have to be 100% sure. We'll have over a 100 or so programs to change IF I'm correct.

Your test is not Apples to Apples. The Path doesn't include a Drive Letter and I'm sure your method would fail on 19c.

The test needs to be JUST THE PATH. Like '/var/spool/hr' or '\Oracle\Save'.

F1 := UTL_FILE.FOPEN('\Oracle\Save','T.TXT','R');

Re: Directory Name with UTL_FILE.FOPEN() [message #686052 is a reply to message #686051] Sat, 04 June 2022 15:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68341
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

"D:\Oracle\Save" is an absolute and correct Windows path just like "/var/spool/hr" is *ix one.
As you can see in the SELECT statement on ALL_DIRECTORIES I posted.

"\Oracle\Save" is a relative path on the instance working directory disk which is of course NOT what you would want.

Re: Directory Name with UTL_FILE.FOPEN() [message #686053 is a reply to message #686052] Sat, 04 June 2022 15:28 Go to previous messageGo to next message
Duane
Messages: 508
Registered: December 2002
Senior Member
Ok, sounds good. I just didn't want something like this to happen...."That guy is using a Drive Letter before the path, so yea, it's going to break for him because of that. My method doesn't return a Drive Letter and it's only the Path so it won't break in 19c.". Then we go round and round about Drive Letter and no Drive Letter.
Re: Directory Name with UTL_FILE.FOPEN() [message #686054 is a reply to message #686053] Sat, 04 June 2022 15:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68341
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

There is no driver letter on *ix, so if you work on *ix, of course, you won't have a drive letter.
But as your function returns the path that ALL_DIRECTORIES contains and as I showed my ALL_DIRECTORIES (working on Windows) does have a drive letter otherwise it won't work as I work on Windows which requires the drive letter (and I showed in my example it works with the drive letter in ALL_DIRECTORIES when using the associated Oracle directory).

Re: Directory Name with UTL_FILE.FOPEN() [message #686055 is a reply to message #686054] Sat, 04 June 2022 16:31 Go to previous messageGo to next message
Duane
Messages: 508
Registered: December 2002
Senior Member
I get that. I can show him your example if it comes down to it.
Re: Directory Name with UTL_FILE.FOPEN() [message #686079 is a reply to message #686042] Wed, 08 June 2022 09:23 Go to previous messageGo to next message
Duane
Messages: 508
Registered: December 2002
Senior Member
So it appears I had the LOOKUP function wrong. I must have done the LOOKUP on the server side which returns a PATH.

On the Oracle side, the LOOKUP function just returns the Directory Name, which, doesn't make any sense to me at all.


DIRECTORY_NAME varchar2(100);

DIRECTORY_NAME := LOOKUP('MYDIR') -- this is a function that looks up MYDIR from the Oracle Directory Table and returns the Directory Name (MYDIR).

DIRECTORY_NAME := 'MYDIR'

So, the LOOKUP function takes a Directory Name and then returns that Directory Name. I'm not even sure why we even have this because it seems pointless to me. It's a function that doesn't even need to be called. Why we're even doing this is beyond me.

These are the same:

F1 := UTL_FILE.FOPEN('MY_DIR','T.TXT','R');

F1 := UTL_FILE.FOPEN(LOOKUP('MY_DIR'),'T.TXT','R');


Anyway, thanks for your help.
Re: Directory Name with UTL_FILE.FOPEN() [message #686082 is a reply to message #686079] Wed, 08 June 2022 11:30 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3191
Registered: January 2010
Location: Connecticut, USA
Senior Member
Well, what does function LOOKUP return if there is no oracle directory object with name passed as parameter?

SY.
Re: Directory Name with UTL_FILE.FOPEN() [message #686083 is a reply to message #686082] Wed, 08 June 2022 12:17 Go to previous messageGo to next message
Duane
Messages: 508
Registered: December 2002
Senior Member
I'm not sure since I haven't looked at his code. Maybe nothing or some default value. I really haven't looked at it too closely since I don't use it. I use the actual Directory Name (i.e. MYDIR, HR, STUDENT...etc) when using a statement that needs it.
Re: Directory Name with UTL_FILE.FOPEN() [message #686084 is a reply to message #686083] Wed, 08 June 2022 12:21 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3191
Registered: January 2010
Location: Connecticut, USA
Senior Member
And default directory can be the answer.

SY.
Re: Directory Name with UTL_FILE.FOPEN() [message #686085 is a reply to message #686084] Wed, 08 June 2022 12:26 Go to previous messageGo to next message
Duane
Messages: 508
Registered: December 2002
Senior Member
I'll have to try it to see what it returns. Now I'm curious. What I find puzzling is if a developer doesn't know the directory name they would have to go to all_directories to find it. Once they have the directory name, they are putting it into a function that just returns the same directory name they themselves looked up. Very odd.
Re: Directory Name with UTL_FILE.FOPEN() [message #686086 is a reply to message #686042] Wed, 08 June 2022 13:36 Go to previous message
Duane
Messages: 508
Registered: December 2002
Senior Member
The LOOKUP function just returns what's given to it. If an Oracle Directory can't be found then it just returns the value supplied in the function.

HR := LOOKUP('HR');

MYDIR := LOOKUP('MYDIR');

ELVIS := LOOKUP('ELVIS');

DOES THIS REALLY WORK := LOOKUP('DOES THIS REALLY WORK');
Previous Topic: List of partitions
Next Topic: Decode used in conjunction with sign
Goto Forum:
  


Current Time: Sun Jan 29 03:14:16 CST 2023