Question 293609 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

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.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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;

  2. 0

    You can use:

    DECLARE
  TYPE list_t IS TABLE OF VARCHAR2(100);
  v_included list_t;
  v_excluded list_t;
  v_json     CLOB;
BEGIN
  -- Insert your logic to load from file here
  v_json  := '{ 
  "included_errors": [ "include1", "include2" ],
  "excluded_errors": [ "exclude1", "exclude2" ]
}';

  SELECT value
  BULK COLLECT INTO v_included
  FROM   JSON_TABLE(
           v_json,
           '$.included_errors[*]'
           COLUMNS(
             value VARCHAR2(100) PATH '$'
           )
         );

  SELECT value
  BULK COLLECT INTO v_excluded
  FROM   JSON_TABLE(
           v_json,
           '$.excluded_errors[*]'
           COLUMNS(
             value VARCHAR2(100) PATH '$'
           )
         );

  DBMS_OUTPUT.PUT_LINE('Included:');
  FOR i IN 1 .. v_included.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(v_included(i));
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('Excluded:');
  FOR i IN 1 .. v_excluded.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(v_excluded(i));
  END LOOP;
END;
/

    Which outputs:

    Included:
include1
include2
Excluded:
exclude1
exclude2

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search