Fetching and Posting JSON using Apex

on

Requirement

Hypothetical method for storing a JSON CLOB in an Apex app region and collection

You want to store information within an app without migrating and tables or data. In this case a list of quality control QA Yes/no attributes for the app.

Benefit is that each app has this data built into it and will thus be migrated with it automatically.


Note: Writing to a region was chosen because it's a CLOB object. There is no API for this, so use at your own risk. In my case I needed to use the power of an apps-owned package (authid definer, granted to my apex user.

Solution

  1. JSON CLOB is stored as a hidden region
  2. DA on page load fetches CLOB and writes to a collection
  3. Collection is exposed as an editable interactive grid
  4. IG editing process updates collection, then writes collection to region.


(1) JSON CLOB is stored as a hidden region



Hidden region on a page.

(2) DA on page load fetches CLOB and writes to a collection





declare

v_region_id number;

begin

    begin
        select region_id
        into v_region_id
        from APEX_APPLICATION_PAGE_REGIONS
        where application_id = 220 /*:APP_ID*/
        and page_name = 'BARRY- QA POC'
        and region_name = 'Source';
    exception
        when no_data_found then
            raise_application_error(-20000,'Fetch: No QA Region set up');
    end;

    XXAPEX_EBS_APEX_QA_UTIL_PK.fetch_qa_values_to_collection(p_app_id     => 220 /*:APP_ID*/, 
                                                              p_session_id => :APP_SESSION,
                                                              p_region_id => v_region_id);

end;  

DB Code:

procedure fetch_qa_values_to_collection(p_app_id                       in     number,
                                       p_session_id                    in     number,
                                       p_region_id                     in     number) is

l_ws_id number;

begin
    select max (workspace_id)
    into l_ws_id
    from apex_applications
    where application_id = p_app_id;

    wwv_flow_api.set_security_group_id (l_ws_id);
    apex_application.g_flow_id := p_app_id;
    apex_application.g_instance := p_session_id;                                                                                                              

    apex_collection.create_or_truncate_collection(p_collection_name => 'QA_VALUES');

    for v_data in (select app_id, page_id, qa_type, qa_yes_no
                  from 
                        (select plug_source
                        from APEX_230100.WWV_FLOW_PAGE_PLUGS
                        where id = p_region_id),          
                  json_table( plug_source ,'$[*]'  columns (APP_ID,PAGE_ID,QA_TYPE,QA_YES_NO))) loop

      APEX_COLLECTION.ADD_MEMBER (p_collection_name => 'QA_VALUES', 
           p_c001 => v_data.app_id,
           p_c002 => v_data.page_id,
           p_c003 => v_data.qa_type,
           p_c004 => v_data.qa_yes_no
          );  

    end loop;  

exception
      when others then
            raise_application_error(-20000,'XXAPEX_EBS_APEX_QA_UTIL_PK.fetch_qa_values_to_collection: '||sqlerrm);

end fetch_qa_values_to_collection;      

(3) Collection is exposed as an editable interactive grid







(4) IG editing process updates collection, then writes collection to region.


PL/SQL Code:

declare

   v_region_id number;

BEGIN
    APEX_COLLECTION.UPDATE_MEMBER_ATTRIBUTE (
        p_collection_name => 'QA_VALUES',
        p_seq => :SEQ_ID,
        p_attr_number => 4,
        p_attr_value => :QA_YES_NO);

    begin
        select region_id
        into v_region_id
        from APEX_APPLICATION_PAGE_REGIONS
        where application_id = 220 --:APP_ID
        and page_name = 'BARRY- QA POC'
        and region_name = 'Source';
    exception
        when no_data_found then
            raise_application_error(-20000,'Save: No QA Region set up for app: '||:APP_ID);
    end;

    XXAPEX_EBS_APEX_QA_UTIL_PK.write_collection_to_qa_values (p_app_id     => 220 /*:APP_ID*/, 
                                                              p_session_id => :APP_SESSION,
                                                              p_region_id => v_region_id);    

            
END;

DB Code:
procedure write_collection_to_qa_values (p_app_id                       in     number,
                                       p_session_id                    in     number,
                                       p_region_id                     in     number) is

v_clob clob;
l_ws_id number;

begin

    select max (workspace_id)
    into l_ws_id
    from apex_applications
    where application_id = p_app_id;

    wwv_flow_api.set_security_group_id (l_ws_id);
    apex_application.g_flow_id := p_app_id;
    apex_application.g_instance := p_session_id; 

   select JSON_ARRAYAGG(json_object('APP_ID' value app_id,
                                     'PAGE_ID' value page_id,
                                     'QA_TYPE' value qa_type,
                                     'QA_YES_NO' value QA_YES_NO format json))
   into v_clob                                     
                  from (select c001 app_id,
                               c002 page_id,
                               c003 qa_type,
                               '"'||c004||'"' qa_yes_no
                        from apex_collections
                        where collection_name = 'QA_VALUES');

   update APEX_230100.WWV_FLOW_PAGE_PLUGS
   set plug_source = v_clob
   where id = p_region_id;

end write_collection_to_qa_values;

Comments

Post a Comment