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.
Photo by Pankaj Patel on Unsplash
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
- (Unpublished) Bug 31913650 – MISSING FORCE IN JSON OBJECT TYPES CAUSES UPGRADE ISSUES Sev 1 SR
- (non public) MOS Note: 2719101.1 – SYS.JSON_OBJECT_T TYPE BODY Object Is Invalid during upgrade
- Stackoverflow – Solution for ORA-2023 during upgrade to 19c by Beda Hammerschmidt
- PLS-103: MOS Note: 2259772.1 – PLSQL Unit Can Fail In 12.2 Database During Compilation/Runtime When Using JSON_* In The Code
–Mike
