json_table loop
SQL> declare
2 begin
3 FOR all_rec IN (select jt.*
4 FROM JSON_TABLE('{
5 "requestId": "1e11#1660d34739a",
6 "result": [
7 {
8 "id": 435593,
9 "firstName": "Matthew",
10 "lastName": "Kehl",
11 "email": "[email protected]",
12 "updatedAt": "2017-01-03T17:01:55Z",
13 "createdAt": "2016-08-24T13:47:07Z"
14 },
15 {
16 "id": 540833,
17 "firstName": "Jeff",
18 "lastName": "Byers",
19 "email": "[email protected]",
20 "updatedAt": "2017-04-12T14:38:28Z",
21 "createdAt": "2016-10-31T13:15:32Z"
22 }
23 ],
24 "success": true
25 }', '$.result[*]'
26 COLUMNS (
27 "lead_id" varchar2(100) PATH '$.id',
28 "first_name" varchar2(100) PATH '$.firstName',
29 "last_name" varchar2(100) PATH '$.lastName',
30 "email_id" varchar2(100) PATH '$.email'
31 )) jt)
32 LOOP
33 dbms_output.put_line(all_rec."lead_id"); <<<=========
34 END LOOP;
35
36 END;
37 /
435593
540833
PL/SQL procedure successfully completed.