Press "Enter" to skip to content

Using Text_IO To Read Files in Oracle D2k

Suppose you want to read a file from D2k client and want to store its content in Oracle database. But if you will insert row by row from client to server it will take more time and increase lot of network traffic / round trips.

The solution of this problem is to store the content of the text file into an array and then pass it to database procedure and insert record through that procedure. Here is the example step by step:

1)  Create a package in Oracle database.

Create or Replace Package DB_insert
Type textrow is table of Varchar2(1000)
    index by binary_integer;

Procedure Insert_into_table (iarray in textrow);

Create or Replace Package Body DB_insert
Procedure Insert_into_table(iarray in textrow)
   For i in 1..iarray.count loop
       Insert into Dummytbl values (iarray(i));
       — you can extract the content from iarray(i) to insert values into multiple fields
       — e.g. iarray(i).fieldname
   End Loop;

2)   Now in D2k write a procedure to read text file and store it into array and pass it to that package you crated above.

Procedure Read_File (ifilename in Varchar2)
 infile Text_IO.File_type;
irow DB_insert.textrow;
nelm number := 1;
   infile := text_io.fopen(ifilename, ‘r’);
     text_io.get_line(infile, irow(nelm));
     nelm := nelm + 1;
End Loop;
   when no_data_found then
     — end of file reached
   message(‘Read Completed.’);
   — pass it to database
  message(‘Data saved.’);
  when others then
    if text_io.is_open(infile) then
    end if;

See also: Reading csv files with Text_io

Vinish Kapoor

Hi, I am a full stack developer and writing about development. I document everything I learn and help thousands of people. is created, written, and maintained by me; it is built on WordPress, and hosted by Bluehost. Connect with me on Facebook, Twitter, GitHub, and get notifications for new posts.


  1. Anonymous Anonymous

    Great example… worked terrifically..

  2. Daniel Daniel

    What is the meaning of this code in above example:
    — pass it to database

  3. its a database package used with array parameter to insert the rows into table, you can use your custom package or database procedure to insert rows.

  4. Daniel Daniel


  5. Nisha Nisha

    Its really a very good example.. thank u so much for this post.

Leave a Reply

Your email address will not be published. Required fields are marked *