Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

ORA-40573: Invalid use of PL/SQL JSON object type in SQL

marco gordiniJul 4 2024

Hi Team,

when i use a custom object type that declare an attribute JSON_ARRAY_T (or json_element_t, json_object_t,…) in SQL query, i get “ORA-40573: Invalid use of PL/SQL JSON object type in SQL”. In PL/SQL script there is no error.

Here an example:

CREATE OR REPLACE TYPE ty_test_o FORCE AS OBJECT
(
--
ja_test json_array_t,

CONSTRUCTOR FUNCTION ty_test_o RETURN SELF AS RESULT,
--
MEMBER FUNCTION serialize_json RETURN VARCHAR2,
--
STATIC FUNCTION new_test(p_json IN VARCHAR2) RETURN ty_test_o
)
NOT persistable;
/

CREATE OR REPLACE TYPE BODY ty_test_o IS

--
CONSTRUCTOR FUNCTION ty_test_o RETURN SELF AS RESULT IS
BEGIN
self.ja_test := json_array_t('[]');
RETURN;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('ty_test_o - ' || SQLERRM);
RAISE;
END ty_test_o;

--
MEMBER FUNCTION serialize_json RETURN VARCHAR2 IS
BEGIN
RETURN self.ja_test.to_string();
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('serialize_json - ' || SQLERRM);
RAISE;
END serialize_json;

--
STATIC FUNCTION new_test(p_json IN VARCHAR2) RETURN ty_test_o IS
o_je ty_test_o;
BEGIN
o_je := ty_test_o();
IF p_json IS NOT NULL THEN
o_je.ja_test := json_array_t(p_json);
END IF;
RETURN o_je;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('new_test - ' || SQLERRM);
RAISE;
END new_test;

END;
/

PL/SQL script with no error:

DECLARE
o_test ty_test_o;
BEGIN
o_test := ty_test_o.new_test('["one","two","three"]');
dbms_output.put_line(o_test.serialize_json());
END;

output: ["one","two","three"]

----------------------------------------------------------------------------

SQL query with error:

SELECT ty_test_o.new_test('["one","two","three"]') AS o_jj FROM dual;

error: ORA-40573: Invalid use of PL/SQL JSON object type in SQL

----------------------------------------------------------------------------

SELECT ty_test_o.new_test('["one","two","three"]').serialize_json() AS json FROM dual;

error: ORA-40573: Invalid use of PL/SQL JSON object type in SQL

----------------------------------------------------------------------------

Could you please help to resolve it?

Thanks in advance!

Comments

Post Details

Added on Jul 4 2024
3 comments
223 views