Solution for ORA-02303 from JSON_OBJECT_INVALID in upgrade to 19c

I just learned yesterday about this error – and as it seems to happen occasionally, you may want to read more about the Solution for ORA-02303 from JSON_OBJECT_INVALID in upgrade to 19c.

What happens?

You are upgrading a database to Oracle 19c, and you see this error pattern:

CREATE OR REPLACE TYPE JSON_Object_T AUTHID CURRENT_USER UNDER JSON_Element_T(
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or table dependents

This is coming when catjson.sql gets executed.

Why does it happen?

Unfortunately the FORCE option is missing when we create the JSON_OBJECT_T. This leads to above error. No big deal but still an error in the upgrade.

How do you solve it?

If you encountered this error during upgrade, then you can fix it by recreating these two types, JSON_OBJECT_T and JSON_SCALAR_T with the FORCE option (see below before AUTHID for both types) as a quick workaround:

CREATE OR REPLACE TYPE JSON_Object_T FORCE AUTHID CURRENT_USER UNDER JSON_Element_T(
   dummy NUMBER, 
   CONSTRUCTOR FUNCTION JSON_Object_T(self IN OUT JSON_OBJECT_T) 
                        RETURN SELF AS RESULT,
   CONSTRUCTOR FUNCTION JSON_Object_T(self IN OUT JSON_OBJECT_T, jsn JDOM_T) 
                        RETURN SELF AS RESULT,     
   CONSTRUCTOR FUNCTION JSON_Object_T(self IN OUT JSON_OBJECT_T, jsn VARCHAR2) 
                        RETURN SELF AS RESULT,
   CONSTRUCTOR FUNCTION JSON_Object_T(self IN OUT JSON_OBJECT_T, jsn CLOB) 
                        RETURN SELF AS RESULT,
   CONSTRUCTOR FUNCTION JSON_Object_T(self IN OUT JSON_OBJECT_T, jsn BLOB) 
                        RETURN SELF AS RESULT,
   CONSTRUCTOR FUNCTION JSON_Object_T(self IN OUT JSON_OBJECT_T, jsn BLOB,
                        format IN VARCHAR2) RETURN SELF AS RESULT,
   CONSTRUCTOR FUNCTION JSON_Object_T(self IN OUT JSON_OBJECT_T, 
                                      e JSON_ELEMENT_T) RETURN SELF AS RESULT,  

   -- override the 'parse' functions to directly RETURN Json_Object_T
   STATIC      FUNCTION  parse(jsn VARCHAR2) RETURN Json_Object_T,
   STATIC      FUNCTION  parse(jsn CLOB) RETURN Json_Object_T,
   STATIC      FUNCTION  parse(jsn BLOB) RETURN Json_Object_T,
   STATIC      FUNCTION  parse(jsn BLOB, format IN VARCHAR2) 
                         RETURN Json_Object_T, 
   MEMBER      FUNCTION  clone RETURN JSON_OBJECT_T,

   MEMBER      FUNCTION  get(self IN JSON_OBJECT_T, key VARCHAR2) 
                         RETURN JSON_Element_T,
   MEMBER      FUNCTION  get_Object(self IN JSON_OBJECT_T, key VARCHAR2) 
                         RETURN JSON_OBJECT_T,
   MEMBER      FUNCTION  get_Array(self IN JSON_OBJECT_T, key VARCHAR2) 
                         RETURN JSON_ARRAY_T,
   MEMBER      FUNCTION  get_String(self IN JSON_OBJECT_T, key VARCHAR2) 
                         RETURN VARCHAR2,
   MEMBER      FUNCTION  get_Number(self IN JSON_OBJECT_T, key VARCHAR2) 
                         RETURN NUMBER,
   MEMBER      FUNCTION  get_Boolean(self IN JSON_OBJECT_T, key VARCHAR2) 
                         RETURN BOOLEAN,
   MEMBER      FUNCTION  get_Date(self IN JSON_OBJECT_T, key VARCHAR2) 
                         RETURN DATE,
   MEMBER      FUNCTION  get_Timestamp(self IN JSON_OBJECT_T, key VARCHAR2) 
                         RETURN TIMESTAMP,
   MEMBER      FUNCTION  get_Clob(self IN JSON_OBJECT_T, key VARCHAR2) 
                         RETURN CLOB,
   MEMBER      PROCEDURE get_Clob(self IN OUT NOCOPY JSON_OBJECT_T, 
                                  key VARCHAR2, c IN OUT NOCOPY CLOB),
   MEMBER      FUNCTION  get_Blob(self IN JSON_OBJECT_T, key VARCHAR2) 
                         RETURN BLOB,
   MEMBER      PROCEDURE get_Blob(self IN OUT NOCOPY JSON_OBJECT_T, 
                                 key VARCHAR2, b IN OUT NOCOPY BLOB),
   MEMBER      PROCEDURE put(self IN OUT NOCOPY JSON_OBJECT_T, key VARCHAR2, 
                             val VARCHAR2),
   MEMBER      PROCEDURE put(self IN OUT NOCOPY JSON_OBJECT_T, key VARCHAR2, 
                             val NUMBER),
   MEMBER      PROCEDURE put(self IN OUT NOCOPY JSON_OBJECT_T, key VARCHAR2, 
                             val BOOLEAN),
   MEMBER      PROCEDURE put(self IN OUT NOCOPY JSON_OBJECT_T, key VARCHAR2, 
                             val DATE),
   MEMBER      PROCEDURE put(self IN OUT NOCOPY JSON_OBJECT_T, key VARCHAR2, 
                             val TIMESTAMP),
   MEMBER      PROCEDURE put(key VARCHAR2, val CLOB),
   MEMBER      PROCEDURE put(key VARCHAR2, val BLOB),
   MEMBER      PROCEDURE put(self IN OUT NOCOPY JSON_OBJECT_T, key VARCHAR2, 
                             val JSON_Element_T),
   MEMBER      PROCEDURE put_Null(self IN OUT NOCOPY JSON_OBJECT_T, 
                                 key VARCHAR2),
   MEMBER      FUNCTION  has(self IN JSON_OBJECT_T, key VARCHAR2) 
                         RETURN BOOLEAN,
   MEMBER      PROCEDURE remove(self IN OUT NOCOPY JSON_OBJECT_T, key VARCHAR2),
   MEMBER      FUNCTION  get_Type(self IN JSON_OBJECT_T, key VARCHAR2) 
                         RETURN VARCHAR2,
   MEMBER      FUNCTION  get_Keys(self IN JSON_OBJECT_T) RETURN JSON_KEY_LIST,
   MEMBER      PROCEDURE rename_Key(self IN OUT NOCOPY JSON_OBJECT_T, 
                                   keyOld VARCHAR2, keyNew VARCHAR2)
) FINAL
/

And this is the second TYPE which needs to be recreated:

CREATE OR REPLACE TYPE JSON_Scalar_T FORCE AUTHID CURRENT_USER UNDER JSON_Element_T(
   dummy NUMBER,

   CONSTRUCTOR FUNCTION JSON_Scalar_T(self IN OUT JSON_SCALAR_T,
                                      jsn JDOM_T)  RETURN SELF AS RESULT,

   CONSTRUCTOR FUNCTION JSON_Scalar_T(self IN OUT JSON_SCALAR_T,
                                      e JSON_ELEMENT_T) RETURN SELF AS RESULT,

   MEMBER      FUNCTION  clone(self IN JSON_SCALAR_T) RETURN JSON_SCALAR_T

) FINAL
/

Of course, you could also edit the ?/rdbms/admin/catjsont.sql script by yourself and add the FORCE option to both types.

But the best and official option is to request a fix on top of your RU for

  • (Unpublished) Bug 31913650 – MISSING FORCE IN JSON OBJECT TYPES CAUSES UPGRADE ISSUES Sev 1 SR

Please recognize that there is no need to restore your database or rerun the upgrade again.

Any patches available yet?

Yes, as of now, you can download a one-off on top of 19.8.0 RU already from MOS. And a request for inclusion into the next RU has been filed already, too.

Further Information and Links

–Mike

Share this: