I assume you have moved the error outside the FOR loop, and it fires?

If so, that means the FOR is coming back with no data, correct (my PL/SQL is not that strong). If you error_sys all the parameters to make sure they are what you think they are, and then run the query with those same values, you get data?

You can also try the non-CFV view just to see if you get data. Or a different query entirely using hard-coded values or something until you find out what is making the query come back empty… Tough to debug without brute-fore trial and error, as nothing seems glaringly wrong in the code.

Joe Kaufman

Hi @Himasha Kapugeekiyanage 

I tried the same without the “Pragma autonomous_transaction;”.

Then the error message ‘###############22’ (according to my code below) popped up.

Declare

cursor get_rec is
select * from customer_order_line_cfv t
where t.order_no = '&NEW:ORDER_NO'
and t.line_no = '&NEW:LINE_NO'
and t.REL_NO = '&NEW:REL_NO'
and t.LINE_ITEM_NO = '&NEW:LINE_ITEM_NO'; 

--Pragma autonomous_transaction;

BEGIN

for rec_ in get_rec loop

Error_sys.System_general('###############22');

end loop;

END; 

With the Pragma autonomous_transaction, this PLSQL block is getting executed in a different transaction, independently from the main transaction. This could be the reason with Pragma autonomous_transaction the error message from the PLSQL block is not popping up with COL save.

Please check whether your intended behavior is achieved after removing the error message in your PLSQL block, with COL save.

/Shardha

Hi @Shardha Weeratunga,

I got below error previously when running the event and to solve the issue i added the pragma block.
ORA-04091: table is mutating, trigger/function may not see it

Removing the Pragma block resolved the issue!

Thanks a lot!