7 Replies Latest reply: Jul 9, 2013 6:34 AM by user10712277 RSS

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

    user10712277

      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

          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)
            Billy~Verreynne

            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

              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)
                Billy~Verreynne

                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

                  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)
                    Billy~Verreynne

                    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

                      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