PL/SQL Oracle Directory

Question

I am using util_file.fopen to import a .txt file into a database. However, I do not want hardcode the directory. Is there a way save the current directory to a variable or log the path? That way I can create an oracle directory that is the current directory, and util_file.fopen will always open the .txt file that is in the directory that I am running my pl/sql from

I know that "HOST CD" will show me my current directory, but I have not been able to save it to a variable or log it

Thanks

Answer 1

Create multiple directory objects:

CREATE DIRECTORY DIRECTORY_1 AS '/some/path';
CREATE DIRECTORY DIRECTORY_2 AS '/some/other/path';
CREATE DIRECTORY DIRECTORY_3 AS '/yet/another/path';

Assign the name of the directory to a variable:

strDirectory_to_use := 'DIRECTORY_1';

Use the variable when opening a file:

-- References /some/path/filename.txt
aFile := UTL_FILE.FOPEN(strDirectory_to_use, 'filename.txt', 'r');

Change the variable to contain the name of a different directory object:

strDirectory_to_use := 'DIRECTORY_2';

Now when you use the variable in a call to UTL_FILE.FOPEN it will look at the directory pointed to by directory object DIRECTORY_2:

-- References /some/other/path/filename.txt
aFile := UTL_FILE.FOPEN(strDirectory_to_use, 'filename.txt', 'r');

Best of luck.