How can I parse JSON string in PL/SQL

With 11.0.4 version (there is no 11.0.4 version, of course) you have at least two choices(apart from writing a parser yourself):

Depending on the version of RDBMS you are using, here are a couple of options:

First one: for Oracle 11.1.0.7 and up, install Apex 5 and use apex_json package:

-- here I have 12.1.0.1 version with version 5 of apex installed

column ora_version format a21;
column apex_version format a21;


select (select version from v$instance) as ora_version
     , (select version_no from apex_release) as apex_version
  from dual;

--drop table test_2;
/* our test table */  
create table test_2(
  c_a date,
  c_b date,
  c_c number,
  c_d number,
  c_e number
);

select * from test_2;

declare
  l_json_doc clob; 
begin
  dbms_output.put_line('Parsing json...');
  l_json_doc := '{"a":"01/01/2015","b":"31/12/2015",
                  "c":"11111111111","d":"1111111111",
                  "e":"1234567890"}';
  apex_json.parse(l_json_doc);
  insert into test_2(c_a, c_b, c_c, c_d, c_e)
    values(apex_json.get_date(p_path=>'a', p_format=>'dd/mm/yyyy'),
           apex_json.get_date(p_path=>'b', p_format=>'dd/mm/yyyy'),
           to_number(apex_json.get_varchar2(p_path=>'c')),
           to_number(apex_json.get_varchar2(p_path=>'d')),
           to_number(apex_json.get_varchar2(p_path=>'e')));
  commit;
  dbms_output.put_line('Done!');
end;
/

column c_c format 99999999999;
select to_char(c_a, 'dd/mm/yyyy') as c_a
     , to_char(c_b, 'dd/mm/yyyy') as c_b
     , c_c
     , c_d
     , c_e 
  from test_2;

Result:

ORA_VERSION           APEX_VERSION         
--------------------- ---------------------
12.1.0.1.0            5.0.2.00.07          

1 row selected.

Table created.

no rows selected.

Parsing json...
Done!
PL/SQL procedure successfully completed.


C_A        C_B                 C_C        C_D        C_E
---------- ---------- ------------ ---------- ----------
01/01/2015 31/12/2015  11111111111 1111111111 1234567890

1 row selected.

Second one: Use opensource PL/JSON. Never used it before, so I'm taking this opportunity to try it out. It's quite similar to apex_json.

declare
  l_json      json;  --json object
  l_json_doc  clob;
begin
  dbms_output.put_line('Parsing json...');

  -- parsing is done upon object instantiation

  l_json_doc := '{"a":"01/01/2015","b":"31/12/2015",
                  "c":"11111111111","d":"1111111111",
                  "e":"1234567890"}';
  l_json := json(l_json_doc);


  insert into test_2(c_a, c_b, c_c, c_d, c_e)
    values(to_date(l_json.get('a').get_string, 'dd-mm-yyyy'),
           to_date(l_json.get('b').get_string, 'dd-mm-yyyy'),
           to_number(l_json.get('c').get_string),
           to_number(l_json.get('d').get_string),
           to_number(l_json.get('e').get_string));
  commit;
  dbms_output.put_line('Done!');
end;

column c_c format 99999999999;
select to_char(c_a, 'dd/mm/yyyy') as c_a
     , to_char(c_b, 'dd/mm/yyyy') as c_b
     , c_c
     , c_d
     , c_e 
  from test_2;

Result:

C_A        C_B                 C_C        C_D        C_E
---------- ---------- ------------ ---------- ----------
01/01/2015 31/12/2015  11111111111 1111111111 1234567890
01/01/2015 31/12/2015  11111111111 1111111111 1234567890

2 rows selected.

Introduction of json_table() in 12.1.0.2 release makes JSON parsing it a bit simpler(just for the sake of demonstration):

insert into test_2
  select to_date(c_a, 'dd-mm-yyyy')
       , to_date(c_b, 'dd-mm-yyyy')
       , c_c
       , c_d
       , c_e
    from json_table('{"a":"01/01/2015",
                      "b":"31/12/2015",
                      "c":"11111111111",
                      "d":"1111111111",
                      "e":"1234567890"}'
                    , '$' 
                    columns ( 
                       c_a varchar2(21) path '$.a',
                       c_b varchar2(21) path '$.b',
                       c_c varchar2(21) path '$.c',
                       c_d varchar2(21) path '$.d',
                       c_e varchar2(21) path '$.e'
                    )) ;

result:

 select *
   from test_2;


C_A         C_B                C_C        C_D        C_E
----------- ----------- ---------- ---------- ----------
1/1/2015    12/31/2015  1111111111 1111111111 1234567890

Since you specified you don't want to use any JSON library, if the format is fixed you could coerce it into something you could parse as XML, starting with stripping the curly braces, replacing the colons with equals signs, and removing the double-quotes from the first part of each name/value pair:

select regexp_replace(regexp_replace(value, '(^{|}$)'),
  '^"(.*)":(".*")($|,)', '\1=\2', 1, 0, 'm')
from tests_1;

REGEXP_REPLACE(REGEXP_REPLACE(VALUE,'(^{|}$)'),'^"(.*)":(".*")($|,)','\1=\2',1,0
--------------------------------------------------------------------------------

a="01/01/2015"
b="31/12/2015"
c="11111111111"
d="1111111111"
e="1234567890"

which you can use as the attributes of a dummy XML node; convert that to XMLType and you can use XMLTable to extract the attributes:

select x.a, x.b, x.c, x.d, x.e
from tests_1 t
cross join xmltable('/tmp'
  passing xmltype('<tmp ' ||regexp_replace(regexp_replace(value, '(^{|}$)'),
    '^"(.*)":(".*")($|,)', '\1=\2', 1, 0, 'm') || ' />')
  columns a varchar2(10) path '@a',
    b varchar2(10) path '@b',
    c number path '@c',
    d number path '@d',
    e number path '@e'
) x;

A          B                      C             D             E
---------- ---------- ------------- ------------- -------------
01/01/2015 31/12/2015   11111111111    1111111111    1234567890

Then you can convert the strings to dates during insert:

insert into test_2 (a, b, c, d, e)
select to_date(x.a, 'DD/MM/YYYY'), to_date(x.b, 'DD/MM/YYYY'), x.c, x.d, x.e
from tests_1 t
cross join xmltable('/tmp'
  passing xmltype('<tmp ' || regexp_replace(regexp_replace(value, '(^{|}$)'),
    '^"(.*)":(".*")($|,)', '\1=\2', 1, 0, 'm') || ' />')
  columns a varchar2(10) path '@a',
    b varchar2(10) path '@b',
    c number path '@c',
    d number path '@d',
    e number path '@e'
) x;

select * from test_2;

A          B                      C             D             E
---------- ---------- ------------- ------------- -------------
2015-01-01 2015-12-31   11111111111    1111111111    1234567890

That will cope with some of the name/value pairs not being there, and you'll get nulls if that happens.

If all the pairs will always be there you could just tokenize the string and pull out the relevant parts:

select to_date(regexp_substr(value, '[^"]+', 1, 4), 'DD/MM/YYYY') as a,
  to_date(regexp_substr(value, '[^"]+', 1, 8), 'DD/MM/YYYY') as b,
  to_number(regexp_substr(value, '[^"]+', 1, 12)) as c,
  to_number(regexp_substr(value, '[^"]+', 1, 16)) as d,
  to_number(regexp_substr(value, '[^"]+', 1, 20)) as e
from tests_1;

A          B                      C             D             E
---------- ---------- ------------- ------------- -------------
2015-01-01 2015-12-31   11111111111    1111111111    1234567890

From Oracle 18c you could use TREAT AS JSON operator:

SQL Enhancements for JSON

• You can specify that a given SQL expression returns JSON data, using TREAT (... AS JSON).

• TREAT (... AS JSON) lets you specify that the return value from a given SQL expression is to be treated as JSON data. Such expressions can include PL/SQL function calls and columns specified by a SQL WITH clause. New data-guide views make it easy to access path and type information for JSON fields, which is recorded for index-backed data guides. Returning generated and queried JSON data in LOB instances widens the scope of the use of relational data.

---

This operator provides a way to inform the database that the content of a VARCHAR2, BLOB, CLOB should be treated as containing JSON. This enables a number of useful features, including the ability to use "Simplified Syntax" on database objects that do not have an "IS JSON" constraint.

And in your example:

create table Test_1(val CLOB);
create table Test_2(a date,b date,c number,d number, e number);

INSERT INTO Test_1(val)
VALUES('{ 
"a":"01/01/2015",
"b":"31/12/2015",
"c":"11111111111",
"d":"1111111111",
"e":"1234567890"
}');

INSERT INTO Test_2(a,b,c,d,e)
SELECT sub.val_as_json.a,
       sub.val_as_json.b,
       sub.val_as_json.c,
       sub.val_as_json.d,
       sub.val_as_json.e
FROM (SELECT TREAT(val as JSON) val_as_json
      FROM Test_1) sub;
COMMIT;

db<>fiddle demo