Re: UTL_FILE example?

Maria,

I do this quite often while dealing with external interfaces to my database. I am including a small file with the basic layout that you need. You will need to make changes to it to customize it to your looping situation. If you have any other questions email me directly.

Andy Hendrickson

procedure proc_name

is

    input_file utl_file.file_type;
    input_rec varchar2(200);

begin

    input_file := utl_file.fopen (data_file_name, 'R');

    utl_file.get_line (input_file, input_rec);     loop

        loop

            --get your string
            --insert data into table
            --exit when no more data to insert

        end loop;
        --get next record to process - when no more data the 'no_data_found'

    end loop;
utl_file.fclose(input_file);
commit;

exception
when no_data_found

            then utl_file.fclose (input_file); when utl_file.internal_error

            then utl_file.fclose (input_file);
                    raise_application_error(-20003,'Internal Error');
when utl_file.invalid_filehandle
            then utl_file.fclose (input_file);
                    raise_application_error(-20003,'Invalid file handle');
when utl_file.invalid_operation
            then utl_file.fclose (input_file);
                    raise_application_error(-20003,'Invalid operation');
when utl_file.invalid_path
            then utl_file.fclose (input_file);
                    raise_application_error(-20003,'Invalid path');
when utl_file.read_error
            then utl_file.fclose (input_file);
                    raise_application_error(-20003,'Read error');
when utl_file.write_error
            then utl_file.fclose (input_file);
                    raise_application_error(-20003,'Write error');
 when value_error
            then utl_file.fclose (input_file);
                    raise_application_error(-20003,'Value error');
 when others
            then utl_file.fclose (input_file);

raise_application_error(-20002,To_Char(SQLCODE)||SQLERRM);

end;

AussieSyd <sydneysd_at_hotmail.com> wrote in message news:37976D2B.74CCD245_at_hotmail.com...
> I am looking for an example of a way to read a data file using UTL_FILE.
>
> I need to read a line, extract a six digit set of numbers, then insert
> the numbers
> into an Oracle table. Then I need to read the same line again and if
> there is another
> set of numbers, I need to insert them into the next row of the Oracle
> table. I need
> to loop through this row in the data file until I reach the end of the
> line. I'm having trouble finding a good example in my Oracle books. Can
> anyone help?
>
> Sincerely - and thank you in advance,
>
> Maria Singleton
>
Received on Fri Jul 23 1999 - 14:45:19 CEST