الطريقة الاولى
بقراءة الملف سطرا بسطر وهي مفيدة كثيرا للتحقق من البيانات المدخله التحقق منها وتنقيحها قبل الادخال ولكنها بطيئة مقارنه بالطريقه الثانيه
يمكنك انشاء اجراء بالشكل التالي
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;
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;