Re: How to avoid # via Utl_file Package

Thanks

Is it possible that send some sample code regarding my query?

Regards

Sharbat

"Mark C. Stock" <mcstockX_at_Xenquery .com> wrote in message news:<yZCdne3nqtgMqMHdRVn-jA_at_comcast.com>...
> "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 - 23:30:31 CST