This discussion is archived
7 Replies Latest reply: Jul 9, 2013 4:34 AM by user10712277 RSS

Metadata information about views object missing (complex view column usage)

user10712277 Newbie
Currently Being Moderated

Hi,

I found this page (http://rwijk.blogspot.ca/2008/10/dbadependencycolumns.html) that shows on how to find columns used by any view (doesn’t work for view with inline function call). The page shows on how to create a custom view called "DBA_DEPENDENCIES_COLUMNS" which uses the column sys.dependency$.D_ATTRS in its logic.


There are some views that calls inline functions and those functions can call multiple other objects (table/views/function/procedure/...). How can we find all t columns used by a view of such (complex view)?


Does anyone have anything working solution or can point to me in the right direction to develop a logic that could work for us?


Thanks for reading and the attempt to help me,

Eric

  • 1. Re: Metadata information about views object missing (complex view column usage)
    user10712277 Newbie
    Currently Being Moderated

    I forgot to say this "I have logged an Oracle SR a little while back on the subject and they didn't help me much as of yet".

  • 2. Re: Metadata information about views object missing (complex view column usage)
    BillyVerreynne Oracle ACE
    Currently Being Moderated

    How about doing it at run-time as follows?

     

    SQL> create or replace type TCol3 is object(
      2     col_type               integer,
      3     col_max_len            integer,
      4     col_name               varchar2(4000),
      5     col_name_len           integer,
      6     col_schema_name        varchar2(32),
      7     col_schema_name_len    integer,
      8     col_precision          integer,
      9     col_scale              integer,
     10     col_charsetid          integer,
     11     col_charsetform        integer,
     12     col_null_ok            varchar2(1),
     13     col_type_name          varchar2(4000),
     14     col_type_name_len      integer
     15  );
     16  /
    Type created.
    SQL> 
    SQL> create or replace type TProjection as table of TCol3;
      2  /
    Type created.
    SQL>
    SQL> create or replace function DescribeSQLObject( object varchar2 ) return TProjection pipelined is
      2          c       integer;
      3          colCnt  integer;
      4          col     DBMS_SQL.DESC_TAB3;
      5  
      6          function BoolToChar( b boolean ) return varchar2 is
      7          begin
      8                  return(
      9                          case b when true then 'Y' when false then 'N' else null end
     10                  );
     11          end;
     12  begin
     13          c := DBMS_SQL.open_cursor;
     14          DBMS_SQL.parse(
     15                  c,
     16                  'select * from '||object,
     17                  DBMS_SQL.native
     18          );
     19          DBMS_SQL.describe_columns3( c, colCnt, col );
     20          DBMS_SQL.close_cursor( c );
     21  
     22          for i in 1..colCnt loop
     23                  pipe row(
     24                          TCol3(
     25                                  col(i).col_type,
     26                                  col(i).col_max_len,
     27                                  col(i).col_name,
     28                                  col(i).col_name_len,
     29                                  col(i).col_schema_name,
     30                                  col(i).col_schema_name_len,
     31                                  col(i).col_precision,
     32                                  col(i).col_scale,
     33                                  col(i).col_charsetid,
     34                                  col(i).col_charsetform,
     35                                  BoolToChar( col(i).col_null_ok ),
     36                                  col(i).col_type_name,
     37                                  col(i).col_type_name_len
     38                          )
     39                  );
     40          end loop;
     41  
     42          return;
     43  end;
     44  /
    Function created.
    SQL> 
    SQL> select col_name, col_name_len  from TABLE(DescribeSQLObject('DBA_OBJECTS'));
    COL_NAME                       COL_NAME_LEN
    ------------------------------ ------------
    OWNER                                     5
    OBJECT_NAME                              11
    SUBOBJECT_NAME                           14
    OBJECT_ID                                 9
    DATA_OBJECT_ID                           14
    OBJECT_TYPE                              11
    CREATED                                   7
    LAST_DDL_TIME                            13
    TIMESTAMP                                 9
    STATUS                                    6
    TEMPORARY                                 9
    GENERATED                                 9
    SECONDARY                                 9
    NAMESPACE                                 9
    EDITION_NAME                             12
    15 rows selected.
    SQL>
  • 3. Re: Metadata information about views object missing (complex view column usage)
    user10712277 Newbie
    Currently Being Moderated

    This logic displays the view columns but not the source columns that I want. Maybe I didn't explain it well in my initial post (my bad). Here's and example of what I am looking for:

     

    Example of view AA_CONSTITUENT:

    The view ODSSRC.AA_CONSTITUENT reference the function F_LIFE_GIFT_SOCIETY in the select statement  "AOKODSF.F_LIFE_GIFT_SOCIETY (APBCONS_PIDM, 'GIFT SOCIETY TYPE')".

     

    The function is the following (this is one of the simple version of a function, a function could call other functions or procedures):

    FUNCTION F_LIFE_TOTAL_COMMITMENT

       (PIDM NUMBER)

    RETURN NUMBER

    AS

    TOTAL_COM    NUMBER := NULL;

    CURSOR CHECK_SUM IS

       SELECT SUM(APRCHIS_AMT_PLEDGED) + SUM(APRCHIS_AMT_GIFT)

         FROM APRCHIS

           WHERE APRCHIS_PIDM = PIDM;

    BEGIN

    OPEN CHECK_SUM;

    FETCH CHECK_SUM INTO TOTAL_COM;

    CLOSE CHECK_SUM;

    RETURN TOTAL_COM;

    END F_LIFE_TOTAL_COMMITMENT;

     

    This function reference the table columns:

    APRCHIS.APRCHIS_AMT_PLEDGED

    APRCHIS.APRCHIS_AMT_GIFT

    APRCHIS.APRCHIS_PIDM

     

    All three columns are not used in the view AA_CONSTITUENT directly.

     

    I would like a query to return all the columns of the source tables of the view and all the column used in the function calls.

     

    Let me know if more info is needed,

    Eric

  • 4. Re: Metadata information about views object missing (complex view column usage)
    BillyVerreynne Oracle ACE
    Currently Being Moderated

    Do not see an easy way to do that type of thing, as SQLs can be very complex ito nesting and in-line SQL structures.

     

    One approach that can be considered is parsing it into a XML structure and then querying the structure.

     

    SQL> create or replace function XmlParse( dynamicSQL varchar2 ) return XmlType is
      2          c       clob;
      3          xml     XmlType;
      4  begin
      5          dbms_lob.CreateTemporary( c, true );
      6          sys.utl_xml.ParseQuery(
      7                  user,
      8                  dynamicSQL,
      9                  c
     10          );
     11  
     12          xml := new XmlType(c);
     13          dbms_lob.FreeTemporary(c);
     14          return( xml );
     15  end;
     16  /
    Function created.
    SQL> 
    SQL> col "SQL" format a50
    SQL> select XmlParse( 'select deptno, count(*), count(distinct empno), sum(sal) from emp group by deptno' ) as "SQL" from dual;
    SQL
    --------------------------------------------------
    <QUERY>
      <SELECT>
        <SELECT_LIST>
          <SELECT_LIST_ITEM>
            <COLUMN_REF>
              <TABLE>EMP</TABLE>
              <COLUMN>DEPTNO</COLUMN>
            </COLUMN_REF>
          </SELECT_LIST_ITEM>
          <SELECT_LIST_ITEM>
            <COUNT/>
          </SELECT_LIST_ITEM>
          <SELECT_LIST_ITEM>
            <COUNT>
              <DISTINCT>
                <COLUMN_REF>
                  <TABLE>EMP</TABLE>
                  <COLUMN>EMPNO</COLUMN>
                </COLUMN_REF>
              </DISTINCT>
            </COUNT>
          </SELECT_LIST_ITEM>
          <SELECT_LIST_ITEM>
            <SUM>
              <COLUMN_REF>
                <TABLE>EMP</TABLE>
                <COLUMN>SAL</COLUMN>
              </COLUMN_REF>
            </SUM>
          </SELECT_LIST_ITEM>
        </SELECT_LIST>
      </SELECT>
      <FROM>
        <FROM_ITEM>
          <TABLE>EMP</TABLE>
        </FROM_ITEM>
      </FROM>
      <GROUP_BY>
        <EXPRESSION_LIST>
          <EXPRESSION_LIST_ITEM>
            <COLUMN_REF>
              <TABLE>EMP</TABLE>
              <COLUMN>DEPTNO</COLUMN>
            </COLUMN_REF>
          </EXPRESSION_LIST_ITEM>
        </EXPRESSION_LIST>
      </GROUP_BY>
    </QUERY>
    SQL> 

     

    But I'm not so sure on how robust and comprehensive such an approach would be, or why one would want this type of metadata in the first place.

     

    Which is a point you have not mentioned. Why exactly the need for this type of metadata? What business/management problem will be addressed with having this data available?

  • 5. Re: Metadata information about views object missing (complex view column usage)
    user10712277 Newbie
    Currently Being Moderated

    Let me explain more.

     

     

    Our datawarhouse is using table triggers to store change records. Not all changes are important since we don't use all the table columns in our ETL processing.

    When some DML is performed against the table in question on columns which we don't care about, we would not like to capture those changes.

     

     

    To resolve this issue, we want to analyse the composite view (contains the first layer of ETL) dynamically to find all columns important for our datawarehouse and make the trigger smarter to only create the change record to the change table when it's absolutely needed.

    Currently, we have a lot of wasted time wasted in our nightly refresh.

  • 6. Re: Metadata information about views object missing (complex view column usage)
    BillyVerreynne Oracle ACE
    Currently Being Moderated

    Is this a once off exercise, or is the idea to have an automated s/w layer detecting column usage and configuring/creating capturing change records?

  • 7. Re: Metadata information about views object missing (complex view column usage)
    user10712277 Newbie
    Currently Being Moderated

    This is not just a one off exercise since we have many triggers that needs this logic (all of our ODS change triggers actually).

     

    SELECT COUNT(*) FROM DBA_TRIGGERS WHERE TRIGGER_NAME LIKE '%_INSERT_ODS_CHANGE'

    -- returns 424

     

    Here's an example of one trigger for the table APRCHIS

    CREATE OR REPLACE TRIGGER AT_APRCHIS_INSERT_ODS_CHANGE

    AFTER UPDATE OR INSERT OR DELETE

    ON APRCHIS

    FOR EACH ROW

    DECLARE

    PRAGMA AUTONOMOUS_TRANSACTION;

    BEGIN

    IF INSERTING THEN

            AOKODST.P_ODS_APRHCHG_CHG ('MAT_CAMPAIGN_GIVING_HISTORY',

                                       :NEW.APRCHIS_PIDM,

                                       :NEW.APRCHIS_CAMPAIGN,

                                       'I');

            AOKODST.P_ODS_AOROCHG_CHG ('MAT_ORGANIZATIONAL_CONSTITUENT',

                                       :NEW.APRCHIS_PIDM,

                                       'U');

            AOKODST.P_ODS_APRCCHG_CHG ('MAT_CONSTITUENT',

                                       :NEW.APRCHIS_PIDM,

                                       'U');

            AOKODST.P_ODS_AGRACHG_CHG ('MAT_ANNUAL_GIVING',

                                       :NEW.APRCHIS_PIDM,

                                       'U');

        ELSIF UPDATING THEN

            AOKODST.P_ODS_APRHCHG_CHG ('MAT_CAMPAIGN_GIVING_HISTORY',

                                       :OLD.APRCHIS_PIDM,

                                       :OLD.APRCHIS_CAMPAIGN,

                                       'U');

            AOKODST.P_ODS_AOROCHG_CHG ('MAT_ORGANIZATIONAL_CONSTITUENT',

                                       :OLD.APRCHIS_PIDM,

                                       'U');

            AOKODST.P_ODS_APRCCHG_CHG ('MAT_CONSTITUENT',

                                       :OLD.APRCHIS_PIDM,

                                       'U');

            AOKODST.P_ODS_AGRACHG_CHG ('MAT_ANNUAL_GIVING',

                                       :OLD.APRCHIS_PIDM,

                                       'U');

        -- Support for MView refresh UPDATE handling.

            IF (:OLD.APRCHIS_PIDM ^= :NEW.APRCHIS_PIDM OR

               :OLD.APRCHIS_CAMPAIGN ^= :NEW.APRCHIS_CAMPAIGN

            ) THEN

                AOKODST.P_ODS_APRHCHG_CHG ('MAT_CAMPAIGN_GIVING_HISTORY',

                                           :NEW.APRCHIS_PIDM,

                                           :NEW.APRCHIS_CAMPAIGN,

                                           'U');

                AOKODST.P_ODS_AOROCHG_CHG ('MAT_ORGANIZATIONAL_CONSTITUENT',

                                           :NEW.APRCHIS_PIDM,

                                           'U');

                AOKODST.P_ODS_APRCCHG_CHG ('MAT_CONSTITUENT',

                                           :NEW.APRCHIS_PIDM,

                                           'U');

                AOKODST.P_ODS_AGRACHG_CHG ('MAT_ANNUAL_GIVING',

                                           :NEW.APRCHIS_PIDM,

                                           'U');

            END IF;

        ELSE

            AOKODST.P_ODS_APRHCHG_CHG ('MAT_CAMPAIGN_GIVING_HISTORY',

                                       :OLD.APRCHIS_PIDM,

                                       :OLD.APRCHIS_CAMPAIGN,

                                       'D');

            AOKODST.P_ODS_AOROCHG_CHG ('MAT_ORGANIZATIONAL_CONSTITUENT',

                                       :OLD.APRCHIS_PIDM,

                                       'U');

            AOKODST.P_ODS_APRCCHG_CHG ('MAT_CONSTITUENT',

                                       :OLD.APRCHIS_PIDM,

                                       'U');

            AOKODST.P_ODS_AGRACHG_CHG ('MAT_ANNUAL_GIVING',

                                       :OLD.APRCHIS_PIDM,

                                       'U');

        END IF;

    --

    END;

    /

     

    You will see that it doesn't matter what column is updated io the table APRCHIS, the trigger will still be fired and all the change table will be updated with a row (4 change table in this example: APRHCHG, AOROCHG, APRCCHG, AGRACHG). If I could detect what source table column are used by the view which is part of the ETL extraction logic, I could simply say to the change table trigger to ignore a specific DML which we don't care to capture in the change table.

     

    Regards,

    Eric

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points