Updateable Oracle SQL Report using Oracle ApEx 4.1

Question

Basically, I have an SQL Updateable report based on the following sample SQL query:

select A, B, C from my_table;

Based on this report, I have made column B a SELECT LIST based on a Named LOV - I have also assigned this column an id of "lov_select"

Column C here is also a text area

My question and unsure how to do this, is, as this report can vary in rows, assuming this report returns 3 rows, based on the value selected from the Column B - select list, I want to use this value in column B select list to retrieve another value from a table and then return this value into Column C text are field, alongside this select list value used.

I would like to do this via a Dynamic Action.

For example:

Column A                Column B (select list LOV)   Column C
----------------------- ---------------------------- ------------------------------------
Row 1                   NAME1                        returns 5                       
Row 2                   NAME2                        returns 6
Row 3                   NAME3                        returns 7

So if I change row 2 Column B and select "NAME2", I then want to execute a query using the value "NAME2" which would return a value of 6, which I then would like populated alongsde NAME2 into Column C.

Answer 1

Manual tabular form with source:

select 
apex_item.text(1, empno) empno,
apex_item.text(2, ename) ename,
apex_item.text(p_idx => 3, p_value => deptno, p_size => 4, p_maxlength => 2, p_attributes => 'class="deptno"') deptno,
apex_item.text(4, null) dname
from emp

2 possible solutions:

1. Dynamic action with ajax process

   • Event: Change
   • Selection Type: jQuery selector
   • jQuery selector: .deptno
   • Condition: no condition

   True action: Execute javascript code

   • Fire on page load: unchecked

     var lFetchFor = $(this.triggeringElement).val(), 
         lTarget = $(this.triggeringElement).closest('tr').find('input[name="f04"]');
     
     $.post('wwv_flow.show', 
            {"p_request"      : "APPLICATION_PROCESS=fetch_value",
             "p_flow_id"      : $v('pFlowId'),
             "p_flow_step_id" : $v('pFlowStepId'),
             "p_instance"     : $v('pInstance'),
             "x01"            : lFetchFor}, 
            function(data){
               lTarget.val(data);
            });
     

   • No affected element type

2. Dynamic actions only and hidden items

   • Event: Change
   • Selection Type: jQuery selector
   • jQuery selector: .deptno
   • Condition: no condition
   • True action: Set value
     • Set type: Javascript expression
     • Javascript expression: $(this.triggeringElement).val()
     • Fire on page load: unchecked
     • Affected elements:
       
       • Selection type: Item(s)
       • Item(s): P37_FIND_DEPTNO
   • True action: Set value
     
     • Set type: SQL statement
     • SQL Statement: select dname from dept where deptno = :P37_FIND_DEPTNO;
     • Page items to submit: P37_FIND_DEPTNO
     • Fire on page load: unchecked
     • Affected elements:
       
       • Selection type: Item(s)
       • Item(s): P37_FIND_DEPTNO_RESULT

   • True action: Execute javascript code

     • Code:

       $(this.triggeringElement).closest('tr').find('input[name="f04"]').val($v ('P37_FIND_DEPTNO_RESULT'));
       

     • Fire on page load: unchecked

     • No affected element type
   • 2 hidden items, value protected set to No
     
     • P37_FIND_DEPTNO
     • P37_FIND_DEPTNO_RESULT

Both take care of fetching the value, just depends on how you'd rather solve it.