Photo by Kira auf der Heide on Unsplash
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.
Table of contents
- Context
- Updates and Errata
- Benefits
- Solution APEX Application
- APEX Data Source Definition:
- REST Web Service Activity Log:
- REST Load Control:
- REST JSON Payloads from API Executions:
- Solution Framework
- Result Log (REST JSON PayLoad):
- REST Load Control:
- Pertinent Source Code Excerpts:
- Load Control:
- REST Processing
- Conclusion
Context
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.
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.
Benefits
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 LOOP apex_rest_source_sync.enable( 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 LOOP APEX_REST_SOURCE_SYNC.SYNCHRONIZE_DATA ( 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 Loop 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 ,APEX_APPL_WEB_SRC_MODULES.Module_Static_ID From APEX_APPL_WEB_SRC_MODULES Order By Module_NameDynamic 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.
REST_JSON_PAYLOAD table:
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) Is -- Copyright 2018. SoftPath, Inc. All Rights Reserved. -- v_VW_REST_Load_Control VW_REST_Load_Control%RowType; v_Response CLOB; Begin 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'); If v_VW_REST_Load_Control.CD_RUN_TYPE NOT IN ('REST_JSON_PAYLOAD_ONLY','REST_PAYLOAD_SYNC_ONLY','REST_LOAD_AND_SYNC') Then 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; Exception -- 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 ) Loop 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 Is 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. Begin -- 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); Begin SELECT URL_Endpoint ,Auth_URL_Endpoint INTO v_Request_URL ,v_Token_URL 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; Exception 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; End; Begin BEGIN -- 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); Else 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); End; -- 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; End; -- -- -- -- 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; Exceptions -- 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' ) Is -- 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); Begin Begin 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 ,p_REST_Load_Control.LC_CREDENTIAL_STATIC_ID ,p_REST_Load_Control.LC_APPLICATION_ID ,p_Limit_Credential_YN; End; Exception -- 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) Is -- 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); Begin Begin 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 ,v_Null ,p_APPLICATION_ID ,'N'; End; Exception -- 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') Is -- 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; Begin 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; Else 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) Loop -- 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); v_tmp_obj.get_string('xxxx')); 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. Exception -- 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.