Сегодня хочу рассказать о том, как получить список информационных панелей Oracle BI из PL/SQL.
1) Обязательно будем учитывать, что для различных учетных записей могут быть доступны различные информационные панели.
Поэтому нам потребуется настроить имперсонацию - т.е. возможность "олицетворения" любых пользователей из-под некоторой системной учетной записи.
Нам это потребуется для того, чтобы при вызове pl/sql-функции, возвращающей список информационных панелей, не требовалось передавать пароль учетной записи, а только логин.
Итак, для начала следует в консоли EM настроить дополнительные разрешения для роли
BI Administrator
Сами разрешения:
oracle.bi.server.impersonateUser
oracle.bi.server.queryUserPopulation
Затем нам необходимо указать, что все пользовательские учетные записи позволяют себя олицетворять.
Так как, скорее всего, все интересующие нас пользователи будут входить в группу
BI Consumer, то
После произведенных манипуляций Oracle BI позволит нам войти в систему под учетной записью test, не указывая ее пароль.
http://localhost:7001/analytics/saw.dll?Dashboard&NQUser=weblogic&NQPassword=weblogic_1&Impersonate=test
2) Ну а дальше я приведу сам код PL/SQL пакета, реализующего логику получения списка информационных панелей, доступных конкретному пользователю.
Но сначала - SQL-типы, необходимые для вызова PL/SQL функции с pipelined-результатом из SQL.
create or replace type bi_dashboard as object ( dashboard_name VARCHAR2(150), dashboard_path VARCHAR2(512) ); create or replace type bi_dashboard_array as table of bi_dashboard;
CREATE OR REPLACE PACKAGE BI_UTILS IS
G_RET_STS_SUCCESS constant varchar2(1) := 'S';
G_RET_STS_ERROR constant varchar2(1) := 'E';
G_RET_STS_UNEXP_ERROR constant varchar2(1) := 'U';
FUNCTION GET_DASHBOARDS(P_USER_NAME IN VARCHAR2,
P_SHOW_HIDDEN IN VARCHAR DEFAULT 'F')
RETURN bi_dashboard_array
PIPELINED;
END BI_UTILS;
/
CREATE OR REPLACE PACKAGE BODY BI_UTILS IS
G_BI_ADMIN_USER varchar2(50) := 'weblogic';
G_BI_ADMIN_PASSWORD varchar2(50) := 'weblogic_1';
G_BI_BASE_URL varchar2(512) := 'http://localhost:7001';
G_BI_LOGIN_URL varchar2(512) := G_BI_BASE_URL ||
'/analytics/saw.dll?Dashboard&NQUser=#ADMINUSER#&NQPassword=#ADMINPASSWORD#&Impersonate=#IMPERSONATE#';
G_BI_LOGOFF_URL varchar2(512) := G_BI_BASE_URL ||
'/analytics/saw.dll?Logoff';
G_BI_SEARCH_URL varchar2(512) := G_BI_BASE_URL ||
'/analytics/saw.dll?CatalogTreeModel';
G_ItemAttributes_NORMAL integer := 0;
G_ItemAttributes_READ_ONLY integer := 1;
G_ItemAttributes_ARCHIVE integer := 2;
G_ItemAttributes_HIDDEN integer := 4;
G_ItemAttributes_SYSTEM integer := 8;
G_ItemAttributes_DO_NOT_INDEX integer := 16;
/*Procedure to print strings larger than 255 chr*/
procedure output(p_string in varchar2) is
l_string long default p_string;
l_str_length number;
l_loop_count number := 0;
begin
l_str_length := length(l_string);
while l_loop_count < l_str_length loop
dbms_output.put_line(substr(l_string, l_loop_count + 1, 255));
l_loop_count := l_loop_count + 255;
end loop;
end output;
/*Procedure to submit HTTP requests*/
procedure http_post(p_url_in in varchar2,
p_data_in in varchar2 default null,
p_data_type in varchar2 default 'text/html',
p_username_in in varchar2 default null,
p_password_in in varchar2 default null,
x_status_code out varchar2,
x_reason_phrase out varchar2,
x_response out clob) is
l_data_in varchar2(32767);
l_http_req utl_http.req;
l_http_resp utl_http.resp;
l_clob clob;
l_text varchar2(32767);
begin
DBMS_LOB.createtemporary(l_clob, FALSE);
utl_http.set_response_error_check(false);
l_http_req := utl_http.begin_request(p_url_in, 'POST', 'HTTP/1.1');
utl_http.set_header(l_http_req,
'User-Agent',
' Mozilla/5.0 (Windows NT 6.1; WOW64; rv:16.0) Gecko/20100101 Firefox/16.0');
utl_http.set_header(l_http_req, 'content-type', p_data_type);
l_data_in := convert(p_data_in, 'utf8');
utl_http.set_header(l_http_req, 'content-length', length(l_data_in));
if p_username_in is not null then
utl_http.set_authentication(l_http_req, p_username_in, p_password_in);
end if;
utl_http.set_body_charset(l_http_req, null);
utl_http.write_text(l_http_req, l_data_in);
l_http_resp := utl_http.get_response(l_http_req);
if (l_http_resp.status_code = utl_http.HTTP_OK) then
x_status_code := G_RET_STS_SUCCESS;
else
x_status_code := G_RET_STS_ERROR;
end if;
x_reason_phrase := l_http_resp.reason_phrase;
BEGIN
LOOP
UTL_HTTP.read_text(l_http_resp, l_text, 32766);
DBMS_LOB.writeappend(l_clob, LENGTH(l_text), l_text);
END LOOP;
EXCEPTION
WHEN utl_http.end_of_body THEN
utl_http.end_response(l_http_resp);
END;
x_response := l_clob;
DBMS_LOB.freetemporary(l_clob);
EXCEPTION
when others then
utl_http.end_response(l_http_resp);
DBMS_LOB.freetemporary(l_clob);
x_status_code := G_RET_STS_UNEXP_ERROR;
x_reason_phrase := sqlerrm;
END http_post;
/*Main function*/
FUNCTION GET_DASHBOARDS(P_USER_NAME IN VARCHAR2,
P_SHOW_HIDDEN IN VARCHAR DEFAULT 'F')
RETURN bi_dashboard_array
PIPELINED IS
l_status_code varchar2(1);
l_reason_phrase varchar2(1024);
e_expected_err exception;
l_url varchar2(1024);
l_post_data varchar2(1024);
l_receive_data clob;
l_db_details_json clob;
l_dummy integer;
l_scid varchar2(20);
l_db_details json;
l_itemsinfos_array json_list;
l_tmp_obj json;
l_tmp_obj_caption json_value;
l_tmp_obj_path json_value;
l_tmp_obj_attr json_value;
BEGIN
/*clear all cookies from previous runnings*/
utl_http.clear_cookies;
/*call login HTTP request*/
l_url := replace(G_BI_LOGIN_URL, '#ADMINUSER#', G_BI_ADMIN_USER);
l_url := replace(l_url, '#ADMINPASSWORD#', G_BI_ADMIN_PASSWORD);
l_url := replace(l_url, '#IMPERSONATE#', P_USER_NAME);
http_post(p_url_in => l_url,
x_status_code => l_status_code,
x_reason_phrase => l_reason_phrase,
x_response => l_receive_data);
if (l_status_code != G_RET_STS_SUCCESS) then
output('LOGIN: status_code = ' || l_status_code);
output('LOGIN: reason_phrase = ' || l_reason_phrase);
raise e_expected_err;
end if;
l_dummy := instr(l_receive_data, 'var obips_scid="') + 16;
l_scid := substr(l_receive_data, l_dummy, 20);
/*call dashboards search HTTP request*/
l_url := G_BI_SEARCH_URL;
l_post_data := 'action=search' || --
'&path=%2F' || --
'&mask=*' || --
'&recurse=t' || --
'&followLinks=f' || --
'&withperm=1' || --
'&withpermmask=1' || --
'&sig=' || --
'&compositeSig=dashboardfolder1' || --
'&icharset=utf-8' || --
'&_scid=' || l_scid || --
'&urlGenerator=qualified';
http_post(p_url_in => l_url,
p_data_in => l_post_data,
p_data_type => 'application/x-www-form-urlencoded',
x_status_code => l_status_code,
x_reason_phrase => l_reason_phrase,
x_response => l_receive_data);
if (l_status_code != G_RET_STS_SUCCESS) then
output('SEARCH: status_code = ' || l_status_code);
output('SEARCH: reason_phrase = ' || l_reason_phrase);
raise e_expected_err;
end if;
--output(l_receive_data);
l_dummy := instr(l_receive_data, '{"itemsinfos"');
l_db_details_json := substr(l_receive_data, l_dummy);
/*call logoff HTTP request*/
l_url := G_BI_LOGOFF_URL;
http_post(p_url_in => l_url,
x_status_code => l_status_code,
x_reason_phrase => l_reason_phrase,
x_response => l_receive_data);
/*parse received JSON data with dashboards details*/
l_db_details := json(l_db_details_json);
l_itemsinfos_array := json_list(l_db_details.get('itemsinfos'));
for i in 1 .. l_itemsinfos_array.count loop
l_tmp_obj := json(l_itemsinfos_array.get(i));
l_tmp_obj_caption := l_tmp_obj.get('caption');
l_tmp_obj_path := l_tmp_obj.get('path');
l_tmp_obj_attr := l_tmp_obj.get('attributes');
if (l_tmp_obj_attr.get_number = G_ItemAttributes_HIDDEN and
upper(P_SHOW_HIDDEN) != 'T') then
null;
else
PIPE ROW(bi_dashboard(l_tmp_obj_caption.get_string,
l_tmp_obj_path.get_string));
end if;
end loop;
return;
EXCEPTION
when e_expected_err then
null; --TODO
END GET_DASHBOARDS;
END BI_UTILS;
/
Как вы, наверняка, заметили - в пакете используется парсинг JSON.
Это пакет PL/JSON, который очень просто скачать и установить в вашей рабочей схеме БД.
И, конечно же, не забудьте привести в соответствие со своей средой глобальные переменные пакета
G_BI_ADMIN_USER - логи административной учетной записи (из-под которой будет выполняться имперсонация).
G_BI_ADMIN_PASSWORD - пароль административной учетной записи.
G_BI_BASE_URL - URL доступа к рабочему серверу BI.
3) Если расширение PL/JSON вы установили, скомпилировали без ошибок пакет BI_UTILS, то теперь простым SQL-запросом вы можете получать список инфопанелей, доступных конкретной учетной записи test.
select dashboard_name, dashboard_path
from table(bi_utils.get_dashboards('test'))
P.S.
Полагаю, что при необходимости можно усложнить приведенный тут код пакета, чтобы:
- кешировать результаты HTTP-запроса в таблице БД;
- в зависимости от параметра (P_FORCE) обращаться к кешу, при его наличии, либо принудительно обновлять кеш;
- добавить аналогичную функцию для получения списка страниц конкретной информационной панели;
- помимо "caption" и "path" можно использовать и ряд других свойств информационных панелей;
- можно добавить возможность поиска информационных панелей лишь внутри конкретной папки (например, "/shared" )
Этот комментарий был удален автором.
ОтветитьУдалить