Solved

Cannot update custom field from custom event

  • 22 July 2021
  • 3 replies
  • 826 views

Userlevel 7
Badge +18

Hi All,

 

I’ve created a persistent custom field in CustomerOrderLine LU and i’m trying to update the custom field through a custom event action.

Event :

Event Action : 

 

The event is fired when the customer order line gets released but it never gets inside the FOR LOOP inside the SQL statement.

You can see that I've added an error message to check if it gets inside the FOR LOOP but it never does. However, I can update the custom field value from PLSQL Developer just by giving the parameters correctly.

Am i missing something here?

icon

Best answer by Shardha Weeratunga 23 July 2021, 10:26

View original

3 replies

Userlevel 6
Badge +12

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

Userlevel 5
Badge +6

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

Userlevel 7
Badge +18

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!

Reply