Skip to content

Function

A function executes logic in the server and returns an output. Functions can be used to query data from the database, but never changes the database content.

A function needs to be defined in the projection file, while the relevant logic is implemented in the plsvc file.


function ConcatTextValues Text {  
   parameter Text1 Text;  
   parameter Text2 Text;  
}  

Example code - Function defined in the projection file


FUNCTION Concat_Values(  
   text1 IN VARCHAR2,  
   text2_ IN VARCHAR2 ) RETURN VARCHAR2  
IS  
   
BEGIN  
   RETURN text1_ || '-' || text2_;<br/>END Concat_Values_;<br/>

Example code - Function logic implementation in the plsvc file

If the function just executes a simple where-statement, and gets the data from the server, then it can be defined in the projection, and the relevant implementation in plsvc can be generated when deploying the projection. In this case there is no need to manually write the implementation in plsvc.


function GetIssueCategories List<Entity(IssueCategory)> {  
   parameter IssueType Enumeration(IssueType);  
   where = "issue_type_db = :IssueType";  
}  

Example code - Function with where-statement

Example 1 (A function with many types of parameters)


function IsDifferedFrmSched Boolean {  
   parameter CompanyId Text;  
   parameter EmpNo Text;  
   parameter DateFrom Date;  
   parameter DateTo Date;  
   parameter HalfDayFrom Text {  
      required = [false];  
   }  
   parameter HalfDayTo Text {  
      required = [false];  
   }  
}  

Example 2 (A function that returns a structure)


function GetInOutDateStr Structure(InOutDateStr) {  
   parameter CompanyId Text;  
   parameter EmpNo Text;  
   parameter TimeFrom Text;  
   parameter TimeTo Text;  
}  

structure InOutDateStr {  
   attribute DateFrom Date;  
   attribute DateTo Date;  
}  

Example 3 (A function that returns an array)


function GetAbsenceTypes List<Entity(AbsenceTypeLov)> {  
   parameter CompanyId Text;  
   where = "company_id = :CompanyId and Absence_Registration_API.Check_Absence_Type_Id_Valid( company_id, absence_type_id) = 'TRUE'";  
}  

Example 4 (A function that returns an entity collection)

These functions can be used as data sources for various client components like Lists, LOVs, etc.


function GetIssueCategories List<Entity(IssueCategory)> {  
   parameter IssueType Enumeration(IssueType);  
}  


FUNCTION Get_Issue_Categories(  
   issuetype_ IN VARCHAR2 )  RETURN Objid_Arr  
IS  
   Custom_where VARCHAR2(500);  
   base_collection_ Objid_Arr := Objid_Arr();CURSOR Get IS  
      SELECT *  
      FROM ISSUE_CATEGORY  
      WHERE issue_type_db = :issue_type_;  
BEGIN  
   FOR rec_ IN Get LOOP  
      base_collection_.extend;  
      base_collection_(base_collection_.last) := rec_.objid;  
   END LOOP;  
   RETURN base_collection_;  
END Get_Issue_Categories_;  

basedon entitysets

When a function returns an entity collection which is used as the data source of some element in the client, it is required to set an entityset in order to create the proper IDs for the records. The entityset need to be based on the same entity that the function returns.

This entityset is also used when performing CRUD actions for those elements, since function data source cannot be used with CRUD actions (function is read-only). This can be done using the basedon property.


entityset OrdersSet for DemoOrder;  

function GetDemoOrders List<Entity(DemoOrder)> {  
   parameter DateFrom Date;  
   parameter DateTo Date;  
   basedon OrdersSet;  
}  

Using dynamic where-statements

A where-statement can be dynamically created, based on the input parameter to the function.


stmt_str := 'SELECT * FROM DEMO_ORDER WHERE start_date > :date_from_ AND end_date < :date_to_';  
   IF date_from_ < year_begin_ THEN  
      stmt_str :=stmt_str||' AND site=''S1''';<br/>   ELSIF date_to_ > year_end_ THEN<br/>      stmt_str :=stmt_str||' AND site=''S1''';<br/>   ELSE<br/>      stmt_str :=stmt_str||' AND (site=''S1'' OR site=''S2'')';<br/>   END IF;<br/>