utl_file

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

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

I have created following block

Expand|Select|Wrap|Line Numbers

  
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

Nov 12 '07 #4

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

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

Nov 13 '07 #6

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

 
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;

Nov 13 '07 #7

orajit

Thanks for ur solution ..but still facing some probm

Expand|Select|Wrap|Line Numbers

 
 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

Nov 13 '07 #8

2,367

Expert 2GB

Thanks for ur solution ..but still facing some probm

Expand|Select|Wrap|Line Numbers

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:

Expand|Select|Wrap|Line Numbers

 
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;

Nov 13 '07 #9

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

Nov 13 '07 #10

2,367

Expert 2GB

Use the below code:

Expand|Select|Wrap|Line Numbers

 
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;

Nov 13 '07 #11

orajit

Use the below code:

Expand|Select|Wrap|Line Numbers

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..

Nov 23 '07 #12

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

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

Nov 23 '07 #14

orajit

yes its working ... thanks for your help

Nov 23 '07 #15

2,367

Expert 2GB

yes its working ... thanks for your help

You are welcome :)

MODERATOR

Nov 23 '07 #16

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.

Feb 6 '08 #17

by: Souhail | last post by:

Hi all, I have a problem to put lines with about 3000 characters in my output file using UTL_FILE.put package. I'm using Oracle 7 !! So I can...

Strange Utl_File Error on NT

by: Mahesh Hardikar | last post by:

Hi , Oracle EE 8.1.7.0.0 on MS WIndows Advanced Server 2000 SP3 We have a proc which is using utl_file . procedure test_dump_csv as l_rows...

UTL_FILE.OPEN problems

by: Candy Robinson | last post by:

I have created a directory for the path name I use for a UTL_FILE.OPEN call, but when I run my call I get error ORA-29280 invalid directory path....

running utl_file

by: Peter Weidner | last post by:

Hello friends I have a problem with UTL_File under Oracle 9.2i there - operating system Windows 2000 Professional. Following: I have a...

utl_file issue

by: Shilpesh | last post by:

I have an issue in usage of utl_file package. I have setup utl_file_dir parameters correctly on the database side. Basically my goal is to...

UTL_FILE and Network path

by: Zima | last post by:

Hi I'm using Oracle 9i My question is how can I use utl_file with network path utl_file_dir like '\\mycomputer\FolderA' I know that in this...

UTL_FILE(HELP)

by: Prasanna | last post by:

Hi All Nice to be in this Forum. I have questions to ask on UTL_FILE :roll: Let me explain the concept 1.When the net is down the user will take...

UTL_FILE inalid directory path

by: djramesh04 | last post by:

ORA-29280: invalid directory path ORA-06512: at "SYS.UTL_FILE", line 33 ORA-06512: at "SYS.UTL_FILE", line 436 ORA-06512: at...

Pipe delimitating function

by: orajit | last post by:

I have created one file c;/temp/test.txt 000|23102007 001|21102007|23102007|GB11223344 001|20102007|22102007|GB11223345...

Re: utl_file.fopen and Oracle v9

by: abulafia | last post by:

Originally posted by Julia Sats

The Top 5 Skills You Need in a PowerApps Developer

by: concettolabs | last post by:

In today's business world, businesses are increasingly turning to PowerApps to develop custom business applications. PowerApps is a powerful tool...

.NET Framework

List of Topmost 8 Electronics Engineering Forum Websites

by: teenabhardwaj | last post by:

How would one discover a valid source for learning news, comfort, and help for engineering designs? Covering through piles of books takes a lot of...

How to minimize a tool bar that shows up when in reports

by: CD Tom | last post by:

This happens in runtime 2013 and 2016. When a report is run and then closed a toolbar shows up and the only way to get it to go away is to right...

how to collapse a custom toolbar in VBA

by: CD Tom | last post by:

This only shows up in access runtime. When a user select a report from my report menu when they close the report they get a menu I've called Add-ins...

https redirect with reverse proxy

by: jalbright99669 | last post by:

Am having a bit of a time with URL Rewrite. I need to incorporate http to https redirect with a reverse proxy. I have the URL Rewrite rules made...

IIS / Internet Information Server

How to layout php header location with a variable as the location?

by: Matthew3360 | last post by:

Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function. Here is my code. ...

PHP

HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computer

by: AndyPSV | last post by:

HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable...

How do I view Redshift db tables in Alteryx

by: Arjunsri | last post by:

I have a Redshift database that I need to use as an import data source. I have configured the DSN connection using the server, port, database, and...

by: WisdomUfot | last post by:

It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific...