Skip to Content
-1

How to find the Complete List of Dependency Objects used in a Calculation View.

HI All,

We have a requirement, where there are Calculation views which has 10 other Calculation views linked to it and some views has 5 dependency views and some has 2 or 3.

We need to develop a model where we can get all the Dependency Calculation views used in it.

Since the Dependency objects in a Calc view is completely dynamic based on the Business requirement. Not sure what is the best way to develop this model.

I know by using the SYS_REPO."ACTIVE_OBJECTCROSSREF" view, we can retrieve all the Dependency objects. but the challenge is how to handle this and how many joins we need to put on the same SYS_REPO view.

Can someone help to provide your idea and thoughts or best way to implement this.

Thanks
Rufus

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Feb 22 at 10:52 PM
    CREATE FUNCTION "DependencyObjectList_TF" ( IP_OBJECT_NAME nvarchar(500) )
    	RETURNS TABLE ( "PackageName" 	  NVARCHAR(300),
    					"CalculationView/TableFunction" NVARCHAR(500),
    					"BaseObjectType"  NVARCHAR(100),
    					"BaseObjectName"  NVARCHAR(500),
    					"BaseObject_Package/SchemaName"  NVARCHAR(300)
    					)
    	      
    	
    	LANGUAGE SQLSCRIPT
    	SQL SECURITY INVOKER AS
    BEGIN
    /***************************** 
    	Write your function logic
     *****************************/
    declare lv_objectname nvarchar(500);
     
    lv_objectname = '%' || :IP_OBJECT_NAME || '%';
     
    Return 
     
     
    select 
     "PackageName",
     "CalculationView/TableFunction",
     "BaseObjectType",
     "BaseObjectName",
     "BaseObject_Package/SchemaName" 
     
     from
    (
    
    
    select    
    
    
    case 
    when locate(DEPENDENT_OBJECT_NAME,'/') != 0
    then
    SUBSTR_BEFORE(DEPENDENT_OBJECT_NAME,'/') 
    else 
    SUBSTR_BEFORE(DEPENDENT_OBJECT_NAME,'::')
    end AS "PackageName",
    
    
    
    
    case
    when locate(DEPENDENT_OBJECT_NAME,'/') != 0
    then 
    SUBSTR_AFTER(DEPENDENT_OBJECT_NAME,'/')
    else
    SUBSTR_AFTER(DEPENDENT_OBJECT_NAME,'::') 
    end AS "CalculationView/TableFunction",
    
    
    case BASE_OBJECT_TYPE
    when 'VIEW'
    then
    'Calculation View'
    else 
    
    
    BASE_OBJECT_TYPE 
    end AS "BaseObjectType",
    
    
    case 
    when BASE_OBJECT_TYPE = 'VIEW'
    then substr_after(BASE_OBJECT_NAME,'/')
    else BASE_OBJECT_NAME
    end  AS "BaseObjectName",
    
    
    case 
    when  BASE_SCHEMA_NAMe like '%_SYS_BIC'
    then substr_before(BASE_OBJECT_NAME,'/')
    else 
    BASE_SCHEMA_NAME 
    end AS "BaseObject_Package/SchemaName"
    
    
    
    
    from "PUBLIC"."OBJECT_DEPENDENCIES" 
    
    
    WHERE  dependent_object_type != 'SYNONYM'  and
    
    
    DEPENDENT_OBJECT_NAME LIKE :lv_objectname and BASE_OBJECT_NAME != 'DUMMY'
    
    
    
    
    )
    where "CalculationView/TableFunction" not like '%hier%'
    order by  "CalculationView/TableFunction" asc, "BaseObjectType" ASC, "BaseObjectName" ASC, "BaseObject_Package/SchemaName" DESC
    ; 
     
    END;
    
    Add comment
    10|10000 characters needed characters exceeded

    • you can use this script, input view name for which you want to find dependency list(views/tables/table Functions). This will also take view name as wild character i.e. you can input partial view name( or its package name)

      Regards,

      Nagaraj