Ora 00001 on collection

[michele-pauluzzi]

I'm testing the main application, i will try to replicate it on the test, but it will be difficult. Still trying to understand how to get the error. I have a Lov with a dynamic action execute pl/sql set another lov, only the first time i get the error

Nice work with the new debug system.

[michele-pauluzzi]

Same error on submit page of a null lov

[bostrowski]

@michele-pauluzzi Without reproduced error at apex.oracle.com its like guessing. At least describe step by step scenario with detailed info:

• dynamic actions list with types, conditions etc
• dynamic action true actions code / screenshot
• how many fields are affected (types, source etc)

It is hard to decode the language I don't speak ;-) I think there is simple answer for this issue but I need at least detailed implementation to inspect.

[michele-pauluzzi]

I know, but i'm still trying to reproduce the step to get the error. For now it's a bit of random. For example i got a deadlock on the collection one time simply changing the selection on the lov over and over again,

[michele-pauluzzi]

I think there is an asynchronous update of the collection and the lov. The dup-key and the deadlock maybe are correlated to that?

[bostrowski]

@michele-pauluzzi As I wrote, I need to see what events you are listening to via dynamic action and what you are executing in Execute PL/SQL code. Right now its guessing for me.

Even if you are not able to provide valid scenario to reproduce the issue then recreate those components at apex.oracle.com using dept / emp and let me see the implementation.

I would say it's not related to the plugin but to your dynamic action implementation. I need to see the configuration to say more.

[michele-pauluzzi]

Related to the asynchronous update,

Go to lov page and press 1. Empty button repeated times. Sometimes the value will remain on the lov.

The button have 2 actions, set to 10, set to null.

Workspace: mpgpi User: demo password: demodemo

[bostrowski]

ok, but there is no collection involved. In your first comment, you have put screenshot with the error which involves collections

APEX_190200.WWV_FLOW_COLLECTIONS_UK

is it related to the example page? Because I think those are two separate scenarios.

In case of error regarding collection. this error is usually raised when a collection is not yet created (not all members are populated) and you want to create it again.

In case of remaing tag after executing your dynamic actions This is not related to the plugin and should not be considered as the plugin bug. In your demo page you have bound items via

• cascading lov functionality,
• setting value from dynamic action which results in performing ajax to retrieve display value
• nulling items via execute pl/sql code

I think it is very abstract example which purpose I can't see. Yes, you are performing multiple ajaxes. What do you want to achieve?

Please clarify the issue because right now I don't see the plugin bug :-)

[michele-pauluzzi]

I'm just trying to replicate the problem and see if it's related to the asynchronous update or not,

In the first image the item Luogo nascita, where there is a double tag, is set via pl/sql and it is in cascading lov with Data Nascita and Forza Luogo Nascita, the two previous items.

[michele-pauluzzi]

In this example you can see how the lov and the collection remain with the value selected. The simple lov have a dynamic action pl/sql to set to null the other two lov when the value change, with the option suppress change event enable.

[michele-pauluzzi]

I found out the problem displayed in the last image. (A) Simple lov --> (B) plugin lov with cascading option on (A) --> (C) another plugin lov with cascading option on (B).

Removed the cascading option to (C) on (B) and the problem was solved.

For the errors in the first two post: Item with a dynamic action on change --> execute pl/sql set item value (A) (B) (C)--> (C) plugin lov with cascading option on (A) (B), --> code of the plugin get fired three time and you get the primary key error on the collection.

still trying to replicate on test workspace.

[michele-pauluzzi]

the dup_val_on_index error can be fixed by changing the line

APEX_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION( v_collection_name ); 

to

begin
APEX_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION( v_collection_name );    
exception
  when dup_val_on_index then null;
end;

same for the apex_collection.add_member.

[michele-pauluzzi]

This are all different lov type, classic, select2 and enhanced. They are all on cascading with the previous one, "Motivo" with "Tipo pratica" "Utente" with "Tipo pratica" and "Motivo" and so on. There isn't a dynamic action, only lovs with the cascading option.

As you can see, the update on the enhanced lov type is triggered several times, one for each change on the previous element. You can see the same for the select2 lov type at the end of the video.

This can trigger ora 00001 in the collection at APEX_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION or APEX_COLLECTION.ADD_MEMBERS

adding

exception
  when dup_val_on_index then null;

fix it

[bostrowski]

@michele-pauluzzi Thank you for this detailed analysis. Before I add this exception I need to check JavaScript regarding performing AJAX calls in case of multiple parent cascading lov items. What I can see right now is that I haven't implemented canceling AJAX in case of cascading LOV. Will get back to you as soon as I can tell more.

edit: I was able to reproduce the issue using

• emp and dept tables,
• and small forced wait in pl/sql (APEX_UTIL.PAUSE(1); at line 971)
• 3 items:
  • dept (master item) - native select list
  • employee (dependent on "dept" via deptno column) - native select list
    • employee (dependent on "employee" via mgr column and dependent on "dept" via deptno column) - Pretius Enhanced LOV item
• scenario:
  • on page load, select value in the plugin
  • change value in dept 2 times so 2 cascading lov request are sent

The fix is on the way but I want to be sure the exception is the best solution for it.