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.
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;