使用CSV导入Oracle Form

1、使用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與名稱。

2、4. 即可執行將檔案上傳至FORM。(1) 按下執行按鈕則會跳出網頁視窗選取欲上傳之檔案。(2) 選好上傳檔案後點選Submit上傳，則會顯示是否成功。(3) 成功後關閉網頁。點選確認已上傳則開始將檔案複製到FORM中。(4) 導入過程中，在畫面左下角會顯示導入內容且畫面也會持續更新內容。(5) 完全執行完畢後會顯示導入筆數。二、注意事項：1.上傳檔案只能是.TXT或.CSV或.TSV且編碼必須為UTF8或ZHT16BIG5否則會失敗。2.檔案抬頭欄位必須保留且名稱必須與複製BLOCK.ITEM名稱相同。(若欲不同則需修改LOAD_FILE判斷抬頭名稱是否與設定欄位相同)3.每一列資料皆要設定

3、欄位區隔與換行符號。如下圖欄位區隔為Tab換行區隔為;(可自訂但決定後不可改變)4.注意若有”上引號時，用EXCEL另存CSV或TSV或TXT檔案時判斷會有問題。需先將”改為其他符號另存新檔後在取替代回”。5.此公用程式只判斷欄位抬頭欄位名稱是否與設定相同未判斷各欄位資料內容是否符合格式做卡關。若需要可自行在LOAD_FILE中自行加入。可在下圖copy前加入卡關!6.在導入之BLOCKTrigger(ON-ERROR)或關閉FORM時，最好再次呼叫CLEAR_FND_LOB以確保上傳檔案確實刪除避免無用檔案上傳造成伺服器負擔。 共用程式FILE_UPLOAD、LOAD_FILE與CLEAR_

4、FND_LOB如下：PROCEDURE LOAD_FILE (V_BLOCK VARCHAR2 , V_FIELD VARCHAR2 , V_COL_SEPARATE VARCHAR2 , V_ROW_SEPARATE VARCHAR2 ) IS FLE_GFM_ID NUMBER; V_FILE VARCHAR2 (1000); V_ROW_SEP VARCHAR2 (10); V_COL_SEP VARCHAR2 (10); V_COMMA_EXIST BOOLEAN := FALSE; MY_FIELD DBMS_UTILITY.UNCL_ARRAY; MY_FIELD_CNT BINA

5、RY_INTEGER; MY_FIELD_LOC BINARY_INTEGER := 1; I NUMBER; FILE_LEN NUMBER; FILE_LOC NUMBER; - File location TEMP_DATA VARCHAR2 (10000); TEMP_TAB VARCHAR2 (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 locat

6、ion on temp buffer COUNTER NUMBER := 0; CONTROL_LANGUAGE NUMBER; V_PERIOD VARCHAR2 (20); V_DATE DATE; V_DATE_NAME VARCHAR2 (20); V_MESG_LEVEL VARCHAR2 (10);BEGIN -用,號分開欄位 ex: XYZ To X,Y,Z DBMS_UTILITY.COMMA_TO_TABLE (V_FIELD, MY_FIELD_CNT, MY_FIELD); -檢查設定欄位是否存在 FOR I IN 1 . MY_FIELD_CNT LOOP MY_FIE

7、LD (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

8、.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_

9、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 len

10、gth 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_VALIDATE); -視需要可不清除 - Select data FILE_LOC := 1; ENTER_LOC := 1; LOOP - Find enter key location SELECT DBMS_LOB.INST

11、R (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 W

12、HEN 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 ) INT

13、O 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_

14、VARCHAR2 (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;

15、 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_VARCHAR2 (TEMP_DATA); TAB_LOC := 1; TAB_LOC_OLD

16、:= 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 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_L

17、OC_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,T

18、G_IMPORT_ITEM_TEMP.ATTRIBUTE3, TG_IMPORT_ITEM_TEMP.SEGMENT1,TG_IMPORT_ITEM_TEMP.DESCRIPTION,TG_IMPORT_ITEM_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

19、; 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 0) LOOP TAB_LOC := INSTR (TEMP

20、_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_TABMY_FIELD (MY_FIELD_LOC) THEN FND_MESSAGE.DEBUG(TEMP_TAB| 欄位與設定欄位 |MY_FIELD (MY_FIELD_

21、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_MESSAG

22、E.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; :S

23、YSTEM.MESSAGE_LEVEL := 5; :SYSTEM.MESSAGE_LEVEL := V_MESG_LEVEL;END LOAD_FILE;PROCEDURE FILE_UPLOAD IS ACCESS_ID NUMBER; L_PARAMETERS VARCHAR2(100); BUTTON_CHOICE INTEGER; L_FILE_ID VARCHAR2(100); L_GFM_ID INTEGER;BEGIN ACCESS_ID := FND_GFM.AUTHORIZE(NULL); L_PARAMETERS := access_id= | ACCESS_ID; FN

24、D_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

25、_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 T