Oracle sql json loop code example

Example: 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.