Hi friends,
I have JSON issue as below.
The procedure JSON_OBJ_ISSUE complied and returning JSON_OBJECT_T type successfully. But when length of output value exceeds 4000 characters, I get error "ORA-40478: output value too large (maximum: 4000)"
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);
INSERT INTO EMP VALUES (7370,'ADAM','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
COMMIT;
create or replace PROCEDURE JSON_OBJ_ISSUE
(
pi_dept_id IN integer
, PO_JSON OUT JSON_OBJECT_T
) AS
vJSon_final clob;
BEGIN
select
json_object(
key 'DeptDetails' value json_object (
key 'DeptID' value d.deptno,
key 'DeptName' value d.dname,
key 'EmpDetails' value (
select json_arrayagg(
json_object (
key 'EmpID' value e.empno,
key 'EmpName' value e.ename,
key 'Salary' value e.sal,
key 'DummyVal' value lpad('a',4000,'*')
)
ORDER BY e.ename
)
from emp e
WHERE e.deptno = d.deptno
)
)
) my_json
into vJSon_final
FROM dept d
WHERE d.deptno = pi_dept_id;
END JSON_OBJ_ISSUE;
/
DECLARE
l_json json_object_t;
BEGIN
json_obj_issue(20,l_json);
DBMS_OUTPUT.put_line(l_json.stringify);
end;
/
-----------------------------------------------------