Синтаксический анализ JSON в таблицу Oracle с помощью PL / SQL

Я создал следующий скрипт для чтения данных из базы данных мобильного приложения (которая основана на MongoDB) от разработчика Oracle SQL: 





DECLARE

  l_param_list     VARCHAR2(512);



  l_http_request   UTL_HTTP.req;

  l_http_response  UTL_HTTP.resp;



  l_response_text  VARCHAR2(32767);

BEGIN



  -- service's input parameters



  -- preparing Request...

  l_http_request := UTL_HTTP.begin_request ('https://api.appery.io/rest/1/db/collections/Photos?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);

  insert into appery values(l_response_text);

  -- finalizing

  UTL_HTTP.end_response(l_http_response);





EXCEPTION

  WHEN UTL_HTTP.end_of_body 

    THEN UTL_HTTP.end_response(l_http_response);  

END;

/


Ответ(l_response_text) является JSON-подобной строкой. Например: 



[{"Postcode":"47100","OutletCode":"128039251","MobileNumber":"0123071303","_createdAt":"2014-11-10 06:12:49.837","_updatedAt":"2014-11-10 06:12:49.837"}, {"Postcode":"32100","OutletCode":"118034251", ..... ]


Код работает нормально, и вставляет ответ в один столбец-таблицу под названиемаппери . Однако мне нужно проанализировать этот ответ таким образом, чтобы каждый массив входил в свой конкретный столбец в таблице под названием appery_test. Таблица appery_test имеет число столбцов, такое же, как и число пар JSON, и в том же порядке.



Я искал и нашел большинство результатов о разборе таблицы Oracle в JSON, а не наоборот. Я нашел, однако, эту ссылку, которая несколько похожа на мою проблему. Однако предложенная библиотека в ответе не имеет никакого примера о том, как использовать ее для вставки JSON в обычную таблицу с помощью PL/SQL.



Н. Б.: я использую 11g, а не 12c. построен в функции не доступны для меня.

1442   4  

4 ответов:

Поскольку этот вопрос набирает высокие баллы в результатах, я хочу опубликовать эту предпочтительную альтернативу:

Oracle выпустилаAPEX 5.0 (15 апреля. 2015). С его помощью вы получаете доступ к большому API для работы с JSON

Я использую его на 11.2 и смог взломать каждый отдельный json, от простых до очень сложных объектов с несколькими массивами и уровнями 4/5. APEX_JSON

Если вы не хотите использовать APEX. Просто установите среду выполнения, чтобы получить доступ к ПРИКЛАДНОЙ ПРОГРАММНЫЙ ИНТЕРФЕЙС.

Пример использования, данные из 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;

Результат: PL / SQL блок выполнен 

S
Standard Generalized Markup Language
XML
13  
2015-08-29 18:46:12

Я использовал библиотеку PL/JSON. В частности, функции пакета JSON_EXT для того, чтобы разобрать его.

Следующий сценарий, вдохновленный Oracle Community answer, сработал для меня

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 .....

Обратите внимание, что json_ext.get_string перезапускает только VARCHAR2, ограниченный 32767 max. Чтобы использовать один и тот же пакет с большим json_list и json_values (>32KB), проверьте здесь.

Если у вас есть APEX 5.0 и выше, лучший вариант и намного лучшая производительность через APEX_JSON пакет. Смотрите ответ @ Olafur Tryggvason для деталей

8  
2018-06-03 05:52:50

Orace предоставляет API PL / SQL DOM для обработки JSON. Настоятельно рекомендую использовать его, так как он предоставляет массу полезных API.

Https://docs.oracle.com/database/122/ADJSN/using-PLSQL-object-types-for-JSON.htm#GUID-F0561593-D0B9-44EA-9C8C-ACB6AA9474EE

1  
2017-08-22 01:27:05

В Oracle 12: https://docs.oracle.com/database/121/SQLRF/functions092.htm#SQLRF56973 

SELECT jt.*
FROM j_purchaseorder,
JSON_TABLE(po_document, '$.ShippingInstructions.Phone[*]'
COLUMNS (row_number FOR ORDINALITY,
         phone_type VARCHAR2(10) PATH '$.type',
         phone_num VARCHAR2(20) PATH '$.number'))
AS jt;

ROW_NUMBER PHONE_TYPE PHONE_NUM
---------- ---------- --------------------
         1 Office     909-555-7307
         2 Mobile     415-555-1234
0  
2018-05-15 18:19:59

Comments

    Ничего не найдено.
Click here to cancel reply.