Wrap up your APEX REST Data Source and put a bow on it...

Elevate your APEX REST Data Source through enhanced APEX app management and increased versatility.


16 min read


APEX REST Data Sources (RDS) provide significant system integration capabilities managed via the APEX Builder. Management encompasses configuring RDSs, synchronizing with database tables that have a similar structure to the RDS, scheduling jobs, and maintaining job audit logs.

This blog builds on this foundation to incorporate more detailed management, log JSON results, change credentials, and handle the synchronization and processing of results, including arrays within the JSON accessible all from within an APEX Application.

Blog created with assistance from discussion in Oracle Developer Forum Seeking PL/SQL API procedure to select a different Web Credential when calling a REST API on same remote server - Oracle Forums that is much appreciated.

Updates and Errata

  • Benefits: Encapsulate system processing of RDS returned content with views defined on the content joining RDS_JSON_PayLoad, REST_Load_Control, and JSON_Table() definition of JSON content. System processes work with content by calling procedure to refresh if necessary and then utilize the view without needing to "understand" the RDS details.

  • Benefits: Accommodate RDS bind variable, URL pattern, and URL query string parameters through the RDS definition and JSON_Load_Control column values.

  • REST Load Control>Dynamic Action Source: Eliminated p_Param_Names and Values parameters, use REST_Load_Control.LC_Param_Names and LC_Param_Values instead.

  • REST Load Control Table: Add LC_URL_QRY_STRING_NAMES and VALUES columns to hold URL Query string parameter values.

  • Source Code>Load Control>Run Load Control Single Job and Group source codes: Eliminate p_Param_Names and Values parameters as noted above.

  • Source Code>REST Data Processing>Execute REST Data Source: Added code to handle REST Data Source bind variables and URL parameters and save parameter values in REST_Load_Control entry. Parameters were not handled properly or as completely previously.

Note: Changes in source code are noted with comments before the code, or at the end of the code line with the date of 5/12/24 included.


  • APEX Application Management: Enable running one or more RDSs; viewing results, including the JSON content returned; and modification of select data driven job characteristics such as credentials selection; by a designated user from an APEX application. Execute RDSs in groups, individually, or by selecting one or more jobs, all while respecting a defined run order / order of precedence that acknowledges interdependencies.

  • Log JSON results (Optional): Log RDS executions, when they were run and processed, the associated job definition, and returned JSON contents. Abstract system processing from RDS details and changes, including selection of credentials. Assists with compliance, testing, and viewing of source data.

  • Credential flexibility: Execute the same RDS selecting from N number of credentials without replicating the RDS definitions. For example: integrate with a CRM system where your system has multiple users each with their own CRM account, Client ID, and Client Secret. Call the same RDSs selecting APEX Web Credentials based on which CRM account data/process is sought.

  • Manage and select from multiple Web Credentials without having to store client IDs and secrets in the database.

  • Synchronization and processing of results that include arrays: APEX RDS synchronization merges API results with a database table structured similarly to the JSON content returned from the API, excluding arrays with multiple elements. Not including arrays is understandable because of the added complexity that would require creation of multiple tables with a parent child (for the arrays) relationship. Create custom synchronization and/or processes that can address arrays as well as any particular processing needs.

  • Retain all functionality and benefits of standard APEX Builder, including the use of scheduling/automation. Automation can take advantage of defined RDS job groupings when executing RDSs and/or processing them by calling select PL/SQL procedures directly.

Solution APEX Application

  • APEX Data Source Definition:

  • APEX REST Data Source inventory with ability to Enable Selected RDS(s), Disable Selected RDS(s), Synchronize Selected RDS(s) in the IG "Actions Menu" using basic APEX provided functionality and view.

  • ๐Ÿ’ก
    Note: Built in APEX synchronization will continue to run with the web credential defined in the APEX Application Builder RDS definition because it is using standard functionality.
  • Notes:

    • IG Source:

    •             Select * from APEX_Appl_Web_SRC_Modules;
    • ๐Ÿ’ก
      Where Page Items populated with currently selected IG row Primary Key values are referred to Anton Neilson's (Insum) IG Get Values Plugin is used to capture those values on each IG row selection change. When only the row Primary Key is desired enter it in the Selected Columns plugin attribute to limit the return result.

      Dynamic Action: Enable Selected RDS, use apex_rest_Source_sync.disable for Disable selected RDS.

    •             -- :P36_MODULE_STATIC_ID_CURR populated with IG Plugin
                  v_array := apex_string.split(:P36_MODULE_STATIC_ID_CURR,':' );
                    FOR i IN 1 .. v_array.count
                                                     p_application_id => :APP_ID
                                                    ,p_module_static_id => v_Array(i) );
                      END Loop;
    • Dynamic Action: Synchronize all selected RDS utilizing APEX RDS defined synchronization

    •               v_array := apex_string.split(:P36_MODULE_STATIC_ID_CURR,':' );
                    FOR i IN 1 .. v_array.count
                                                                p_module_static_id      => v_Array(i)
                                                                ,p_run_in_background    => FALSE
                                                                ,p_application_id       => :APP_ID );
                      END Loop;
  • REST Web Service Activity Log:

  • Review REST call results from APEX provided log view.

    • IG Source:

    •             Select *
                    From APEX_WEBSERVICE_LOG;

      (Additional columns for viewing depending on preferences/need.)

  • REST Load Control:

  • Enter Job details, Run Selected Jobs, Run Selected Job Groups, and Process Selected REST Data Source from "Actions Menu". See the description of "REST and Processing Control" below for details on Job details and definition options.

    Create multiple entries for each data source / web credential combination where a single data source has N number of credentials that could apply. (See CRM example above). When each RDS is run and processed it will then run with provided combination.

    • IG Source: IG Table/View of REST_LOAD_CONTROL table, which is defined below. Set IG Edit attribute to enabled. Delete the Virtual columns from the column list so standard Edit functionality can be used without additional update code.

    • Run Selected Job(s): Execute all selected Job entries designated by clicking on the Row check box.

    • ๐Ÿ’ก
      Note: Depending on the Load Control row settings it might only execute the RDS, just the Process, or both; it could archive some or all Payload entries; or any combination based on the LC_RUN_TYPE_CID code (Run RDS only, Run Process only, Run both). Same is true for Running Selected Groups
    • Dynamic Action Source: (Code details below in Solution Framework section)

    •             For I in 1..v_Array.Count
                      v_Section     := '60:';
                      v_PrimaryKey  := v_PrimaryKey_Base || ' I: ' || i || ' Current ID: ' || v_Array(I);
                      -- If there are Job parameter values they will be retrieved from the job entry
                      Pkg_REST_Load_Control.Run_REST_LOAD_Control_Job( p_REST_LOAD_Control_ID    => v_Array(I));
                      -- 5/12/2024
                      -- Eliminated need for p_Param_Names and Value 
                      -- parameters, use LC_Param_Names and LC_Param_Values
                      -- from REST_Load_Control table instead,
                        --                                    ,p_Param_Names                     => Null
                        --                                    ,p_Param_Values            => Null);
                    End Loop;

      Run Selected Load Group: Each grouping / category aggregates jobs according to purpose, for example Lookup tables, sales, orders, etc. allowing them to be run as groups. The "Selected Load Group" dropdown value is referenced by the "Run Selected Group" Actions Menu Item. The IG column "LC_REST_LOAD_GROUP_CID" is a "Select list" of the same distinct Load Groups available.

    • ๐Ÿ’ก
      Reference Simplify managing application codes and their values for possible approach to managing codes.
    • Dynamic Action Code: (Code details below in Solution Framework section)

    •               Pkg_REST_Load_Control.Run_REST_Load_Control_Group(p_LC_REST_LOAD_GROUP_CID => :P36_LC_REST_LOAD_GROUP_CID);
    • Process Selected REST Data Source: The "Selected REST Data Source" dropdown value is referenced by the "Process Selected REST Data Source" Actions Menu Item. The IG column "LC_REST_PROCESS_CID" is a "Select list" of the same distinct Processes available.

      "Process Selected REST Data Source" will process the JSON content of every REST_JSON_PayLoad row with matching Module_Static_ID (RDS) that has not yet been processed and set the payload row Processed date to current date. If multiple RDS calls were made selecting different Web Credentials each time (see CRM example in Benefits section) without processing, this process could run once, process the JSON content of each entry, and updated it as processed.

    • Selected REST Data Source code is:

    •             Select APEX_APPL_WEB_SRC_MODULES.Module_Name
                    From APEX_APPL_WEB_SRC_MODULES
                    Order By Module_Name
    • Dynamic Action Code: (Code details below in Solution Framework section)

    •             Pkg_REST_JSON_PayLoad.Process_REST_Data_Source( p_Module_Static_ID => :P36_MODULE_STATIC_ID
                                                                 ,p_Application_ID => :APP_ID);
    • Process_REST_Data_Source will call the appropriate PL/SQL procedure or function based on the RDS Module_Static_ID. The creation of a procedure or function for each RDS enables synchronization of JSON content containing arrays with table(s) appropriately designed, and/or processing of the data directly into the system in whatever fashion desired customized for each source.

REST JSON Payloads from API Executions:

    • Review and track the RDS and Processing log containing RDS / credential identifying information, activity dates, and JSON content received. Enable/Disable/secure IG Attribute Edit functionality depending on need.


      IG Source: Table/View : REST_JSON_PAYLOAD

Solution Framework

  • Define RDSs and Web Credentials in APEX Builder as usual. If multiple credentials apply to the same RDS assign one to the RDS and create the others separately.

  • Result Log (REST JSON PayLoad):

  • REST JSON Payload table tracks RDS execution, job definition (JP_REST_LOAD_CONTROL_ID), RDS identifier (JP_MODULE_STATIC_ID, JP_CREDENTIAL_ID, JP_APP_ID), result Key IDs (KEYS_1 and KEYS_2), run and process dates, resulting JSON "payload".

  • ๐Ÿ’ก
    Note: The REST_JSON_PAYLOAD table can be omitted if desired by altering the processing stage (to be described) to run at the same time the RDS executes and directly access the resulting JSON result, skipping the intermediate step.

  •             Create Table REST_JSON_PAYLOAD
                  REST_JSON_PAYLOAD_ID        NUMBER(20)            Default on Null REST_JSON_PAYLOAD_ID.Nextval,
                  JP_REST_LOAD_CONTROL_ID     NUMBER(20)            ,
                  JP_MODULE_STATIC_ID         Varchar2(600 CHAR)    Constraint REST_JSON_PAYLOAD_MS Not Null,
                  JP_CREDENTIAL_ID            Varchar2(256 CHAR)    Constraint REST_JSON_PAYLOAD_CR Not Null,
                  JP_APP_ID                   Integer               Constraint REST_JSON_PAYLOAD_AI Not Null,
                  JP_DATA_KEYS_1              Varchar2(32767 CHAR)  ,
                  JP_DATA_KEYS_2              Varchar2(32767 CHAR)  ,
                  JP_REST_RUN_DATE            Date                  Constraint REST_JSON_PAYLOAD_RD Not Null,
                  JP_PROCESS_DATE             Date                  ,
                  JP_JSON_PAYLOAD             Varchar2(32767 CHAR)  Constraint REST_JSON_PAYLOAD_SI Check( JP_JSON_PAYLOAD IS JSON),
                  JP_EFFECTIVE                DATE                  Default on Null Trunc(Current_Date) CONSTRAINT REST_JSON_PAYLOAD_EE_NN  NOT NULL ,
                  JP_EXPIRES                  DATE                  Default on Null To_Date('12/31/2999','MM/DD/YYYY') CONSTRAINT REST_JSON_PAYLOAD_EX_NN    NOT NULL ,
                  VC_JP_MODULE_STATIC_ID_CAPS Varchar2(600 CHAR) Generated Always As (Upper(JP_MODULE_STATIC_ID)),
                  VC_JP_CREDENTIAL_ID_CAPS      Varchar2(256 CHAR) Generated Always AS (Upper(JP_CREDENTIAL_ID)),
                  Constraint                  REST_JSON_PAYLOAD_JP_PK Primary Key (REST_JSON_PAYLOAD_ID)
  • REST Load Control:

  • Data driven RDS and synchronization/processing table defining RDS/Web Credential combination to run (Module, Credential, Application), # of days of REST_JSON_PAYLOAD to retain (Retain Days), whether to expire all unprocessed matching (Module, Credential, Application) REST_JSON_PAYLOAD entries before running job (Expire UnProcessed), the category this job is in, i.e. lookup tables, employee, sales, orders, etc. (Load Group), abbreviation for system process to run using the REST_JSON_PAYLOAD content (Rest Process), whether to only run the RDS, or only the system process, or both (Run Type), parameter names (Param Names), parameter values (Param Values), order to run multiple jobs in if applicable acknowledging inter-dependencies (Run Order), primary table the data will be merged with (Target Table), and when the entry is active (Effective / Expires).

    Entries offer significant flexibility in defining which RDSs and processes to run, their timing, combinations, pre-defined groups, ad-hoc selections within the APEX Application, custom synchronization and process definitions, and the ease of adding and expiring jobs.

  • Load Control

  •             Create Table REST_Load_Control
                    REST_Load_Control_ID            NUMBER(20)            Default on Null REST_Load_Control_ID.Nextval,
                    LC_MODULE_STATIC_ID             VARCHAR2(256 CHAR)    Constraint REST_LOAD_CONTROL_MS_NN Not Null,
                    LC_CREDENTIAL_STATIC_ID         VARCHAR2(256 CHAR)    Constraint REST_LOAD_CONTROL_CS_NN Not Null,
                    LC_APPLICATION_ID               Integer               Constraint REST_LOAD_CONTROL_AP_NN Not Null,
                    LC_RETAIN_DAYS                  Integer               Constraint REST_LOAD_CONTROL_RD_NN Not Null,
                    LC_EXPIRE_ALL_UNPRCSD_YN        VARCHAR2(1 CHAR)      Constraint REST_LOAD_CONTROL_UN_NN Not Null,
                    LC_REST_LOAD_GROUP_CID          NUMBER(20)            Constraint REST_LOAD_CONTROL_GR_NN Not Null,
                    LC_REST_PROCESS_CID             NUMBER(20)            Constraint REST_LOAD_CONTROL_PR_NN Not Null,
                    LC_RUN_TYPE_CID                 NUMBER(20)            Constraint REST_LOAD_Control_RT_NN Not Null,
                    LC_PARAM_NAMES                  Varchar2(32767 CHAR)  ,
                    LC_PARAM_VALUES                 Varchar2(32767 CHAR)  ,
                    -- 5/12/2024
                    -- Add columns to collect/hold URL Query String parameter values
                    LC_URL_QRY_STRING_NAMES       Varchar2(32767 CHAR)  ,
                    LC_URL_QRY_STRING_VALUES      Varchar2(32767 CHAR)  ,
                    LC_RUN_ORDER                    INTEGER               Constraint REST_LOAD_CONTROL_RO_NN Not Null,
                    LC_TARGET_TABLE                 VARCHAR2(256 CHAR)    Constraint REST_LOAD_CONTROL_TT_NN Not Null,
                    LC_EFFECTIVE                    DATE                  Default on Null Trunc(Current_Date) CONSTRAINT REST_Load_Control_EE_NN  NOT NULL ,
                    LC_EXPIRES                      DATE                  Default on Null To_Date('12/31/2999','MM/DD/YYYY') CONSTRAINT REST_Load_Control_EX_NN    NOT NULL ,
                      VC_LC_MODULE_STATIC_ID_CAPS     Varchar2(256 CHAR) Generated Always AS (Upper(LC_MODULE_STATIC_ID)),
                      VC_LC_CREDENTIAL_STATIC_ID_CAPS Varchar2(256 CHAR) Generated Always AS (Upper(LC_CREDENTIAL_STATIC_ID)),
                      VC_LC_TARGET_TABLE_CAPS         Varchar2(256 CHAR) Generated Always AS (Upper(LC_TARGET_TABLE)),
                      Constraint                      REST_Load_Control_EN_FK Foreign Key ( LC_VPA_ENTITY_ID       ) References VPA_ENTITY(VPA_ENTITY_ID),
                      Constraint                      REST_Load_Control_LG_FK Foreign Key ( LC_REST_LOAD_GROUP_CID ) References VPA_Codes_Value(VPA_Codes_Value_ID),
                      Constraint                      REST_Load_Control_RP_FK Foreign Key ( LC_REST_PROCESS_CID    ) References VPA_Codes_Value(VPA_Codes_Value_ID),
                      Constraint                        REST_LOAD_Control_RT_FK Foreign Key ( LC_RUN_TYPE_CID        ) References VPA_Codes_Value(VPA_Codes_Value_ID),
                      Constraint                      REST_Load_Control_LC_PK   Primary Key (REST_Load_Control_ID)

Pertinent Source Code Excerpts:

Load Control:

  • Run Load Control single Job

    •             -- Run single REST_Load_Control job.
                  Procedure Run_REST_LOAD_Control_Job( p_REST_LOAD_Control_ID    IN REST_Load_Control.REST_Load_Control_ID%Type
                                                                                          ,p_Param_Names                     IN Pkg_VPA_Codes_Value.s_Varchar2_Max_Length
                                                                                          ,p_Param_Values                    IN Pkg_VPA_Codes_Value.s_Varchar2_Max_Length)
                    -- Copyright 2018.  SoftPath, Inc. All Rights Reserved.
                        v_VW_REST_Load_Control    VW_REST_Load_Control%RowType;
                      v_Response                CLOB;
                    v_VW_REST_Load_Control := Pkg_REST_Load_Control.Qry_VW( p_REST_Load_Control_ID    => p_REST_Load_Control_ID);
                                                                                                                                          ,p_Error_On_No_Find            => 'N');
                      v_Message               := 'Error: Process run type ' || v_VW_REST_Load_Control.CD_RUN_TYPE || ' (' || v_VW_REST_Load_Control.LC_RUN_TYPE_CID || ') not recognized.';
                      Raise e_Error;
                    End If;
                    If v_VW_REST_Load_Control.CD_RUN_TYPE IN ('REST_JSON_PAYLOAD_ONLY','REST_LOAD_AND_SYNC') Then
                      v_Response               := PKG_REST_JSON_PAYLOAD.Execute_REST_Data_Source( p_REST_Load_Control    => v_VW_REST_Load_Control
                     -- 5/12/2024
                     -- Eliminate reference to p_Param_Names and Values Parameters
                     -- Function will use REST_JSON_Load_Control LE_Param_Names and Values 
                     -- columns instead.
                     --                                                        ,p_Param_Names            => p_Param_Names
                       --                                                    ,p_Param_Values            => p_Param_Values);
                    End If;
                    If v_VW_REST_Load_Control.CD_RUN_TYPE IN ('REST_PAYLOAD_SYNC_ONLY','REST_LOAD_AND_SYNC') Then
                      -- If Limit_Credential is Y then only Process jobs matching both the RDS Static ID and Credential_ID
                      -- otherwise process all jobs with a matching RDS static ID and avoid having to call for each 
                      -- combination.
                      Pkg_REST_JSON_PayLoad.Process_Job( p_REST_Load_Control        => v_VW_REST_Load_Control
                                                        ,p_Limit_Credential_YN    => 'N');
                    End If;
                    -- Insert your logging and error handling here.
                  End Run_REST_Load_Control_Job;
  • Run Load Control Group:

                  For c_REST_Load_Control In    (    Select     *
                                                  From     VW_REST_Load_Control
                                                  Where VW_REST_LOAD_Control.LC_REST_LOAD_GROUP_CID = p_LC_REST_LOAD_GROUP_CID
                                                  Order By VW_REST_Load_Control.LC_RUN_ORDER
                      Pkg_REST_Load_Control.Run_REST_LOAD_Control_Job( p_REST_LOAD_Control_ID    => c_REST_Load_Control.REST_Load_Control_ID
                     -- 5/12/2024
                     -- Eliminate p_Param_Names and p_Param_Values
                     --                            ,p_Param_Names             => c_REST_Load_Control.LC_PARAM_NAMES
                       --                            ,p_Param_Values            => c_REST_Load_Control.LC_PARAM_VALUES
                    End Loop;

REST Processing

  • Execute REST Data Source:

    •             Function Execute_REST_Data_Source( p_REST_Load_Control    IN VW_REST_Load_Control%RowType
                                                    ,p_Param_Names                 IN Pkg_VPA_Codes_Value.s_Varchar2_Max_Length
                                                    ,p_Param_Values                IN Pkg_VPA_Codes_Value.s_Varchar2_Max_Length)
                  Return Pkg_VPA_Codes_Value.s_Varchar2_Max_Length
                    v_Param_Names        apex_application_global.vc_arr2;
                    v_Param_Values        apex_application_global.vc_arr2;
                    v_Names             Apex_application_global.vc_arr2;-- 5/12
                    v_Values            Apex_Application_Global.vc_arr2;-- 5/12
                    v_Response            CLOB;
                    v_Request_URL        Varchar2(4000);
                    v_Token_URL            Varchar2(4000);
                    v_REST_ID             Varchar2(256);
                    v_Expire_As_Of        REST_JSON_PayLoad.JP_Effective%Type;
                    v_Results             JSON_Array_T;
                    v_Element             JSON_OBJECT_T;
                    v_tmp_obj             JSON_OBJECT_T;
                    v_tmp_Arr             JSON_Array_T;
                    v_REST_JSON_PAYLOAD   REST_JSON_PAYLOAD%RowType;
                    -- 5/12/2024 Add
                    -- Variables for v_Request_URL manipulation
                    v_Name_String        Varchar2(1000) ;
                    v_Value_String        Varchar2(1000) ;
                    v_Pos_Names            Number(20);
                    v_Pos_Values        Number(20);
                    v_Cur_Name            Varchar2(200);
                    v_Cur_Value         Varchar2(200);
                  -- Declarations, error handling, etc.
                    --  Delete old entries per p_JSON_PayLoad_Retain_Days parameter.
                    PKG_REST_JSON_PAYLOAD.Del_REST_Data_Source(  p_JP_Module_Static_ID  => p_Rest_Load_Control.LC_MODULE_STATIC_ID
                                                                ,p_JP_Credential_ID     => p_Rest_Load_Control.LC_CREDENTIAL_STATIC_ID
                                                                ,p_JP_APP_ID            => p_Rest_Load_Control.LC_APPLICATION_ID
                                                                ,p_Days_Old              => p_Rest_Load_Control.LC_RETAIN_DAYS);
                    If Upper(Trim(Nvl(p_Rest_Load_Control.LC_EXPIRE_ALL_UNPRCSD_YN,'N'))) = 'Y' Then
                      -- Expire UnProcessed entries
                      PKG_REST_JSON_PAYLOAD.Expire_REST_Data_Source( p_JP_Module_Static_ID  => p_Rest_Load_Control.LC_MODULE_STATIC_ID
                                                                       ,p_JP_Credential_ID     => p_Rest_Load_Control.LC_CREDENTIAL_STATIC_ID
                                                                     ,p_JP_APP_ID            => p_Rest_Load_Control.LC_APPLICATION_ID
                                                                    ,p_Expire_As_Of           => v_Expire_As_Of);
                    End If;
                    v_REST_ID         := Upper(Trim(p_Rest_Load_Control.LC_MODULE_STATIC_ID));
                    -- 5/12 v_Param_Names        := apex_string.string_to_table(p_Param_Names);
                    -- 5/12 v_Param_Values    := apex_string.string_to_table(p_Param_Values);
                        FROM APEX_Appl_Web_Src_Modules
                        WHERE     Upper(apex_appl_web_src_modules.module_static_id) = v_REST_ID
                            AND Application_ID                                    = p_Rest_Load_Control.LC_APPLICATION_ID; 
                        When No_Data_Found Then
                          v_Message                 := 'Error: Rest static ID ' || v_REST_ID || ' not found in src modules for App ' || p_Rest_Load_Control.LC_APPLICATION_ID;
                          Raise e_Error;
                        -- Loop through the input string
                        v_Name_String    := p_Rest_Load_Control.LC_PARAM_NAMES;
                        v_Value_String    := p_REST_Load_Control.LC_PARAM_VALUES;
                        v_Pos_Names        := INSTR(v_Name_String, ',', 1);
                        v_Pos_Values    := Instr(v_Value_String,',',1);
                        -- v_Request_URL to replace bind variables wiht valuables if applicable
                        WHILE v_Pos_Names > 0 LOOP
                            v_Cur_Name     := ':' || TRIM(SUBSTR(v_Name_String, 1, v_Pos_Names - 1));
                            v_Cur_Value    := Trim(SUbstr(v_Value_String, 1, v_Pos_Values -1));
                          -- Replace the match to v_Cur_Name with value of v_Cur_Value
                          v_Request_URL    := REPLACE(v_Request_URL, v_Cur_Name, v_Cur_Value);
                            v_Name_String    := SUBSTR(v_Name_String, v_Pos_Names + 1);
                            v_Pos_Names     := INSTR(v_Name_String, ',', 1);
                            v_Value_String:= SubStr(v_Value_String, v_Pos_Values +1 );
                            v_Pos_Values    := Instr(v_Value_String, ',', 1);
                        END LOOP;
                        if Substr(v_Name_string,1,1) <> ':' Then
                            v_Cur_Name    := ':' || Trim(v_Name_String);
                            v_Cur_Name     := Trim(v_Name_String);
                        End If;
                        v_Cur_Value        := Trim(v_Value_String);
                        v_Request_URL    := REPLACE(v_Request_URL, v_Cur_Name, v_Cur_Value);
                    -- URL parameter values
                    v_Param_Names        := apex_string.string_to_table(p_REST_Load_Control.LC_URL_QRY_STRING_NAMES);
                    v_Param_Values        := apex_string.string_to_table(p_REST_Load_Control.LC_URL_QRY_STRING_VALUES);
                     v_Response := apex_web_service.make_rest_request( p_URL                                     => v_Request_URL
                                                       , p_http_method             => 'GET'
                                                       , p_parm_name             => v_Param_Names
                                                       , p_parm_value             => v_Param_Values
                                                       , p_credential_static_id => p_Rest_Load_Control.LC_CREDENTIAL_STATIC_ID
                                                       , p_token_url             => v_Token_URL);
                     -- Add error checking
                    If apex_web_service.g_status_code != 200 Then
                      v_Message             := 'Error: API call failed: ' || APEX_Web_Service.g_Status_Code || ' : ' || v_Response
                                                || ' URL: ' || v_Request_URL  || ' Credential: ' || p_Rest_Load_Control.LC_CREDENTIAL_STATIC_ID;
                      -- Your error handling and logging here.
                    End If;
                  --    v_results := JSON_OBJECT_T.PARSE(v_Response).get_array('results');
                  --  For i In 0..v_Results.get_Size() -1 Loop
                  --      v_Element := treat(v_results.get(i) as JSON_OBJECT_T);
                        v_REST_JSON_PayLoad.REST_JSON_PAYLOAD_ID        := Null;
                        v_REST_JSON_PayLoad.JP_REST_LOAD_CONTROL_ID := p_REST_Load_Control.REST_LOAD_CONTROL_ID;
                        v_REST_JSON_PayLoad.JP_MODULE_STATIC_ID         := p_Rest_Load_Control.LC_MODULE_STATIC_ID;
                        v_REST_JSON_PayLoad.JP_Credential_ID          := p_Rest_Load_Control.LC_CREDENTIAL_STATIC_ID;
                        v_REST_JSON_PayLoad.JP_APP_ID                 := p_Rest_Load_Control.LC_APPLICATION_ID;
                      v_REST_JSON_PayLoad.JP_DATA_KEYS_1              := p_REST_Load_Control.LC_PARAM_NAMES;  -- 5/12/24
                        v_REST_JSON_PayLoad.JP_DATA_KEYS_2              := p_REST_Load_Control.LC_PARAM_Values; -- 5/12/24
                        v_REST_JSON_PayLoad.JP_REST_RUN_DATE            := Pkg_VPA_Codes_Value.g_Today;
                        v_REST_JSON_PayLoad.JP_PROCESS_DATE             := Null;
                        v_REST_JSON_PayLoad.JP_JSON_PAYLOAD             := v_RESPONSE;
                        v_REST_JSON_PayLoad.JP_EFFECTIVE                := Pkg_VPA_Codes_Value.g_Today;
                        v_REST_JSON_PayLoad.JP_EXPIRES                  := Pkg_VPA_Codes_Value.g_Date_Infinity;
                        v_REST_JSON_PayLoad.SY_USER_ID_ADD              := Null;
                        v_REST_JSON_PayLoad.SY_DATE_ADD                 := Null;
                        v_REST_JSON_PayLoad.SY_USER_ID_MOD              := Null;
                        v_REST_JSON_PayLoad.SY_DATE_MOD                 := Null;
                        v_REST_JSON_PayLoad := PKG_REST_JSON_PAYLOAD.Ins_Returning(p_REST_JSON_PayLoad => v_REST_JSON_Payload);
                    --  End Loop;
                    Return v_Response;
                    -- Your error and logging code here.
                  End Execute_REST_Data_Source;
  • Process_Job:

    • ๐Ÿ’ก
      Process_Job, Process_REST_DataSource, and individual RDS process jobs rely on the individual PL/SQL procedures being named Process[RDS Module Static ID]. Procedures are essentially the same, except Process Job begins with the Load Control entry, and Process REST Data Source begins with the RDS Static ID. Eliminate that restriction by replacing the execute immediate section with a case statement executing specific PL/SQL procedures based on a process code passed to the procedure.
  •             -- Call appropriate procedure to process the Job according to Load Control entry identified.
                -- If Limit_Credential_YN is Y, only REST_DATA_Sources matching both the data source AND
                -- credential will be processed, otherwise all matching REST Data Sources (Modules) will be
                -- processed.
                Procedure Process_Job( p_REST_Load_Control        IN VW_REST_Load_Control%RowType
                                      ,p_Limit_Credential_YN    IN Pkg_VPA_Codes_Value.s_Varchar2_1 := 'N'
                  -- Copyright 2018.  SoftPath, Inc. All Rights Reserved.
                  v_Sync_Procedure_Call Pkg_VPA_Codes_Value.s_Varchar2_Max_Length;
                  v_Procedure_Name            Pkg_VPA_Codes_Value.s_Varchar2_256;
                  v_Null                                 Varchar2(1);
                    v_Null                    := Null;
                    v_Procedure_Name        := 'Pkg_REST_JSON_PayLoad.Process_' || p_Rest_Load_Control.LC_MODULE_STATIC_ID;
                    v_Sync_Procedure_Call    := 'Begin ' || v_Procedure_Name
                                                || '( p_Module_Static_ID => :p1, p_Credential_Static_ID => :p2, p_Application_ID => :p3, p_Limit_Credential_YN => :p4); end;';
                    Execute Immediate v_Sync_Procedure_Call using in p_Rest_Load_Control.LC_MODULE_STATIC_ID
                  -- Your error and logging code here
                End Process_Job;

Process REST Data Source: (See Process Job Note above)

  •             -- Call appropriate procedure to process the Module/REST Data Source from REST_JSON_PAYLOAD.
                Procedure Process_REST_Data_Source( p_Module_Static_ID    IN APEX_APPL_WEB_SRC_MODULES.Module_Static_ID%Type
                                                   ,p_Application_ID    IN REST_Load_Control.LC_APPLICATION_ID%Type)
                  -- Copyright 2018.  SoftPath, Inc. All Rights Reserved.
                  v_Sync_Procedure_Call Pkg_VPA_Codes_Value.s_Varchar2_Max_Length;
                  v_Procedure_Name        Pkg_VPA_Codes_Value.s_Varchar2_256;
                  v_Null                Varchar2(1);
                    v_Null                := Null;
                    v_Procedure_Name            := 'Pkg_REST_JSON_PayLoad.Synchronize_' || p_Module_Static_ID;
                    v_Sync_Procedure_Call    := 'Begin ' || v_Procedure_Name
                                                || '( p_Module_Static_ID => :p1, p_Credential_Static_ID => :p2, p_Application_ID => :p3, p_Limit_Credential_YN => :p4); end;';
                    Execute Immediate v_Sync_Procedure_Call using in p_Module_Static_ID
                  -- Insert your error and logging code here
                End Process_REST_Data_Source;

Process_[REST Data Source Static ID]: Each procedure called to process a particular data source.

  •             Procedure Process_[REST Data Source Static ID]( p_Module_Static_ID        IN REST_LOAD_COntrol.LC_MODULE_STATIC_ID%Type
                                                                ,p_Credential_Static_ID    IN REST_Load_Control.LC_CREDENTIAL_STATIC_ID%Type
                                                                ,p_Application_ID        IN REST_Load_Control.LC_APPLICATION_ID%Type
                                                                ,p_Limit_Credential_YN    IN Pkg_VPA_Codes_Value.s_Varchar2_1 := 'N')
                  -- Copyright 2018.  SoftPath, Inc. All Rights Reserved.
                  v_Rest_Static_ID         REST_JSON_PayLoad.JP_MODULE_STATIC_ID%Type;
                  v_Credential_ID          REST_JSON_PayLoad.JP_CREDENTIAL_ID%Type;
                  v_JSON_PayLoad_Only_YN Pkg_VPA_Codes_Value.s_Varchar2_1;
                  v_Response             Pkg_VPA_Codes_Value.s_Varchar2_Max_Length;
                  v_Limit_Credential_YN     Pkg_VPA_Codes_Value.s_Varchar2_1;
                  v_Results               JSON_Array_T;
                  v_Results_Object           JSON_OBJECT_T;
                  v_Mirror_Table          Mirror_Table%RowType;  -- varies for each process
                  v_tmp_obj               JSON_OBJECT_T;
                  v_tmp_Arr               JSON_Array_T;
                  v_Count                   Integer;
                  v_Limit_Credential_YN    := Upper(Nvl(p_Limit_Credential_YN,'N'));
                  If v_Limit_Credential_YN Not in ('Y','N') Then
                    v_Message                     := 'Error: Limit Credential must be Y or N, but is ' || p_Limit_Credential_YN;
                    Raise e_Error;
                  End if;
                  If p_Credential_Static_ID Is Null And v_Limit_Credential_YN = 'Y' Then
                    v_Message                        := 'Error: Credential ID missing, but required since will be limited by credentials.';
                    Raise e_Error;
                    v_Credential_ID         := Upper(Trim(p_CREDENTIAL_STATIC_ID));
                  End If;
                  For c_REST_JSON_PayLoad In (    Select    *
                                                From     REST_JSON_PayLoad
                                                Where     REST_JSON_PayLoad.VC_JP_MODULE_STATIC_ID_CAPS = v_Rest_Static_ID
                                                      And REST_JSON_PayLoad.JP_APP_ID                    = p_APPLICATION_ID
                                                      And REST_JSON_PayLoad.JP_Process_Date             Is Null)
                         -- Proceed with processing each JSON row.
                        If c_REST_JSON_PayLoad.VC_JP_CREDENTIAL_ID_CAPS     = v_Credential_ID Or v_Limit_Credential_YN = 'N' Then
                        v_Results := JSON_OBJECT_T.PARSE(c_REST_JSON_PayLoad.JP_JSON_PAYLOAD).get_array('results');
                        For i In 0..v_Results.get_Size() -1 Loop
                          v_Results_Object := treat(v_results.get(i) as JSON_OBJECT_T);
                          -- Populate "v_Mirror_Table" with values and merge with table
                          -- and/or do whatever processing is desired with the data.
                          -- If the JSON Content has a/an arrays/array process the array
                          -- accordingly to child mirror table(s) and/or system tables.
                          v_tmp_arr := v_Results_Object.get_array('links');
                          For j in 0..v_tmp_Arr.get_Size() -1 Loop
                            v_tmp_obj := treat(v_tmp_arr.get(j) as JSON_OBJECT_T);
                           End Loop;
                          -- Update REST_JSON_PAYLOAD processed date to indicate row is processed.
                          PKG_REST_JSON_PAYLOAD.Upd_To_Processed(     p_REST_JSON_PayLoad_ID => c_REST_JSON_PAYLOAD.REST_JSON_PAYLOAD_ID
                                                                    ,p_Error_On_No_Find         => 'Y');
                        End Loop;
                      End If;      -- Limit Credential test
                    End Loop;  -- Each REST_JSON_Payload row.
                  -- Your error and logging code here
                End Process_[REST Data Source Static ID];
  • Conclusion

  • In summary, wrapping the APEX REST Data Source with additional features and exposure through an APEX Application can enhance functionality and versatility while retaining all built in APEX functionality.

  • I hope you found this useful. Your comments and Likes are welcome and appreciated.