Suppose want to read a file from d2k client and want to store it content in Oracle database. But if you would insert row by row from the client to server it would take the more time and increase lot of network Traffic/round Trips.
the solution of this problem are to store the content of the text file into an array and th En pass it to database procedure and insert record through that procedure. Here are the example step by step:
1) Create a package in Oracle database.
Create or Replace package Db_insert
As
Type Textrow is Table of VARCHAR2 (1000)
Index by Binary_integer;
Procedure insert_into_table (IArray in Textrow);
End;
/
Create or Replace package Body Db_insert
As
Procedure insert_into_table (IArray in Textrow)
Is
Begin
For I in 1..iarray.count loop
Insert into DUMMYTBL values (IArray (i));
--Can extract the content from IArray (i) to insert values into multiple fields
--e.g. IArray (i). fieldname
--
End Loop;
Commit;
End;
/2) Now on d2k write a procedure to read the text file and store it into an array and pass it to this package you crated abo ve.
Procedure Read_file (ifilename in Varchar2)
Is
InFile Text_io. File_type;
IRow Db_insert.textrow;
Nelm number: = 1;
Begin
InFile: = Text_io.fopen (Ifilename, ' R ');
Loop
Text_io.get_line (infile, IRow (Nelm));
Nelm: = Nelm + 1;
End Loop;
Exception
When No_data_found Then
--End of file reached
Text_io.fclose (infile);
Message (' Read completed. ');
--Pass it to database
Db_insert.insert_into_table (IRow);
Message (' Data saved. ');
When others then
If Text_io.is_open (infile) Then
Text_io.fclose (infile);
End If;
Message (SQLERRM);
End;See also:reading CSV files with Text_io
Http://foxinfotech.blogspot.com/2013/02/reading-and-importing-comma-delimited.html
Using Text_io to Read Files in Oracle d2k
