My Oracle Support Banner

Intermitent ORA-00604: and ORA-01031: Insufficient Privileges, using RESTFul Services (Doc ID 2439386.1)

Last updated on FEBRUARY 11, 2020

Applies to:

Oracle Database Cloud Service - Version N/A to N/A [Release 1.0]
Oracle REST Data Services - Version 17.4 to 18.2
Information in this document applies to any platform.

Symptoms

ORDS in Database Cloud Service (DBCS / DBaaS)

When attempting to execute any RESTFul Service, the the following error occurs.

Caused by: Error : 604, Position : 0, Sql = select nvl(h.items_per_page,m.items_per_page) items_per_page, t.etag_type, t.etag_query, h.source_type, cursor(select p.name, p.bind_variable_name, p.source_type,p.access_method, p.param_type from user_ords_parameters p where p.handler_id = h.id) parameters, h.source from user_ords_modules m, user_ords_templates t, user_ords_handlers h where m.status = 'PUBLISHED' and t.id = h.template_id and m.id = t.module_id and h.id = :1 , OriginalSql = select nvl(h.items_per_page,m.items_per_page) items_per_page, t.etag_type, t.etag_query, h.source_type, cursor(select p.name, p.bind_variable_name, p.source_type,p.access_method, p.param_type from user_ords_parameters p where p.handler_id = h.id) parameters, h.source from user_ords_modules m, user_ords_templates t, user_ords_handlers h where m.status = 'PUBLISHED' and t.id = h.template_id and m.id = t.module_id and h.id = ?,

Error Msg = ORA-00604: error occurred at recursive SQL level 1
ORA-01031: insufficient privileges


The issue can be reproduced some times with the following steps:
1. Try to execute any RESTFul Service
2. A 500 error message appears on the screen and error above appears in the ORDS related logs.


Other Information

The problem can also be reproduced in by executing the SQL query directly from the schema using SQLPlus or SQL Developer. For example:

SELECT
nvl(h.items_per_page,m.items_per_page) items_per_page,
t.etag_type,
t.etag_query,
h.source_type,
CURSOR (
SELECT
p.name,
p.bind_variable_name,
p.source_type,
p.access_method,
p.param_type
FROM
user_ords_parameters p
WHERE
p.handler_id = h.id
) parameters,
h.source
FROM
user_ords_modules m,
user_ords_templates t,
user_ords_handlers h
WHERE
m.status = 'PUBLISHED'
AND t.id = h.template_id
AND m.id = t.module_id

Changes

 De-Installed the ORDS that comes with the DBCS and installed the latest version from OTN on a middle-tier machine

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.