Updateable Oracle SQL Report using Oracle ApEx 4.1

1
votes

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.

1
plsqloracle-apex
the skillbuilders superlov would work great if this were single record mode, but there is no real tabular form alternative (there is one but it is poorly supported and i don't find it elegant). When your report has been generated on page load, column C already has values? And by saying "then want to execute a query using the value "NAME2" ", do you mean actually using "NAME2" (ie the display value) and not the return value to execute the query for C? – Tom
Sorry Tom, I actually mean the return value which here is the same as the display value. So as I change the value in the select list, go off and execute a pl/sql query that retrieves a default value from a table based on the select list return value and display this default value in Column C. – tonyf

1 Answers

2
votes

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.