使用oracleplsql，请告诉我如何使用json_table、bfilename和"Bulk Collect"从json文件中读取数据.
json文件包含两个字符串列表:
{
"included_errors": [ "include1", "include2" ],
"excluded_errors": [ "exclude1", "exclude2" ]
}
在不使用中间表的情况下，我希望将两个字符串列表加载到两个嵌套表的列表变量中，即:
TYPE list_t IS TABLE OF VARCHAR2(100);
l_included_errors list_t;
l_excluded_errors list_t;
然后我将在for循环中遍历列表.
我最后做了以下事情，这很管用:
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;