ORACLE APP培訓筆記(1) -- OutBound

用儲存過程實現資料outbound例子 [@more@]

1. 建立stored procedure，命名為"test_denilsen_util_p"
CREATE OR REPLACE PROCEDURE test_denilsen_util_p (errbuf      OUT VARCHAR2,
                                               retcode     OUT NUMBER,
                                               p_data_out_dir IN VARCHAR2,
                                               p_filename IN VARCHAR2
                                               ) IS
CURSOR c_get_data IS
 SELECT invoice_id,
  invoice_num,
  vendor_id
  FROM   ap_invoices_all
  WHERE  rownum<100;

v_file_handle   UTL_FILE.FILE_TYPE;
v_file_name       VARCHAR2(100);
v_utl_file_dir    VARCHAR2(150);
BEGIN
v_file_name := p_filename;
v_utl_file_dir   := p_data_out_dir;
v_file_handle := UTL_FILE.FOPEN(v_utl_file_dir,v_file_name,'w'); -- FILE opens for writing
FOR r_get_data IN c_get_data LOOP

UTL_FILE.PUT_LINE(v_file_handle,
    r_get_data.invoice_id ||' '
    ||r_get_data.invoice_num ||' '
    ||r_get_data.vendor_id);
END LOOP;
UTL_FILE.FCLOSE(v_file_handle);
COMMIT;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Data written into file Successfully');
EXCEPTION
WHEN UTL_FILE.INVALID_PATH THEN
     UTL_FILE.FCLOSE_ALL;
     FND_FILE.PUT_LINE(FND_FILE.LOG,'Error - 20004 : Invalid FilePath.' ||' SQL ERROR MASSAGE :'||SQLERRM);
     dbms_output.put_line('Invalid Path');
WHEN UTL_FILE.INVALID_MODE THEN
     UTL_FILE.FCLOSE_ALL;
     dbms_output.put_line('Invalid Mode');
     FND_FILE.PUT_LINE(FND_FILE.LOG,'Error - 20004 : Invalid Mode.' ||' SQL ERROR MASSAGE :'||SQLERRM);
WHEN UTL_FILE.INVALID_OPERATION THEN
     UTL_FILE.FCLOSE_ALL;
     dbms_output.put_line('Invalid Operation');
     FND_FILE.PUT_LINE(FND_FILE.LOG,'Error - 20004 : Invalid Operation.' ||' SQL ERROR MASSAGE :'||SQLERRM);
WHEN UTL_FILE.INVALID_FILEHANDLE THEN
     UTL_FILE.FCLOSE_ALL;
     dbms_output.put_line('Invalid File Handle');
     FND_FILE.PUT_LINE(FND_FILE.LOG,'Error - 20004 : Invalid Filehandle.' ||' SQL ERROR MASSAGE :'||SQLERRM);
WHEN UTL_FILE.WRITE_ERROR THEN
     UTL_FILE.FCLOSE_ALL;
WHEN OTHERS THEN
     errbuf := 'Some Error occurred during the program: ' ||TO_CHAR(SQLCODE)||' '|| SQLERRM;
     dbms_output.put_line('Some error Occured'|| SQLERRM);
     FND_FILE.PUT_LINE(FND_FILE.LOG,'Some Error occurred during the program: ' ||TO_CHAR(SQLCODE)||' '
                || SQLERRM);
END;

2. 使用者DENILSEN以application developer的responsibility登陸
3. click Concurrent -> Executable
4. 建立Executable，命名為"denilsen_invoice"，其中Executable Method為"PL/SQL Stored Procedure"，Executable File Name為儲存過程名稱"test_denilsen_util_p"
5. click concurrent -> Program
6. 建立Program,命名為"Denilsen_invoice"，其中Executable Name為第4步建立的Executable：Denilsen_Invoice。設定引數"路徑"和"檔名"的屬性
7. Switch Responsibility,Login as System Administrator
8. Click Security -> Responsibility -> Define
9. 找到Responsibility:denilsen_test，將其Request Group設為"OM Concurrent Program"
10. Click Security -> Responsibility -> Request
11. 找到group: "OM Concurrent Program"，在Request裡新增Type: "Program Name","Denilsen_Invoice"
12. Switch Responsibility, Login as "Order Management Super User,Vision Operation(USA)"
13. Click Reports,Requests -> Run Requests
14. Select Single Reuqest, then click "OK"
15. 選擇Denilsen_Invoice，給引數路徑和檔名賦值，Then click "Submit"
16. Click menu item View -> Request, select "Find all requests"
17. Telnet到伺服器上，檢視指定目錄下檔案是否已經生成
******END******