Home » SQL & PL/SQL » SQL & PL/SQL » UTL_FILE Line Length Problem (9i)
UTL_FILE Line Length Problem [message #314072] Tue, 15 April 2008 14:46 Go to next message
gsmith140
Messages: 2
Registered: April 2008
Junior Member
Hi,

I'm writing a procedure that will read lines from a text file, break the lines up into fields and then insert the values into various tables. It's not very complex, but the lines in the text file are pretty large, around 3000 characters.

I'm using utl_file.get_line to retrieve the lines from the text file. The problem I'm having is that get_line is not getting the entire line. Also, for different lines, it grabs different lengths. For the first line, it gets 792 characters, for the next, 805 characters and so on. I have set the buffer to the max in the FOPEN function, and I've also set the line length in get_line. Is it possible there is some hidden newline character in my file? Below is a code sample, any help on this would be greatly appreciated. Thanks.


l_log_dir_read VARCHAR2(50) :='/tmp';
l_log_name_read VARCHAR2(200) :='TESTFILE.txt';
l_log_handle UTL_FILE.FILE_TYPE;
v_line VARCHAR2(4000);

--Open the file for reading
l_log_handle := UTL_FILE.FOPEN(l_log_dir_read, l_log_name_read, 'r', 32767);

utl_file.get_line(l_log_handle, v_line, 3000);
Re: UTL_FILE Line Length Problem [message #314217 is a reply to message #314072] Wed, 16 April 2008 01:47 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Why can't you do the same using external tables and multi table insert ? It is efficient and easier (lesser code).

Regards

Raj
Re: UTL_FILE Line Length Problem [message #314327 is a reply to message #314217] Wed, 16 April 2008 06:27 Go to previous messageGo to next message
gsmith140
Messages: 2
Registered: April 2008
Junior Member
I'm relatively new to Oracle, I'm not familiar with this technique. Can you describe this technique or point me to a site with more information? My procedure has a lot of decision statements based on certain values from the text files, so it allows me a certain level of flexibility. Can I still get this from your suggestion?
Re: UTL_FILE Line Length Problem [message #314343 is a reply to message #314327] Wed, 16 April 2008 07:14 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Check this link. It all depends on how complicated your logic.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9014.htm#i2163698
http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/part_et.htm#i436567

Regards

Raj
Previous Topic: Problem with the Oracle Comment's syntax (merged 2 threads)
Next Topic: varchar length problem in materialized view with my function.
Goto Forum:
  


Current Time: Mon Dec 05 07:15:32 CST 2016

Total time taken to generate the page: 0.14212 seconds