How to parse JSON in oracle?

 In 11g, we need to install the utilities for  JSON
1.
APEX_JSON

https://docs.oracle.com/cd/E59726_01/doc.50/e39149/apex_json.htm#AEAPI29635

2.

PL/JSON

https://sourceforge.net/p/pljson/wiki/Home/

3. upgrade to 12c

After the installation we can use the JSON, JSON_LIST type we can parse the JSON files...

HOW TO GRANT THE JSON OBJECT TYPE TO USER IN ORACLE?

SQL> GRANT EXECUTE ON JSON TO MURUGA;
Grant succeeded


SQL> GRANT EXECUTE ON JSON_LIST TO MURUGA;
Grant succeeded

SQL> GRANT EXECUTE ON JSON_VALUE TO MURUGA;
Grant succeeded


SQL> GRANT EXECUTE ON JSON_VALUE_ARRAY TO MURUGA;
Grant succeeded

HOW TO LIST THE JSON OBJECT TYPES IN ORACLE?


SQL> SELECT * FROM USER_OBJECTS WHERE OBJECT_TYPE = 'TYPE' AND OBJECT_NAME LIKE '%JSON%'
  2  ;
OBJECT_NAME                                                                      SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED     LAST_DDL_TIME TIMESTAMP           STATUS  TEMPORARY GENERATED SECONDARY  NAMESPACE EDITION_NAME
-------------------------------------------------------------------------------- ------------------------------ ---------- -------------- ------------------- ----------- ------------- ------------------- ------- --------- --------- --------- ---------- ------------------------------
JSON                                                                                                                443607                TYPE                5/19/2017 9 5/19/2017 9:2 2017-05-19:09:23:57 VALID   N         N         N                  1
JSON_LIST                                                                                                           443606                TYPE                5/19/2017 9 5/19/2017 9:2 2017-05-19:09:23:57 VALID   N         N         N                  1
JSON_TAB                                                                                                            439925                TYPE                5/18/2017 4 5/18/2017 4:3 2017-05-18:16:33:19 VALID   N         N         N                  1
JSON_VALUE                                                                                                          443604                TYPE                5/19/2017 9 5/19/2017 9:2 2017-05-19:09:23:56 VALID   N         N         N                  1
JSON_VALUE_ARRAY                                                                                                    443605                TYPE                5/19/2017 9 5/19/2017 9:2 2017-05-19:09:23:57 VALID   N         N         N                  1

How to Create oracle Nested Table?

How to Create oracle Nested Table?

Create a table with NESTED TABLE column:

CREATE OR REPLACE TYPE my_tab_t AS TABLE OF VARCHAR2(30);
/
CREATE TABLE nested_table (id NUMBER, col1 my_tab_t)
       NESTED TABLE col1 STORE AS col1_tab;

Insert data into table:

INSERT INTO nested_table VALUES (1, my_tab_t('A'));
INSERT INTO nested_table VALUES (2, my_tab_t('B', 'C'));
INSERT INTO nested_table VALUES (3, my_tab_t('D', 'E', 'F'));
COMMIT;

Select from nested table:

SQL> SELECT * FROM nested_table;
        ID COL1
---------- ------------------------
         1 MY_TAB_T('A')
         2 MY_TAB_T('B', 'C')
         3 MY_TAB_T('D', 'E', 'F')

Unnesting the subtable:

SQL> SELECT id, COLUMN_VALUE FROM nested_table t1, TABLE(t1.col1) t2;
        ID COLUMN_VALUE
---------- ------------------------
         1 A
         2 B
         2 C
         3 D
         3 E
         3 F
6 rows selected.

A more complicated multi-column nested table where customers can have multiple addresses:

CREATE TYPE address_t AS OBJECT (
   street  VARCHAR2(30),
   city    VARCHAR2(20),
   state   CHAR(2),
   zip     CHAR(5) );
/
CREATE TYPE address_tab IS TABLE OF address_t;
/
CREATE TABLE customers (
   custid  NUMBER,
   address address_tab )
NESTED TABLE address STORE AS customer_addresses;

INSERT INTO customers VALUES (1,
            address_tab(
              address_t('101 First', 'Redwood Shores', 'CA', '94065'),
              address_t('123 Maple', 'Mill Valley',    'CA', '90952')
            )                );

Unnesting the subtable:

SQL> select c.custid, u.*
  2  from customers c, table (c.address) u
  3  ;

    CUSTID STREET                         CITY                 ST ZIP
---------- ------------------------------ -------------------- -- -----
         1 101 First                      Redwood Shores       CA 94065
         1 123 Maple                      Mill Valley          CA 90952

To view the TYPE creation content

select text from user_source where name = 'MY_TAB_T' order by line 

select text from user_source where name = 'MY_TAB_T' order by line 


select * from  ALL_TYPES;

Source : http://www.orafaq.com/wiki/NESTED_TABLE 

How to identify the distinct data types used in a oracle schema?

How to identify the distinct data types used in a oracle schema?

select distinct data_type,data_length,data_precision,data_scale
from all_tab_columns;

How to identify the oracle schema names in a database / Display all oracle Schemas

How to identify the oracle schema names in a database? / Display all oracle Schemas

 select distinct
   owner
from
   dba_segments
where
   owner in
   (select username
    from dba_users
    where default_tablespace not in ('SYSTEM','SYSAUX')
   )
;

or

select distinct
   owner
from
   dba_segments
where
   owner not in (
'SYSTEM', 'XDB', 'SYS', 'TSMSYS', 'MDSYS', 'EXFSYS', 'WMSYS', 'ORDSYS', 'OUTLN', 'DBSNMP');

Source : http://www.dba-oracle.com/t_display_all_schemas.htm

How to generate AWR & ASH from SQLPLUS or through procedure

set serveroutput on;
declare
dbid number;
instance_id number;
start_id number;
end_id number;
begin
dbms_output.enable(1000000);
select max(snap_id)-1,max(snap_id) into start_id,end_id from dba_hist_snapshot;
/* If you know the snap_id then comment the above line  and use the below code
start_id :=1233;
end_id := 1234;
*/
select dbid into dbid from v$database;
select instance_number into instance_id from v$instance;

--dbms_output.put_line('<PRE>');
for rc in ( select output from
   table(dbms_workload_repository.awr_report_text(dbid,instance_id,start_id,end_id))
           ) loop
  -- dbms_output.put_line('<BR>');
   dbms_output.put_line(rc.output);
end loop;
--dbms_output.put_line('</PRE>');
end;

FOR AWR html output

declare
dbid number;
instance_id number;
start_id number;
end_id number;
begin
dbms_output.enable(1000000);
select max(snap_id)-1,max(snap_id) into start_id,end_id from dba_hist_snapshot;
/* If you know the snap_id then comment the above line  and use the below code
start_id :=1233;
end_id := 1234;
*/
select dbid into dbid from v$database;
select instance_number into instance_id from v$instance;

--dbms_output.put_line('<PRE>');
for rc in ( select output from
   table(dbms_workload_repository.awr_report_html(dbid,instance_id,start_id,end_id))
           ) loop
  -- dbms_output.put_line('<BR>');
   dbms_output.put_line(rc.output);
end loop;
--dbms_output.put_line('</PRE>');
end;

/*privileges needed

grant select any dictionary to username;

grant SELECT ON SYS.V_$DATABASE  to username;

grant SELECT ON SYS.V_$INSTANCE to username;

grant EXECUTE ON SYS.DBMS_WORKLOAD_REPOSITORY  to username;

grant SELECT ON SYS.DBA_HIST_DATABASE_INSTANCE to username;

grant SELECT ON SYS.DBA_HIST_SNAPSHOT to username;

grant ADVISOR to username;

*/

ASH Report for the past 30 minutes

declare
dbid number;
instance_id number;
begin
select dbid into dbid from v$database;
select instance_number into instance_id from v$instance;
dbms_output.enable(500000);
dbms_output.put_line('<PRE>');
for rc in ( select output from
   table(dbms_workload_repository.ash_report_text( dbid,instance_id,SYSDATE-31/1440, SYSDATE-1/1440))) loop
   dbms_output.put_line(rc.output);
end loop;
dbms_output.put_line('</PRE>') ;
end;

for ASH HTML output

declare
dbid number;
instance_id number;
begin
select dbid into dbid from v$database;
select instance_number into instance_id from v$instance;
dbms_output.enable(500000);
dbms_output.put_line('<PRE>');
for rc in ( select output from
   table(dbms_workload_repository.ash_report_html( dbid,instance_id,SYSDATE-31/1440, SYSDATE-1/1440))) loop
   dbms_output.put_line(rc.output);
end loop;
dbms_output.put_line('</PRE>') ;
end;