How to copy the information from a text file into a table using PL/SQL - Oracle

In the previous section, we simply counted the number of lines available in a text file.  Now, let us further extend the same program by transferring the information available in the text file into an Oracle database using PL/SQL.

Before going to the PL/SQL program, we need to create a simple table named “sampletable” as follows:

create table SampleTable
(
    remarks varchar2(200)
)

You can understand that I created a table “SampleTable” with only one column, “remarks” (max. up to 200 chars).  Now, we need to insert rows into that table using PL/SQL.

The PL/SQL code to achieve the desired is the following:

declare
    f utl_file.file_type;
    s varchar2(200);
    c number := 0;
begin
    f := utl_file.fopen('SAMPLEDATA','sample1.txt','R');
    loop
        utl_file.get_line(f,s);
        insert into sampletable values (s);
        c := c + 1;
    end loop;
exception
    when NO_DATA_FOUND then
        utl_file.fclose(f);
        dbms_output.put_line('No. of rows inserted : ' || c);
end;

The only extra statement I used from the above statement is the “INSERT” statement.  You may also use the statement “COMMIT” either at the INSERT statement or after you close the file handle (in the exception section).  I just left it without committing.  But it is recommended that you commit once you finish the work.