首页 > 代码库 > Using Text_IO To Read Files in Oracle D2k
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.
http://foxinfotech.blogspot.com/2013/02/reading-and-importing-comma-delimited.html
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
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));
-- you can extract the content from iarray(i) to insert values into multiple fields
-- e.g. iarray(i).fieldname
--
End Loop;
Commit;
End;
/
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.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));
-- you can extract the content from iarray(i) to insert values into multiple fields
-- e.g. iarray(i).fieldname
--
End Loop;
Commit;
End;
/
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_iois
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;
http://foxinfotech.blogspot.com/2013/02/reading-and-importing-comma-delimited.html
Using Text_IO To Read Files in Oracle D2k
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。