I have a JSON table, how can I create a sql table with this information in PLSQL?

user_16497479 0
Viewed 43

I'm working with PLSQL and I have this JSON table (first variable), How can I insert this data into a sql table, i have already try this, but i have SQL statement ignored error:

CREATE TABLE Prueba_ins_json (
        userId number(3),
        id number(3),
        title VARCHAR2(200),
        body VARCHAR2(800)
    );
DECLARE
  json_prueba VARCHAR(5000) := '[
                              {
                                "userId": 1,
                                "id": 1,
                                "title": "sunt aut facere repellat provident occaecati excepturi optio reprehenderit",
                                "body": "quia et suscipit\nsuscipit recusandae consequuntur expedita et cum\nreprehenderit molestiae ut ut quas totam\nnostrum rerum est autem sunt rem eveniet architecto"
                              },
                              {
                                "userId": 1,
                                "id": 2,
                                "title": "qui est esse",
                                "body": "est rerum tempore vitae\nsequi sint nihil reprehenderit dolor beatae ea dolores neque\nfugiat blanditiis voluptate porro vel nihil molestiae ut reiciendis\nqui aperiam non debitis possimus qui neque nisi nulla"
                              },
                              {
                                "userId": 1,
                                "id": 3,
                                "title": "ea molestias quasi exercitationem repellat qui ipsa sit aut",
                                "body": "et iusto sed quo iure\nvoluptatem occaecati omnis eligendi aut ad\nvoluptatem doloribus vel accusantium quis pariatur\nmolestiae porro eius odio et labore et velit aut"
                              },
                              {
                                "userId": 1,
                                "id": 4,
                                "title": "eum et est occaecati",
                                "body": "ullam et saepe reiciendis voluptatem adipisci\nsit amet autem assumenda provident rerum culpa\nquis hic commodi nesciunt rem tenetur doloremque ipsam iure\nquis sunt voluptatem rerum illo velit"
                              }
                              ]';
BEGIN
INSERT INTO Prueba_ins_json
  SELECT * FROM JSON_TABLE( json_prueba, '$[*]'
                              columns(
                                userId, id, title, body
                              )
                            );

END;
oracle11g plsql oracle json
1 Answers

Just need to provide PATH(preferably along with data types) clause such as

INSERT INTO Prueba_ins_json
SELECT *
  FROM JSON_TABLE(json_prueba,
                  '$[*]' COLUMNS(
                                  userId INT           PATH '$.userId',
                                  id     INT           PATH '$.id',
                                  title  VARCHAR2(200) PATH '$.title',
                                  body   VARCHAR2(800) PATH '$.body'
                                 )
                  );

Demo

Edit (as you're using DB 11g) :

You can alternatively use XMLTABLE() along with APEX_JSON.TO_XMLTYPE() instead such as

INSERT INTO Prueba_ins_json
WITH t(jsCol) AS
(
 SELECT '<your_JSON_value>' 
   FROM dual 
)
SELECT userId, id, title, body
  FROM t,
       XMLTABLE('/json/row'
                PASSING APEX_JSON.TO_XMLTYPE(jsCol)
                COLUMNS 
                  userId INT           PATH 'userId',
                  id     INT           PATH 'id',
                  title  VARCHAR2(200) PATH 'title',
                  body   VARCHAR2(800) PATH 'body'
               )
user_58413060
Related