PL/SQL调用BIEE WebServices清理BI Server缓存

BIEE自身提供了几种缓存管理的方法，事件表或者是在物理层设置缓存的失效时间，又或者是调用SAPurgeAllCache()过程。

前两种方法都是被动的管理策略(事件表是定时轮询，失效时间是指定有效时长)，比较低效；个人更中意主动的缓存管理策略。即一旦数据发生变化就主动清理缓存。

最佳的作法就是在etl结束的时候通过调用API完成缓存的清理，要实现这种方式就需要调用BI Server提供的缓存管理存储过程，BI Server提供如下缓存管理过程：

SAPurgeCacheByQuery
SAPurgeCacheByTable
SAPurgeCacheByDatabase
SAPurgeAllCache

有关ODBC过程的介绍可以参阅Oracle BIEE BI Server ODBC 存储过程指南

每个过程的用途从名称都可以看出来，现在的问题来了，怎么在程序中调用这些过程呢？

有两种方法，一种是使用jdbc连接到bi server然后调用，另一种就是本文中要介绍的通过web services的方法。

这里要用到之前介绍的BI Server Metadata Web Services，有关BIEE Web Services的介绍请参阅BIEE 11g WebService指南

在使用BI Server Metadata Web Services之前我们需要先进行一些配置，步骤如下：

注：本文基于biee 11g  11.1.1.7

1、配置数据源

为了让web services能连接到bi server，我们需要先在console中配置数据源，登录weblogic console:

http://xxxxx:port/console 点击左侧的Services，然后在新出来的页面点击Data Sources ,然后点击“New”按钮(如果按钮不可用，请先点击左上角的Lock & Edit，)选择“Generic Data Source”，如下图所示：

打开Create a New JDBC Data Source页面，分别填写如下信息：

Name : BIServer (此名称可以自定义)

JNDI name: jdbc/bi/server

Database Type:Other　然后点击Next ，Database Driver也选择为Other，继续Next;在接下来的Transaction Options 页面选择

Supports Global Transactions 及t One-Phase Commit.　然后Next

在接下来的Connection Properties 页面填写相关信息：

Database Name：随便填写，比如abc

Host Name:BI server主机名(此处可以先填localhost占位，后面我们还更改)

Port:BI Server端口

Database User Name: weblogic

Password:weblogic的密码

Confirm Password:在次重复输入weblogic的密码

填完之后Next,在Test Database Connection页面填写如下信息

Driver Class Name:  oracle.bi.jdbc.AnaJdbcDriver

URL:  jdbc:oraclebi://Host Name:9703/

Properties: user=weblogic

Test Table Name:  SQL {call NQSGetSQLCatalogs()}

然后点击Test Configuration按钮，提示Connection test succeeded.　然后继续Next

在Select Targets 页面选择我们需要将数据源部署到哪个目标上，这里选择bi_cluster，因为web services的应用是部署在Managerd Server上的。

然后点击Finish。在接下来的Data Sources 页面点击刚才配置的BIServer数据，进入BIServer数据源的设置页面，切换到Connection Pool标签页，

将Statement Cache Size设置为０，然后点击下方的 Advanced 按钮展开高级设置，将Connection Creation Retry Frequency 设置为10，然后点击Save保存设置，

最后不要忘了点击左上角的Activate Changes激活更改。

2、配置Web Services安全策略

默认情况下，metadata services没有安全策略，谁都可以调用，当然这样不符合我们的要求，下面以添加oracle/wss_http_token_service_policy这种最基本的安全策略

为例讲解配置过程。这是一种通过HTTP传递用户名和密码完成认证的方式。

登录em，然后按下图所示操作

从Available Policies中选择oracle/wss_http_token_service_policy然后点击Attach按钮即可。

配置结束之后，我们需要测试一下Web Serives是否正常工作，这里我们采用SoapUI这款图形化软件来完成Web Services的测试。

3、利用SoapUI测试Web Services

从SoapUI官网下载开源免费版SoapUI OpenSource(https://www.soapui.org/downloads/soapui.html)并安装，然后打开程序File-New SOAP Project

填入WSDL ：http://xxxxxx:port/AdminService/AdminService?WSDL    注:port与analytics应用的端口相同

点击OK，出现如下界面，然后下图所示步骤操作

点击执行之后，出现下图未认证的提示

这是因为我们对web services配置了安全策略，所以我们必须提供用户名和密码，如下图所示：

点击OK，在次执行，成功！

至此，我们的web services就配置成功了，接下来是开始编写客户端调用代码了。客户端可以使用你喜欢的语言进行开发，比如Java。

本例以PL/SQL为例主要是因为我们的ETL大多采用PL/SQL编写。

３、编写PL/SQL代码调用Web Services

pl/sql调用web services有多种方法，比如UTL_DBWS以及APEX中提供的包，但这些方法都需要配置额外的包，还不如直接使用UTL_HTTP发送SOAP消息然后用XPATH解析来的简单直接，所以本例以UTL_HTTP包为例，讲解调用web services的方法。

由于本例使用的是oracle 11g R2数据库，在使用UTL_HTTP包访问外部网络资源时，需要配置network acl，不然无法访问网络资源。

具体配置方法，大家可以参见：https://oracle-base.com/articles/11g/fine-grained-access-to-network-services-11gr1

配置好ACL之后，就可以创建package了，下面直接给出代码

包头：

注意：大家需要根据自己的实际情况替换包头中的  g_admin_service_url变量值

create or replace package PF_BIEE_UTL AUTHID CURRENT_USER is  -- Author  : 风在身后  -- Created : 2015-10-30 下午 04:12:05  -- Purpose : 用于etl任务过后清理biee缓存  /***  ***/  g_admin_service_url varchar2(200):='http://xxxxx:8803/AdminService/AdminService';  procedure set_admin_service_url(p_url varchar2);  procedure purge_cache_by_tab(p_db_name varchar2, p_cat_name varchar2 default '',p_schema_name varchar2,p_tab_name varchar2);  procedure purge_cache_by_db(p_db_name varchar2);end PF_BIEE_UTL;

包体：

create or replace package body PF_BIEE_UTL is function send_soap_msg(p_url varchar2,v_soap_msg varchar2) return varchar2 is   v_resp_content varchar2(2048);   req     utl_http.req;   resp    utl_http.resp;   v_value VARCHAR2(32767); begin    --UTL_HTTP.SET_WALLET(path => 'file:/home/bitest/wallets/test_wallet');　　--这里可以使用wallet来管理用户名和密码     req := utl_http.begin_request(p_url, 'POST', utl_http.http_version_1_1);   utl_http.set_authentication(req, 'username',  'passwd');      UTL_HTTP.SET_AUTHENTICATION_FROM_WALLET(req, 'biee_adm_ws');     utl_http.set_header(req, 'Content-Type', 'text/xml; charset=utf-8');   utl_http.set_header(req, 'Content-Length', length(v_soap_msg));   utl_http.write_text(req, v_soap_msg);   ---获取响应   resp := utl_http.get_response(r => req);   IF (resp.status_code <> UTL_HTTP.http_ok) then     UTL_HTTP.end_response (resp);     raise_application_error(-20021,'BIEE Admin Service 连接失败,原因如下：'||resp.reason_phrase);   end if;   BEGIN     LOOP       utl_http.read_line(resp, v_value, TRUE);       v_resp_content := v_resp_content || v_value;     END LOOP;     utl_http.end_response(r => resp);   EXCEPTION     WHEN UTL_HTTP.END_OF_BODY THEN          utl_http.end_response(r => resp);     WHEN OTHERS THEN             raise;         END;   return v_resp_content; end;  procedure set_admin_service_url(p_url varchar2) as  begin    if g_admin_service_url is not null then    g_admin_service_url:=p_url;    else        raise_application_error(-20020,'BIEE Admin Service URL不能为空');    end if;    end;  procedure purge_cache_by_tab(p_db_name varchar2, p_cat_name varchar2 default '',p_schema_name varchar2,p_tab_name varchar2) as   --这里是请求的内容  v_content VARCHAR2(32767) := '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ws="http://ws.admin.obiee.oracle/">   <soapenv:Header/>   <soapenv:Body>      <ws:callProcedureWithResults>         <procedureName>SAPurgeCacheByTable(''#dbname#'',''#cat_name#'',''#sche_name#'',''#tab_name#'')</procedureName>      </ws:callProcedureWithResults>   </soapenv:Body></soapenv:Envelope> ';   v_xmltable xmltype;   v_resp_content varchar2(2048);   v_result varchar2(100); BEGIN   --utl_http.set_response_error_check ( enable => true );   -- utl_http.set_detailed_excp_support ( enable => true );   --dbms_output.put_line('STATUS CODE: ' || 'resp.status_code');   ---替换变量   v_content:=replace(v_content,'#dbname#',p_db_name);   v_content:=replace(v_content,'#cat_name#',p_cat_name);   v_content:=replace(v_content,'#sche_name#',p_schema_name);   v_content:=replace(v_content,'#tab_name#',p_tab_name);    dbms_output.put_line(v_content);   ---发起请求 ，并获取响应结果   v_resp_content:=send_soap_msg(g_admin_service_url,v_content);   ---解析结果   v_xmltable := xmltype(v_resp_content);   --这里解析返回的结果 如果有namespace需要在extract参数里声明。   v_result:=v_xmltable.extract('/env:Envelope/env:Body/ns0:callProcedureWithResultsResponse/return/rows/columns[2]/value/text()', 'xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns0="http://ws.admin.obiee.oracle/" xmlns:xsd="http://www.w3.org/2001/XMLSchema"')                        .getclobval();   if v_result <> '[59118] Operation SAPurgeCacheByTable succeeded!' then      raise_application_error(-20022,'缓存清理失败,原因如下：'||v_result);   end if; END;  procedure purge_cache_by_db(p_db_name varchar2) as   --这里是请求的内容  v_content VARCHAR2(32767) := '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ws="http://ws.admin.obiee.oracle/">   <soapenv:Header/>   <soapenv:Body>      <ws:callProcedureWithResults>         <procedureName>SAPurgeCacheByDatabase(''#dbname#'')</procedureName>      </ws:callProcedureWithResults>   </soapenv:Body></soapenv:Envelope> ';   v_xmltable xmltype;   v_resp_content varchar2(2048);   v_result varchar2(50); BEGIN   --utl_http.set_response_error_check ( enable => true );   -- utl_http.set_detailed_excp_support ( enable => true );   --dbms_output.put_line('STATUS CODE: ' || 'resp.status_code');   ---替换变量   v_content:=replace(v_content,'#dbname#',p_db_name);   ---发起请求 ，并获取响应结果   v_resp_content:=send_soap_msg(g_admin_service_url,v_content);   ---解析结果   v_xmltable := xmltype(v_resp_content);   --这里解析返回的结果 如果有namespace需要在extract参数里声明。   v_result:=v_xmltable.extract('/env:Envelope/env:Body/ns0:callProcedureWithResultsResponse/return/rows/columns[2]/value/text()', 'xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns0="http://ws.admin.obiee.oracle/" xmlns:xsd="http://www.w3.org/2001/XMLSchema"')                        .getclobval();   if v_result <> '[59118] Operation SAPurgeCacheByDatabase succeeded!' then      raise_application_error(-20022,'缓存清理失败,原因如下：'||v_result);   end if; END;end PF_BIEE_UTL;

代码中UTL_HTTP.SET_WALLET可以指定wallet来存取biee的用户名和密码，而无须在代码中明文写入密码，有关wallet的介绍请参考：Oracle Databasae wallet使用指南

另外，对于web services返回的xml格式的SOAP消息，需要利用oracle database的xml处理函数来处理，有关这部分的内容请大家自行百度。

4、调用方法

begin
pf.pf_biee_utl.purge_cache_by_tab('BIRPOD','','APPS','A');
end;

注意：参数1 为RPD中物理层 数据库名

参数2 为RPD中物理层 catalog名，对为Oracle数据库来说 可以忽略，为空就行

参数3 为RPD中物理层方案名

参数4 为RPD中物理层物理表名 (别名表只需要对源表调用即可，除非别名表的缓存属性覆盖了源表的）

如果缓存清理成功，则不会抛出任何异常，如果失败则会抛出异常，

所以大家在过程里调用的时候 需要进行异常处理！

５、结语

上述过程只是一个基本框架，还有很多和安全相关的设置需要大家自行考虑，比如使用什么biee帐号来调用这个web services？不建议直接使用weblogic帐号，因为这样风险太大，应该单独建一个帐号用于清理缓存，这个帐号不能用任何访问biee报表的权限。另外，在此基础上我们还可以进一步封装对外提供服务，比如BIE环境是有多个团队共用，我们肯定希望每个团队只能清理属于他们开发的那部分缓存，这个时候就需要把RPD中的元数据存到数据库里，并在程序逻辑中判断他们要清理的缓存是不是属于他们开发的。