如果需要大量的資料匯入, 可以選擇用 SQL Loader, 但是我不會用 (汗)
Oracle 還是很貼心的, 給我們一個 API : FND_GFM, 提供比較直覺的方式, 用 tsv / csv 檔的方式把資料上傳到 Form 上
- 選擇檔案 (跳出選擇檔案的頁面)
FUNCTION OPEN_FILE RETURN NUMBER IS
ACCESS_ID NUMBER;
L_SERVER_URL VARCHAR2 (255);
L_URL VARCHAR2 (255);
FLE_GFM_ID NUMBER;
BUTTON_CHOICE NUMBER;
BEGIN
-- Get process ID
ACCESS_ID := FND_GFM.AUTHORIZE (NULL);
-- Get web server address
FND_PROFILE.GET ('APPS_WEB_AGENT', L_SERVER_URL);
-- Call web page
L_URL := RTRIM (L_SERVER_URL, '/')
|| '/fnd_file_upload.displayGFMform?access_id='
|| TO_CHAR(ACCESS_ID)
|| CHR(38)
|| 'l_server_url='
|| L_SERVER_URL;
IF (L_URL IS NULL) THEN
RAISE FORM_TRIGGER_FAILURE;
END IF;
-- Open web page
FND_UTILITIES.OPEN_URL(L_URL);
-- This action is waiting for user select file, and check upload
FND_MESSAGE.SET_NAME('FND', 'ATCHMT-FILE-UPLOAD-COMPLETE');
BUTTON_CHOICE := FND_MESSAGE.QUESTION (BUTTON1 => 'YES'
, BUTTON2 => NULL
, BUTTON3 => 'NO'
, DEFAULT_BTN => 1
, CANCEL_BTN => 3
, ICON => 'question'
);
-- Get file ID
FLE_GFM_ID := FND_GFM.GET_FILE_ID (ACCESS_ID);
IF FLE_GFM_ID IS NOT NULL AND FLE_GFM_ID > 0 THEN
IF BUTTON_CHOICE = 1 THEN
RETURN FLE_GFM_ID; -- File is selected and upload
ELSE
RETURN 0; -- File is selected but not upload
END IF;
ELSE
RETURN -1; -- File is not selected
END IF;
END OPEN_FILE;
- 開始解讀檔案內容
PROCEDURE LOAD_EXCEL (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 BINARY_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 location 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
--Sperate V_FIELD to MY_FIELD (Example: 'x,y,z' -> 'x', 'y', 'z')
DBMS_UTILITY.COMMA_TO_TABLE (V_FIELD, MY_FIELD_CNT, MY_FIELD);
--Check all columns is exist BLACK.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 ('Item : ' || V_BLOCK || '.' || MY_FIELD (I) || ' does not exist !!');
RAISE FORM_TRIGGER_FAILURE;
END IF;
END;
END LOOP;
-- Get column separate char
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;
-- Get row separate char
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;
------------------------
------------------------
-- Open file
FLE_GFM_ID := FORM_LOAD_EXCEL.OPEN_FILE;
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;
GO_BLOCK (V_BLOCK);
CLEAR_BLOCK (NO_VALIDATE);
-- 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;
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;
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;
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_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;
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;
TEMP_DATA := LTRIM (RTRIM (UTL_RAW.CAST_TO_VARCHAR2 (TEMP_DATA)));
TAB_LOC := 1;
TAB_LOC_OLD := 1;
MY_FIELD_LOC := 1;
IF COUNTER =0 THEN
NULL;
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;
COPY (TEMP_TAB, V_BLOCK || '.' || MY_FIELD (MY_FIELD_LOC));
MY_FIELD_LOC := MY_FIELD_LOC + 1;
END LOOP;
NEXT_RECORD;
END IF;
COUNTER := COUNTER + 1;
EXIT WHEN FILE_LOC >= FILE_LEN;
END LOOP;
FIRST_RECORD;
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
FORM_LOAD_EXCEL.CLEAR_FND_LOB;
V_MESG_LEVEL := :SYSTEM.MESSAGE_LEVEL;
:SYSTEM.MESSAGE_LEVEL := '5';
:SYSTEM.MESSAGE_LEVEL := V_MESG_LEVEL;
END LOAD_EXCEL;
- 清除 FND_LOB
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;
STANDARD.COMMIT;
COPY ('0', 'system.message_level');
END IF;
END CLEAR_FND_LOB;
全站熱搜
留言列表
Web (1)
{{ article.title }}
