Home » SQL & PL/SQL » SQL & PL/SQL » Read text file with delimiter besides UTL & SQL* Loader (DB 8.1.7, Solaris)
Read text file with delimiter besides UTL & SQL* Loader [message #325158] Thu, 05 June 2008 03:17 Go to next message
ying
Messages: 143
Registered: May 2002
Senior Member
Hi,

I have gone through most of the message posted about read text files. Most are using UTL_FILE & SQL*Loader, is there any other way. UTL is limited to only 32k which can't solve my problem.

Pls advise.

Ying
Re: Read text file with delimiter besides UTL & SQL* Loader [message #325160 is a reply to message #325158] Thu, 05 June 2008 03:24 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
UTL_FILE is limited to 32767 bytes at a single read, but is there any reason why you can't concatenate the results of muliple reads together?

As always, try telling us what you are trying to do, rather than how you want to achieve it.
Re: Read text file with delimiter besides UTL & SQL* Loader [message #325161 is a reply to message #325158] Thu, 05 June 2008 03:26 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

You mean for loading the data ?

Try External tables

Thumbs Up
Rajuvan.
Re: Read text file with delimiter besides UTL & SQL* Loader [message #325164 is a reply to message #325161] Thu, 05 June 2008 03:59 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
External tables won't help. You've got a 4000 byte limit on varchar2 columns, and you can't have LOBs or LONGs in them.
Re: Read text file with delimiter besides UTL & SQL* Loader [message #325214 is a reply to message #325164] Thu, 05 June 2008 05:26 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Are you sure because I cannot see any restrictions about CLOB and BLOB but I could see that external table cannot have long column. Or Did I misinterpret something.

http://www.oracle-base.com/articles/10g/ExternalTablesContainingLobData.php

http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/et_concepts.htm#i1009380

Regards

Raj
Re: Read text file with delimiter besides UTL & SQL* Loader [message #325280 is a reply to message #325214] Thu, 05 June 2008 09:21 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It might be a restriction that was relaxed in 10g, but from the 9 Documentation
Quote:
Restrictions on External Tables

* No other clauses are permitted in the same CREATE TABLE statement if you specify the external_table_clause.
* An external table cannot be a temporary table.
* You cannot specify constraints on an external table.
* An external table cannot have object type columns, LOB columns, or LONG columns.


<pause while I check the 10g docs>

Yup, the restriction has been relaxed a bit in 10g.
From the docs
Quote:
Restrictions on External Tables
External tables are subject to the following restrictions:

* An external table cannot be a temporary table.
* You cannot specify constraints on an external table.
* An external table cannot have object type, varray, or LONG columns. However, you can populate LOB columns of an external table with varray or LONG data from an internal database table.


I don't know how to interpret that last restriction, as External tables are still read only in 10g (as I understand it).
Re: Read text file with delimiter besides UTL & SQL* Loader [message #325322 is a reply to message #325280] Thu, 05 June 2008 14:31 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
External tables didn't exist in the original poster's version, 8.1.7.
Re: Read text file with delimiter besides UTL & SQL* Loader [message #325462 is a reply to message #325322] Fri, 06 June 2008 04:44 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Missed that.

Looks like they're stuck with UTL_FILE
Re: Read text file with delimiter besides UTL & SQL* Loader [message #325804 is a reply to message #325462] Mon, 09 June 2008 00:02 Go to previous messageGo to next message
ying
Messages: 143
Registered: May 2002
Senior Member
Hi,

Thanks for all the reply. I strongly agree with JRowbottom - As always, try telling us what you are trying to do, rather than how you want to achieve it.

What I'm trying to is -> We are in the midst of implementing a third party recruitment system and we are required to transfer the applicants data back to our Oracle Recruitment System. The third party recruitment system will generate the data in csv/text format. So I have two main tasks here.

One is finding the solution how to read the text/csv files and

Second, study the API in order to load the data into the tables.

We can't always assume that the csv/text files will not over 32kb. So that why I'm looking for better approach.

By the way, I really have no idea on how to "concatenate the results of muliple reads together?"

Pls advise.

Ying


Re: Read text file with delimiter besides UTL & SQL* Loader [message #325840 is a reply to message #325804] Mon, 09 June 2008 02:58 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The limit on UTL_FILE is that it can read at most 32767 characters from the file in a single call to UTL_FILE.GET_LINE
If you make another call to UTL_FILE.GET after that, you can read the next line of text in from the file.
Generally, the structure for ussing UTL_FILE is something like:
Open file with call to UTL_FILE>FOPEN
Loop
  Read Line from File with GET_LINE

  Trap the No_Data_Found exception that occurrs if you read past
  the end of the file and Exit if this occurrs

  Process the line that you've just read
End Loop
Close File with UTL_FILE.FCLOSE

If you need to read all the data from the file, and have it all available at once, use a CLOB datatype, and handle it withDBMS_LOB
Re: Read text file with delimiter besides UTL & SQL* Loader [message #326107 is a reply to message #325158] Tue, 10 June 2008 04:01 Go to previous messageGo to next message
ying
Messages: 143
Registered: May 2002
Senior Member
Guess I have missunderstood, if I can understand you correctly, it only limit the get_line to the max of 32767 characters per line, not the entire text of the file, right?

For example:Assume that I have a text.txt with file content as below:

1001,MICHELLE,20,C001
1002,JAMES,23,C001
1003,JENNY,25,C008

And my code is as below:
declare
2 file_handle utl_file.file_type;
3 output_line varchar2(32767);
4 i number;
5 begin
6 i := 0;
7 file_handle := utl_file.fopen('TEST_DIR','test.txt','r');
8 loop
9 begin
10 utl_file.get_line(file_handle,output_line);
11 dbms_output.put_line('Line Number : ' || i || ' and length is : ' || length(output_line));
12 i := i + 1;
13 exception
14 when no_data_found
15 then
16 exit;
17 end;
18 end loop;
19 if utl_file.is_open(file_handle)
20 then
21 utl_file.fclose(file_handle);
22 end if;
23* end;


Then even the file size is over 32KB, then it will not be a problem. Is it correct?

Pls advise.

Ying
Re: Read text file with delimiter besides UTL & SQL* Loader [message #326116 is a reply to message #326107] Tue, 10 June 2008 04:31 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That's correct. There's no limit to the size of file that it can read (that I know of).
Re: Read text file with delimiter besides UTL & SQL* Loader [message #326135 is a reply to message #325840] Tue, 10 June 2008 05:16 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
hi,

irrespective of limitation in terms of bytes, when i am using
utl_file. followed parameters are showing compilation error on using at Oracle XE apex 3.0.

what went wrong with this utl_file syntax. or is there any other
syntax equilant to utl_file

yours
dr.s.raghunathan
Re: Read text file with delimiter besides UTL & SQL* Loader [message #326137 is a reply to message #326135] Tue, 10 June 2008 05:18 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Fancy showing us the actual error message that you're getting, and the portion of code that the error complains about?
Re: Read text file with delimiter besides UTL & SQL* Loader [message #326157 is a reply to message #326135] Tue, 10 June 2008 06:49 Go to previous message
Martin Eysackers
Messages: 80
Registered: October 2005
Location: Belgium
Member
you seem to be running in the same problem again :

http://www.orafaq.com/forum/m/326119/78349/#msg_326119

read up your posts
it's probably the same problem Smile
Previous Topic: INSERT using ROWNUM
Next Topic: Problem: The moment snapshot is get refreshed a message should be printed
Goto Forum:
  


Current Time: Sun Dec 04 06:26:03 CST 2016

Total time taken to generate the page: 0.23066 seconds