CODEWITHSUNDEEP
sqldatabaseoracle-databasereadonlytemp
logan
logan

Reputation: 8366

Derived query throws error ORA-00604: error occurred at recursive SQL level 1

My inner sub query is working fine which starts from line 3 in below query. But when i do a query on top of it , it shows error ORA-00604: error occurred at recursive SQL level 1

  SELECT PU , CA , COUNT(CA) CNT FROM
      (
        SELECT CASE
        WHEN DP.STRVAL = 'A' then 'A1' 
        ELSE 'OTHERS' END PU,
        (CASE WHEN UPPER(CP.STRVAL) = 'YES' THEN 'YES' ELSE 'NO' END) CA 
        FROM DM.MAP AP
        LEFT OUTER JOIN DM.MAC BP ON AP.ISSUEID  = BP.ISSUE AND BP.CF = 'RD'
        LEFT OUTER JOIN DM.MAC CP ON AP.ISSUEID  = CP.ISSUE AND CP.CF = 'CA'
        LEFT OUTER JOIN DM.MAC DP ON AP.ISSUEID  = DP.ISSUE AND DP.CF = 'PU'    
      ) AB 
    GROUP BY PU, CA

above query is throwing error at line which is having first left join

ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database open for read-only access
00604. 00000 -  "error occurred at recursive SQL level %s"
*Cause:    An error occurred while processing a recursive SQL statement
           (a statement applying to internal dictionary tables).
*Action:   If the situation described in the next error on the stack
           can be corrected, do so; otherwise contact Oracle Support.
Error at Line: 15 Column: 19

I dont see any issues with this query but not sure why its throwing error.

Upvotes: 1

Views: 1521

Answers (1)

Lalit Kumar B
Lalit Kumar B

Reputation: 49092

Possible issue is with:

ORA-16000: database open for read-only access

When you are applying the JOIN and GROUP BY, it might need some TEMP space. And you do not have the privilege to access the temp space.

Check whether you have a temp tablespace.

Also,

DM.MAP

Can you try the query by logging in as the DM user.

If it is a locally managed temp tablespace, then with proper privileges, there shouldn't be an issue. But, I have this strong feeling that you do not have the required privileges.

My suggestion, consult your DBA, there might be vital things which your DBA might be able to address.

Upvotes: 3

Related Questions