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

[former_member462348]

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

[former_member213277 · Accepted solution]

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;

[former_member213277 · Accepted solution]

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