Return List of Values in PLSQL Function

 

In this post I'll explain how we can write a PLSQL Function to return list of values of data.

Problem: 

Let's imagine below kind of requirement. We have to get data from a Custom View, but if we use this Custom View or Custom Fields in our code and when trying to run the sanity build in build place it will give build errors due to no configurations available in build place.

 

CURSOR get_service_org_data (ord_id_ VARCHAR2, serv_del_unit_ VARCHAR2) IS 
SELECT
   ca.address,
   sb.CF$_C_DEL_UNIT_CONTACT_NAME contact_name,
   sb.CF$_C_DEL_UNIT_CONTACT_PHONE phone_no,
   sb.CF$_C_DEL_UNIT_CONTACT_EMAIL email 
FROM
   SERVICE_BRANCH_CFV sb,
   SITE s,
   COMPANY_ADDRESS ca 
WHERE
   sb.COMPANY = s.COMPANY 
   AND sb.ALT_LOGISTIC_SITE     = s.CONTRACT 
   AND s.COMPANY                = ca.company 
   AND s.DELIVERY_ADDRESS       = ca.address_id 
   AND sb.ORGANIZATION_ID       = ord_id_ 
   AND sb.SERVICE_DELIVERY_UNIT = serv_del_unit_;OPEN get_service_org_data(rec_header_.c_org_id, rec_header_.serv_del_unit);
FETCH get_service_org_data INTO c_site_def_del_add_, c_contact_name_, c_contact_phone_, c_contact_email_;
CLOSE get_service_org_data; 

Solution:

In here we have two problems.

1. We cannot version control files, because it will give build errors.
2. Get values which returns from the cursor.  

To avoid first issue, we can write a PLSQL statement as a dynamic statement. To fix second issue we can define a new record type in our .plsql file to bind and return multiple values from a function.

.PLSQL Changes (e.g:- Jt_Task_API)

Public Declaration:

Create new record type like below and include/declare all the fields which are needed to return.

TYPE C_Serv_Rec IS RECORD(address VARCHAR2(2000), contact_name VARCHAR2(2000), phone_no VARCHAR2(2000), email VARCHAR2(2000));

New Cust Function:

Use newly created record type in your function and write the cursor inside a dynamic statement like below to return data. 

FUNCTION C_Get_Service_Org_Data ( 
   in_ord_id_          IN VARCHAR2,  
   in_serv_del_unit_   IN VARCHAR2 ) RETURN C_Serv_Rec
IS 
   stmt_    VARCHAR2(4000); 
   rec_     C_Serv_Rec;
BEGIN 
   IF (Installation_SYS.View_Exist('SERVICE_BRANCH_CFV')) THEN 
      stmt_ := ' 

               DECLARE 
                  CURSOR get_data (ord_id_ VARCHAR2, serv_del_unit_ VARCHAR2) IS
                     SELECT ca.address, sb.CF$_C_DEL_UNIT_CONTACT_NAME contact_name, sb.CF$_C_DEL_UNIT_CONTACT_PHONE phone_no, sb.CF$_C_DEL_UNIT_CONTACT_EMAIL email
                     FROM SERVICE_BRANCH_CFV sb, SITE s, COMPANY_ADDRESS ca
                     WHERE sb.COMPANY               = s.COMPANY
                     AND   sb.ALT_LOGISTIC_SITE     = s.CONTRACT
                     AND   s.COMPANY                = ca.company
                     AND   s.DELIVERY_ADDRESS       = ca.address_id
                     AND   sb.ORGANIZATION_ID       = ord_id_
                     AND   sb.SERVICE_DELIVERY_UNIT = serv_del_unit_;

               BEGIN 

                  OPEN  get_data(:s1, :s2); 
                  FETCH get_data INTO :s3; 
                  IF get_data%found THEN 
                     CLOSE get_data; 
                  ELSE 
                     CLOSE get_data; 
                  END IF;                  
               END;'; 

      @ApproveDynamicStatement(2025-07-01,DmcAsankG) 
      EXECUTE IMMEDIATE stmt_ USING IN in_ord_id_, in_serv_del_unit_, OUT rec_; 

   END IF; 

   RETURN rec_; 

END C_Get_Service_Org_Data; 

 

Access Data:

You can get the data to somewhere by accessing above function.

To do that declare the rec like below and access through the API call, since this example I have deployed the new function to Jt_Task_API code will be like below.

rec_serv_org_ Jt_Task_API.C_Serv_Rec;

rec_serv_org_ := Jt_Task_API.C_Get_Service_Org_Data(c_org_id_, serv_del_unit_);