Parse JSON en la tabla de Oracle usando PL/SQL

Contamos con tu apoyo para difundir nuestros posts acerca de las ciencias informáticas.

Solución:

Dado que esta pregunta obtiene una puntuación alta en los resultados, quiero publicar esta alternativa preferida:

Oracle ha lanzado APEX 5.0 (15 de abril de 2015). Con él obtienes acceso a una gran API para trabajar con JSON

Lo estoy usando en 11.2 y he podido analizar cada json, desde objetos simples hasta objetos muy complejos con múltiples matrices y 4/5 niveles. APEX_JSON

Si no desea utilizar APEX. Simplemente instale el entorno de tiempo de ejecución para obtener acceso a la API.

Ejemplo de uso, datos del ejemplo de json.org:

declare
    sample_json   varchar2 (32767)
        := '
    "glossary": 
        "title": "example glossary",
        "GlossDiv": 
            "title": "S",
            "GlossList": 
                "GlossEntry": 
                    "ID": "SGML",
                    "SortAs": "SGML",
                    "GlossTerm": "Standard Generalized Markup Language",
                    "Acronym": "SGML",
                    "Abbrev": "ISO 8879:1986",
                    "GlossDef": 
                        "para": "A meta-markup language, used to create markup languages such as DocBook.",
                        "GlossSeeAlso": ["GML", "XML"]
                    ,
                    "GlossSee": "markup"
                
            
        
    
';
begin
    apex_json.parse (sample_json);
    dbms_output.put_line (apex_json.get_varchar2 ('glossary.GlossDiv.title'));
    dbms_output.put_line (apex_json.get_varchar2 ('glossary.GlossDiv.GlossList.GlossEntry.GlossTerm'));
    dbms_output.put_line (apex_json.get_varchar2 ('glossary.GlossDiv.GlossList.GlossEntry.GlossDef.GlossSeeAlso[%d]', 2));
end;

Resultado: bloque PL/SQL ejecutado

S
Standard Generalized Markup Language
XML

Usé la biblioteca PL/JSON. Específicamente, el paquete JSON_EXT funciona para analizarlo.

El siguiente script inspirado en la respuesta de Oracle Community funcionó para mí

DECLARE
  l_param_list     VARCHAR2(512);

  l_http_request   UTL_HTTP.req;
  l_http_response  UTL_HTTP.resp;

  l_response_text  VARCHAR2(32767);

l_list json_list;
A_id           VARCHAR2(200);
UserId         VARCHAR2(100);
UserName       VARCHAR2(100);
OutletCode     VARCHAR2(100);
OutletName     VARCHAR2(100);
MobileNumber   VARCHAR2(100);
PhoneNumber    VARCHAR2(100);
Address        VARCHAR2(100);
City           VARCHAR2(100);
State          VARCHAR2(100);
Postcode       VARCHAR2(100);
Email          VARCHAR2(100);
UpdateCount    VARCHAR2(100);
loginCount     VARCHAR2(100);
ReferencePhoto VARCHAR2(100);
Updates        VARCHAR2(100);
AccountLocked  VARCHAR2(100);
Oracle_Flag    VARCHAR2(100);
acl            VARCHAR2(100);


BEGIN

  -- service's input parameters

  -- preparing Request...
  l_http_request := UTL_HTTP.begin_request('https://api.appery.io/rest/1/db/collections/Outlet_Details?where=%7B%22Oracle_Flag%22%3A%22Y%22%7D'
                                          , 'GET'
                                          , 'HTTP/1.1');

  -- ...set header's attributes
  UTL_HTTP.set_header(l_http_request, 'X-Appery-Database-Id', '53f2dac5e4b02cca64021dbe');
  --UTL_HTTP.set_header(l_http_request, 'Content-Length', LENGTH(l_param_list));

  -- ...set input parameters
 -- UTL_HTTP.write_text(l_http_request, l_param_list);

  -- get Response and obtain received value
  l_http_response := UTL_HTTP.get_response(l_http_request);

  UTL_HTTP.read_text(l_http_response, l_response_text);

  DBMS_OUTPUT.put_line(l_response_text);
  l_list := json_list(l_response_text);

FOR i IN 1..l_list.count
LOOP
  A_id           := json_ext.get_string(json(l_list.get(i)),'_id');
  UserId         := json_ext.get_string(json(l_list.get(i)),'UserId');
  UserName       := json_ext.get_string(json(l_list.get(i)),'UserName');
  OutletCode     := json_ext.get_string(json(l_list.get(i)),'OutletCode');
  OutletName     := json_ext.get_string(json(l_list.get(i)),'OutletName');
  MobileNumber   := json_ext.get_string(json(l_list.get(i)),'MobileNumber');
  PhoneNumber    := json_ext.get_string(json(l_list.get(i)),'PhoneNumber');
  Address        := json_ext.get_string(json(l_list.get(i)),'Address');
  City           := json_ext.get_string(json(l_list.get(i)),'City');
  State          := json_ext.get_string(json(l_list.get(i)),'State');
  Postcode       := json_ext.get_string(json(l_list.get(i)),'Postcode');
  Email          := json_ext.get_string(json(l_list.get(i)),'Email');
  UpdateCount    := json_ext.get_string(json(l_list.get(i)),'UpdateCount');
  loginCount     := json_ext.get_string(json(l_list.get(i)),'loginCount');
  ReferencePhoto := json_ext.get_string(json(l_list.get(i)),'ReferencePhoto');
  Updates        := json_ext.get_string(json(l_list.get(i)),'Updates');
  AccountLocked  := json_ext.get_string(json(l_list.get(i)),'AccountLocked');
  Oracle_Flag    := json_ext.get_string(json(l_list.get(i)),'Oracle_Flag');
  acl            := json_ext.get_string(json(l_list.get(i)),'acl');


insert .....

Darse cuenta de json_ext.get_string solo devuelve VARCHAR2 limitado a 32767 máx. Para usar el mismo paquete con json_list y json_values ​​más grandes (>32 KB), marque aquí.

Si tiene APEX 5.0 y superior, una mejor opción y un rendimiento mucho mejor a través del paquete APEX_JSON. Vea la respuesta de @Olafur Tryggvason para más detalles

Reseñas y puntuaciones del post

Agradecemos que desees añadir valor a nuestra información añadiendo tu veteranía en las acotaciones.

---

---

Utiliza Nuestro Buscador