How to avoid # via Utl_file Package

From: Sharbat <sharbats_at_hotmail.com>
Date: 20 Mar 2004 03:07:50 -0800
Message-ID: <68aa131b.0403200307.233b6c80@posting.google.com>

Hello ,

I am using UTL_FILE package in order to read the text from a text file and insert this text into a table.
But the text contain special character like #.

This is the nature of this text data.

 For example the O/S text file contain the following text:

TEXT:

Name of the default schema being used in the current schema.
Name of the default schema being used in the current schema. 
Name of the default schema being used in the current schema.
###################################################

Name of the default schema being used in the current schema. Name of the default schema being used in the current schema.

###############################################

Name of the default schema being used in the current schema.
&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;
&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;
&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;
&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;&#8230;

How should we avoid reading the following line via UTL_FILE from the text file?
 So the text without these special characters will be inserted into a table.

###################################################

I wanted that these special characters would not be inserted into a table via UTL_FILE.
What code I can add in my following routine in order to avoid the reading of special characters # or a following line from the text file?

###################################################

Thanks

Sharbat

UTL FILE Code:

Declare

l_file_handle UTL_FILE.FILE_TYPE;

l_buffer VARCHAR2(4000);

BEGIN l_file_handle := UTL_FILE.FOPEN('c:\temp', 'test.txt', 'r', 4000);                                                 

loop

UTL_FILE.get_line(l_file_handle,l_buffer);

insert into TEST (text) values(l_buffer);

end loop;

exception

when no_data_found then

UTL_FILE.FCLOSE(l_file_handle);

when others then

if utl_file.is_open(l_file_handle)
then
utl_file.fclose(l_file_handle);
end if;

end; Received on Sat Mar 20 2004 - 05:07:50 CST