JSON support in 12c

 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;

/