Using Text_IO To Read Files in Oracle D2k

发布时间:2017-6-25 21:48:53 编辑:www.fx114.net 分享查询网我要评论
本篇文章主要介绍了"Using Text_IO To Read Files in Oracle D2k ",主要涉及到Using Text_IO To Read Files in Oracle D2k 方面的内容,对于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.

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.
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
   



上一篇:Set Font Properties On Mouse Hover Of Push Button And Text Items At Run time In Oracle Forms
下一篇:shell函数

相关文章

相关评论

本站评论功能暂时取消,后续此功能例行通知。

一、不得利用本站危害国家安全、泄露国家秘密,不得侵犯国家社会集体的和公民的合法权益,不得利用本站制作、复制和传播不法有害信息!

二、互相尊重,对自己的言论和行为负责。