469,293 Members | 1,319 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,293 developers. It's quick & easy.

utl_file

75
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
Nov 12 '07 #1
16 4517
amitpatel66
2,367 Expert 2GB
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?
Nov 12 '07 #2
amitpatel66
2,367 Expert 2GB
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
Nov 12 '07 #3
orajit
75
I have created following block

Expand|Select|Wrap|Line Numbers
  1.  
  2. SQL>   declare
  3.   2      filename    varchar2(1000);
  4.   3      input_file   utl_file.file_type;
  5.   4      input_buffer integer;
  6.   5      vNewLine  varchar2(1000);
  7.   6    begin
  8.   7      input_file := utl_file.fopen ('/tmp','ADDER_GENEVA_REVENUE_REQUEST_23102007121005.dat', 'R'
  9. );
  10.   8      LOOP
  11.   9       BEGIN
  12.  10         utl_file.get_line(input_file,vNewLine);
  13.  11         --insert into test_12 values(vNewLine);
  14.  12         dbms_output.put_line(vNewLine);
  15.  13      EXCEPTION
  16.  14         WHEN OTHERS THEN
  17.  15           EXIT;
  18.  16       END;
  19.  17     END LOOP;
  20.  18     utl_file.fclose(input_file);
  21.  19    end;
  22.  20  /
  23.  
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
Nov 12 '07 #4
amitpatel66
2,367 Expert 2GB
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?
Nov 12 '07 #5
orajit
75
No .I wanted to store the highligted text (date ) into the(two ) variables and Insert it in the table .
Nov 13 '07 #6
amitpatel66
2,367 Expert 2GB
No .I wanted to store the highligted text (date ) into the(two ) variables and Insert it in the table .
Try something like this:

Expand|Select|Wrap|Line Numbers
  1. declare
  2. start_date VARCHAR2(20);
  3. end_date VARCHAR2(20);
  4. buffer VARCHAR2(200);
  5. BEGIN
  6. --<your utl file code goes here>;
  7. --buffer:= getline() function to read a line from the file
  8. start_date := SUBSTR(buffer,5,8);
  9. IF(length(buffer) > 13) THEN
  10. end_date:= SUBSTR(buffer,14,8);
  11. INSERT INTO table_name VALUES(start_date,end_date);
  12. COMMIT;
  13. END;
  14.  
Nov 13 '07 #7
orajit
75
Thanks for ur solution ..but still facing some probm

Expand|Select|Wrap|Line Numbers
  1.  DECLARE
  2.  start_date VARCHAR2(20);
  3.  end_date VARCHAR2(20);
  4.  buffer VARCHAR2(2000);
  5.  filename    VARCHAR2(1000);
  6.  input_file   utl_file.file_type;
  7.  input_buffer INTEGER;
  8.  vNewLine  VARCHAR2(1000);
  9.  BEGIN
  10.    loop
  11.       begin
  12.        input_file := utl_file.fopen ('/tmp','ADDER_GENEVA_REVENUE_REQUEST_23102007121005.  dat', 'R');
  13.         buffer:=utl_file.get_line(input_file,vNewLine);
  14.          dbms_output.put_line(buffer);
  15.           start_date := SUBSTR(buffer,5,8);
  16.           IF(LENGTH(buffer) > 13) THEN
  17.           end_date:= SUBSTR(buffer,14,8);
  18.           end if;
  19.           INSERT INTO  am_test VALUES(start_date,end_date);
  20.           COMMIT;
  21.       EXCEPTION
  22.          WHEN OTHERS THEN
  23.            EXIT;
  24.       END;
  25.     END LOOP;
  26.       utl_file.fclose(input_file);
  27.      end;
  28.  


But it gives me one error

ORA-06550: line 13, column 17:
PLS-00222: no function with name 'GET_LINE' exists in this scope
Nov 13 '07 #8
amitpatel66
2,367 Expert 2GB
Thanks for ur solution ..but still facing some probm

Expand|Select|Wrap|Line Numbers
  1.  DECLARE
  2.  start_date VARCHAR2(20);
  3.  end_date VARCHAR2(20);
  4.  buffer VARCHAR2(2000);
  5.  filename    VARCHAR2(1000);
  6.  input_file   utl_file.file_type;
  7.  input_buffer INTEGER;
  8.  vNewLine  VARCHAR2(1000);
  9.  BEGIN
  10.    loop
  11.       begin
  12.        input_file := utl_file.fopen ('/tmp','ADDER_GENEVA_REVENUE_REQUEST_23102007121005.  dat', 'R');
  13.         buffer:=utl_file.get_line(input_file,vNewLine);
  14.          dbms_output.put_line(buffer);
  15.           start_date := SUBSTR(buffer,5,8);
  16.           IF(LENGTH(buffer) > 13) THEN
  17.           end_date:= SUBSTR(buffer,14,8);
  18.           end if;
  19.           INSERT INTO  am_test VALUES(start_date,end_date);
  20.           COMMIT;
  21.       EXCEPTION
  22.          WHEN OTHERS THEN
  23.            EXIT;
  24.       END;
  25.     END LOOP;
  26.       utl_file.fclose(input_file);
  27.      end;
  28.  


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:

Expand|Select|Wrap|Line Numbers
  1. DECLARE
  2.  start_date VARCHAR2(20);
  3.  end_date VARCHAR2(20);
  4.  filename    VARCHAR2(1000);
  5.  input_file   utl_file.file_type;
  6.  input_buffer INTEGER;
  7.  vNewLine  VARCHAR2(1000);
  8.  BEGIN
  9.        input_file := utl_file.fopen ('/tmp','ADDER_GENEVA_REVENUE_REQUEST_23102007121005.  dat', 'R');
  10.        utl_file.get_line(input_file,vNewLine);
  11.          LOOP
  12.          EXIT WHEN length(vNewLine) <= 0);
  13.          dbms_output.put_line(vNewLine);
  14.           start_date := SUBSTR(vNewLine,5,8);
  15.           IF(LENGTH(vNewLine) > 13) THEN
  16.           end_date:= SUBSTR(vNewLine,14,8);
  17.           end if;
  18.           INSERT INTO  am_test VALUES(start_date,end_date);
  19.           COMMIT;
  20.          vNewLine:= NULL;
  21.          END LOOP;
  22.       EXCEPTION
  23.          WHEN OTHERS THEN
  24.                  utl_file.fclose(input_file);
  25.       END;
  26.  
Nov 13 '07 #9
orajit
75
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
Nov 13 '07 #10
amitpatel66
2,367 Expert 2GB
Use the below code:

Expand|Select|Wrap|Line Numbers
  1. DECLARE
  2.  start_date VARCHAR2(20);
  3.  end_date VARCHAR2(20);
  4.  filename    VARCHAR2(1000);
  5.  input_file   utl_file.file_type;
  6.  input_buffer INTEGER;
  7.  vNewLine  VARCHAR2(1000);
  8.  BEGIN
  9.        input_file := utl_file.fopen ('/tmp','ADDER_GENEVA_REVENUE_REQUEST_23102007121005.  dat', 'R');
  10.        utl_file.get_line(input_file,vNewLine);
  11.          LOOP
  12.          EXIT WHEN length(vNewLine) <= 0);
  13.          dbms_output.put_line(vNewLine);
  14.          IF(LENGTH(vNewLine) > 13) THEN          
  15.          start_date := SUBSTR(vNewLine,5,8);
  16.          end_date:= SUBSTR(vNewLine,14,8);
  17.           INSERT INTO  am_test VALUES(start_date,end_date);
  18.           COMMIT;
  19.          end if;
  20.          vNewLine:= NULL;
  21.         start_date:= NULL;
  22.         end_date:= NULL;
  23.          END LOOP;
  24.       EXCEPTION
  25.          WHEN OTHERS THEN
  26.                  utl_file.fclose(input_file);
  27.       END;
  28.  
Nov 13 '07 #11
orajit
75
Use the below code:

Expand|Select|Wrap|Line Numbers
  1. DECLARE
  2.  start_date VARCHAR2(20);
  3.  end_date VARCHAR2(20);
  4.  filename    VARCHAR2(1000);
  5.  input_file   utl_file.file_type;
  6.  input_buffer INTEGER;
  7.  vNewLine  VARCHAR2(1000);
  8.  BEGIN
  9.        input_file := utl_file.fopen ('/tmp','ADDER_GENEVA_REVENUE_REQUEST_23102007121005.  dat', 'R');
  10.        utl_file.get_line(input_file,vNewLine);
  11.          LOOP
  12.          EXIT WHEN length(vNewLine) <= 0);
  13.          dbms_output.put_line(vNewLine);
  14.          IF(LENGTH(vNewLine) > 13) THEN          
  15.          start_date := SUBSTR(vNewLine,5,8);
  16.          end_date:= SUBSTR(vNewLine,14,8);
  17.           INSERT INTO  am_test VALUES(start_date,end_date);
  18.           COMMIT;
  19.          end if;
  20.          vNewLine:= NULL;
  21.         start_date:= NULL;
  22.         end_date:= NULL;
  23.          END LOOP;
  24.       EXCEPTION
  25.          WHEN OTHERS THEN
  26.                  utl_file.fclose(input_file);
  27.       END;
  28.  
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..
Nov 23 '07 #12
amitpatel66
2,367 Expert 2GB
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!!
Nov 23 '07 #13
orajit
75
Thanks u for ur valuable time .. I will try from my side and get back to u ..thanks
Nov 23 '07 #14
orajit
75
yes its working ... thanks for your help
Nov 23 '07 #15
amitpatel66
2,367 Expert 2GB
yes its working ... thanks for your help
You are welcome :)

MODERATOR
Nov 23 '07 #16
orajit
75
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.
Feb 6 '08 #17

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Mahesh Hardikar | last post: by
2 posts views Thread by Candy Robinson | last post: by
reply views Thread by Peter Weidner | last post: by
1 post views Thread by Shilpesh | last post: by
1 post views Thread by Zima | last post: by
2 posts views Thread by Prasanna | last post: by
reply views Thread by abulafia | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by harlem98 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.