How to avoid # via Utl_file Package
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: How to avoid # via Utl_file Package

  1. #1
    Join Date
    Mar 2004
    Location
    INDIA
    Posts
    25

    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;

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    How about using some standard SQL string compariuson functions/operators? Like "<>", "LIKE", "INSTR" - you choose one. Something like:
    Code:
    ...
    if l_buffer LIKE '#%' then
      insert into TEST ...
    end if;
    ...
    or
    Code:
    ...
    if INSTR(l_buffer,'#') > 0 then
      insert into TEST ...
    end if;
    ...
    or ...... I mean, totaly basic SQL functions/operators.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width