Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Change directory path dynamically using UTL_FILE

user7351276Jul 11 2012 — edited Jul 11 2012
I have a directory TEST_DIR which points to /test/files/


CREATE OR REPLACE PROCEDURE file_exist (v_country in varchar2) is

v_check_file_exist BOOLEAN;
v_dir VARCHAR2 (256) := 'TEST_DIR';

begin

UTL_FILE.fgetattr (V_DIR || V_COUNTRY,'file123' ||'.txt', v_check_file_exist, v_a,v_b);
IF NOT v_check_file_exist THEN
DBMS_OUTPUT.put_line (TO_CHAR(from_date,'YYYYMMDD')||'.rds');
END IF;
END LOOP;


I would like to change the directory path based on the country which is passed as input to the procedure

exec file_exist('IND'), so in this case I want the procedure to look for file 'file123.txt' under path /test/files/IND.

But this is not working..

But when I change the directory to

create or replace directory TESTDIR as '/test/files/IND' and then change my UTL_FILE.fgetattr (V_DIR,'file123' ||'.txt', v_check_file_exist, v_a,v_b)(removing || v_country, this time it works.

am trying this as I have many country folders under the path like
/test/files/IND
/test/files/USA
/test/files/AFR
and want to change the direcotry path dynamically according to input given.

Any suggestions
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 8 2012
Added on Jul 11 2012
9 comments
2,392 views