使用CSV导入Oracle Form..

使用CSV、TSV或TXT檔案上傳至FORM方法：
我想在從事Oracle EBS客製的各位應該多少都有碰到User提出是否可以用EXCEL匯入資料。

當然除了Oracle Web ADI功能外，另外就是使用下述方法搭配標準檔案上傳到Server在讀取組成二維陣列資料，塞到對應的畫面欄位。

因此我在網路上找到了一些資料，稍作修改做了一個共用程式給各位參考!!
一、步驟：
1.先將FILE_UPLOAD、LOAD_FILE與CLEAR_FND_LOB建立至FORM的程式單元中。

<備註一>
2.新增按鈕觸發執行。

執行內容範例如下：
3.新增參數：FLE_FILE_NAME、FLE_GFM_ID紀錄上傳檔案ID與名稱。

4.即可執行將檔案上傳至FORM。

(1)按下執行按鈕則會跳出網頁視窗選取欲上傳之檔案。

(2)選好上傳檔案後點選Submit上傳，則會顯示是否成功。

(3)成功後關閉網頁。

點選確認已上傳則開始將檔案複製到FORM中。

(4)導入過程中，在畫面左下角會顯示導入內容且畫面也會持續更新內容。

(5)完全執行完畢後會顯示導入筆數。

二、注意事項：
1.上傳檔案只能是.TXT或.CSV或.TSV且編碼必須為UTF8或ZHT16BIG5否則會失敗。

2.檔案抬頭欄位必須保留且名稱必須與複製BLOCK.ITEM名稱相同。

(若欲不同則需修改LOAD_FILE 判斷抬頭名稱是否與設定欄位相同)
3.每一列資料皆要設定欄位區隔與換行符號。

如下圖欄位區隔為Tab換行區隔為;(可自訂但決定後不可改變)
4.注意若有”上引號時，用EXCEL另存CSV或TSV或TXT檔案時判斷會有問題。

需先將”改為其他符號另存新檔後在取替代回”。

5.此公用程式只判斷欄位抬頭欄位名稱是否與設定相同未判斷各欄位資料內容是否符合格式做卡關。

若需要可自行在LOAD_FILE中自行加入。

可在下圖copy前加入卡關!!
6.在導入之BLOCK Trigger(ON-ERROR)或關閉FORM時，最好再次呼叫CLEAR_FND_LOB以確保上傳檔案確實刪除避免無用檔案上傳造成伺服器負擔。

<備註二>
<備註一> 共用程式FILE_UPLOAD、LOAD_FILE與CLEAR_FND_LOB如下：
PROCEDURE LOAD_FILE (V_BLOCK V ARCHAR2
, V_FIELD V ARCHAR2
, V_COL_SEPARATE V ARCHAR2
, V_ROW_SEPARATE V ARCHAR2
) IS
FLE_GFM_ID NUMBER;
V_FILE V ARCHAR2 (1000);
V_ROW_SEP V ARCHAR2 (10);
V_COL_SEP V ARCHAR2 (10);
V_COMMA_EXIST BOOLEAN := FALSE;
MY_FIELD DBMS_UTILITY.UNCL_ARRAY;
MY_FIELD_CNT BINARY_INTEGER;
MY_FIELD_LOC BINARY_INTEGER := 1;
I NUMBER;
FILE_LEN NUMBER;
FILE_LOC NUMBER; -- File location
TEMP_DATA V ARCHAR2 (10000);
TEMP_TAB V ARCHAR2 (10000);
ENTER_LOC NUMBER; -- ENTER_KEY location on temp buffer
TAB_LOC NUMBER; -- TAB_KEY location on temp buffer
TAB_LOC_OLD NUMBER; -- TAB_KEY location on temp buffer
COUNTER NUMBER := 0;
CONTROL_LANGUAGE NUMBER;
V_PERIOD V ARCHAR2 (20);
V_DATE DATE;
V_DATE_NAME V ARCHAR2 (20);
V_MESG_LEVEL V ARCHAR2 (10);
BEGIN
--用,號分開欄位ex: 'XYZ' To 'X,Y,Z'
DBMS_MA_TO_TABLE (V_FIELD, MY_FIELD_CNT, MY_FIELD);
--檢查設定欄位是否存在
FOR I IN 1 .. MY_FIELD_CNT LOOP
MY_FIELD (I) := LTRIM ( RTRIM (MY_FIELD (I)));
DECLARE
ITEM_ID ITEM;
BEGIN
ITEM_ID := FIND_ITEM (V_BLOCK || '.' || MY_FIELD (I));
IF ID_NULL (ITEM_ID) THEN
FND_MESSAGE.DEBUG ('欄位: ' || V_BLOCK || '.' || MY_FIELD (I) || ' 不存在!!');
CLEAR_FND_LOB; --發現設定欄位不合則刪除上傳檔案
RAISE FORM_TRIGGER_FAILURE;
END IF;
END;
END LOOP;
--FND_MESSAGE.DEBUG('欄位個數: '||MY_FIELD_CNT);
-- 判斷欄位區隔符號
IF UPPER (V_COL_SEPARATE) = '' THEN
V_COL_SEP := CHR (09);
ELSIF UPPER (V_COL_SEPARATE) <> 'NULL' THEN
V_COL_SEP := V_COL_SEPARATE;
ELSE
V_COL_SEP := CHR (0);
END IF;
--FND_MESSAGE.DEBUG('V_COL_SEPARATE : '||V_COL_SEPARATE);
-- 判斷行數區隔符號
IF UPPER (V_ROW_SEPARATE) = '' THEN
V_ROW_SEP := CHR (13);
ELSIF UPPER (V_ROW_SEPARATE) <> 'NULL' THEN
V_ROW_SEP := V_ROW_SEPARATE;
ELSE
V_ROW_SEP := CHR (0);
END IF;
--FND_MESSAGE.DEBUG('V_ROW_SEPARATE : '||V_ROW_SEPARATE); -- 開啟檔案
FLE_GFM_ID := :PARAMETER.FLE_GFM_ID;
--FND_MESSAGE.DEBUG('FILE_ID : '||FLE_GFM_ID);
IF FLE_GFM_ID > 0 THEN
-- Get file length
SELECT DBMS_LOB.GETLENGTH (FILE_DATA)
INTO FILE_LEN
FROM FND_LOBS
WHERE 0 = 0
AND FILE_ID = FLE_GFM_ID;
--FND_MESSAGE.DEBUG('資料讀取名稱: '||FILE_LEN);
GO_BLOCK (V_BLOCK);
CLEAR_BLOCK (NO_V ALIDATE); --視需要可不清除
-- Select data
FILE_LOC := 1;
ENTER_LOC := 1;
LOOP
-- Find enter key location
SELECT DBMS_LOB.INSTR (FILE_DATA
, UTL_RAW.CAST_TO_RAW (V_ROW_SEP)
, FILE_LOC
, 1
)
INTO ENTER_LOC
FROM FND_LOBS
WHERE 0 = 0
AND FILE_ID = FLE_GFM_ID;
--FND_MESSAGE.DEBUG('ENTER_LOC : '||ENTER_LOC);
BEGIN
SELECT DECODE (LANGUAGE, 'US', 1, 2)
INTO CONTROL_LANGUAGE
FROM FND_LOBS
WHERE 0 = 0
AND FILE_ID = FLE_GFM_ID;
EXCEPTION
WHEN NO_DATA_FOUND THEN
CONTROL_LANGUAGE := 2;
END;
--FND_MESSAGE.DEBUG('CONTROL_LANGUAGE : '||CONTROL_LANGUAGE);
IF ENTER_LOC > 0 THEN
IF CONTROL_LANGUAGE = 1 THEN
SELECT UTL_RAW.CONVERT (DBMS_LOB.SUBSTR (FILE_DATA, ENTER_LOC - FILE_LOC, FILE_LOC)
, 'AMERICAN_AMERICA.UTF8'
, 'AMERICAN_AMERICA.ZHT16BIG5'
)
INTO TEMP_DATA
FROM FND_LOBS
WHERE 0 = 0
AND FILE_ID = FLE_GFM_ID;
--FND_MESSAGE.DEBUG('TEMP_DATA : '||TEMP_DATA);
ELSE
SELECT DBMS_LOB.SUBSTR (FILE_DATA, ENTER_LOC - FILE_LOC, FILE_LOC)
INTO TEMP_DATA
FROM FND_LOBS
WHERE 0 = 0
AND FILE_ID = FLE_GFM_ID;
END IF;
-- TEMP_DATA := LTRIM (RTRIM (UTL_RAW.CAST_TO_V ARCHAR2 (TEMP_DATA)));
FILE_LOC := ENTER_LOC + 2;
ENTER_LOC := ENTER_LOC + 2;
ELSE
IF CONTROL_LANGUAGE = 1 THEN
SELECT UTL_RAW.CONVERT (DBMS_LOB.SUBSTR (FILE_DATA, 1000, FILE_LOC)
, 'AMERICAN_AMERICA.UTF8'
, 'AMERICAN_AMERICA.ZHT16BIG5'
)
INTO TEMP_DATA
FROM FND_LOBS
WHERE 0 = 0
AND FILE_ID = FLE_GFM_ID;
ELSE
SELECT DBMS_LOB.SUBSTR (FILE_DATA, 1000, FILE_LOC)
INTO TEMP_DATA
FROM FND_LOBS
WHERE 0 = 0
AND FILE_ID = FLE_GFM_ID;
END IF;
FILE_LOC := FILE_LEN + 2;
END IF;
--FND_MESSAGE.DEBUG('TEMP_DATA : '||TEMP_DATA);
TEMP_DATA := LTRIM (RTRIM (UTL_RAW.CAST_TO_V ARCHAR2 (TEMP_DATA)));
TAB_LOC := 1;
TAB_LOC_OLD := 1;
MY_FIELD_LOC := 1;
--FND_MESSAGE.DEBUG('COUNTER : '||COUNTER); --第n筆
--FND_MESSAGE.DEBUG('TEMP_DATA : '||TEMP_DATA);
IF COUNTER >0 THEN --第0行為抬頭標題不做insert
WHILE (MY_FIELD_LOC <= MY_FIELD_CNT AND TAB_LOC > 0)
LOOP
TAB_LOC := INSTR (TEMP_DATA, V_COL_SEP, TAB_LOC_OLD);
IF TAB_LOC > 0 THEN
TEMP_TAB := REGEXP_REPLACE(SUBSTR (TEMP_DATA, TAB_LOC_OLD, TAB_LOC - TAB_LOC_OLD),chr(10),''); --去除空行
ELSE
TEMP_TAB := REGEXP_REPLACE(SUBSTR (TEMP_DATA, TAB_LOC_OLD),chr(10),''); --去除空行
END IF;
SYNCHRONIZE; --更新畫面欄位資料若速度過慢可拿除
TAB_LOC_OLD := TAB_LOC + 1;
--Start 該作業特別新增檢查
IF V_BLOCK || '.' || MY_FIELD (MY_FIELD_LOC) IN ('TG_IMPORT_ITEM_TEMP.RECEIVING_ROUTING_ID','TG_IMPORT_ITEM_TEMP.ATTRIBUTE3',
'TG_IMPORT_ITEM_TEMP.SEGMENT1','TG_IMPORT_ITEM_TEMP.DESCRIPTION','TG_IMPORT_IT EM_TEMP.PRIMARY_UOM_CODE') THEN --檢查上傳檔案欄位內容
IF TEMP_TAB IS NULL THEN
FND_MESSAGE.SET_STRING('上傳檔案內容錯誤!!'||chr(10)||'請檢查畫面上黃色必輸欄位是否為空!!');
FND_MESSAGE.SHOW;
CLEAR_FND_LOB;
RAISE FORM_TRIGGER_FAILURE;
END IF;
END IF;
-- End 該作業特別新增檢查
COPY (TEMP_TAB, V_BLOCK || '.' || MY_FIELD (MY_FIELD_LOC));
FND_MESSAGE.SET_STRING('資料導入中... 請勿關閉視窗!!第'||COUNTER||' 筆- '||TEMP_TAB || '導入成功!!');
FND_MESSAGE.HINT;
MY_FIELD_LOC := MY_FIELD_LOC + 1;
END LOOP;
NEXT_RECORD;
ELSE --檢查抬頭欄位名稱是否和設定相同
WHILE (MY_FIELD_LOC <= MY_FIELD_CNT AND TAB_LOC > 0)
LOOP
TAB_LOC := INSTR (TEMP_DATA, V_COL_SEP, TAB_LOC_OLD);
IF TAB_LOC > 0 THEN
TEMP_TAB := SUBSTR (TEMP_DATA, TAB_LOC_OLD, TAB_LOC - TAB_LOC_OLD);
ELSE
TEMP_TAB := SUBSTR (TEMP_DATA, TAB_LOC_OLD);
END IF;
TAB_LOC_OLD := TAB_LOC + 1;
IF TEMP_TAB<>MY_FIELD (MY_FIELD_LOC) THEN
FND_MESSAGE.DEBUG(TEMP_TAB||' 欄位與設定欄位'||MY_FIELD (MY_FIELD_LOC)||'不合，請檢查上傳檔案內容!!');
CLEAR_FND_LOB;
RAISE FORM_TRIGGER_FAILURE;
END IF;
MY_FIELD_LOC := MY_FIELD_LOC + 1;
END LOOP;
END IF;
COUNTER := COUNTER + 1;
EXIT WHEN FILE_LOC >= FILE_LEN;
END LOOP;
FIRST_RECORD;
FND_MESSAGE.SET_STRING(''); --執行結束清空左下角訊息
FND_MESSAGE.HINT;
COUNTER :=COUNTER-1;--排除不算抬頭行數
FND_MESSAGE.DEBUG('共導入'||COUNTER||' 筆資料成功!!');
END IF;
----------------
-- Delete temp data
IF FLE_GFM_ID >= 0 THEN
:PARAMETER.FLE_GFM_ID := FLE_GFM_ID;
END IF;
SELECT FILE_NAME
INTO :PARAMETER.FLE_FILE_NAME
FROM FND_LOBS
WHERE 0 = 0
AND FILE_ID = FLE_GFM_ID;
-- Clear LOB File
CLEAR_FND_LOB;
V_MESG_LEVEL := :SYSTEM.MESSAGE_LEVEL;
:SYSTEM.MESSAGE_LEVEL := '5';
:SYSTEM.MESSAGE_LEVEL := V_MESG_LEVEL;
END LOAD_FILE;
PROCEDURE FILE_UPLOAD IS
ACCESS_ID NUMBER;
L_PARAMETERS V ARCHAR2(100);
BUTTON_CHOICE INTEGER;
L_FILE_ID V ARCHAR2(100);
L_GFM_ID INTEGER;
BEGIN
ACCESS_ID := FND_GFM.AUTHORIZE(NULL);
L_PARAMETERS := 'access_id=' || ACCESS_ID;
FND_FUNCTION.EXECUTE(FUNCTION_NAME => 'FND_FNDFLUPL'
,OPEN_FLAG => 'Y'
,SESSION_FLAG => 'Y'
,OTHER_PARAMS => L_PARAMETERS);
FND_MESSAGE.SET_STRING('檔案是否已上傳!?'||CHR(10)||
'請點選"確認"已上傳或"取消"返回畫面');
BUTTON_CHOICE := FND_MESSAGE.QUESTION('確認',NULL,'取消',1,3,'NOTE');
IF (BUTTON_CHOICE = 3) THEN
L_GFM_ID := FND_GFM.GET_FILE_ID(ACCESS_ID);
:PARAMETER.FLE_GFM_ID :=L_GFM_ID;
CLEAR_FND_LOB; --若已上傳則刪除上傳檔
RAISE FORM_TRIGGER_FAILURE;
ELSIF (BUTTON_CHOICE = 1) THEN
L_GFM_ID := FND_GFM.GET_FILE_ID(ACCESS_ID);
:PARAMETER.FLE_GFM_ID :=L_GFM_ID;
END IF;
IF L_GFM_ID IS NULL THEN --檢查是否上傳成功
FND_MESSAGE.DEBUG('檔案上傳不成功，煩請重新上傳!!');
RAISE FORM_TRIGGER_FAILURE;
END IF;
END;
PROCEDURE CLEAR_FND_LOB IS
BEGIN
IF :PARAMETER.FLE_GFM_ID >= 0 THEN
COPY ('5', 'system.message_level');
DELETE FND_LOBS
WHERE 0 = 0
AND FILE_ID = :PARAMETER.FLE_GFM_ID;
MIT;
COPY ('0', 'system.message_level');
END IF;
END CLEAR_FND_LOB;
<備註二>
在BLOCK→ON-ERROR加入
DECLARE
V_ERROR_CODE NUMBER;
V_ERROR_TEXT V ARCHAR2 (2000);
V_DBMS_ERROR_CODE NUMBER;
V_DBMS_ERROR_TEXT V ARCHAR2 (2000);
BEGIN
V_ERROR_CODE := ERROR_CODE;
V_ERROR_TEXT := ERROR_TEXT;
V_DBMS_ERROR_CODE := DBMS_ERROR_CODE;
V_DBMS_ERROR_TEXT := DBMS_ERROR_TEXT;
IF V_ERROR_CODE IS NOT NULL THEN
SET_ALERT_PROPERTY('errmes', TITLE, 'Info.'||LTRIM(TO_CHAR(V_ERROR_TEXT)));
SET_ALERT_PROPERTY('errmes', ALERT_MESSAGE_TEXT, V_ERROR_TEXT);
IF :PARAMETER.FLE_GFM_ID >= 0 THEN
CLEAR_FND_LOB;
RAISE FORM_TRIGGER_FAILURE; --若錯誤則刪除檔案後停止
END IF;
END IF;
END;
在FORM→CLOSE_WINDOW加入
IF :PARAMETER.FLE_GFM_ID >= 0 THEN
CLEAR_FND_LOB;
END IF;
APP_CUSTOM.CLOSE_WINDOW(:SYSTEM.EVENT_WINDOW);。