@@header


/*
*
*  Author     : Vishal Gupta
*  Purpose    : Show column dependency
*  Versions   : 11.1 and above
*  Parameters : 1 - Owner           (% - wildchar, \ - escape char)
*               2 - Object Name     (% - wildchar, \ - escape char)
*               3 - Column Name     (% - wildchar, \ - escape char)
*               4 - Dependent Owner (% - wildchar, \ - escape char)
*               5 - Dependent Name  (% - wildchar, \ - escape char)
*               6 - Dependent Type  (% - wildchar, \ - escape char)
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  12-Nov-15  Vishal Gupta  Created
*
*/

UNDEFINE referenced_owner
UNDEFINE referenced_name
UNDEFINE referenced_column_name
UNDEFINE dependent_owner
UNDEFINE dependent_name
UNDEFINE dependent_type

DEFINE referenced_owner="&&1"
DEFINE referenced_name="&&2"
DEFINE referenced_column_name="&&3"
DEFINE dependent_owner="&&4"
DEFINE dependent_name="&&5"
DEFINE dependent_type="&&6"



COLUMN  _referenced_owner        NEW_VALUE referenced_owner         NOPRINT
COLUMN  _referenced_name         NEW_VALUE referenced_name          NOPRINT
COLUMN  _referenced_column_name  NEW_VALUE referenced_column_name   NOPRINT
COLUMN  _dependent_owner         NEW_VALUE dependent_owner          NOPRINT
COLUMN  _dependent_name          NEW_VALUE dependent_name           NOPRINT
COLUMN  _dependent_type          NEW_VALUE dependent_type           NOPRINT

set term off

SELECT DECODE(UPPER('&&referenced_owner'),'','%','&&referenced_owner')              "_referenced_owner"
     , DECODE(UPPER('&&referenced_name'),'','%','&&referenced_name')                "_referenced_name"
     , DECODE(UPPER('&&referenced_column_name'),'','%','&&referenced_column_name')  "_referenced_column_name"
     , DECODE(UPPER('&&dependent_owner'),'','%','&&dependent_owner')                "_dependent_owner"
     , DECODE(UPPER('&&dependent_name'),'','%','&&dependent_name')                  "_dependent_name"
     , DECODE(UPPER('&&dependent_type'),'','%','&&dependent_type')                  "_dependent_type"
FROM DUAL
;

set term on


PROMPT
PROMPT ***************************************************************************************************
PROMPT * D E P E N D E N C Y
PROMPT * 
PROMPT * Input Parameters 
PROMPT *    - Owner           = '&&referenced_owner'
PROMPT *    - Object Name     = '&&referenced_name'
PROMPT *    - Column Name     = '&&referenced_column_name'
PROMPT *    - Dependent Owner = '&&dependent_owner'
PROMPT *    - Dependent Name  = '&&dependent_name'
PROMPT *    - Dependent Type  = '&&dependent_type'
PROMPT ***************************************************************************************************
 

 

COLUMN referenced_object_name    HEADING "Object Name"                 FORMAT a40
COLUMN referenced_column_name    HEADING "Column Name"                 FORMAT a20
COLUMN referenced_type           HEADING "Object Type"                 FORMAT a20 TRUNCATE
COLUMN object_name               HEADING "Dep Object Name"             FORMAT a50
COLUMN type                      HEADING "DepObject Type"              FORMAT a20 TRUNCATE
COLUMN referenced_link_name      HEADING "Ref Link Name"               FORMAT a20
COLUMN dependency_type           HEADING "Depend|Type"                 FORMAT a10

BREAK ON referenced_object_name  ON referenced_column_name

SELECT d.referenced_owner || '.' || d.referenced_name   referenced_object_name
     , c.name                                           referenced_column_name
     --, d.referenced_type
     , d.owner || '.' || d.name                         object_name
     , d.type
     , d.referenced_link_name
     , d.dependency_type     
  FROM dba_dependencies d 
     JOIN dba_objects o ON o.owner = d.owner AND o.object_name = d.name AND o.object_type = d.type 
     JOIN dba_objects ro ON ro.owner = d.referenced_owner AND ro.object_name = d.referenced_name AND ro.object_type = d.referenced_type
     JOIN sys.dependency$ d2 ON d2.d_obj# = o.object_id AND d2.p_obj# = ro.object_id AND d2.d_attrs IS NOT NULL
     JOIN sys.col$ c ON c.obj# = d2.p_obj# 
     /* 
      - D_ATTRS is raw column containing HEX value. 
      - First 8 characters are something like 00010000 or 00030000 or 00030100, they can be ignored.
      - Digits from 9th character onwards represent the BITAND flag for column in bytes for 8 column pairs
      - 9-10 digit represent first 8 columns
      - 11-12 digit represent next 8 columns 
      - and so on.
     */
     AND power(2,mod(c.col#,8)) = BITAND( TO_NUMBER(SUBSTR(SUBSTR(d2.d_attrs,9), 1 + 2*TRUNC(c.col#/8) ,2) ,'XX' ) , power(2,mod(c.col#,8)) )
WHERE 1=1
  AND d.referenced_owner LIKE '&&referenced_owner'
  AND d.referenced_name  LIKE '&&referenced_name'
  AND c.name             LIKE '&&referenced_column_name'
  AND d.owner            LIKE '&&dependent_owner'
  AND d.name             LIKE '&&dependent_name'
  AND d.type             LIKE '&&dependent_type'
ORDER BY d.referenced_owner  
       , d.referenced_name
       , c.name
       , d.owner
       , d.name
;

@@footer