Re: UTL_FILE - Unstringing text

Something seems really wrong, like your main Begin/End isn't all together. The original code was closing the input file after every read/insert, so you were getting the same record over and over. Open the file, loop through reading until the no_data_found, then close the file.

*Maybe* the following is more like what you wanted.

> 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;
>
>
> begin
> infile := UTL_FILE.FOPEN(infilepath, infilename, 'r');
> dbms_output.put_line ('File opened successfully');
>
> looP
> init_ele;
> utl_file.get_line (infile, buffer);
> /* 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); -- THIS SHOULD PROBABLY BE +7
> ele.name2 := substr(buffer,t_name2+5,20); -- THIS SHOULD PROBABLY BE +7
ALSO
>
>
> /* INSERT VALUE INTO TABLE */
>
> insert_ele;
> commit;
>
> end loop;
>
> utl_file.fclose(infile);
>
>
> exception
> when no_data_found then
> exit;
> 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;
> end;
> /

"avin" <avinthr_at_yahoo.co.uk> wrote in message news:a1c3c00e.0311130324.216be6df_at_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 - 07:40:59 CST