PROCEDURE PROC_READ_PARTS_DAT(
i_pid IN CIS_MS_TEMP_ITEM.INS_PID%TYPE,
i_employ_no IN CIS_MS_TEMP_ITEM.INS_EMPLOY_NO%TYPE,
i_path IN VARCHAR2,
i_back_path IN VARCHAR2,
o_sts OUT BINARY_INTEGER,
o_errm OUT VARCHAR2)
IS
PROCESS_ERROR EXCEPTION;
w_file_name VARCHAR2(100);
c_gst_filepath VARCHAR2(20) := 'DAT_GLOGOS_FILEPATH';
c_back_filepath VARCHAR2(30) := 'DAT_GLOGOS_FILEPATH_BACK';
w_fexist_flg BOOLEAN;
w_fopen_flg BOOLEAN;
w_flength NUMBER;
w_block_size BINARY_INTEGER;
w_line_content VARCHAR2(4000);
w_insertFlag VARCHAR2(10);
vSFile UTL_FILE.FILE_TYPE;
....
--変数
w_temp_conversion_data VARCHAR2(4000);
w_temp_data VARCHAR2(200);
w_read_data_flag VARCHAR2(2):='20';
w_create_folder_sql VARCHAR2(200);
w_j NUMBER:=0;
w_last_index NUMBER:=0;
w_length NUMBER:=0;
w_rownumber NUMBER:=0;
w_filenumber NUMBER:=0;
--SIZE
w_line_max_size NUMBER:=32767;
-- ファイル名
CURSOR CUR_FILE_NAME(filepath IN VARCHAR2) IS
SELECT name FROM (SELECT * FROM table(util.ls(filepath)));
BEGIN
o_sts := c_process_sts_ok;
--フォルダを指定する
w_create_folder_sql :=' create or replace directory '|| c_gst_filepath|| ' as '''
|| i_path ||'''';
EXECUTE IMMEDIATE w_create_folder_sql;
--バックフォルダを指定する
w_create_folder_sql :=' create or replace directory '|| c_back_filepath|| ' as '''
|| i_back_path ||'''';
EXECUTE IMMEDIATE w_create_folder_sql;
--処理ファイル
OPEN CUR_FILE_NAME(c_gst_filepath);
LOOP
--処理ファイルの名前を取得する
FETCH CUR_FILE_NAME INTO w_file_name;
IF CUR_FILE_NAME%NOTFOUND THEN
IF w_filenumber=0 THEN
RAISE PROCESS_ERROR;
ELSE
EXIT;
END IF;
ELSE
w_filenumber:=w_filenumber+1;
END IF;
--ファイルを読みます
vSFile := utl_file.fopen(c_gst_filepath,w_file_name,'r',32767);
w_fopen_flg := utl_file.is_open(vSFile);
IF NOT w_fopen_flg THEN
RAISE PROCESS_ERROR;
END IF;
--ファイル存在の判断
utl_file.fgetattr(c_gst_filepath,w_file_name,w_fexist_flg,w_flength,w_block_size);
IF NOT w_fexist_flg THEN
RAISE PROCESS_ERROR;
END IF;
--ファイルデータの取得
LOOP
BEGIN
utl_file.get_line (vSFile, w_line_content,32767);
w_rownumber:=w_rownumber+1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
IF w_rownumber=0 THEN
RAISE PROCESS_ERROR;
ELSE
EXIT;
END IF;
END;
w_insertFlag:=substr(w_line_content,0,2);
--受信ファイルのRecord IDは"20"であるデータだけを読込んでテーブルに挿入する.
IF w_insertFlag = '20' THEN
--部品コード
w_item_code:=TRIM(substr(w_line_content,w_item_code_start_no,w_item_code_size));
--部品名
w_temp_conversion_data:=substr(w_line_content,w_item_description_start_no,w_length);
w_item_description:=TRIM(substrb(w_temp_conversion_data,0,w_item_description_size_db));
...
--入り数
w_j:=w_last_index;
w_temp_conversion_data:=substr(w_line_content,w_j,w_no_of_inner_size);
--テーブルにデータの追加
BEGIN
INSERT INTO cis_ms_temp_item
(
ITEM_CODE,
INS_EMPLOY_NO,
COMPLETE_FLAG
)
VALUES
(
w_item_code,
i_employ_no,
w_complete_flag
);
IF sql%rowcount=0 THEN
RAISE PROCESS_ERROR;
END IF;
END;
END IF;
END LOOP;
utl_file.fclose(vSFile);
--バックフォルダにファイルを移動する
utl_file.fcopy(c_gst_filepath,w_file_name,c_back_filepath,w_file_name);
utl_file.fremove(c_gst_filepath,w_file_name);
COMMIT;
END LOOP;
CLOSE CUR_FILE_NAME;
utl_file.fclose_all();
EXCEPTION
WHEN PROCESS_ERROR THEN
ROLLBACK;
IF utl_file.is_open(vSFile) THEN
utl_file.fclose(vSFile);
END IF;
o_sts:=c_process_sts_error;
o_errm :=dbms_utility.format_error_stack;
WHEN OTHERS THEN
ROLLBACK;
IF utl_file.is_open(vSFile) THEN
utl_file.fclose(vSFile);
END IF;
--DBMS_OUTPUT.PUT_LINE( dbms_utility.format_error_stack);
o_sts:=c_process_sts_error;
o_errm :=dbms_utility.format_error_stack;
END PROC_READ_PARTS_DAT;