An unusual error from APEX and ORDS

Two SQL statements on tables in the ORDS_METADATA schema failed with ORA-00604 and ORA-00942. The select statements were printed to the ORDS log together with:

ORA-00604: error occurred at recursive SQL level 1 
ORA-00942: table or view does not exist

The two statements where almost the same, formatted below:

SQL_ID: 3zuzjar8zcf9g

select nvl(h.items_per_page,m.items_per_page) items_per_page, t.etag_type, 
    t.etag_query, h.source_type, h.mle_env_name, m.origins_allowed, 
    cursor(
        select p.name, p.bind_variable_name, p.source_type,
            p.access_method, p.param_type 
        from ORDS_METADATA.user_ords_parameters p 
        where p.handler_id = h.id) parameters, 
    h.source 
from ORDS_METADATA.user_ords_modules m, 
    ORDS_METADATA.user_ords_templates t, 
    ORDS_METADATA.user_ords_handlers h 
where m.status = 'PUBLISHED' 
    and t.id = h.template_id 
    and m.id = t.module_id 
    and h.id = :1

SQL_ID: 1pvaudrr9jh3w

select nvl(h.items_per_page,m.items_per_page) items_per_page, t.etag_type,
    t.etag_query, h.source_type, m.origins_allowed, 
    cursor(
        select p.name, p.bind_variable_name, p.source_type,
            p.access_method, p.param_type
        from ORDS_METADATA.user_ords_parameters p 
        where p.handler_id = h.id) parameters, 
    h.source
from ORDS_METADATA.user_ords_modules m, 
    ORDS_METADATA.user_ords_templates t, 
    ORDS_METADATA.user_ords_handlers h 
where m.status = 'PUBLISHED' 
    and t.id = h.template_id 
    and m.id = t.module_id 
    and h.id = :1

If you have access to My Oracle Support you can check KB170982 and KB927081. KB170982 is dated November 3, 2025, and mentions two grants that need to be given to “your_user”, whatever that is. KB927081 is dated November 5 this year, and does not refer to the previous one though the symptom is the same. Last KB mentions grant select on four tables in the ORDS_METADATA schema:

grant select on ords_metadata.user_ords_modules to PARSING_SCHEMA;
grant select on ords_metadata.user_ords_templates to PARSING_SCHEMA;
grant select on ords_metadata.user_ords_handlers to PARSING_SCHEMA;
grant select on ords_metadata.user_ords_parameters to PARSING_SCHEMA;

These are the same tables that occur in the select statement. I was a bit confused on why error message said error occurred at recursive SQL level 1, and then, ORA-00942: table or view does not exist as if some of the underlying tables did not exist. I decided to use DBMS_UTILITY.EXPAND_SQL_TEXT to find the underlying tables and grant select directly on them.1 From the output I identified tables and granted select on them:

grant select on ORDS_METADATA.ORDS_SCHEMAS to foo;
grant select on ORDS_METADATA.ORDS_PARAMETERS to foo;
grant select on ORDS_METADATA.ORDS_MODULES to foo;
grant select on ORDS_METADATA.ORDS_TEMPLATES to foo;
grant select on ORDS_METADATA.ORDS_HANDLERS to foo;

From a SQL trace file where the SQL statemented actually succeeded I could only find parsing on dep=1 by uid 0 (SYS) against internal tables like obj$, the problem had to be elsewhere. Parsing on level 2 (dep=2) though included the tables listed above.2

In hindsight I should have enabled trace on the proper parsing (event 10053), or the ORA-604 itself, or perhaps on the SQL compiler as shown in this blogpost at oracle-base.com

But the problem came back later. I then got the idea to check PARSING_SCHEMA in gv$sql for these two SQL statements and saw that in some cases the user APEX_PUBLIC_ROUTER was used. I repeated the grants above to APEX_PUBLIC_ROUTER which seems to have solved the problem. It is possible that the original four grants on the USER-views are sufficient. I will update this post next time this happens in another database.


  1. This is not normal procedure; it is perfectly normal to have select privilege on a view, but not on the underlying tables - that is how we hide information and only reveal what we want to specific users and roles. ↩︎

  2. I was not sure if level 1 in the error message actually mapped to dep=1 in the trace file, so I did some quick research with an AI, but it did not find any good references on it. But it came up with this: The level identifies the recursive cursor that failed back to the layer above it. That isn’t necessarily the statement where the real error happened. There’s a published example in MOS Doc 2384597.1, where the APEX installation log shows ORA-00604: error occurred at recursive SQL level 1, ORA-12899: value too large for column “SYS”.”AUDIT_DDL”.”NAME” … ORA-06512: at line 6. That’s a DDL trigger: The CREATE statement is dep=0. The trigger’s PL/SQL block runs at dep=1. The INSERT INTO AUDIT_DDL inside the trigger runs at dep=2, and that’s where ORA-12899 is raised. The error message still says level 1. PL/SQL passes the dep=2 error up as an unhandled exception, and ORA-00604 is raised when the dep=1 trigger block fails back to the dep=0 statement. So the level does match dep= exactly, but it points at the dep=1 cursor whose failure caused ORA-00604. The statement that actually failed can be deeper, so look for the ERROR #n:err= lines at dep=1 and below it. ↩︎