3 Replies Latest reply on Jan 11, 2019 3:28 AM by PaulP

    How to capture the new Primary Keys when adding rows to an IG

    PaulP

      APEX 18.2

      I have an IG which is filtered by a list of comma separated IDs which are stored in an item called P1004_CAS_UID_MULTIPLE.  ( the table has over 2 million rows)

      the where clause on my IG is

      ...where CAS_UID in ( select * from table( apex_string.split_numbers(:P1004_CAS_UID_MULTIPLE, ',')))

       

      After creating one or more rows in the IG I need to append the new PKs to :P1004_CAS_UID_MULTIPLE so when the new records are created and the IG refreshes, they appear in the query via the "where" clause.

      How do I capture the PKs?  Can I use SEQUENCE.CURRVAL somewhere?

      thanks in advance

      PaulP

        • 1. Re: How to capture the new Primary Keys when adding rows to an IG
          Niels IH

          You could get your PK sequence nextval through ajax at Row initialization of the interactive grid and then use the setValue() method of the model to set the value of your inserted row's PK column and then append this value also to P1004_CAS_UID_MULTIPLE.   When the new row is deleted you need to remove the value again from  P1004_CAS_UID_MULTIPLE

          • 2. Re: How to capture the new Primary Keys when adding rows to an IG
            John Snyders-Oracle

            HI,

            The IG DML process expects to be able to find, using the source SQL, all the rows updated or inserted. Normally you don't even need to refresh the IG or the page because the server returns the updated/inserted rows with up to date values such as for the PKs.

             

            I have not tried this but I think you can switch the IG DML process to target type PL/SQL Code (see the example in the page designer help tab for PL/SQL Code to Insert/Update/Delete). Then you can append the inserted PKs onto the page item.

             

            Regards,
            -John

            • 3. Re: How to capture the new Primary Keys when adding rows to an IG
              PaulP

              I found a solution

              Create an after submit PLSQL process (Point: Processing) after the DML IG process

              1. declare 
              2. v_currval number(15); 
              3. v_UID_MULTIPLE varchar2(2000); 
              4. begin 
              5.   select CDB_CAS_UID_SEQ.currval 
              6.   into v_currval 
              7.   from dual; 
              8.   if instr(:P1004_CAS_UID_MULTIPLE,v_currval) = 0 then 
              9.       v_UID_MULTIPLE :=:P1004_CAS_UID_MULTIPLE||','||v_currval; 
              10.   else  
              11.       v_UID_MULTIPLE :=:P1004_CAS_UID_MULTIPLE; 
              12.   end if; 
              13.    APEX_UTIL.SET_SESSION_STATE('P1004_CAS_UID_MULTIPLE',v_UID_MULTIPLE); 
              14. end

              In the process Execution Scope Attribute "For Created and Modified Rows" (See below for workaround if Execution Scope does not appear)

              NOTE: bug??

              You have to first set the process to: Interactive Grid ...(DML)

              and select the GRID name in Editable Region

              then SET IT BACK TO A PLSQL PROCESS and Execution Scope remains.

               

              PaulP