Данный пост является логическим продолжением поста о получении списка информационных панелей PL/SQL-функцией.
Как оказалось, получение списка страниц информационной панели предыдущим способом НЕ ПОЗВОЛЯЕТ определить какие страницы являются скрытыми.
Поэтому пришлось доработать PL/SQL пакет...
1. Создадим SQL типы
create or replace type bi_dashboard_page as object ( page_name VARCHAR2(150) ); create or replace type bi_dashboard_page_array as table of bi_dashboard_page;
2. Создадим новую пакетную функцию GET_DASHBOARD_PAGES
CREATE OR REPLACE PACKAGE BI_UTILS IS
/* Constants */
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';
/* Record types */
TYPE T_ITEMINFO_REC is record(
CAPTION VARCHAR2(250),
PATH VARCHAR2(1024),
ATTRIBUTES INTEGER);
TYPE T_DB_PAGE_REC is record(
NAME VARCHAR2(250),
HIDDEN BOOLEAN);
/* Collection types */
TYPE T_ITEMINFO_TAB IS TABLE OF T_ITEMINFO_REC INDEX BY BINARY_INTEGER;
TYPE T_DB_PAGE_TAB IS TABLE OF T_DB_PAGE_REC INDEX BY BINARY_INTEGER;
/* GET_DASHBOARDS */
FUNCTION GET_DASHBOARDS(P_USER_NAME IN VARCHAR2,
P_SHOW_HIDDEN IN VARCHAR DEFAULT 'F')
RETURN bi_dashboard_array
PIPELINED;
/* GET_DASHBOARD_PAGES */
FUNCTION GET_DASHBOARD_PAGES(P_DASHBOARD_PATH IN VARCHAR2,
P_USER_NAME IN VARCHAR2,
P_SHOW_HIDDEN IN VARCHAR DEFAULT 'F')
RETURN bi_dashboard_page_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#';
G_BI_IMPERSONATE_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_BI_PORTAL_PROPERTIES_URL varchar2(512) := G_BI_BASE_URL ||
'/analytics/saw.dll?PortalProperties2';
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;
/* get_catalog_elements */
FUNCTION get_catalog_elements(P_USER_NAME IN VARCHAR2,
P_PATH IN VARCHAR2,
P_RECURSE IN VARCHAR2 DEFAULT 'F',
P_SUGNATURE IN VARCHAR2 DEFAULT NULL)
RETURN T_ITEMINFO_TAB IS
l_iteminfo_rec T_ITEMINFO_REC;
l_iteminfo_tab T_ITEMINFO_TAB;
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_IMPERSONATE_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);
--output(l_scid);
/*call dashboards search HTTP request*/
l_url := G_BI_SEARCH_URL;
l_post_data := 'action=search' || --
'&path=' || utl_url.escape(P_PATH, TRUE, 'UTF-8') || --
'&mask=*' || --
'&recurse=' || lower(P_RECURSE) || --
'&followLinks=f' || --
'&withperm=1' || --
'&withpermmask=1' || --
'&compositeSig=' || P_SUGNATURE || --
'&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');
l_iteminfo_rec.CAPTION := l_tmp_obj_caption.get_string;
l_iteminfo_rec.PATH := l_tmp_obj_path.get_string;
l_iteminfo_rec.ATTRIBUTES := l_tmp_obj_attr.get_number;
l_iteminfo_tab(i) := l_iteminfo_rec;
end loop;
return l_iteminfo_tab;
EXCEPTION
when e_expected_err then
null; --TODO
END get_catalog_elements;
/* get_catalog_elements */
FUNCTION get_db_pages_properties(P_DASHBOARD_PATH IN VARCHAR2)
RETURN T_DB_PAGE_TAB IS
l_db_page_rec T_DB_PAGE_REC;
l_db_page_tab T_DB_PAGE_TAB;
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_dbp_details_json clob;
l_dummy integer;
l_scid varchar2(20);
l_dbp_details json;
l_pages_array json_list;
l_tmp_obj json;
l_tmp_obj_name json_value;
l_tmp_obj_hidden 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);
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);
--output(l_scid);
/*call dashboard properties HTTP request*/
l_url := G_BI_PORTAL_PROPERTIES_URL;
l_post_data := 'PortalPath=' || P_DASHBOARD_PATH || --
'&icharset=utf-8' || --
'&_scid=' || l_scid;
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, '{"Styles"');
l_dbp_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 dashboard pages details*/
l_dbp_details := json(l_dbp_details_json);
l_pages_array := json_list(l_dbp_details.get('Pages'));
for i in 1 .. l_pages_array.count loop
l_tmp_obj := json(l_pages_array.get(i));
l_tmp_obj_name := l_tmp_obj.get('name');
l_tmp_obj_hidden := l_tmp_obj.get('hidden');
l_db_page_rec.NAME := l_tmp_obj_name.get_string;
l_db_page_rec.HIDDEN := l_tmp_obj_hidden.get_bool;
l_db_page_tab(i) := l_db_page_rec;
end loop;
return l_db_page_tab;
EXCEPTION
when e_expected_err then
null; --TODO
END get_db_pages_properties;
/* GET_DASHBOARDS */
FUNCTION GET_DASHBOARDS(P_USER_NAME IN VARCHAR2,
P_SHOW_HIDDEN IN VARCHAR DEFAULT 'F')
RETURN bi_dashboard_array
PIPELINED IS
l_iteminfo_tab T_ITEMINFO_TAB;
e_expected_err exception;
BEGIN
l_iteminfo_tab := get_catalog_elements(P_USER_NAME => P_USER_NAME,
P_PATH => '/',
P_RECURSE => 'T',
P_SUGNATURE => 'dashboardfolder1');
for i in 1 .. l_iteminfo_tab.count loop
if (l_iteminfo_tab(i)
.ATTRIBUTES < G_ItemAttributes_HIDDEN or P_SHOW_HIDDEN = 'T') then
PIPE ROW(bi_dashboard(l_iteminfo_tab(i).CAPTION,
l_iteminfo_tab(i).PATH));
end if;
end loop;
return;
EXCEPTION
when e_expected_err then
null; --TODO
END GET_DASHBOARDS;
/* GET_DASHBOARD_PAGES */
FUNCTION GET_DASHBOARD_PAGES(P_DASHBOARD_PATH IN VARCHAR2,
P_USER_NAME IN VARCHAR2,
P_SHOW_HIDDEN IN VARCHAR DEFAULT 'F')
RETURN bi_dashboard_page_array
PIPELINED IS
l_iteminfo_tab T_ITEMINFO_TAB;
l_db_page_tab T_DB_PAGE_TAB;
e_expected_err exception;
is_hidden boolean;
BEGIN
l_iteminfo_tab := get_catalog_elements(P_USER_NAME => P_USER_NAME,
P_PATH => P_DASHBOARD_PATH,
P_RECURSE => 'F',
P_SUGNATURE => null);
if (P_SHOW_HIDDEN = 'T' or l_iteminfo_tab.count = 0) then
for i in 1 .. l_iteminfo_tab.count loop
if (l_iteminfo_tab(i).ATTRIBUTES < G_ItemAttributes_HIDDEN) then
PIPE ROW(bi_dashboard_page(l_iteminfo_tab(i).CAPTION));
end if;
end loop;
else
l_db_page_tab := get_db_pages_properties(P_DASHBOARD_PATH => P_DASHBOARD_PATH);
for i in 1 .. l_iteminfo_tab.count loop
if (l_iteminfo_tab(i).ATTRIBUTES < G_ItemAttributes_HIDDEN) then
is_hidden := true;
for j in 1 .. l_db_page_tab.count loop
if (l_iteminfo_tab(i).CAPTION = l_db_page_tab(j).NAME) then
is_hidden := l_db_page_tab(j).HIDDEN;
end if;
end loop;
if not(is_hidden) then
PIPE ROW(bi_dashboard_page(l_iteminfo_tab(i).CAPTION));
end if;
end if;
end loop;
end if;
return;
EXCEPTION
when e_expected_err then
null; --TODO
END GET_DASHBOARD_PAGES;
END BI_UTILS;
/
3. Результат выполнения функции, параметрами которой являются путь до конкретной информационной панели и имя пользователя, под полномочиями которого хотим получить доступные страницы.
Внес изменения в код пакета и код создания типов.
ОтветитьУдалитьТеперь функция GET_DASHBOARD_PAGES позволяет фильтровать страницы по признаку "hidden" (который указан в свойствах информационной панели, а не является атрибутом страницы как объекта веб-каталога).
Подскажите, пожалуйста,как определить какой из анализов с именем "анализ" используется на инф.панели?
ОтветитьУдалить