3

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

Improve this question
2
  • How do you pass directory to your program? fopen needs a directory as a parameter. You can first save it to variable then pass to fopen and later use variable. Commented Nov 7, 2016 at 19:28
  • Hi, I currently pass it in using Bob's method below (creating the directory with a hardcoded path). My issue is that I don't want the Path hardcoded, I'd like it to be the current directory where I am running from Commented Nov 8, 2016 at 19:59

2 Answers 2

Reset to default
4

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Dont forget to grant read and write right concerning the Oracle directories.
Hi Bob - Thanks for the response! However, this is already what I am currently doing. I create a directory and then use the variable in utl_file.fopen just like you do above. The part I want to eliminate is hardcoding the Path when creating the directory. Something like: Create directory directory_1 as [current directory]; I've been toying around with the following without success: create directory diretory_1 as host cd;
Sorry to break it to you, but there's no way to do that. Best of luck.
bummer... do you know if there is some way to log the command "Host CD"? I am not able to log it by spooling and turning feedback on or by doing something creative with dbms_output.put_line
0

If you want to use current working directory you can create that directory dinamically. Problem is when multiple processes try to access your procedure.

If you can ensure that only one process is using procedure at same time you can do:

create or replace procedure (mypath varchar2) as
begin
execute immediate 'CREATE OR REPLACE DIRECTORY DIRECTORY_1 AS ' || mypath;
aFile := UTL_FILE.FOPEN(DIRECTORY_1, 'filename.txt', 'r');
end;
/

If you can't ensure that only one thread will call procedure at same time it gets tricky. You can try to lookup directory.

create or replace procedure (mypath varchar2) as
dirname varchar2;
begin
select DIRECTORY_NAME into dirname from dba_directories where DIRECTORY_PATH = mypath;
if (dirname is null) then
    dirname := substr(replace(replace(replace(replace(dirname,'\',''),':',''),'/',''),'.',''),1,30);
    execute immediate 'CREATE OR REPLACE ' || dirname || ' AS ' || mypath;
end if;
    aFile := UTL_FILE.FOPEN(dirname, 'filename.txt', 'r');
end;
    /
Improve this answer

1 Comment

replace and generating substring should be done better.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.