How to avoid # via Utl_file Package
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.
…………………………………………………………………………
…………………………………………………………………………
…………………………………………………………………………
…………………………………………………………………………
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
Chuck
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;
