UTL_FILE.FOPEN - ORACLE

I am using fopen

Here is the syntax.

UTL_FILE.FOPEN ('PURPLE_TOP_DIR', '06_03_2015_14_49-converted.csv', 'R');

06_03_2015_14_49-converted.csv

Instead of hard-coding is there a way to say *.csv or something of that kind? File name is date and time.  Help is appreciated.
LVL 6
anumosesAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
I don't believe there are any UTL_FILE programs that allow a wildcard.

You will likely have to have something on the OS that changes the file to something 'common' to load.
Mark GeerlingsDatabase AdministratorCommented:
Correct, the utl_file.fopen command does not accept wildcards.  But it does accept a variable for the file name, so you can use a cursor or some other PL\SQL logic to supply a value dynamically.  Another possibility would be to write (or find) a  Java program that could read a directory to find a file name, then call that Java program from PL\SQL.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
anumosesAuthor Commented:
create global temporary table DIR_LIST
    ( filename varchar2(255) )
    on commit delete rows		

Open in new window


create or replace
       and compile java source named "DirList"
    as
    import java.io.*;
    import java.sql.*;
    
    public class DirList
    {
    public static void getList(String directory)
                      throws SQLException
   {
       File path = new File( directory );
       String[] list = path.list();
       String element;
   
       for(int i = 0; i < list.length; i++)
       {
           element = list[i];
           #sql { INSERT INTO DIR_LIST (FILENAME)
                  VALUES (:element) };
       }
   }
   
   }

Open in new window


create or replace
    procedure get_dir_list( p_directory in varchar2 )
    as language java
    name 'DirList.getList( java.lang.String )';

Open in new window


This is what I created looking at examples online

exec get_dir_list( '/home/hbc_data/data/purple_top_data' );                               

select * from dir_list where rownum < 5;

FILENAME
06_03_2015_14_49-converted.csv

PROCEDURE load_purple_top_data IS
      F UTL_FILE.FILE_TYPE;
      V_LINE VARCHAR2 (1000);
      V_UNIT_ID VARCHAR2(25);
	  V_HGB NUMBER;
      V_PLT NUMBER;
	  v_procedure_c constant varchar2(30) := 'load_purple_top_data';
	  v_location_i integer;	  	
	  v_donor_id varchar2(10);
	  v_gender   varchar2(1);
	  v_date_of_birth date;
	  v_first_name varchar2(25);
	  v_last_name varchar2(30);
	  v_middle_name varchar2(25);
	  v_blood_type varchar2(5);
	  v_ethnic_group varchar2(5);
	  v_street_name varchar2(50); 
	  v_suite_apt varchar2(10);
	  v_city   varchar2(40); 
	  v_state  varchar2(2); 
	  v_zip varchar2(5);
	  v_email varchar2(128);
	  v_d_phone_no varchar2(15); 
	  v_h_phone_no varchar2(15);
	  v_coll_date date;
	 

Cursor C1 is
select distinct d.donor_id,gender,date_of_birth,first_name,
       last_name,middle_name,blood_type,ethnic_group,
	   street_name, suite_apt,city, state, zip, da.email,
	   d_area_code||d_phone_no as d_phone_no, 
	   h_area_code||h_phone_no as h_phone_no,do.coll_date
  from donors_don d, donations_don do,
       donor_addresses_don da,
       valid_city_state_don vcs 
  where d.donor_id = do.donor_id
    and da.donor_id = d.donor_id
	and da.usps_id = vcs.usps_id
	and do.unit_id = v_unit_id;
	
BEGIN
v_location_i := 1000;
      --F := UTL_FILE.FOPEN ('PURPLE_TOP_DIR', 'purple_top.csv', 'R');
	  F := UTL_FILE.FOPEN ('PURPLE_TOP_DIR', '06_03_2015_14_49-converted.csv', 'R');
v_location_i := 2000;	  
    IF UTL_FILE.IS_OPEN(F) THEN
      LOOP
        BEGIN
          UTL_FILE.GET_LINE(F, V_LINE, 1000);
          IF V_LINE IS NULL THEN
            EXIT;
          END IF;
		  v_location_i := 3000;
		   V_UNIT_ID := substr(V_LINE,1,instr(V_LINE,',',1,1)-1);
		   V_HGB := to_number(substr(V_LINE, instr(V_LINE,',',1,1)+1, instr(V_LINE,',',1,2) - instr(V_LINE,',',1,1)-1 ));
           V_PLT := to_number(trim(substr(replace(replace(V_LINE,chr(10),' '),chr(13),' '), instr(replace(replace(V_LINE,chr(10),' '),chr(13),' '),',',1,2)+1)));
				
 v_location_i := 4000;       
		OPEN C1;
          LOOP
           FETCH C1 INTO v_donor_id,v_gender,v_date_of_birth,v_first_name,
                         v_last_name,v_middle_name,v_blood_type,v_ethnic_group,
	                     v_street_name, v_suite_apt,v_city, v_state, v_zip, v_email,
	   					 v_d_phone_no, v_h_phone_no,v_coll_date;
		  EXIT WHEN C1%NOTFOUND;
		     INSERT INTO DS_PURPLE_TOP (DONOR_ID, 
			 			 				  GENDER, 
										  DATE_OF_BIRTH, 
										  FIRST_NAME, 
										  LAST_NAME, 
										  MIDDLE_NAME, 
										  BLOOD_TYPE, 
										  ETHNIC_GROUP, 
										  STREET_NAME, 
										  SUITE_APT, 
										  CITY, 
										  STATE, 
										  ZIP, 
										  EMAIL, 
										  D_PHONE_NO, 
										  H_PHONE_NO, 
										  UNIT_ID, 
										  HGB, 
										  PLT,
										  COLL_DATE)--HEIGHT_FT, HEIGHT_INCHES, WEIGHT 
			                       VALUES(v_donor_id,
								 		  v_gender,
										  v_date_of_birth ,
										  v_first_name,
										  v_last_name ,
										  v_middle_name ,
										  v_blood_type ,
										  v_ethnic_group ,
										  v_street_name ,
										  v_suite_apt ,
										  v_city  , 
										  v_state  , 
										  v_zip ,
										  v_email ,
										  v_d_phone_no , 
										  v_h_phone_no,
										  v_unit_id,
										  v_hgb,
										  v_plt,
										  v_coll_date);
          COMMIT;
		  END LOOP;
		CLOSE C1;
		EXCEPTION
        WHEN NO_DATA_FOUND THEN
          	 EXIT;
        when PU_FAILURE then
      		 raise PU_FAILURE;
		when OTHERS then

--    Send email notification that the process failed.
                             
      dbms_output.put_line('Error in ' || pg_package_c || '.' || v_procedure_c ||
                    ' at line: ' || v_location_i);
      dbms_output.put_line(sqlerrm);
      
      tsc_spu_pkg.capture_error_vars;
      tsc_spu_pkg.analyze_error(tsc_globals_pkg.sg_system_prefix_c);
      tsc_spu_pkg.log_error(tsc_globals_pkg.sg_system_prefix_c,
                            pg_package_c,
                            v_procedure_c,
                            v_location_i,
                            tsc_globals_pkg.sg_current_user_c,
                            'No Parameters.');
      raise_application_error(-20000, tsc_spu_pkg.get_friendly_error);
		END;
      END LOOP;
    END IF;
    UTL_FILE.FCLOSE(F);
END load_purple_top_data;
  

Open in new window


I am attaching the procedure. Just want to know how to use java file name in the procedure
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Wasim Akram ShaikCommented:
There is already a program available for it.. refer to the tomkyte blog..

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:439619916584
anumosesAuthor Commented:
Thanks for the link. I got the information from here only. My question is after the program how to call this in my plsql procedure?

Cursor C2 is
select distinct filename
  from dir_list;

 
BEGIN

get_dir_list( '/home/hbc_data/data/purple_top_data' );

Open C2;
Fetch C2 into FILENAME;
Close C2;

  F := UTL_FILE.FOPEN ('PURPLE_TOP_DIR', FILENAME, 'R');

When I use FILENAME in utl_file.fopen I get error.
anumosesAuthor Commented:
Thanks
Wasim Akram ShaikCommented:
I understand that you open the up the cursor, but why did you close it?

i believe it should be something like this

declare
TYPE c1 IS REF CURSOR;
v_c1                      c1;
v_c1_sql                  VARCHAR2 (1000);
f_extract_file UTL_FILE.FILE_TYPE;
v_file_name varchar2(1000);
begin
get_dir_list( '/home/hbc_data/data/purple_top_data' );
v_c1_sql:='select distinct filename
  from dir_list';
OPEN v_c1 FOR v_c1_sql;
fetch v_c1 into v_file_name ;
loop
f_extract_file :=UTL_FILE.FOPEN ('PURPLE_TOP_DIR', v_file_name, 'R');
-----do whatever you want to do here next
----after this write your own code
end loop;
end;
Wasim Akram ShaikCommented:
Incase you encounter any error.. post us the error..
anumosesAuthor Commented:
F   UTL_FILE.FILE_TYPE;
FILENAME  varchar2(255);

f  :=UTL_FILE.FOPEN ('PURPLE_TOP_DIR', FILENAME, 'R');

This worked for me. So I had closed. Thanks
Wasim Akram ShaikCommented:
Then you could have added my comment as a solution too.. Right?,

Anyways.. Don't bother now.. Its okay...
anumosesAuthor Commented:
You had provided the link that I had already looked into and wrote the solution for my issue. So I did not include yours. Sorry.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.