Home » SQL & PL/SQL » SQL & PL/SQL » Got Problem in Reading Data From Text Files Using UTL_FILE Package in Oracle 10g Environment
Got Problem in Reading Data From Text Files Using UTL_FILE Package in Oracle 10g Environment [message #312919] Thu, 10 April 2008 02:14 Go to next message
sirige
Messages: 6
Registered: April 2008
Junior Member
Hi All,
This is my first Form which I am posting.
I had a problem in reading the data from a text file using UTL_FILE package.
In the text file from which data has to be read, There may be Lines which are blank, but after 2-3 lines there may be data.But, I have to read all the data in the text file up to the last lie in it and has to store data into the table.
The sample data in the text file is as shown below.

101,Henry,bankingSector,wanted


109,Gorge,MarketingSector,Unwanted
200,Sandy,BankingSector,Wanted


900,kajol,Accounts,Unwanted

The data is as above and I had to read all the above data till the end of the file and has to store that in the table, will anybody pls help in resolving this.


Thanks in Advance.
Sirige.
Re: Got Problem in Reading Data From Text Files Using UTL_FILE Package in Oracle 10g Environment [message #312924 is a reply to message #312919] Thu, 10 April 2008 02:24 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
Instead of using the UTL_FILE you can explore SQL LOADER / EXTERNAL TABLE options which are meant for such purposes.


regards,
Re: Got Problem in Reading Data From Text Files Using UTL_FILE Package in Oracle 10g Environment [message #312925 is a reply to message #312924] Thu, 10 April 2008 02:27 Go to previous messageGo to next message
sirige
Messages: 6
Registered: April 2008
Junior Member
Thanks for you response, But the problem is that we have to use PL/SQL only, other than that no other tools should be used. Is there any way to Read such type of data using PL/SQL developer only. Pls help me regarding this
Re: Got Problem in Reading Data From Text Files Using UTL_FILE Package in Oracle 10g Environment [message #312938 is a reply to message #312925] Thu, 10 April 2008 03:04 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
Ok.Can you post what you tried and the problem you are facing.Refer to the ORAFAQ guidelines on How to format your code.


regards,
Re: Got Problem in Reading Data From Text Files Using UTL_FILE Package in Oracle 10g Environment [message #312950 is a reply to message #312925] Thu, 10 April 2008 03:31 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:
Thanks for you response, But the problem is that we have to use PL/SQL only, other than that no other tools should be used. Is there any way to Read such type of data using PL/SQL developer only. Pls help me regarding this
So this is homework then? In future, please post homework questions in the appropriate forum.
Re: Got Problem in Reading Data From Text Files Using UTL_FILE Package in Oracle 10g Environment [message #312976 is a reply to message #312938] Thu, 10 April 2008 04:34 Go to previous messageGo to next message
sirige
Messages: 6
Registered: April 2008
Junior Member
Hi,
The Following is the code which I tried to read data from the Text file which I stated as above.
The Code is as follows:

CREATE OR REPLACE PROCEDURE proc_name(v_file_name IN VARCHAR2 DEFAULT 'Test.txt') AS
l_file_handle UTL_FILE.FILE_TYPE;
l_record VARCHAR2(1000);
l_file_name VARCHAR2(100);
l_file_path VARCHAR2(200);

BEGIN
  l_file_name := 'employee_detials.txt';
  l_file_path := 'D:\Temp';
  l_file_handle := UTL_FILE.FOPEN(l_file_path,l_file_name,'R');
  LOOP
    BEGIN
      UTL_FILE.GET_LINE(lfile_handle,lrecord);
      IF lrecord IS NULL THEN
         EXIT;
      ELSIF SUBSTR(lrecord,1,1)<>'H' THEN
         INSERT INTO tb_name(column_num1,column_num2,column_num3)
             VALUES('value1',value2,
                     substr(lrecord,1,INSTRlrecord,';')-1));
      END IF;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
              EXIT;
    END;
  END LOOP;
  COMMIT;
  UTL_FILE.FCLOSE(lfile_handle);
END proc_name;

If the Text file 'employee_detials.txt' has data as follows,
101employeename1employeeaddress1phone1state1
102employeename2employeeaddress2phone2state2
103employeename3employeeaddress3phone3state3

104employeename4employeeaddress4phone4state4
105employeename5employeeaddress5phone5state5

106employeename6employeeaddress6phone6state6

107employeename7employeeaddress7phone7state7
108employeename8employeeaddress8phone8state8

and so on. So, If the lrecord Is NULL then the Filehandle Getting out of the Loop and hence not reading all the Data upto the end of the file.

Let me know the solution for this type of text files.

Thanks in Advance.
Sirige
Re: Got Problem in Reading Data From Text Files Using UTL_FILE Package in Oracle 10g Environment [message #312980 is a reply to message #312976] Thu, 10 April 2008 04:41 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
So remove the ...

IF lrecord IS NULL THEN
   EXIT;


and maybe change the next line too something that skips lines that are NULL.

Re: Got Problem in Reading Data From Text Files Using UTL_FILE Package in Oracle 10g Environment [message #312981 is a reply to message #312976] Thu, 10 April 2008 04:41 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Why Don't you try to call Sql Loader from PL/SQL.
Re: Got Problem in Reading Data From Text Files Using UTL_FILE Package in Oracle 10g Environment [message #312982 is a reply to message #312919] Thu, 10 April 2008 04:45 Go to previous messageGo to next message
mohdbfaq
Messages: 5
Registered: April 2008
Junior Member
If no text was read due to end of file, the NO_DATA_FOUND exception is raised. Handle the EOF using exception instead of specifying EXIT condition inside the loop.

Try after removing this line
-- IF lrecord IS NULL THEN EXIT;


Mohan

[Updated on: Thu, 10 April 2008 05:22] by Moderator

Report message to a moderator

Re: Got Problem in Reading Data From Text Files Using UTL_FILE Package in Oracle 10g Environment [message #312985 is a reply to message #312981] Thu, 10 April 2008 04:47 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
You Can Make A Control File for sqlloader with conditions about data that has to be loaded by using UTL_FILE Package.

Then make a java procedure in database that will return the system console where you can directly call sql loader.

I think that will be a much easier approach.


Google for how to call sqlloader from pl/sql.
Re: Got Problem in Reading Data From Text Files Using UTL_FILE Package in Oracle 10g Environment [message #312988 is a reply to message #312982] Thu, 10 April 2008 04:53 Go to previous message
sirige
Messages: 6
Registered: April 2008
Junior Member
Thanks for your Quick response, I am not getting the point which ou specify about EOF, How can I use that EOF Exception in my code. will you please change that and let me know.
Previous Topic: Table Column
Next Topic: Delete from 2 tables at the same time
Goto Forum:
  


Current Time: Sun Dec 04 12:29:38 CST 2016

Total time taken to generate the page: 0.11713 seconds