欢迎光临
一个废柴的博客

如何通过Oracle E-BUSINESS SUITE发布Web Service

需求:将PL/SQL的程序包通过Oracle E-BUSINESS SUITE(EBS)发布为WEB SERVICE(SOAP),然后通过axis2客户端调用WEB SERVICE(SOAP),从而执行相应的存储过程。

具体通过Oracle E-BUSINESS SUITE发布Web Service的步骤如下:

1.创建 PRODUCT FAMILY(cux_pf为例)(如果之前环境中已经部署过可以不进行此操作)

begin

-- Call the procedure

ad_pa_insert_package.insert_ad_pm_product_info(x_product_abbreviation => 'cux_pf',

x_pseudo_product_flag => 'N',

x_product_family_flag => 'Y',

x_application_short_name => NULL,

x_product_name => 'CUX Developer',

x_product_family_abbreviation => NULL,

x_product_family_name => NULL,

x_aru_update_date => to_char(SYSDATE,'YYYY-MM-DD HH24:MI:SS'),

x_currdate => to_char(SYSDATE,'YYYY-MM-DD HH24:MI:SS'),

x_last_updated_by => -1,

x_created_by => -1);

COMMIT;

end;

2.注册PRODUCT (如果之前环境中已经部署过可以不进行此操作)

--注册CUX应用到产品家族中

begin

-- Call the procedure

ad_pa_insert_package.insert_ad_pm_product_info(x_product_abbreviation => 'cux',

x_pseudo_product_flag => 'N',

x_product_family_flag => 'N',

x_application_short_name => 'CUX',

x_product_name => 'CUX Developer',

x_product_family_abbreviation => NULL,

x_product_family_name => NULL,

x_aru_update_date => to_char(SYSDATE,'YYYY-MM-DD HH24:MI:SS'),

x_currdate => to_char(SYSDATE,'YYYY-MM-DD HH24:MI:SS'),

x_last_updated_by => -1,

x_created_by => -1);

COMMIT;

end;

3.关联CUX Developer应用到产品家族CUX Developer下 (如果之前环境中已经部署过可以不进行此操作)

--关联CUX应用到产品家族CUX Developer下

begin

-- Call the procedure

ad_pa_insert_package.insert_ad_pm_prod_family_map(x_product_abbreviation => 'cux',

x_product_family_abbreviation => 'cux_pf',

x_aru_update_date => to_char(SYSDATE,'YYYY-MM-DD HH24:MI:SS'),

x_currdate => to_char(SYSDATE,'YYYY-MM-DD HH24:MI:SS'),

x_last_updated_by => -1,

x_created_by => -1);

COMMIT;

end;

执行完成后通过下面2条SQL验证是否添加成功,查出3条数据表示添加成功。

SELECT * FROM ad_pm_product_info x WHERE x.product_name LIKE 'CUX%';

SELECT * FROM ad_pm_prod_family_map m WHERE m.product_abbreviation LIKE 'cux%';

4.增加一个BUSINESS_ENTITY的lookup_code(如果你想要在原来建好的lookup_code下发布服务,则这一步骤也不是必须的)

这里新建的代码为SIX_WS_CLIENT,这个代码会在下面发布服务的程序里的注释中( @rep:category BUSINESS_ENTITY SIX_WS_CLIENT)用到,具体增加lookup_code的路径如下图(应用开发员职责->应用产品->代码->Oracle Application Object Library):

5.发布服务

1)根据规范编写PL/SQL程序包的包头代码:

CREATE OR REPLACE PACKAGE HDPI_FDL_XJ_T_PKG AS
  /* $Header: $ */
  /*#
  * Mobile WS Client For GONGHAO FDL STORAGE REPORT
  * @rep:scope public
  * @rep:product CUX
  * @rep:lifecycle active
  * @rep:displayname HDPI_FDL_XJ_T_PKG 
  * @rep:compatibility S
  * @rep:category BUSINESS_ENTITY SIX_WS_CLIENT
  * @rep:ihelp FND/@o_funcsec#o_funcsec See the related online help
  */

  /*#
  * 查询各个电厂当日发电量
  * @param P_DATE_STR    String1
  * @param R_FDL_DR_COLUMN    TYPE_FDL_DR_COLUMN
  * @rep:scope public
  * @rep:lifecycle active
  * @rep:displayname QUERY_FDL_COLUMN
  * @rep:compatibility S
  * @rep:ihelp FND/@mesgdict#mesgdict See the related online help
  */
  TYPE RECORD_FDL_DR_OBJ IS RECORD(
    FDL_DR        NUMBER,
    REGION_ID     NUMBER,
    ENTITY_ORG_ID NUMBER,
    CMIS_COMPANY_NAME VARCHAR2(250),
    REGION_NAME       VARCHAR2(250));
  TYPE TYPE_FDL_DR_COLUMN IS TABLE OF RECORD_FDL_DR_OBJ INDEX BY BINARY_INTEGER;
  PROCEDURE QUERY_FDL_COLUMN(P_DATE_STR      IN VARCHAR2,
                             R_FDL_DR_COLUMN OUT TYPE_FDL_DR_COLUMN);

  /*#
  * 查询当日发电量小计.
  * @param P_DATE_STR    String1
  * @param R_FDL_DR_XJ_COLUMN    TYPE_FDL_DR_XJ_COLUMN
  * @rep:scope public
  * @rep:lifecycle active
  * @rep:displayname QUERY_FDL_COLUMN
  * @rep:compatibility S
  * @rep:ihelp FND/@mesgdict#mesgdict See the related online help
  */
  TYPE RECORD_FDL_DR_XJ_OBJ IS RECORD(
    FDL_DR_XJ        NUMBER,
    REGION_ID     NUMBER,
    REGION_NAME       VARCHAR2(250));
  TYPE TYPE_FDL_DR_XJ_COLUMN IS TABLE OF RECORD_FDL_DR_XJ_OBJ INDEX BY BINARY_INTEGER;
  PROCEDURE QUERY_FDL_XJ_COLUMN(P_DATE_STR      IN VARCHAR2,
                             R_FDL_DR_XJ_COLUMN OUT TYPE_FDL_DR_XJ_COLUMN);

  /*#
  * 查询报表结果集,返回所有需要的数据
  * @param P_DATE_STR    String1
  * @param R_FDL_DR_COLUMN    TYPE_FDL_DR_COLUMN
  * @param R_FDL_DR_XJ_COLUMN    TYPE_FDL_DR_XJ_COLUMN
  * @rep:scope public
  * @rep:lifecycle active
  * @rep:displayname QUERY_GH_REPORT
  * @rep:compatibility S
  * @rep:ihelp FND/@mesgdict#mesgdict See the related online help
  */
  PROCEDURE QUERY_GH_REPORT(P_DATE_STR         IN VARCHAR2,
                            R_FDL_DR_COLUMN    OUT TYPE_FDL_DR_COLUMN,
                            R_FDL_DR_XJ_COLUMN OUT TYPE_FDL_DR_XJ_COLUMN);
end HDPI_FDL_XJ_T_PKG;

这个代码中:

  • @rep:product CUX表示发布的服务属于这个产品;
  • @rep:displayname HDPI_FDL_XJ_T_PKG表示服务显示为这个名称;
  • @rep:category BUSINESS_ENTITY SIX_WS_CLIENT表示服务的目录结构,这里服务表示发布在SIX_WS_CLIENT(测试6)下面;

程序包的包体代码如下:

CREATE OR REPLACE PACKAGE BODY HDPI_GH_REPORT_PKG AS
  PROCEDURE QUERY_FDL_COLUMN(P_DATE_STR      IN VARCHAR2,
                             R_FDL_DR_COLUMN OUT TYPE_FDL_DR_COLUMN) AS
  BEGIN
    select ds2.FDL_DR, ds1.REGION_ID, ds1.ENTITY_ORG_ID,ds1.CMIS_COMPANY_NAME,ds1.REGION_NAME
      BULK COLLECT
      INTO R_FDL_DR_COLUMN
      FROM (SELECT e.entity_org_id,
                   e.entity_code,
                   e.CMIS_COMPANY_NAME,
                   e.entity_id,
                   r.region_cd,
                   decode(r.region_name, '宁夏', '宁夏省', r.region_name) region_name,
                   r.REGION_ID,
                   e.sort_id
              FROM datacenter.entity@connect_to_dtcenter e,
                   datacenter.region@connect_to_dtcenter r
             WHERE e.entity_type = '电厂'
               and e.level_three = '火电'
               and e.region_id = r.region_id
               and e.CMIS_COMPANY_NAME is not null
             order by case
                        when r.region_name like '山东%' then
                         0
                        when r.region_name like '宁夏%' then
                         1
                        when r.region_name like '安徽%' then
                         2
                        when r.region_name like '河南%' then
                         3
                        when r.region_name like '四川%' then
                         4
                        when r.region_name like '河北%' then
                         5
                        when r.region_name like '广东%' then
                         6
                        when r.region_name like '浙江%' then
                         7
                      end,
                      E.SORT_ID) ds1,
           (SELECT SUM(A.POWER_GEN) FDL_DR, B.ENTITY_ID
              FROM datacenter.GRIDPOWER_FCT@connect_to_dtcenter A,
                   datacenter.HDPI_SET@connect_to_dtcenter      B
             WHERE A.SET_ID = B.SET_ID
               AND A.CALENDAR_ID = P_DATE_STR
             GROUP BY B.ENTITY_ID) ds2
     WHERE ds1.ENTITY_ID = ds2.ENTITY_ID;
  END QUERY_FDL_COLUMN;
  PROCEDURE QUERY_FDL_XJ_COLUMN(P_DATE_STR         IN VARCHAR2,
                                R_FDL_DR_XJ_COLUMN OUT TYPE_FDL_DR_XJ_COLUMN) AS
  BEGIN
    SELECT ROUND(sum(ds2.FDL_DR),2) AS FDL_DR_XJ,ds1.REGION_ID,ds1.REGION_NAME
    BULK COLLECT
    INTO R_FDL_DR_XJ_COLUMN
    FROM (SELECT e.entity_org_id,
                 e.entity_code,
                 e.CMIS_COMPANY_NAME,
                 e.entity_id,
                 r.region_cd,
                 decode(r.region_name, '宁夏', '宁夏省', r.region_name) region_name,
                 r.REGION_ID,
                 e.sort_id
            FROM datacenter.entity@connect_to_dtcenter e,
                 datacenter.region@connect_to_dtcenter r
           WHERE e.entity_type = '电厂'
             and e.level_three = '火电'
             and e.region_id = r.region_id
             and e.CMIS_COMPANY_NAME is not null
           order by case
                      when r.region_name like '山东%' then
                       0
                      when r.region_name like '宁夏%' then
                       1
                      when r.region_name like '安徽%' then
                       2
                      when r.region_name like '河南%' then
                       3
                      when r.region_name like '四川%' then
                       4
                      when r.region_name like '河北%' then
                       5
                      when r.region_name like '广东%' then
                       6
                      when r.region_name like '浙江%' then
                       7
                    end,
                    E.SORT_ID) ds1,
         (SELECT SUM(A.POWER_GEN) FDL_DR, B.ENTITY_ID
            FROM datacenter.GRIDPOWER_FCT@connect_to_dtcenter A,
                 datacenter.HDPI_SET@connect_to_dtcenter      B
           WHERE A.SET_ID = B.SET_ID
             AND A.CALENDAR_ID = P_DATE_STR
           GROUP BY B.ENTITY_ID) ds2
   WHERE ds1.ENTITY_ID = ds2.ENTITY_ID
   group by ds1.REGION_ID,ds1.REGION_NAME;
  END QUERY_FDL_XJ_COLUMN;
  PROCEDURE QUERY_GH_REPORT(P_DATE_STR         IN VARCHAR2,
                            R_FDL_DR_COLUMN    OUT TYPE_FDL_DR_COLUMN,
                            R_FDL_DR_XJ_COLUMN OUT TYPE_FDL_DR_XJ_COLUMN) AS
  BEGIN
    QUERY_FDL_COLUMN(P_DATE_STR, R_FDL_DR_COLUMN);
    QUERY_FDL_XJ_COLUMN(P_DATE_STR, R_FDL_DR_XJ_COLUMN);
  END QUERY_GH_REPORT;
END;

在数据库中创建上面的程序包,创建成功之后,将包头的创建代码复制一份,另存为.pls格式文件,比如我这里叫HDPI_FDL_XJ_T_PKG.pls

2)将保存的.pls文件上传(我使用的是WinSCP)到$CUX_TOP/patch/115/sql目录下;

3)执行如下命令来生成.ildt文件:

$IAS_ORACLE_HOME/perl/bin/perl $FND_TOP/bin/irep_parser.pl -g -v -username=sysadmin cux:patch/115/sql:HDPI_FDL_XJ_T_PKG.pls:12.0=HDPI_FDL_XJ_T_PKG.pls

4)执行如下命令将上面生成的iLDT文件通过FNDLOAD命令工具上传到Oracle Integration Repositoy中:

FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/wfirep.lct HDPI_FDL_XJ_T_PKG_pls.ildt

5)接下来用管理员身份登录,到集成SOA网关->集成信息库->下的HDPI Custom Application Family->Custom Application->测试6中(或者直接在集成信息库中搜索发布的服务名)找到发布的服务名:

全选你发布的方法,点击创建授权,搜索你要授权的用户名,点击应用;


6)接下来生成wsdl文件;

7)生成wsdl文件之后,勾选“用户名变量”,进行部署;

8)接下来到你生成wsdl文件的目录下(我的是/sourcing/prod/oraapp/inst/apps/test_dev03/soa/PLSQL/4710)找到你程序包中的几个过程名的wsdl文件,删除文件中的下列行:

IRepOverloadSeq = 1

到此,服务发布结束,你可以用soapui测试一下发布的soap服务(不知为何我的授权要一个多小时才生效)。

参考文章:

  1. http://blog.csdn.net/wx110120121wx/article/details/64915967;
  2. https://jingyan.baidu.com/article/6079ad0e7bfde428ff86db90.html;
  3. http://www.cnblogs.com/xiyuanbaiyun/p/4277881.html;

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址