Hi team,
I have JSON issue as below.
The procedure JSON_OBJ_ISSUE complied successfully. But while invoking from an anonymous block, I get "ORA-40573: Invalid use of PL/SQL JSON object type".
Could you please help to resolve it?
Thanks in advance!
-----------------------------------------------------
CREATE TABLE EMP (
EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
);
INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
COMMIT;
create or replace PROCEDURE JSON_OBJ_ISSUE
(
pi_emp_id IN VARCHAR2
, PO_JSON OUT JSON_OBJECT_T
) AS
BEGIN
po_json := NEW json_object_t;
SELECT json_object_t (
json_object(
KEY 'EmpDetails'
VALUE json_object (KEY 'EmpID' VALUE e.empno,
KEY 'EmpName' VALUE e.ename)
)
)
INTO po_json
FROM emp e
where e.empno = pi_emp_id;
exception
WHEN others THEN
po_json := NULL;
dbms_output.put_line('Error - '||sqlerrm);
END JSON_OBJ_ISSUE;
/
DECLARE
l_json json_object_t;
BEGIN
l_json := NEW json_object_t;
json_obj_issue(7369,l_json);
DBMS_OUTPUT.put_line(l_json.stringify);
end;
/