Upload CSV file Insert to Table [ Data Type = Blob ]

哈哈哈哈, 終於找到在Oracle ERP 裡面上傳檔案的方案


我討厭 SQL Loader...因為這樣子使用者要知道格式, 再將檔案放到特定的位置
而且有問題, 還要看得懂Log檔

實在是太麻煩了

參考網址: http://lyhpcha.blogspot.tw/2012/06/oracle-erp.html

寫到讀取上傳檔案的方法 ( Data Type = Blob)

declare
     tempdata        varchar2(32767);
     li_pos             INTEGER  := 0;
     li_old_pos       INTEGER  := 0;
begin

loop
    tempdata :='';
    --chr(10)換行符號，li_pos為符合的字串長度，
    -- (li_old_pos +1)開始位置，1為符合chr(10)的第一筆資料
    --存在Blob欄位的資料是連續的raw字元
    --下列SQL會取出文字檔一行的資料，因為每一行是以斷行chr(10)做為分行
    select dbms_lob.instr(file_data, utl_raw.cast_to_raw(chr(10)),li_old_pos+1,1)
      into li_pos      --取出的資料格式是raw類型，不是一般的文字格式。
      from fnd_lobs
     where file_id = P04_FILE_ID;  --指定File_id
                  
 exit when li_pos = 0 ;  -- no more data            
      
    begin
    --取出指定字串，(li_pos - li_old_pos-1) 字串長度，(li_old_pos +1) 開始的位置                 
    select utl_raw.cast_to_varchar2(
              dbms_lob.substr(file_data, (li_pos - li_old_pos-1), li_old_pos +1))
      into  tempdata
      from  fnd_lobs
     where file_id = P04_FILE_ID;  --指定File_id
     end;
     
     -- 讀取出來的資料動態組成我要的內容       
     V01_SQL := 'begin insert into table_name(Col01, Col02) values(''' || replace(replace(replace(tempdata, chr(13), ''), ',', ''','), chr(10), '); end;';
      
      -- 若有問題, 可以在 log file 看到insert 語法
      fnd_file.put_line(fnd_file.log,V01_SQL);

      -- 執行動態組合的語法
      execute immediate V01_SQL;
           
      li_old_pos := li_pos;  -- pos of a file
  end loop;

Form - Button - When-button-pressed

FUNCTION UPLOAD_CSV_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 - web 顯示: Package: FND_FILE_UPLOAD

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;

 FND_MESSAGE.DEBUG('web: '||L_URL);

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

[ Package] FND_FILE_UPLOAD.DisplayGFMForm 這裡就是生成 Upload File 的 Web

原本ERP的網址, 會長成以下的樣子
http://erpap.xxxxx.com:8001/pls/TEST/fnd_file_upload.displayGFMform?access_id=7088649330&l_server_url=http://erpap.xxxxx.com:8001/pls/TEST