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_; END Concat_Values_;
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
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]; } }
function GetInOutDateStr Structure(InOutDateStr) { parameter CompanyId Text; parameter EmpNo Text; parameter TimeFrom Text; parameter TimeTo Text; } structure InOutDateStr { attribute DateFrom Date; attribute DateTo Date; }
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'"; }
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_;
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; }
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'''; ELSIF date_to_ > year_end_ THEN stmt_str :=stmt_str||' AND site=''S1'''; ELSE stmt_str :=stmt_str||' AND (site=''S1'' OR site=''S2'')'; END IF;