Re: How to avoid # via Utl_file Package

"Sharbat" <sharbats_at_hotmail.com> wrote in message news:68aa131b.0403200307.233b6c80_at_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;&#823 0;&#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;&#823 0;&#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;&#823 0;&#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;&#823 0;&#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;

all you need to do is construct an appropriate if statement and not do the insert
if the next line read should also not be inserted, then you'll need to set a boolean variable and also check that in your if condition

;-{ mcs Received on Sat Mar 20 2004 - 06:13:44 CST