Skip to Content

Hi All

 

I was recently asked if there is a way to extract the list of all calculated columns in a view or package along with the data type of the calculated column and the logic used in the calculated column ..

Unfortunately there seem to be no easy way to extract the list of calculated columns from the view ..The only option is to parse the XML of the view and extract the calculated columns …

 

The entire code is broken down into 2 steps

  1. Procedure to Parse the XML of the views
  2. Read the Parsed XML and extract the calculated columns

 

  1. Procedure to Parse the XML

Create 2 procedures – Change the schema as needed

create PROCEDURE "VGOLI"."p_decode_xml_entities" ( 
  IN p_encoded_text nclob  -- text containing xml entities
, OUT p_decoded_text nclob -- text but with xml entities replaced by text 
) 
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
READS SQL DATA
as 
BEGIN
  declare i integer default 1;
  declare n integer default length(p_encoded_text);
  declare v_from_position integer;
  declare v_to_position integer;
  declare v_encoded_text nclob default p_encoded_text;
  declare v_text nclob default '';
  declare v_token nvarchar(12);
  
  v_encoded_text = 
    replace(
      replace(
        replace(
          replace(v_encoded_text, 
            '>', '>')
          , '&lt;', '<')
          , '&quot;', '"')
          , '&apos;', '''')
  ;
  
  while i <= n do
    select  locate_regexpr('&(#(\d+|[xX]?[\dA-Za-z]+));' in v_encoded_text from i)
    into    v_from_position
    from    dummy
    ;
    if v_from_position = 0 then
      v_text = v_text || substr(v_encoded_text, i);
      i = n + 1;
    else
      v_text = v_text || substr(v_encoded_text, i, v_from_position - i);
      v_to_position = locate(v_encoded_text, ';', i);
      v_token = substr(v_encoded_text, v_from_position + 1, v_to_position - v_from_position - 1);
      if substr(v_token, 1, 1) = '#' then
        if substr(v_token, 2, 1) = 'x' then 
          v_text = v_text || bintostr(hextobin(substr(v_token, 3)));
        else
          v_text = v_text || nchar(cast(substr(v_token, 2) as integer));
        end if; 
      elseif v_token = 'amp' then
        v_text = v_text || '&';
      elseif v_token = 'apos' then
        v_text = v_text || '''';
      elseif v_token = 'lt' then
        v_text = v_text || '<';
      elseif v_token = 'gt' then
        v_text = v_text || '>';
      elseif v_token = 'quot' then
        v_text = v_text || '"';
      else
        signal sql_error_code 10000
        set message_text = 'Unrecognized entity '||v_token;
      end if;
      i = v_to_position + 1;
    end if
    ; 
  end while
  ;
  v_text = replace(v_text, '&amp;', '&');
  p_decoded_text = v_text;
END;
create PROCEDURE p_parse_xml (
  p_xml nclob
, out p_dom table (
    node_id           int           -- unique id of the node
  , parent_node_id    int           -- id of the parent node
  , node_type         tinyint       -- dom node type constant: 1=element, 2=attribute, 3=text, 4=cdata, 5=entityref, 6=entity, 7=processing instruction, 8=comment, 9=document, 10=document type, 11=document fragment, 12=notation
  , node_name         nvarchar(64)  -- dom node name: tagname for element, attribute name for attribute, target for processing instruction, document type name for document type, "#text" for text and cdata, "#comment" for comment, "#document" for document, "#document-fragment" for document fragment. 
  , node_value        nclob         -- dom node value: text for text, comment, and cdata nodes, data for processing instruction node, null otherwise.
  , pos               int           -- character position of token
  , len               int           -- lenght of token.
  )
, out p_error table (
    error_code        int
  , error_message     nvarchar(255)
  , position          int
  , node_name         nvarchar(64)
  )
, p_strip_empty_text  tinyint default 1
) 
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER 
reads sql data
AS
BEGIN
  -- default regexp flag: s: . includes newline; m: ^ and $ match start/end of input (not of line)
  declare RX_FLAG     CONSTANT nchar(2)       default 'sm';
  -- less than
  declare RX_LT       CONSTANT nchar(1)       default '<';
  declare RX_LT_LEN   CONSTANT tinyint        default length(RX_LT);
  -- greater than
  declare RX_GT       CONSTANT nchar(1)       default '>';
  -- single quote    
  declare RX_APOS     CONSTANT nchar(8)       default '''';
  -- double quote    
  declare RX_QUOT     CONSTANT nchar(8)       default '"';
  -- name start char
  declare RX_NSCHAR   CONSTANT nchar(27)      default ':_A-Za-z\xC0-\xD6\xD8-\xF6';
  -- name char
  declare RX_NCHAR    CONSTANT nchar(38)      default '\-\.'||RX_NSCHAR||'0-9\xB7';
  -- name: one or more word characters. (TODO: xml probably allows more chars than \w)
  declare RX_NAME     CONSTANT nchar(70)      default '['||RX_NSCHAR||']['||RX_NCHAR||']*';
  -- qualified name: name with optional prefix
  declare RX_QNAME    CONSTANT nchar(146)     default '(('||RX_NAME||':)?'||RX_NAME||')';
  -- whitespace
  declare RX_WS       CONSTANT nchar(3)       default '\s+';
  -- optional whitespace
  declare RX_OPTWS    CONSTANT nchar(3)       default '\s*';
  -- single quoted string: single quote, followed by anything byt a single quote or a left angle parenthesis, followed by single quote
  declare RX_SQSTR    CONSTANT nchar(8)       default RX_APOS||'[^'||RX_APOS||RX_LT||']*'||RX_APOS;
  -- double quoted string: double quote, followed by anything but a double quote or a left angle parenthesis, followed by double quote
  declare RX_DQSTR    CONSTANT nchar(8)       default RX_QUOT||'[^'||RX_QUOT||RX_LT||']*'||RX_QUOT;
  -- quoted string: either a double or a single quoted string
  declare RX_QSTR     CONSTANT nchar(19)      default '('||RX_SQSTR||'|'||RX_DQSTR||')';
  -- attribute: whitespace, qname, optional whitespace, equals sign, optional whitespace, quoted string.
  declare RX_ATT      CONSTANT nchar(178)      default '('||RX_WS||RX_QNAME||RX_OPTWS||'='||RX_OPTWS||RX_QSTR||')';
  -- literal question mark (used in pi)
  declare RX_Q        CONSTANT nchar(2)       default '\?';
  -- start pi
  declare RX_SPI      CONSTANT nchar(3)       default RX_LT||RX_Q;
  -- end pi
  declare RX_EPI      CONSTANT nchar(3)       default RX_Q||RX_GT;
  -- processing instruction: name, mandatory whitespace, followed by anything that is not a end pi delimiter
  declare RX_PI       CONSTANT nchar(193)      default RX_SPI||'('||RX_NAME||')('||RX_WS||'.*(?<!'||RX_EPI||'))'||RX_EPI;
  -- dash
  declare RX_DASH     CONSTANT nchar(1)       default '-';
  -- dashdash
  declare RX_DASHDASH CONSTANT nchar(2)       default RX_DASH||RX_DASH;
  -- start comment
  declare RX_SCOMM    CONSTANT nchar(4)       default RX_LT||'!'||RX_DASHDASH;
  declare RX_SCOMM_LEN CONSTANT tinyint       default length(RX_SCOMM);
  -- end comment
  declare RX_ECOMM    CONSTANT nchar(3)       default RX_DASHDASH||RX_GT;
  declare RX_ECOMM_LEN CONSTANT tinyint       default length(RX_ECOMM);
  -- no dash
  declare RX_NODASH   CONSTANT nchar(4)       default '[^-]';
  -- comment: 
  declare RX_COMMENT  CONSTANT nchar(20)      default RX_SCOMM||'('||RX_NODASH||'|'||RX_DASH||RX_NODASH||')*'||RX_ECOMM;
  -- start cdata:
  declare RX_SCDATA   CONSTANT nchar(11)      default RX_LT||'!\[CDATA\[';
  declare RX_SCDATA_LEN CONSTANT tinyint      default length(RX_SCDATA);
  -- end cdata:
  declare RX_ECDATA   CONSTANT nchar(5)       default '\]\]'||RX_GT;
  declare RX_ECDATA_LEN CONSTANT tinyint      default length(RX_ECDATA);
  -- cdata
  declare RX_CDATA    CONSTANT nchar(28)      default RX_SCDATA||'.*(?<!'||RX_ECDATA||')'||RX_ECDATA;
  -- external id
  declare RX_EXTID    CONSTANT nchar(61)      default '((SYSTEM|PUBLIC'||RX_WS||RX_QSTR||')'||RX_WS||RX_QSTR||')';
  -- doctype start
  declare RX_SDOCTYPE CONSTANT nchar(9)       default RX_LT||'!DOCTYPE';
  -- doctype
  declare RX_DOCTYPE  CONSTANT nchar(155)     default RX_SDOCTYPE||RX_WS||'('||RX_NAME||')('||RX_WS||RX_EXTID||')?'||RX_OPTWS||RX_GT;
  declare RX_DOCTYPE_LEN CONSTANT tinyint     default length(RX_SDOCTYPE);
  -- opening tag
  declare RX_STAG     CONSTANT nchar(334)     default RX_LT||RX_QNAME||'('||RX_ATT||'*)'||RX_OPTWS||'/?'||RX_GT;
  -- opening tag
  declare RX_CTAG     CONSTANT nchar(149)     default RX_LT||'/'||RX_QNAME||RX_GT;
  -- text: any content between > and <
  declare RX_TEXT     CONSTANT nchar(16)      default '(?<='||RX_GT||')[^'||RX_LT||']+(?='||RX_LT||')';
  --
  declare REGXP       CONSTANT nchar(901)     default RX_PI
                                               ||'|'||RX_COMMENT
                                               ||'|'||RX_CDATA
                                               ||'|'||RX_DOCTYPE
                                               ||'|'||RX_STAG
                                               ||'|'||RX_CTAG
                                               ||'|'||RX_TEXT
  ;
  declare CLOSE_ELEMENT               CONSTANT tinyint default 0;  -- pseudo-nodetype used to signal closing element

  -- DOM node types.
  declare ELEMENT_NODE                CONSTANT tinyint default 1;  
  declare ATTRIBUTE_NODE              CONSTANT tinyint default 2;  
  declare TEXT_NODE                   CONSTANT tinyint default 3;  
  declare CDATA_SECTION_NODE          CONSTANT tinyint default 4;  
  declare ENTITY_REFERENCE_NODE       CONSTANT tinyint default 5;  
  declare ENTITY_NODE                 CONSTANT tinyint default 6;  
  declare PROCESSING_INSTRUCTION_NODE CONSTANT tinyint default 7;  
  declare COMMENT_NODE                CONSTANT tinyint default 8;  
  declare DOCUMENT_NODE               CONSTANT tinyint default 9;  
  declare DOCUMENT_TYPE_NODE          CONSTANT tinyint default 10; 
  declare DOCUMENT_FRAGMENT_NODE      CONSTANT tinyint default 11; 
  declare NOTATION_NODE               CONSTANT tinyint default 12; 

  declare v_node_id integer default 0;
  declare v_element_stack integer array;
  declare v_parent_node_id integer default 0;
  
  declare v_node_type tinyint;
  declare v_node_name nvarchar(64);
  declare v_node_value nclob;
  declare v_chars char(12);
  declare v_index integer default 1;
  declare v_length integer;
  declare v_end   integer default length(:p_xml);
  declare v_token nclob;

  declare v_atts  nclob;
  declare v_atts_index  integer;
  declare v_atts_length integer;
  declare v_atts_end    integer;
  declare v_att         nclob;
  declare v_att_name    nvarchar(64);
  declare v_att_value   nclob;

  declare v_row_num integer default 1;
  declare v_node_ids integer array;
  declare v_parent_node_ids integer array;
  declare v_node_types tinyint array;
  declare v_node_names nvarchar(64) array;
  declare v_node_values nclob array;
  declare v_positions integer array;
  declare v_lengths integer array;
  
  declare exit handler for sqlexception
    begin
      p_error = select 
        ::SQL_ERROR_CODE    error_code
      , ::SQL_ERROR_MESSAGE error_message
      , v_index             position
      , v_node_name         node_name
      from dummy;
      p_dom = unnest(
        :v_node_ids
      , :v_parent_node_ids
      , :v_node_types
      , :v_node_names
      , :v_node_values
      , :v_positions
      , :v_lengths
      ) as dom (
        node_id
      , parent_node_id
      , node_type
      , node_name
      , node_value
      , pos
      , len
      );
    end;
    
  v_element_stack[v_row_num] = v_node_id;
  
  v_node_ids[v_row_num] = v_node_id;
  v_parent_node_ids[v_row_num] = null;
  v_node_types[v_row_num] = DOCUMENT_NODE;
  v_node_names[v_row_num] = '#document';
  v_node_values[v_row_num] = null;
  v_positions[v_row_num] = 1;
  v_lengths[v_row_num] = v_end;
  
  while v_index < v_end do
  
    select  substr_regexpr(REGXP flag RX_FLAG in :p_xml from v_index)
    into    v_token
    from    dummy;
  
    v_length = length(v_token);
    v_node_id = v_node_id + 1;
    v_parent_node_id = :v_element_stack[CARDINALITY(:v_element_stack)];
    v_node_name = null;
    v_node_type = null;
    v_node_value = null;
    v_atts = null;
    
    if v_token is null then 
      -- check for whitespace trailing the document
      select substr_regexpr('\s+$' flag RX_FLAG in :p_xml from v_index)
      into v_token
      from dummy;
      if length(v_token) > 0 then
        v_index = v_index + length(v_token);
        v_node_type = 0;
      else
        signal sql_error_code 10000
          set message_text = 'No token found at '||cast(v_index as varchar(12));
      end if;
    elseif left(v_token, RX_LT_LEN) = RX_LT then
      if substr(v_token, 2, 1) = '?' then
        v_node_type = PROCESSING_INSTRUCTION_NODE;
        select  substr_regexpr(RX_PI flag RX_FLAG in v_token group 1)
        ,       substr_regexpr(RX_PI flag RX_FLAG in v_token group 2)
        into    v_node_name
        ,       v_node_value
        from    dummy;
        v_atts = v_node_value;
      elseif left(v_token, RX_SCOMM_LEN) = RX_SCOMM then
        v_node_type = COMMENT_NODE;
        v_node_name = '#comment';
        v_node_value = substr(v_token, RX_SCOMM_LEN + 1, v_length - RX_SCOMM_LEN - RX_ECOMM_LEN);
      elseif left(v_token, RX_SCDATA_LEN) = RX_SCDATA then
        v_node_type = CDATA_SECTION_NODE;
        v_node_name = '#text';
        v_node_value = substr(v_token, RX_SCDATA_LEN + 1, v_length - RX_SCDATA_LEN - RX_ECDATA_LEN);
      elseif left(v_token, RX_DOCTYPE_LEN) = RX_SDOCTYPE then
        v_node_type = DOCUMENT_TYPE_NODE;
        select  substr_regexpr(RX_DOCTYPE flag RX_FLAG in v_token group 1)
        into    v_node_name
        from    dummy;
      elseif substr(v_token, 2, 1) = '/' then
        v_node_type = CLOSE_ELEMENT;
        v_node_id = v_node_id - 1;
        v_element_stack = trim_array(:v_element_stack, 1);
      else
        v_node_type = ELEMENT_NODE;
        select  substr_regexpr(RX_STAG in v_token group 1)
        ,       substr_regexpr(RX_STAG in v_token group 3)
        into    v_node_name
        ,       v_atts
        from    dummy;
        v_chars = substr(v_token, v_length - 1, 1);
        if v_chars != '/' then 
           v_element_stack[CARDINALITY(:v_element_stack) + 1] = v_node_id;
        end if;
      end if;
    else
      v_node_type = TEXT_NODE;
      v_node_name = '#text';
      call "VGOLI"."p_decode_xml_entities"(
        v_token
      , v_node_value
      );
    end if;
    
    -- lose non-significant whitespace.
    if p_strip_empty_text != 0 and v_node_type = TEXT_NODE then
      select case count(*) when 1 then 0 else v_node_type end 
      ,      v_node_id - count(*)
      into   v_node_type, v_node_id
      from dummy 
      where replace_regexpr ('^\s+$' flag RX_FLAG in v_token with '') = '';
    end if;
    
    if v_node_type > 0 then
      v_row_num = v_row_num + 1;
      
      v_node_ids[v_row_num] = v_node_id;
      v_parent_node_ids[v_row_num] = v_parent_node_id;
      v_node_types[v_row_num] = v_node_type;
      v_node_names[v_row_num] = v_node_name;
      v_node_values[v_row_num] = v_node_value;
      v_positions[v_row_num] = v_index;
      v_lengths[v_row_num] = v_length;
      
      if not v_atts is null then
        v_parent_node_id = v_node_id;
        v_atts_index = 1;
        v_atts_end = length(v_atts);
        
        while v_atts_index < v_atts_end do
          select  substr_regexpr(RX_ATT flag RX_FLAG in v_atts from v_atts_index group 1)
          ,       substr_regexpr(RX_ATT flag RX_FLAG in v_atts from v_atts_index group 2)
          ,       substr_regexpr(RX_ATT flag RX_FLAG in v_atts from v_atts_index group 4)
          into    v_att, v_att_name, v_att_value
          from    dummy;
          v_atts_length = length(v_att);
       
          if v_att_name is null then
            signal sql_error_code 10000
            set message_text = 'No attribute found in '||v_atts||' at index '||cast(v_atts_index as varchar(12));
          else
            v_node_id = v_node_id + 1;

            call "VGOLI"."p_decode_xml_entities"(
              substr(v_att_value, 2, length(v_att_value) - 2)
            , v_att_value 
            );
            
            v_row_num = v_row_num + 1;
            
            v_node_ids[v_row_num] = v_node_id;
            v_parent_node_ids[v_row_num] = v_parent_node_id;
            v_node_types[v_row_num] = ATTRIBUTE_NODE;
            v_node_names[v_row_num] = v_att_name;
            v_node_values[v_row_num] = v_att_value;
            v_positions[v_row_num] = v_index + v_atts_index;
            v_lengths[v_row_num] = v_atts_length;
          end if;
          v_atts_index = v_atts_index + v_atts_length;
        end while;        
      end if;      
    end if;
    v_index = v_index + v_length;
  end while;
  
  p_dom = unnest(
    :v_node_ids
  , :v_parent_node_ids
  , :v_node_types
  , :v_node_names
  , :v_node_values
  , :v_positions
  , :v_lengths
  ) as dom (
    node_id
  , parent_node_id
  , node_type
  , node_name
  , node_value
  , pos
  , len
  );
  
END;

 

2)Read the Parsed XML and extract the calculated columns

 

do 
begin


  declare p_package_id      nvarchar(255)  default  'system-local.private.Temp.perf'; --'%';
-- p_object_name is the View Name -Pass Individual View Name or % for all the views with in the p_package_id
-- The SQL will consider the sub views used in the main view as well while collecting the calculated columns
--declare p_object_name     nvarchar(255)  default 'INPUT5'; --'%';
  declare p_object_name     nvarchar(255)  default '%';

 declare p_object_suffix   nvarchar(255)  default '%';
 declare p_recursive       tinyint        default 1;
 declare  p_cols table (
     schema_name nvarchar(128)
   , table_name  nvarchar(128)
   , column_name nvarchar(128)
   , views       nclob
  );



declare v_row_num            integer default 0;
declare v_package_ids        nvarchar(255) array;
declare v_object_names       nvarchar(255) array;
declare v_schema_names       nvarchar(128) array;
declare v_table_names        nvarchar(128) array;
declare v_column_names       nvarchar(128) array;
declare v_object_suffix      nvarchar(255);
declare v_object_viewname    nvarchar(255);
declare  dsnodeid            int           array;   
declare dsparentnodeid       int           array;  
declare dsnodetype           tinyint       array;
declare dsnodevalue          nclob         array;
declare dsnodename           nvarchar(64)  array;
declare dstypenodeid         int           array;   
declare dstypeparentnodeid   int           array;  
declare dstypenodetype       tinyint       array;
declare dstypenodevalue      nclob         array;
declare dstypenodename       nvarchar(64)  array;
declare viewname             nvarchar(255) array;
declare v_error_count        integer default 0;
declare tab_dom table(
    node_id           int           
  , parent_node_id    int           
  , node_type         tinyint       
  , node_name         nvarchar(64)  
  , node_value        nclob         
  , pos               int           
  , len               int           
  );
declare tab_error table (
    error_code        int
  , error_message     nvarchar(255)
  , position          int
  , node_name         nvarchar(64)
  );
declare cols table (
    package_id   nvarchar(255)
  , object_name  nvarchar(255)
  , schema_name  nvarchar(128)
  , table_name   nvarchar(128)
  , column_name  nvarchar(128)
  );
declare views table (
    viewname nvarchar(255)
    );
declare tab_dom_c table(
    dsnodeid             int           
  , dsparentnodeid       int           
  , dsnodetype           tinyint   
  , dsnodevalue          nclob    
  , dsnodename           nvarchar(64)  
  , dstypenodeid         int           
  , dstypeparentnodeid   int           
  , dstypenodetype       tinyint   
  , dstypenodevalue      nclob    
  , dstypenodename       nvarchar(64)
  ,viewname              nvarchar(255) 
    );
   
  
  
  declare cursor c_views for
    with params as (
      select  :p_package_id    as p_package_id
      ,       :p_object_name   as p_object_name
      ,       :p_object_suffix as p_object_suffix
      ,       :p_recursive     as p_recursive
      from    dummy
    )
    , top_level_views as (
      select     ao.package_id
      ,          ao.object_name
      ,          ao.object_suffix
      from       params p
      inner join  "_SYS_REPO"."ACTIVE_OBJECT" ao
      on          ao.package_id    like p.p_package_id
      and         ao.object_name   like p.p_object_name
      and         ao.object_suffix like p.p_object_suffix
      where       ao.object_suffix in (
                    'analyticview'
                  , 'attributeview'
                  , 'calculationview'
                  )
    )
    , top_level_and_base_views as (
      select      tv.package_id
      ,           tv.object_name
      ,           tv.object_suffix
      from        top_level_views tv
      union
      select      ao.package_id
      ,           ao.object_name
      ,           ao.object_suffix
      from        top_level_views     tv
      inner join  object_dependencies od
      on          tv.package_id||'/'||tv.object_name = od.dependent_object_name
      and         '_SYS_BIC'                         = od.dependent_schema_name
      and         'VIEW'                             = od.dependent_object_type
      and         '_SYS_BIC'                         = od.base_schema_name
      and         'VIEW'                             = od.base_object_type
      inner join  _SYS_REPO.ACTIVE_OBJECT ao
      on          substr_before(od.base_object_name, '/') = ao.package_id
      and         substr_after(od.base_object_name, '/') = ao.object_name
      and         ao.object_suffix in (
                    'analyticview'
                  , 'attributeview'
                  , 'calculationview'
                  )
      where       (
                    select p_recursive
                    from   params
                  ) != 0
      union 
      select      cr.to_package_id
      ,           cr.to_object_name
      ,           cr.to_object_suffix
      from        top_level_views                  tv
      inner join  _SYS_REPO.ACTIVE_OBJECTCROSSREF  cr
      on          tv.package_id      = cr.from_package_id
      and         tv.object_name     = cr.from_object_name
      and         tv.object_suffix   = cr.from_object_suffix
      and         cr.to_object_suffix in (
                    'analyticview'
                  , 'attributeview'
                  , 'calculationview'
                  )
      where       (
                    select p_recursive
                    from   params
                  ) != 0
    )
    select      v.package_id
    ,           v.object_name
    ,           v.object_suffix
    ,           v.cdata
    from        top_level_and_base_views tbv
    inner join  _SYS_REPO.ACTIVE_OBJECT  v
    on          tbv.package_id    = v.package_id
    and         tbv.object_name   = v.object_name
    and         tbv.object_suffix = v.object_suffix    
    ;


  for r_view as c_views do
    v_object_suffix = r_view.object_suffix;
    v_object_viewname = r_view.object_name;
    call p_parse_xml(r_view.cdata, tab_dom, tab_error);

    select count(*) 
    into   v_error_count 
    from :tab_error
    ;
    if v_error_count != 0 then
      select * from :tab_dom;
      select * from :tab_error;
      signal sql_error_code 10000 
        set message_text = 'Error parsing '
        ||r_view.object_suffix
        ||' '||r_view.object_name
        ||' in package '||r_view.package_id
        ||'.'
        ;
    end if;

     v_row_num = v_row_num + 1;
     v_package_ids[v_row_num] = :v_object_viewname;
    views = unnest(
    :v_package_ids
    ) as (
    viewname
  );

  begin
      declare cursor c_deps for 
        with calcview_base_table_datasource as (
        
       select    ds.node_id as dsnodeid, ds.parent_node_id as dsparentnodeid,ds.node_type as dsnodetype,ds.node_value as dsnodevalue,ds.node_name as dsnodename,
              ds_type.node_id as dstypenodeid, ds_type.parent_node_id as dstypeparentnodeid,ds_type.node_type as dstypenodetype,ds_type.node_value as dstypenodevalue,ds_type.node_name as dstypenodename,
           :v_object_viewname as ViewName    
          from       :tab_dom ds
          inner join :tab_dom ds_type
          on         ds.node_type = 1
          and        ds.node_name = 'calculatedViewAttribute'
          and        ds.node_id = ds_type.parent_node_id
          )

select   dsnodeid,dsparentnodeid,dsnodetype,dsnodevalue,dsnodename,dstypenodeid,dstypeparentnodeid,dstypenodetype,dstypenodevalue,dstypenodename,ViewName    
              from  calcview_base_table_datasource
  UNION ALL
select    ds_type.node_id as dstypenodeid, ds_type.parent_node_id as dstypeparentnodeid,ds_type.node_type as dstypenodetype,ds_type.node_value as dstypenodevalue,ds_type.node_name as dstypenodename, 
               ds.node_id as dsnodeid, ds.parent_node_id as dsparentnodeid,ds.node_type as dsnodetype,ds.node_value as dsnodevalue,ds.node_name as dsnodename,:v_object_viewname as ViewName    
          from       :tab_dom ds
          inner join :tab_dom ds_type
          on         ds.parent_node_id IN (select   ds_type.node_id 
          from       :tab_dom ds
          inner join :tab_dom ds_type
          on         ds.node_type = 1
          and        ds.node_name = 'calculatedViewAttribute'
          and        ds.node_id = ds_type.parent_node_id)
          AND        ds.node_type = 3
          and        ds.parent_node_id = ds_type.node_id;
          
     
 for r_deps as c_deps do 
   v_row_num = v_row_num + 1;
   dsnodeid[v_row_num] = r_deps.dsnodeid;
   dsparentnodeid[v_row_num] = r_deps.dsparentnodeid;
   dsnodetype[v_row_num] = r_deps.dsnodetype;
   dsnodevalue[v_row_num] = r_deps.dsnodevalue;
   dsnodename[v_row_num] = r_deps.dsnodename;
   dstypenodeid[v_row_num] = r_deps.dstypenodeid;
   dstypeparentnodeid[v_row_num] = r_deps.dstypeparentnodeid;
   dstypenodetype[v_row_num] = r_deps.dstypenodetype;
   dstypenodevalue[v_row_num] = r_deps.dstypenodevalue;
   dstypenodename[v_row_num] = r_deps.dstypenodename;
   viewname[v_row_num] = r_deps.viewname;
  end for;
  end;
 end for;
 
     
views = unnest(:v_package_ids) as (viewname);
--Display the List of Views/SubViews
select distinct(viewname) from :views where viewname is not null;
  
tab_dom_c = unnest(:dsnodeid,:dsparentnodeid,:dsnodetype,:dsnodevalue,:dsnodename,:dstypenodeid,:dstypeparentnodeid,:dstypenodetype,:dstypenodevalue,:dstypenodename,:viewname) as 
                  ( dsnodeid, dsparentnodeid, dsnodetype, dsnodevalue, dsnodename, dstypenodeid, dstypeparentnodeid, dstypenodetype, dstypenodevalue, dstypenodename, viewname);

--Display the List of Calculated Columns - Each Calculated column will be displayed in 4-5 rows 
  select * from :tab_dom_c where dsnodeid is not null and dsparentnodeid is not null and dstypenodetype is not null order by dsnodeid, dsparentnodeid,dstypenodetype  ;

--Formulate the above data with Each Corresponding to 1 Calculated Column  
  select id.dstypenodevalue as CalculatedColumn,text.dstypenodevalue as Syntax,datatype.dstypenodevalue as datatype,length.dstypenodevalue as length,id.viewname as View from 
               :tab_dom_c as id
   inner join  :tab_dom_c as text 
   on
   id.dstypeparentnodeid = text.dsparentnodeid and id.dstypenodename = 'id' and   text.dstypenodename ='#text' 
   inner join :tab_dom_c as datatype
   on datatype.dstypeparentnodeid =  id.dstypeparentnodeid and datatype.dstypenodename = 'datatype'
 left outer join :tab_dom_c as length
   on id.dstypeparentnodeid = length.dstypeparentnodeid and length.dstypenodename =  'length';
   
    end; 

 

Now Lets test

Test1) Pass a single View –  View  is located at  system-local.private.Temp.perf/INPUT5

INPUT5 View

The View INPUT5 Contains another view INPUTDATE1 from the same package

Calculated Columns in INPUT5

Calculated Columns in INPUTDATE1

 

Now lets the Query  2)Read the Parsed XML and extract the calculated columns

This part have 2 important parameters – I will  pass the  package name and view name as default values

declare p_package_id nvarchar(255) default ‘system-local.private.Temp.perf

declare p_object_name nvarchar(255) default ‘INPUT5’;

Now I  will  execute the 2 query with above parameters ( The PARSE XML Procedure is called in the second query )

 

Please note the query execution generated 3 result tables

 

Result Tab1:  Will give the list of views/sub views

 

Result Tab2:  Will give the list of Calculated Columns along with the corresponding view name – Each Calculated is divided in 3-4 rows

 

Result Tab3:  Will give the list of Calculated Columns 1 row per calculated column

 

 

 

Test2) Pass a package –   system-local.private.Temp.perf  

declare p_package_id nvarchar(255) default ‘system-local.private.Temp.perf’; –‘%’;

declare p_object_name nvarchar(255) default ‘%’;

This will consider all the views in the package

Query Results

 

List of calculated columns in the entire package

 

Please note there is still some room for performance improvement..I will update as soon as i make some progress

 

Also  special thanks to @Roland Bouman  for coming up the sql for parsing the xml

Thank you all. Please provide feedback

 

Thanks

Venkat

 

To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

Leave a Reply