Link to home
Start Free TrialLog in
Avatar of anumoses
anumosesFlag for United States of America

asked on

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.
SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of anumoses

ASKER

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
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
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.
Thanks
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;
Incase you encounter any error.. post us the error..
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
Then you could have added my comment as a solution too.. Right?,

Anyways.. Don't bother now.. Its okay...
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.