Then create one more dynamic action (Execute PL/SQL code) on bill month LOV (before Refresh DA) to update the table the grid based on. Below is an example:

Update yourGridTable
   set collection_amount = service_charge
where yourGridcondition;

Then after the refresh, it will show the collection amount.

In Oracle Apex, upload data wizard logs error information in the collection and creates the report using the collection.

So it is better to modify the query to replace the error column usually (c048) using the case statement. Below is an example:

In the “Data Load Results” page, modify the failed records region query as following:

select n001 as row_num,
wwv_flow_lang.system_message( 'DATA_LOAD.' || c049 ) as action,
case when substr(c048, 1, 14) = '-1 : ORA-00001' then
'Duplicate row'
else 
c048
end error,
c001, c002, c003,
c004, c005, c006,
c007, c008, c009,
c010, c011, c012,
c013, c014, c015,
c016, c017, c018,
c019, c020, c021,
c022, c023, c024,
c025, c026, c027,
c028, c029, c030,
c031, c032, c033,
c034, c035, c036,
c037, c038, c039,
c040, c041, c042,
c043, c044, c045
from apex_collections
where collection_name = 'LOAD_CONTENT'
and c047 in ('FAILED') 
order by seq_id

In the above query, you can see, I am checking the first 14 characters of the C048 column, that if the value is ‘-1 : ORA-00001’, then replacing the error message with Duplicate Row. Similarly, you can replace any other errors too.

The following is the output now:

Hi Ratana,

1 – First create one dummy hidden  PAGE_ITEM

2 – Create Dynamic Action

1.    Create a dynamic action Event Type as a Get Focus
2.      Selection Type  Column
3.      Region  Your region name     ( My_Region)
4.      Column    Your column name  (Line_Number)
5.     Client_Side Condition   Item/Column is null
6.     Component Type   Column
7.     Column    (Line_Number)

(1)  Action

•     Set Value
•     PL/SQL Expression  _____  NVL(:P1_DUMMY,0)+1
•     Item to Submit  ____  P1_DUMMY
•     Affected Elements 
•    Selection Type  Column
•    Column   Line_Number
•    Fire on Initialization    Yes
•    Stop Execution On Error  Yes
•    Wait For Result  Yes

(2) Action

•     Set Value
•     PL/SQL Expression _____  NVL(:P1_DUMMY,0)+1
•     Items to Submit ____  Null
•     Affected Elements 
•    Selection Type
•     Item(s)  P1_DUMMY
•    Fire on Initialization    Yes
•    Stop Execution On Error  Yes
•    Wait For Result  Yes

Below is an example to print CLOB data using PL/SQL dynamic content in Oracle Apex.

Create a region as PL/SQL dynamic content and add the following code in the PL/SQL code section:

declare
v_clob clob;
n_amt INTEGER := 4000;
n_pos INTEGER := 1;
v_buf VARCHAR2(4000);
begin

Select yourClobColumn into v_clob
  from yourTable where col1 = :px_item;

loop
begin
 dbms_lob.read(v_clob, n_amt, n_pos, v_buf);
 n_pos := n_pos + n_amt;
 htp.p(v_buf); 
 exception
 when no_data_found then
   exit;
end;
end loop;
END;

You can create validations on individual fields to check if that already exists. But if you need to check on a combination of fields, you can do this by creating a process before the form process. Follow these steps:

Click on the process tab and then create a process and put it before for the form save process. Then you can add your PL/SQL code to check if a record exists. The following is an example:

Declare
   v_exists varchar2(1);
   e_data_found exception;
Begin
if :p2_job = 'MANAGER' then
   Select 'Y' into v_exists from emp
     where deptno = :p2_deptno 
           and job = 'MANAGER';

   raise e_data_found;
end if;

exception
   when no_data_found then
      -- everything is good
      null;
   when others then
    apex_error.add_error ( p_message => 'Job Manager already exists in this department', 
p_display_location => apex_error.c_inline_in_notification); 
      raise;
End;

This will not allow the save until the error is resolved.