需求:将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服务(不知为何我的授权要一个多小时才生效)。
参考文章:
- http://blog.csdn.net/wx110120121wx/article/details/64915967;
- https://jingyan.baidu.com/article/6079ad0e7bfde428ff86db90.html;
- http://www.cnblogs.com/xiyuanbaiyun/p/4277881.html;