3 Replies Latest reply on Jun 17, 2019 8:32 AM by Carsten Czarski-Oracle

    New Form ARP - Should I use the column Item or page Item?

    LukeT

      I've been testing the PL/SQL feature of the new form ARP and have been retrieving the column values using the column names rather than the page Item but found out they were not always the same. 

       

      I using APEX 19.1.00.15 with Patch 29369869 applied.

       

      For a test, I used the wizard to create a simple form page (page 8) on EMP table and then change the form ARP to PL/SQ code and added this.

      (note: set EMPNO to primary key and also added P8_MSG to store results.)

       

      begin

         APEX_UTIL.set_session_state('P8_MSG',

             'APEX$ROW_STATUS=' || v('APEX$ROW_STATUS') ||

             ', EMPNO='    || v('EMPNO')  ||

             ', P8_EMPNO=' || v('P8_EMPNO')  ||

             ', ENAME='    || v('ENAME')  ||

             ', P8_ENAME=' || v('P8_ENAME') );

      end;

       

      These are the results when running a Create,Update and Delete.

       

      APEX$ROW_STATUS=C, EMPNO=, P8_EMPNO=, ENAME=HELLO, P8_ENAME=HELLO

      APEX$ROW_STATUS=U, EMPNO=7698, P8_EMPNO=7698, ENAME=BLAKE, P8_ENAME=BLAKE

      APEX$ROW_STATUS=D, EMPNO=7698, P8_EMPNO=7698, ENAME=, P8_ENAME=BLAKE

       

      I understand why the primary column (EMPNO) is NULL in the Create, but why is the non-primary column item (ENAME) null in the delete?. Is this expected?

      In the Interactive Grid ARP theses values exist (even in Delete) and I wanted to use the same PL/SQL code in the form ARP.

      Is there a reason why its different in forms? Is it safe to use the column ITEM or do we have to stick with the Page ITEM.

       

      Luke

        • 1. Re: New Form ARP - Should I use the column Item or page Item?
          Carsten Czarski-Oracle

          Hi Luke,

           

          PLSQL code for form regions is supposed to work the same way as it does for page processes: so you can (and should) use page items. Interactive Grid is different, since there are no page items there - column names are the only available reference.

           

          Column Names are also available for the Form region (as you have noticed). In PLSQL code, column name binds are available for all values which APEX uses to execute the actual DML operation. For a delete, only the primary key is needed, so APEX binds only the primary key item(s).

           

          May I ask why you need the :ENAME / :P8_ENAME value for a DELETE operation? One could load the form, change the ENAME value to e.g. JOHNDOE and click DELETE. Your PLSQL code would process JOHNDOE (which is meaningless, because it was never stored in the table). If you want to get a hold on the old column value of a table row for a DELETE operation, you should work with the RETURNING clause ...

           

          declare

             l_ename emp.ename%type;

          begin

             DELETE FROM EMP WHERE EMPNO = 7839 RETURNING ENAME INTO l_ename;

          end;

           

          This would retrieve the ENAME value which was actually stored in the table before the DELETE operation.

           

          I hope this helps

           

          Carsten

          • 2. Re: New Form ARP - Should I use the column Item or page Item?
            LukeT

            Hi Carsten,

             

            The ENAME was just a test I created, but in my actual situation I have a complex view involving multiple tables and the column I need is a user input like "reason for deleting". The user can delete in two modes, one individual record (which I use the form ARP) and a multi delete feature (which I use the IG).  My ARP code is all stored in packages and I tend to use the v() function to grab data rather than passing parameters via bind variable. I was hoping to use the column name (rather than the page item name) so my package would work regards what page or process called it.

             

            Thanks

            Luke

            • 3. Re: New Form ARP - Should I use the column Item or page Item?
              Carsten Czarski-Oracle

              Hi Luke,

               

              so yes, in this case you would need to use item names ...

               

              Best regards

               

              -Carsten