get error on add CONSTRAINT to table oracle-CodePudding

i use oracle 19c

the table tbl_users is exist and can other constraint to table this problem happen after i drop json constraint from table and want to add again

when running this code

ALTER TABLE TBL_USERS
ADD CONSTRAINT TBL_USERS_JSON_chk CHECK 
(CUSTOM_DATA IS JSON
AND REQUESTS_STATUS IS JSON
AND LOCATION IS JSON
AND MULTI_ACCESS IS JSON)
ENABLE;

get this error

Error report -
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
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.

I can add other CONSTRAINT to the table but just want to add JSON CONSTRAINT to get this error

SQL* OUTPUT

SQL> SELECT COUNT(*) FROM ECODB.TBL_USERS;

  COUNT(*)
----------
     24364

SQL> ALTER TABLE ECODB.TBL_USERS
  2  ADD CONSTRAINT TBL_USERS_JSON_chk CHECK
  3  (CUSTOM_DATA IS JSON
  4  AND REQUESTS_STATUS IS JSON
  5  AND LOCATION IS JSON
  6  AND MULTI_ACCESS IS JSON)
  7  ENABLE;
ALTER TABLE ECODB.TBL_USERS
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist

CodePudding user response：

Oracle said it all:

ORA-00942: table or view does not exist

You can't add a constraint o a non-existing table. Looks like you

didn't create it
dropped it
mistyped its name

Note that not all Oracle database versions support the JSON check. For example, in 11g it won't work:

SQL> select * From v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL> create table tbl_users
  2    (custom_data varchar2(20),
  3     requests_status varchar2(20)
  4    );

Table created.

SQL> alter table tbl_users add constraint tbl_users_json_chk check
  2    (    custom_data     is json
  3     and requests_status is json
  4    );
  (    custom_data     is json
                          *
ERROR at line 2:
ORA-00908: missing NULL keyword


SQL>

In 12c and above it'll work:

SQL> select * From v$version;
<snip>
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
<snip>

SQL> create table tbl_users
  2    (custom_data varchar2(20),
  3     requests_status varchar2(20)
  4    );

Table created.

SQL> alter table tbl_users add constraint tbl_users_json_chk check
  2    (    custom_data     is json
  3     and requests_status is json
  4    );

Table altered.

SQL>

[EDIT]

That's really strange. Went to My Oracle Support and found document ID 2665636.1 - it says that adding an IS JSON check constraint on a table which has dependent views fails.

What do you get if you run this query (while connected as ECODB user)?

select name, type
from user_dependencies
where type = 'VIEW'
  and referenced_type = 'TABLE'
  and referenced_name = 'TBL_USERS';

If query returned some rows, then yes - you've hit the bug.

What to do? Apply the patch which fixes it.