UTL_FILE - Unstringing text

From: avin <avinthr_at_yahoo.co.uk>
Date: 13 Nov 2003 03:24:51 -0800
Message-ID: <a1c3c00e.0311130324.216be6df@posting.google.com>

Hi

I am reading in a flat file which looks like this :

2Name 1: Mohammad

Name 2: Hassan
Name 3:
Name 4:

Title 1: Mullah
Title 2: Hadji
Designation: First Deputy, Council of Ministers DOB: Approximately 1958
POB: Kandahar, AfghanistanGood quality a.k.a.: Low quality a.k.a.:Nationality: Afghan
Passport no.:
National identification no.:
Address:25 Jan. 2001

I am then stripping the data between the Name1: and Name 2: tags and then inserting it into a table. The code I am using is listed below. This does not want to work, as it keeps overwriting the previous value. Please could someone assist and shed some light on the problem.....

create or replace procedure unstring_unlist as

   infilepath VARCHAR2(2000) :=
'/udd001/app/oracle/admin/tbpa/bopcus/data';

   infilename VARCHAR2(2000) := 'unlist.txt';    infile UTL_FILE.FILE_TYPE;
   buffer varchar2(10000);
   ele ci_embargo_users%rowtype;

   t_name1 number;
   t_name2 number;
   t_name3 number;
   t_name4 number;
   t_name5 number;
   t_title number;
   t_title1 number;
   t_title2 number;
   t_title3 number;
   t_title4 number;
   t_designation number;
   t_date_of_birth number;
   t_alias1 number;
   t_alias2 number;
   t_alias3 number;
   t_alias4 number;
   t_alias5 number;
   t_alias6 number;
   t_alias7 number;
   t_blank  number;

/* Initialising all elements to be used */

procedure init_ele is
begin

  ele.name1 := null;
  ele.name2 := null;
  ele.name3 := null;
  ele.name4 := null;
  ele.name5 := null;
  ele.title1 := null;
  ele.title2 := null;
  ele.title3 := null;
  ele.title4 := null;
  ele.designation := null;
  ele.date_of_birth := null;
  ele.alias1 := null;
  ele.alias2 := null;
  ele.alias3 := null;
  ele.alias4 := null;
  ele.alias5 := null;
  ele.alias6 := null;
  ele.alias7 := null;
  t_name1 := 0;
  t_name2 := 0;
  t_name3 := 0;
  t_name4 := 0;
  t_name5 := 0;
  t_title := 0;
  t_title1 := 0;
  t_title2 := 0;
  t_title3 := 0;
  t_title4 := 0;
  t_designation := 0;
  t_date_of_birth := 0;
  t_alias1 := 0;
  t_alias2 := 0;
  t_alias3 := 0;
  t_alias4 := 0;
  t_alias5 := 0;
  t_alias6 := 0;

  t_alias7 := 0;
  t_blank := 0;
end;

procedure insert_ele is
begin
  insert into ci_embargo_users

    (NAME1,
     NAME2,
     NAME3,
     NAME4,
     NAME5,
     TITLE1,
     TITLE2,
     TITLE3,
          TITLE4,
     DESIGNATION,
     DATE_OF_BIRTH,
     ALIAS1,
     ALIAS2,
     ALIAS3,
     ALIAS4,
     ALIAS5,
     ALIAS6,
     ALIAS7)
  values(ele.name1,
         ele.name2,
         ele.name3,
         ele.name4,
         ele.name5,
         ele.title1,
         ele.title2,
         ele.title3,
         ele.title4,
         ele.designation,
         ele.date_of_birth,
         ele.alias1,
         ele.alias2,
         ele.alias3,
         ele.alias4,
         ele.alias5,
         ele.alias6,
         ele.alias7);

end; end;

/* FIND LOCATION OF TABS */
   t_name1 := instr(buffer,'Name 1:',1,1);    t_name2 := instr(buffer,'Name 2:',1,1);

/* EXTRACT THE DATA BETWEEN THE TAGS */
  ele.name1 := substr(buffer,t_name1+4,20);   ele.name2 := substr(buffer,t_name2+5,20);

/* INSERT VALUE INTO TABLE */
 insert_ele;
commit;

utl_file.fclose(infile);
 end loop;

• utl_file.fclose(infile);

exception

   when utl_file.invalid_path then

         dbms_output.put_line('File location or name was invalid.');
      when utl_file.invalid_mode then
         dbms_output.put_line('Invalid mode specified.');
      when utl_file.invalid_operation then
         dbms_output.put_line('File could not be opened as
requested.');
      when others then
         dbms_output.put_line(sqlcode);
         dBms_output.put_line(substr(sqlerrm, 1, 70));

end;
/

begin

   infile := UTL_FILE.FOPEN(infilepath, infilename, 'r');    dbms_output.put_line ('File opened successfully');  looP
   begin

      init_ele;
      utl_file.get_line (infile, buffer);
   exception
      when no_data_found then
          exit;

Received on Thu Nov 13 2003 - 05:24:51 CST