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!