Read txt file in to table by using form

هنالك طريقتان لتحميل البيانات من ملف نصي الى قاعدة البيانات باستخدام الفورم
الطريقة الاولى
بقراءة الملف سطرا بسطر وهي مفيدة كثيرا للتحقق من البيانات المدخله التحقق منها وتنقيحها قبل الادخال ولكنها بطيئة مقارنه بالطريقه الثانيه
يمكنك انشاء اجراء بالشكل التالي

PROCEDURE echo_file_contents IS
in_file Text_IO.File_Type; linebuf VARCHAR2(1800); filename VARCHAR2(30); cr number (9):=0; sq varchar2 (200);BEGIN filename:=:FIC;--'d:\a\gsm.txt';--==GET_FILE_NAME('c:/temp/', File_Filter=>'Text Files (*.txt)*.txt'); filename:=GET_FILE_NAME('c:/temp/', File_Filter=>'Text Files (*.txt)*.txt'); in_file := Text_IO.Fopen(filename, 'r'); cr:=0; LOOP Text_IO.Get_Line(in_file, linebuf); --:text_item5:=:text_item5linebufchr(10); -- you can use these line if you want to show inserted line if true then -- instade of true you can put your condition sq:='insert into ':t' values ('linebuf')';
Forms_DDL(sq); else :err:=:errlinebufchr(10); -- to display error line end if ; iF NOT Form_Success THEN Message ('insert Failed');
ELSEMessage ('The data had been inserted sucsseflly '); END IF; -- commit; cr:=cr+1; :c:=cr; END LOOP; commit;EXCEPTION WHEN no_data_found THEN Text_IO.Put_Line('Closing the file...'); Text_IO.Fclose(in_file);commit; END;

الطريق الثانيه

يمكنك استخدام sqlloder

ويقوم الاجراء التالي بانشاء جميع الملفات الخاصة المساعدة وتنفيذها من الفورم مباشرة

PROCEDURE readf IS
p_username varchar2(100) := get_application_property(username); p_password varchar2(100) := get_application_property(password);p_connect varchar2(100):= get_application_property(connect_String);p_userid varchar2(300) := null;p_text varchar2(500) := null; N_FILE text_io.file_type; v_entity_dtl varchar2(50);v_entity_dtl_file varchar2(100);v_file varchar2(150);batch_file varchar2(150);b_file text_io.file_type;v_table_name varchar2(40):= :T;--Table name v_columns varchar2(1000) := :fl;--columns name
begintool_env.getvar('service_name' ,p_connect);
v_file := :FIC;
if :FIC is null then
message('Invalid data file...');end if;n_file := TEXT_IO.FOPEN(:FIC,'R');if not text_io.is_open( n_file ) then
message('Source file not found...!!');elsetext_io.fclose( n_file );end if;
-- CREATE A CONTROL FILEn_file := TEXT_IO.FOPEN( v_file'.ctl','W');
-- INSERT THE TEXT ENTRY TO THE CONTROL FILE TEXT_IO.PUT_LINE( N_FILE ,'LOAD DATA' );TEXT_IO.PUT_LINE( N_FILE ,'INFILE ''''':FIC''''); TEXT_IO.PUT_LINE( N_FILE ,'APPEND'' INTO TABLE 'v_table_name); TEXT_IO.PUT_LINE( N_FILE ,'FIELDS TERMINATED BY '','' OPTIONALLY ENCLOSED BY ''"''TRAILING NULLCOLS('v_columns' )');
if text_io.is_open( n_file ) then TEXT_IO.FCLOSE(N_FILE);end if;
-- USERID PARAMETER OF THE SQLLDRp_userid := p_username '/' p_password '@' p_connect;
-- THE EXACT SQLLDR COMMAND INCLUDING THE PARAMETERp_text := 'sqlldr userid=' p_userid ' control='v_file'.ctl ''log='v_file'.log';
-- create a batch file here before a call by the hostb_file := TEXT_IO.FOPEN( v_file'.bat','W');
-- INSERT THE TEXT COMMAND TO THE BATCH FILETEXT_IO.PUT_LINE( b_file ,'echo off' );TEXT_IO.PUT_LINE( b_file ,p_text );TEXT_IO.PUT_LINE( b_file ,'' );TEXT_IO.PUT_LINE( b_file ,'echo ...............................................');TEXT_IO.PUT_LINE( b_file ,'echo * *');TEXT_IO.PUT_LINE( b_file ,'echo --- C L O S E T H I S W I N D O W N O W ---');if text_io.is_open( b_file ) then TEXT_IO.FCLOSE(b_file);end if;Set_application_property(Cursor_style,'BUSY');batch_file := v_file'.BAT';
-- EXECUTE THE BATCH FILE host( batch_file );

if not form_success or form_fatal or form_failure thenSet_application_property(Cursor_style,'DEFAULT');message('Errors occured during the process, pls. check all the recources..');--==,'I',true);elsif Form_Success THEN Set_application_property(Cursor_style,'DEFAULT');
message('process finished sucessfuly ');
end if;
END;