Oracle Stored Procedure read files

Oracle Stored PROCEDURE read file create or replace PROCEDURE PR_FINANCE_PRODUCT_PARSE (v_filename IN VARCHAR2, -- Resolution file name v_retvalue out number -- 1 is successful, 0 is failed) AS -- file handle filehandle utl_file.file_type; -- buffer filebuffer varchar2 (255); -- file name lv_filename varchar2 (100); -- file-to-table field ing lv_product_number varchar2 (255); lv_product_name varchar2 (255 ); lv_min_capital varchar2 (255); lv_max_capital varchar2 (255); lv_interest_rate varchar2 (255); lv_begin_time varchar2 (255); lv_end_time varchar2 (255); lv_time_limit varchar2 (255 ); lv_product_status varchar2 (255); -- column pointer lv_ I number; -- string location resolution pointer segment number; lv_sposition3 number; lv_sposition4 number; lv_sposition5 number; lv_sposition6 number; segment number; lv_sposition8 number; -- declares an invalid_data_format exception; BEGIN if v_filename is null then lv_filename: = 'finance _ product _ '| substr (to_char (sysdate, 'yyyymmddhh24miss) | '.txt '; else lv_filename: = v_filename; end if; execute immediate 'truncate table T_FINANCING_PRODUCT'; -- open a file handle, and the first parameter of fopen must be capital filehandle: = utl_file.fopen ('finance _ PRODUCT_DIR ', lv_filename, 'R'); Loop begin -- Read File Information to the buffer filebuffer, and read a row of utl_file.get_line (filehandle, filebuffer) each time ); dbms_output.put_line (filebuffer); -- the data delimiter '|' in the string of a data file. The location lv_sposition1: = instr (filebuffer, '|',); lv_sposition2: = instr (filebuffer, '|',); lv_sposition3: = instr (filebuffer, '|',); lv_sposition4: = instr (filebuffer, '|',); lv_sposition5: = instr (filebuffer, '|',); lv_sposition6: = instr (filebuffer, '|',); lv_sposition7: = instr (filebuffer, '| ); lv_sposition8: = instr (filebuffer, '|',); -- string parsing normal conditions if lv_sposition1 <> 0 then lv_product_number: = substr (filebuffer, 0, lv_sposition1-1 ); -- first column else raise syntax; end if; if lv_sposition2 <> 0 then lv_product_name: = substr (filebuffer, lv_sposition1 + 1, lv_sposition2-lv_sposition1-1); -- second column else raise invalid_data_format; end if; if lv_sposition3 <> 0 then lv_min_capital: = substr (filebuffer, lv_sposition2 + 1, lv_sposition3-lv_sposition2-1); -- the third column else raise else; end if; if lv_sposition4 <> 0 then lv_max_capital: = substr (filebuffer, lv_sposition3 + 1, lv_sposition4-lv_sposition3-1); -- fourth column else raise else; end if; if lv_sposition5 <> 0 then lv_interest_rate: = substr (filebuffer, lv_sposition4 + 1, lv_sposition5-lv_sposition4-1); -- Fifth Column else raise invalid_data_format; end if; if lv_sposition6 <> 0 then lv_begin_time: = substr (filebuffer, lv_sposition5 + 1, lv_sposition6-lv_sposition5-1); -- Sixth column else raise loss; end if; if lv_sposition7 <> 0 then lv_end_time: = substr (filebuffer, lv_sposition6 + 1, lv_sposition7-lv_sposition6-1); -- the seventh column of else raise invalid_data_format; end if; if lv_sposition8 <> 0 then lv_time_limit: = substr (filebuffer, lv_sposition7 + 1, lv_sposition8-lv_sposition7-1); -- eighth column lv_product_status: = substr (filebuffer, lv_sposition8 + 1 ); -- The ninth column else raise invalid_data_format; end if; -- after each column is cyclically completed, save the information to dbms_output.put_line ('insert data into table'); insert into T_FINANCING_PRODUCT (PRODUCT_NUMBER, PRODUCT_NAME, MIN_CAPITAL, MAX_CAPITAL, INTEREST_RATE, BEGIN_TIME, END_TIME, TIME_LIMIT, PRODUCT_STATUS) values (lv_product_number, lv_product_name, lv_min_capital, primary, lv_product_status ); exception WHEN no_data_found THEN exit; end; End Loop; commit; -- close handle utl_file.fclose (filehandle); v_retvalue: = 1; exception when invalid_data_format THEN v_retvalue: = 0; dbms_output.put_line ('invalid _ data_format exception'); when others then v_retvalue: = 0; dbms_output.put_line ('othre exception, error code = '| sqlcode | ', error msg = '| sqlerrm); END PR_FINANCE_PRODUCT_PARSE;