Skip to content

Filter Optimized FunctionsΒΆ

A developer can model a function which returns a collection of entities. This function can have a custom PLSQL implementation or a custom from implementation. If a user issues a $fitler request against these functions, there can be a performance impact since Oracle cannot optimize the logic inside the PLSQL block. It is possible to overcome this issue by using the filter optimized functions.

This functionality does not support Boolean attributes or projection only attributes. So in a case where these types of attributes can be included in a $filter, do not implement this functionality.

In a function with a custom PLSQL implementation you need to add the property in the projection model.

img.png

By adding this property, it will generate the method stub with additional parameters in plsvc.

img_1.png

In a function with a custom from statement you need to add the property and should pass the additional bind parameter to the function call.

Note: In this scenario always check the bind name are according to the below image. Otherwise, you will get runtime errors. This is not checked in the development time. img_2.png

In both cases, function implementation should be implemented with the help of those two additional parameters.

  • filter_where$_ :- This parameter will contain the where statement that is related to the $filter. If there is no $filter applied to an API call then this parameter will return null. Because of that there should be a null check when using this parameter.
  • filter_bind$_ :- This parameter is type of FNDCG_PROJECTION_SVC_TYPES_API.BIND_ARR. This will contain the
  • Name of the bind variable
  • Data type of the bind variable
  • Value of the bind variable

If there is no $filter applied to an API call then this will return an empty array. So, need special handling for that.

img_3.png img_4.png

As a best practice you need to log the final statement using Log_sys as displayed above. This will help to debug any errors that will occur. As shown in above image you only need to cast 'NUMBER' data type. Other data types will be handled from the server side when creating the where condition.

To make the Filter Optimized Functions with custom PLSQL implementations aware of custom fields, the data source(view_name) given to the SQL statement should change accordingly. To handle this situation, Dictionary_SYS.Get_Resolved_View can be used. When the view_name_ and LU is passed into the method, the appropriate cf_view_name will be returned if exists. If not, the same view_name passed to the method will be returned.

Note: Pipeline functions and functions with custom from implementations will not support custom fields when implemented as Filter Optimized Functions!

img_5.png