JSON SUPPROT IN ORACLE 12C
Oracle has started json support from 12c version onwards . JSON is used nowadays more populalry for data transfer among different subsystems similar to XML.
below are some examples to understand the json support.
SET SERVEROUTPUT ON;
DECLARE
TYPE nt_fName IS TABLE OF VARCHAR2 (20);
TYPE nt_JName IS TABLE OF VARCHAR2 (20);
fname nt_fName;
LJOB nt_JName;
BEGIN
SELECT Ename, JOB
BULK COLLECT INTO fName, LJOB
FROM empL;
--Print values from the collection--
FOR idx IN 1..fName.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE (idx||' - EMP NAME IS : '||fName (idx) ||' AND JOB : '||LJOB (idx));
END LOOP;
END;
---------------------JSON --------------------
SELECT JSON_OBJECT('ENAME ' VALUE ENAME ) FROM EMPL---
SELECT JSON_OBJECTAGG('ENAME' VALUE ENAME ) FROM EMPL--
SELECT JSON_ARRAY(ENAME ) FROM EMPL --
SELECT JSON_ARRAYAGG(ENAME) FROM EMPL
/
--GENERATE JSON DATA FRRMAT FOR DEPT AND EMPLOYEES WORKING IN THOSE DEPT
SELECT JSON_OBJECTAGG(D.DNAME
VALUE JSON_ARRAY(E.ENAME))
FROM EMPL E, DEP D
WHERE E.DEPNO=D.DEPNO
GROUP BY D.DNAME
/
SELECT JSON_OBJECTAGG(D.DNAME
VALUE JSON_ARRAYAGG(E.ENAME))
FROM EMPL E, DEP D
WHERE E.DEPNO=D.DEPNO
GROUP BY D.DNAME
--- dynamic sql--- EMPL , DEP
SET SERVEROUTPUT ON;
DECLARE
SQL_STMT VARCHAR2(1000);
EMP_TOT VARCHAR2(1000);
v_qry varchar2(100);
BEGIN
v_qry := &a;
SQL_STMT := 'SELECT COUNT(*) FROM ' ||v_qry; --always it shud be insingle quotes
EXECUTE IMMEDIATE SQL_STMT INTO EMP_TOT;
DBMS_OUTPUT.PUT_LINE('TOTAL rowcount from table '|| v_qry || ' is : '||EMP_TOT);
END;
/
