Search code examples
javascriptoracleoracle-apexapex

Oracle Apex - Dynamic action error: Ajax call returned server error ORA-20987

I have a form and one input item must be unique. (It is primary key.) When I click to add, I get an error message. It is OK, but I want to get this error message when I set the item's value not when I filled the whole forms and click on the ADD button.

For example I have a COUNTRY table with a COUNTRY_ID column. The COUNTRY_ID column must be unique.

  1. I created a hidden page item: C_ID_CHECK

  2. I created a dynamic action:

Event --> change

Selection type --> Item

Item --> P6_COUNTRY_ID

  1. I created a true dynamic action. Execute Server-side Code --> pl/sql
DECLARE pk_error   NUMBER;
   BEGIN
      SELECT 1 INTO pk_error
         FROM COUNTRY
         WHERE COUNTRY_ID = :P6_COUNTRY_ID;
     :P6_C_ID_CHECK := 'Y';
    
EXCEPTION
   WHEN others THEN
      :P6_C_ID_CHECK := 'N';
END;

Items to submit --> :P6_COUNTRY_ID

Items to Return --> :P6_C_ID_CHECK

So if the new COUNTRY_ID already exsist, then C_ID_CHECK value is Y, otherwise N.

  1. I created another true dynamic action. Action --> Alert

Client-side Condition:

Type --> Item = Value

Item --> P6_C_ID_CHECK

Value --> Y

So if the first dynamic action put Y value to the P6_C_ID_CHECK item, then this dynamic action raise an alert if the value is Y.

When I tried it I got this message:

Ajax call returned server error ORA-20987: APEX - Attempt to save item P6_COUNTRY_ID in session state during show processing. Item protection level indicates: Item may be set when accompanied by a "session" checksum.

No checksum was passed in or the checksum passed in would be suitable for an item with protection level "(No checksum was provided)".

Note: End users get a different error message. - Contact your application administrator. for ajax_set_session_state.

What could go wrong? Is this a good way to get immediate notification if I try to insert wrong data or is there any other / easier way?

Solution

  • This is not the correct way of handling primary keys in apex. Always have the database handle your primary keys, either by (1) using a trigger and a sequence - this is the old way or (2) using an identity column - this is the more modern way.

    Then in your application, let the native form processes do the work for you. Mark the primary key item in your form as "Primary Key" and you're done.

    Test this out in a sample app on the emp/dept sample data. Create a new page of type Interactive Report and check "include form". Use "EMP" as the table. This will generate a report and form that handles both creation of new records or updating existing records. There never is a need for a check if the PK is unique.

    Here is a very complete blog from the apex dev team explaining the form functionality.