FRM-40735: post-insert trigger raised unhandled exception ora-01722

Question

I create POST-INSERT Trigger on Block when I am getting this error on Oracle Forms 11gR2

"FRM-40735: post-insert trigger raised unhandled exception ora-01722"

POST-INSERT Trigger Code:

Insert into we_group (GROUP_ID, GROUP_SIZE, NRSP_STATUS, GROUP_RECEIVED)
Select DISTINCT GROUP_ID, ('Select COUNT(*) from we_group_hof_k'), 
nrsp_status, sysdate
from we_group_hof_k;

commit_form;

How to solve this problem?

Answer 1

Remove ' to prevent number to char convertion

 Select DISTINCT GROUP_ID, (select COUNT(*) from we_group_hof_k), 
 nrsp_status, sysdate
 from we_group_hof_k;

Answer 2

Consider using analytic version of the COUNT function (if Forms version you use supports it; 10g doesn't, I can't tell about 11g):

INSERT INTO we_group (GROUP_ID,
                      group_size,
                      nrsp_status,
                      group_received)
   SELECT DISTINCT GROUP_ID,
                   COUNT (*) OVER (ORDER BY NULL),
                   nrsp_status,
                   SYSDATE
     FROM we_group_hof_k;

Answer 3

It evidently seems ORA-01722 raises due to an attempt to insert a quoted string

( 'Select COUNT(*) from we_group_hof_k' ) into a numeric column( GROUP_SIZE ).

So, first of all you need to get rid of those quotes, and even whole sub-select, since you already try to select from the same table in the main query, and just consider to include a group by clause instead:

Insert Into we_group(group_id, group_size, nrsp_status, group_received)
  Select group_id,Count(1),nrsp_status, sysdate
    From we_group_hof_k
   Group By group_id,nrsp_status;

Lastly do not use a commit or commit_form inside a POST-INSERT trigger, that usage is considered as illegal restricted there.