ITPub博客

首页 > 数据库 > Oracle > ORACLE APP培训笔记(1) -- OutBound

ORACLE APP培训笔记(1) -- OutBound

原创 Oracle 作者:zhyuh 时间:2004-10-22 09:19:40 0 删除 编辑
用存储过程实现数据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******

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/207/viewspace-779595/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 一地桂花
请登录后发表评论 登录
全部评论
  • 博文量
    233
  • 访问量
    2012735