utl_file

[orajit]

I have created one file
c;/temp/test.txt

000 23102007
001 21102007 [23102007 GB11223344
001 20102007 22102007 GB11223345
001 19102007 22102007 GB11223346
999 3

The highlighted text are started date and end date

Suppose I wanted to write a select statement by reading the data from the test.txt .. In my where clause I have to give input parameter as start date and end date .

cud u please tell me how to to it .

Thnaks

[amitpatel66]

I have created one file
c;/temp/test.txt

000 23102007
001 21102007 [23102007 GB11223344
001 20102007 22102007 GB11223345
001 19102007 22102007 GB11223346
999 3

The highlighted text are started date and end date

Suppose I wanted to write a select statement by reading the data from the test.txt .. In my where clause I have to give input parameter as start date and end date .

cud u please tell me how to to it .

Thnaks

Kindly POST what you have tried to achieve this?

[amitpatel66]

If you want to use input parameters for fetching the data, then

1. transfer the data from flat file in to oracle database table
2. from the table, make use of select statement to fetch the data by passing input parameters

[orajit]

I have created following block

 
SQL>   declare
  2      filename    varchar2(1000);
  3      input_file   utl_file.file_type;
  4      input_buffer integer;
  5      vNewLine  varchar2(1000);
  6    begin
  7      input_file := utl_file.fopen ('/tmp','ADDER_GENEVA_REVENUE_REQUEST_23102007121005.dat', 'R'
);
  8      LOOP
  9       BEGIN
 10         utl_file.get_line(input_file,vNewLine);
 11         --insert into test_12 values(vNewLine);
 12         dbms_output.put_line(vNewLine);
 13      EXCEPTION
 14         WHEN OTHERS THEN
 15           EXIT;
 16       END;
 17     END LOOP;
 18     utl_file.fclose(input_file);
 19    end;
 20  /
 

The output as

000 23102007
001 21102007 23102007 GB11223344
001 20102007 22102007 GB11223345
001 19102007 22102007 GB11223346
999 3

PL/SQL procedure successfully completed.


Now I wanted to store the highligted date in the variable ...how shoud i do the Cud u plz tell me .

Thanks

[amitpatel66]

I have created following block

SQL> declare
2 filename varchar2(1000);
3 input_file utl_file.file_type;
4 input_buffer integer;
5 vNewLine varchar2(1000);
6 begin
7 input_file := utl_file.fopen ('/tmp','ADDER_GENEVA_REVENUE_REQUEST_23102007121005. dat', 'R'
);
8 LOOP
9 BEGIN
10 utl_file.get_line(input_file,vNewLine);
11 --insert into test_12 values(vNewLine);
12 dbms_output.put_line(vNewLine);
13 EXCEPTION
14 WHEN OTHERS THEN
15 EXIT;
16 END;
17 END LOOP;
18 utl_file.fclose(input_file);
19 end;
20 /
The output as

000 23102007
001 21102007 23102007 GB11223344
001 20102007 22102007 GB11223345
001 19102007 22102007 GB11223346
999 3

PL/SQL procedure successfully completed.


Now I wanted to store the highligted date in the variable ...how shoud i do the Cud u plz tell me .

Thanks

Do you have the data stored in oracle table that you have ina flat file?

[orajit]

No .I wanted to store the highligted text (date ) into the(two ) variables and Insert it in the table .

[amitpatel66]

No .I wanted to store the highligted text (date ) into the(two ) variables and Insert it in the table .

Try something like this:

declare
start_date VARCHAR2(20);
end_date VARCHAR2(20);
buffer VARCHAR2(200);
BEGIN
--<your utl file code goes here>;
--buffer:= getline() function to read a line from the file
start_date := SUBSTR(buffer,5,8);
IF(length(buffer) > 13) THEN
end_date:= SUBSTR(buffer,14,8);
INSERT INTO table_name VALUES(start_date,end_date);
COMMIT;
END;
 

[orajit]

Thanks for ur solution ..but still facing some probm

 DECLARE
 start_date VARCHAR2(20);
 end_date VARCHAR2(20);
 buffer VARCHAR2(2000);
 filename    VARCHAR2(1000);
 input_file   utl_file.file_type;
 input_buffer INTEGER;
 vNewLine  VARCHAR2(1000);
 BEGIN
   loop
      begin
       input_file := utl_file.fopen ('/tmp','ADDER_GENEVA_REVENUE_REQUEST_23102007121005.  dat', 'R');
        buffer:=utl_file.get_line(input_file,vNewLine);
         dbms_output.put_line(buffer);
          start_date := SUBSTR(buffer,5,8);
          IF(LENGTH(buffer) > 13) THEN
          end_date:= SUBSTR(buffer,14,8);
          end if;
          INSERT INTO  am_test VALUES(start_date,end_date);
          COMMIT;
      EXCEPTION
         WHEN OTHERS THEN
           EXIT;
      END;
    END LOOP;
      utl_file.fclose(input_file);
     end;
 

But it gives me one error

ORA-06550: line 13, column 17:
PLS-00222: no function with name 'GET_LINE' exists in this scope

[amitpatel66]

Thanks for ur solution ..but still facing some probm

 DECLARE
 start_date VARCHAR2(20);
 end_date VARCHAR2(20);
 buffer VARCHAR2(2000);
 filename    VARCHAR2(1000);
 input_file   utl_file.file_type;
 input_buffer INTEGER;
 vNewLine  VARCHAR2(1000);
 BEGIN
   loop
      begin
       input_file := utl_file.fopen ('/tmp','ADDER_GENEVA_REVENUE_REQUEST_23102007121005.  dat', 'R');
        buffer:=utl_file.get_line(input_file,vNewLine);
         dbms_output.put_line(buffer);
          start_date := SUBSTR(buffer,5,8);
          IF(LENGTH(buffer) > 13) THEN
          end_date:= SUBSTR(buffer,14,8);
          end if;
          INSERT INTO  am_test VALUES(start_date,end_date);
          COMMIT;
      EXCEPTION
         WHEN OTHERS THEN
           EXIT;
      END;
    END LOOP;
      utl_file.fclose(input_file);
     end;
 

But it gives me one error

ORA-06550: line 13, column 17:
PLS-00222: no function with name 'GET_LINE' exists in this scope

Please use the below code:

DECLARE
 start_date VARCHAR2(20);
 end_date VARCHAR2(20);
 filename    VARCHAR2(1000);
 input_file   utl_file.file_type;
 input_buffer INTEGER;
 vNewLine  VARCHAR2(1000);
 BEGIN
       input_file := utl_file.fopen ('/tmp','ADDER_GENEVA_REVENUE_REQUEST_23102007121005.  dat', 'R');
       utl_file.get_line(input_file,vNewLine);
         LOOP
         EXIT WHEN length(vNewLine) <= 0);
         dbms_output.put_line(vNewLine);
          start_date := SUBSTR(vNewLine,5,8);
          IF(LENGTH(vNewLine) > 13) THEN
          end_date:= SUBSTR(vNewLine,14,8);
          end if;
          INSERT INTO  am_test VALUES(start_date,end_date);
          COMMIT;
         vNewLine:= NULL;
         END LOOP;
      EXCEPTION
         WHEN OTHERS THEN
                 utl_file.fclose(input_file);
      END;
 

[orajit]

Thanks for ur solution .. It run successfully


SQL> select * from am_test;

START_DATE END_DATE
-------------------- --------------------
23102007
21102007 23102007
20102007 22102007
19102007 22102007
3 22102007

But I dont want the highlighted data ...
Input file is

000 23102007
001 21102007 23102007 GB11223344
001 20102007 22102007 GB11223345
001 19102007 22102007 GB11223346
999 3

I wanted to insert only highlighted values ...
Ur last comment on this issue plz.

Thank u very much

[amitpatel66]

Use the below code:

DECLARE
 start_date VARCHAR2(20);
 end_date VARCHAR2(20);
 filename    VARCHAR2(1000);
 input_file   utl_file.file_type;
 input_buffer INTEGER;
 vNewLine  VARCHAR2(1000);
 BEGIN
       input_file := utl_file.fopen ('/tmp','ADDER_GENEVA_REVENUE_REQUEST_23102007121005.  dat', 'R');
       utl_file.get_line(input_file,vNewLine);
         LOOP
         EXIT WHEN length(vNewLine) <= 0);
         dbms_output.put_line(vNewLine);
         IF(LENGTH(vNewLine) > 13) THEN          
         start_date := SUBSTR(vNewLine,5,8);
         end_date:= SUBSTR(vNewLine,14,8);
          INSERT INTO  am_test VALUES(start_date,end_date);
          COMMIT;
         end if;
         vNewLine:= NULL;
        start_date:= NULL;
        end_date:= NULL;
         END LOOP;
      EXCEPTION
         WHEN OTHERS THEN
                 utl_file.fclose(input_file);
      END;
 

[orajit]

Use the below code:

DECLARE
 start_date VARCHAR2(20);
 end_date VARCHAR2(20);
 filename    VARCHAR2(1000);
 input_file   utl_file.file_type;
 input_buffer INTEGER;
 vNewLine  VARCHAR2(1000);
 BEGIN
       input_file := utl_file.fopen ('/tmp','ADDER_GENEVA_REVENUE_REQUEST_23102007121005.  dat', 'R');
       utl_file.get_line(input_file,vNewLine);
         LOOP
         EXIT WHEN length(vNewLine) <= 0);
         dbms_output.put_line(vNewLine);
         IF(LENGTH(vNewLine) > 13) THEN          
         start_date := SUBSTR(vNewLine,5,8);
         end_date:= SUBSTR(vNewLine,14,8);
          INSERT INTO  am_test VALUES(start_date,end_date);
          COMMIT;
         end if;
         vNewLine:= NULL;
        start_date:= NULL;
        end_date:= NULL;
         END LOOP;
      EXCEPTION
         WHEN OTHERS THEN
                 utl_file.fclose(input_file);
      END;
 

if i have my txt file as shown below ... the space is replaced by pipe'|' . Now I wanted to instert the pipe seperated values using above code ..

Could u please tell me how to do that ...

000|23102007
001|21102007|23102007|GB11223344
001|20102007|22102007|GB11223345
001|19102007|22102007|GB11223346
999|3


Thank you very much..

[amitpatel66]

if i have my txt file as shown below ... the space is replaced by pipe'|' . Now I wanted to instert the pipe seperated values using above code ..

Could u please tell me how to do that ...

000|23102007
001|21102007|23102007|GB11223344
001|20102007|22102007|GB11223345
001|19102007|22102007|GB11223346
999|3


Thank you very much..

You can try using the same code that I have provided you in the prevoius post. Its just that the space is replaced by a pipe and my above posted code will work for this file as well. If it does not then POST back here and we will help you out!!

[orajit]

Thanks u for ur valuable time .. I will try from my side and get back to u ..thanks

[orajit]

yes its working ... thanks for your help

[amitpatel66]

yes its working ... thanks for your help

You are welcome :)

MODERATOR

[orajit]

if i have my txt file as shown below ...


000|23102007
001|R|21102007|23102007|GD112112111|B|LIss
001|R|20102007|22102007|GD112|B|WOss
001|R|19102007|22102007|GD113334444223346||WOss
001|R|19102007|22102007|GD111||WOss
001|R|19102007|22102007|GD113344||WOss
999|5


Now i wanted to read and insert account number using above code ... The account number is shown as bold (GD133331223344,GD112 etc)

Could u please advice me how to do it.