logo

24 янв. 2013 г.

BIEE 11g: список страниц информационной панели (PL/SQL)

Привет всем!

Данный пост является логическим продолжением поста о получении списка информационных панелей 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. Результат выполнения функции, параметрами которой являются путь до конкретной информационной панели и имя пользователя, под полномочиями которого хотим получить доступные страницы.


1 комментарий:

  1. Внес изменения в код пакета и код создания типов.
    Теперь функция GET_DASHBOARD_PAGES позволяет фильтровать страницы по признаку "hidden" (который указан в свойствах информационной панели, а не является атрибутом страницы как объекта веб-каталога).

    ОтветитьУдалить