jsonoracle-databaseplsqlnested-tablebulk-collect

Oracle plsql: how lo load json file into nested tables


Using oracle plsql, please tell me how to use JSON_TABLE, bfilename and "bulk collect" in order to read data from a json file.

The json file contains 2 lists of strings:

{ 
  "included_errors": [ "include1", "include2" ],
  "excluded_errors": [ "exclude1", "exclude2" ]
}

Without using intermediate tables, I want to load both string lists into 2 list variables which are nested tables, i.e.:

TYPE list_t IS TABLE OF VARCHAR2(100);

l_included_errors list_t;
l_excluded_errors list_t;

Then I will traverse the lists in a for loop.


Solution

  • I ended doing the following, which works:

    DECLARE
      TYPE T_STRINGS IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
      include_list T_STRINGS;
      exclude_list T_STRINGS;
      json_file    BFILE;
    BEGIN
      json_file := BFILENAME('MY_DIRECTORY', 'my_file.json');
    
      SELECT my_data
        BULK COLLECT
        INTO include_list
        FROM JSON_TABLE(json_file,
                        '$.included_errors[*]'
                        COLUMNS(my_data VARCHAR2(1000) PATH '$'));
    
      SELECT valor
        BULK COLLECT
        INTO exclude_list
        FROM JSON_TABLE(json_file,
                        '$.excluded_errors[*]'
                        COLUMNS(valor VARCHAR2(1000) PATH '$'));
    
      -- process both lists here
      NULL;
    END;