如果需要大量的資料匯入, 可以選擇用 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;
創作者介紹

Aloz 的 Oracle ERP 天地

Aloz 發表在 痞客邦 PIXNET 留言(8) 人氣()


留言列表 (8)

發表留言
  • Ashish Vishwakarma
  • Thank you very much for uploading wonderfull code, I was searching for this code since couple of months.

    Much Appreciated.
  • You're welcome :)

    Aloz 於 2012/03/27 12:53 回覆

  • gide
  • 請問要如何匯入大量訂單筆數?因本司訂單筆數超過1千筆,要如何快速自excel 檔輸入OM Line,成了大家頭痛的問題
  • 你好, SQL Loader 應該是比較好的方式, 只是方法你要再尋找就是了. 不然就是客製一個 Form, 目標 Table 是 SO Interface, 這也是個可以考慮的方式 :)

    Aloz 於 2012/04/02 10:36 回覆

  • Li 仁仁
  • 你好,你寫的非常的清楚,
    但我不知是那個部份有錯,運作上仍怪怪的,請問可以跟你要完整的範嗎,非常感謝~
  • 你好, 這邊就已經是完整的範例了, 可能要看你是哪邊怪怪的...

    Aloz 於 2012/08/01 14:03 回覆

  • 訪客
  • 請問 以下這一段是必須的嗎?
    可以講一下
    DBMS_UTILITY.COMMA_TO_TABLE (V_FIELD, MY_FIELD_CNT, MY_FIELD);
    的用法嗎??
    謝謝!!

    --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;

  • 你好, 抱歉把隱藏解開你才看的到

    這一段應該是必須的, 因為 csv 檔"基本"上是用逗號 "," 來區分欄位的, tsv 則是用 tab 來區分, 所以這一段就是用這樣的特性來把所有欄位與資料切開成 Table

    Aloz 於 2014/09/30 11:45 回覆

  • 訪客
  • 再請問一下,傳進去的V_FIELD 這個是什麼呢??
    DBMS_UTILITY.COMMA_TO_TABLE (V_FIELD, MY_FIELD_CNT, MY_FIELD);
  • 應該就是你檔案的字串

    Aloz 於 2014/09/30 16:49 回覆

  • 訪客
  • 感謝版主的回答,因為目前UPLOAD 到TABLE FND_LOBS ,資料型態是BLOB ,請問這樣是不是就不能用指令,
    DBMS_UTILITY.COMMA_TO_TABLE (V_FIELD, MY_FIELD_CNT, MY_FIELD);
    還是說 BLOB 可以轉成VARCHAR2 還是有其他的指令嗎
  • 先說聲抱歉, 因為我很久沒有用這個 Function, 所以已經有點忘記了

    V_FIELD 是 LOAD_EXCEL 的 Input Parameter, 型態是 VARCHAR2, 所以應該沒有你說的問題, FND_LOBS 應該是後面的事了

    Aloz 於 2014/09/30 17:31 回覆

  • 虎嵐 熊
  • 我這邊補充一下Aloz大的這Function好了!!先前我也有使用過...再套用上是有些小問題,但基本上是可用的。先說你貼的第一段...是可以不用!!這段主要是要檢查你欲上傳的欄位名稱是否在該BLOCK存在,若不存在則跳離。你可以將這檢查放置後段開啟檔案時讀取再做檢查也行。因我是提供USER標準上傳檔案格式(第一列為FORM的欄位名稱;第二列為欄位中文解釋;第三列開始才是上傳資料)。
    第二DBMS_UTILITY.COMMA_TO_TABLE的用法,此FUN是字串式的陣列資料,就是V_FIELD是你的字串組合(需注意子資料開頭不能為數字),MY_FIELD_CNT是字串欄位的個數,MY_FIELD是字串欄位的各個值。
  • 只能推一個了 XD

    Aloz 於 2014/10/02 11:29 回覆

  • 虎嵐 熊
  • 抱歉!!怕你誤會我再補充我剛說第一段的不用是說Check all columns is exist BLACK.FIELD
    那段檢查可以不用...但DBMS_UTILITY.COMMA_TO_TABLE (V_FIELD, MY_FIELD_CNT, MY_FIELD);
    這段是必須的。因後段在讀取時會使用這個去將子資料COPY到對應欄位上!!
找更多相關文章與討論

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼