Skip to Main Content
  • Questions
  • Error on insert record for PL SQL of Dynamic Action

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Wright.

Asked: March 04, 2020 - 12:16 am UTC

Last updated: March 04, 2020 - 10:54 am UTC

Version: 5.1.0.00.45

Viewed 1000+ times

You Asked

I've got a small form to add data to lists for when people want to add them (not using Interactive grid because it's fairly long at this point).
The dynamic action runs an Execute PL/SQL Code, submits three items (the three values) and returns one item (the response).
However, whenever I test it, it brings up:

"
Ajax call returned server error: ORA-20876: Stop APEX Engine For Execute PL/SQL Code
" as an error?

Here's the PL/SQL I'm using:

DECLARE
     v_count NUMBER(10);
BEGIN
    SELECT COUNT(*) INTO v_count
    FROM TBL_REF_LIST_1
    where LIST_CODE = :P7_LIST_CODE
    OR LIST_DESC = :P7_LIST_DESC
    OR LIST_CATEGORY = :P7_LIST_CATEGORY;

     IF v_count > 0 THEN
        :P7_RESPONSE_VALUE := 'List Item exists with one or more of the listed criteria';

    ELSE
        Insert INTO TBL_REF_LIST_1
        (LIST_CODE, LIST_DESC, LIST_CATEGORY, ACTIVE)
        VALUES
        (:P7_LIST_CODE, :P7_LIST_DESC, :P7_LIST_CATEGORY, 1);
        :P7_RESPONSE_VALUE0 := 'List Item loaded';

    END IF;

END;


What's wrong with what I've got here? Is it a permissions thing?

and Chris said...

I'm no APEX expert, so this is an educated guess...

There's a bug associated with this error that's fixed in 19.1

https://docs.oracle.com/en/database/oracle/application-express/19.1/htmrn/index.html#HTMRN-GUID-32C4974D-FBCE-4B23-A72D-934E4248C78C

So you could try upgrading to that.

You could also try asking on the APEX forums

https://community.oracle.com/community/groundbreakers/database/developer-tools/application_express

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

APEX

Keep your APEX skills fresh by attending their regular Office Hours sessions.