CODEWITHSUNDEEP
arraysjsonoracle-databaseplsql
Menja
Menja

Reputation: 39

Get value from from a json_array in oracle

i need the values of a json_array. I tried this:

    DECLARE
   l_stuff        json_array_t;
BEGIN
   l_stuff := json_array_t ('["Stirfry", "Yogurt", "Apple"] ');  

   FOR indx IN 0 .. l_stuff.get_size - 1
   LOOP
        INSERT INTO t_taböe (name, type)
         VALUES(l_stuff.get(i), 'TEXT');
   END LOOP;
END;

Upvotes: 1

Views: 12977

Answers (4)

0xdb
0xdb

Reputation: 3697

First convert the JSON array into an ordinary PL/SQL array, then use a bulk insert.

Here is a reproducible example:

create table tab (name varchar2 (8), type varchar2 (8))
/
declare
    type namelist is table of varchar2(8) index by pls_integer;
    names namelist;
    arr json_array_t := json_array_t ('["Stirfry", "Yogurt", "Apple"]');
begin
    for idx in 1..arr.get_size loop 
        names(idx) := arr.get_string(idx-1);
    end loop;
    forall idx in indices of names
        insert into tab (name, type) values (names(idx), 'TEXT');
end;
/

The query and outcomes:

select * from tab
/
NAME     TYPE    
-------- --------
Stirfry  TEXT    
Yogurt   TEXT    
Apple    TEXT

Upvotes: 2

MT0
MT0

Reputation: 167774

You do not need PL/SQL and can do it in a single SQL statement:

INSERT INTO t_taböe (name, type)
SELECT value,
       'TEXT'
FROM   JSON_TABLE(
         '["Stirfry","Yogurt","Apple"]',
         '$[*]'
         COLUMNS (
           value VARCHAR2(50) PATH '$'
         )
       );

db<>fiddle here

Upvotes: 4

Alex Poole
Alex Poole

Reputation: 191235

You are passing the position as i instead of indx; but you need a string so use get_string(indx) as @Sayan said.

But if you try to use that directly in an insert you'll get "ORA-40573: Invalid use of PL/SQL JSON object type" because of a still-outstanding (as far as I know) bug.

To work around that you can assign the string to a variable first:

  l_name := l_stuff.get_string(indx);
  INSERT INTO t_taböe (name, type)
  VALUES(l_name, 'TEXT');

db<>fiddle

Upvotes: 4

Sayan Malakshinov
Sayan Malakshinov

Reputation: 8655

Just use get_string:

DECLARE
   l_stuff        json_array_t;
BEGIN
   l_stuff := json_array_t ('["Stirfry", "Yogurt", "Apple"] ');  

   FOR indx IN 0 .. l_stuff.get_size - 1
   LOOP
        --INSERT INTO t_taböe (name, type)
        -- VALUES(l_stuff.get_string(indx), 'TEXT');
        dbms_output.put_line(l_stuff.get_string(indx));
   END LOOP;
END;

Upvotes: 1

Related Questions