webentwicklung-frage-antwort-db.com.de

Analysieren Sie JSON mithilfe von PL/SQL in eine Oracle-Tabelle

Ich habe das folgende Skript erstellt, um Daten aus Mobile App DB (die auf MongoDB basiert) aus Oracle SQL Developer zu lesen:

ERKLÄREN
 l_param_list VARCHAR2 (512); 

 l_http_request UTL_HTTP.req; 
 l_http_response UTL_HTTP.resp; 

 l_response_text VARCHAR2 (32767); 
 BEGIN 

 - Eingabeparameter des Dienstes 

 - Vorbereitung der Anfrage ...
 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 '); 

 - ... setzt die Attribute des Headers 
 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)); 

 - ... Eingabeparameter einstellen 
 - UTL_HTTP.write_text (l_http_request, l_param_list); 

 - Holen Sie sich eine Antwort und erhalten Sie den erhaltenen Wert 
 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); 
 Einfügen in Appery-Werte (l_response_text); 
 - Finalisierung 
 UTL_HTTP.end_response (l_http_response); 


 AUSNAHME 
 WENN UTL_HTTP.end_of_body 
 DANN UTL_HTTP.end_response (l_http_response); 
ENDE;
/

Die Antwort (l_response_text) ist eine JSON-ähnliche Zeichenfolge. Zum Beispiel:

[{"Postleitzahl": "47100", "OutletCode": "128039251", "MobileNumber": "0123071303", "_ createdAt": "2014-11-10 06: 12: 49.837", "_ updatedAt": "2014- 11-10 06: 12: 49.837 "}, {" Postleitzahl ":" 32100 "," OutletCode ":" 118034251 ", .....]

Der Code funktioniert einwandfrei und fügt die Antwort in eine Spaltentabelle mit dem Namen appery ein. Ich muss diese Antwort jedoch so analysieren, dass jedes Array in seine spezifische Spalte in einer Tabelle namens appery_test aufgenommen wird. Die Tabelle appery_test hat eine Anzahl von Spalten, die der Anzahl der JSON-Paare in derselben Reihenfolge entspricht.

Ich suchte und fand die meisten Ergebnisse über das Analysieren von Oracle-Tabellen in JSON und nicht das Gegenteil. Ich fand jedoch diesen Link, der meiner Ausgabe etwas ähnelt. Die vorgeschlagene Bibliothek in der Antwort enthält jedoch kein Beispiel, wie JSON mithilfe von PL/SQL in eine herkömmliche Tabelle eingefügt werden kann. 

N.B .: Ich verwende 11g und nicht 12c. Die eingebauten Funktionen stehen mir also nicht zur Verfügung.

14
Hawk

Ich habe PL/JSON-Bibliothek verwendet. Das JSON_EXT-Paket funktioniert insbesondere, um es zu analysieren. 

Das folgende, von Oracle Community inspirierte Skript funktionierte für mich

ERKLÄREN
 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); 
 Benutzername VARCHAR2 (100); 
 OutletCode VARCHAR2 (100) ; 
 OutletName VARCHAR2 (100); 
 MobileNumber VARCHAR2 (100); 
 PhoneNumber VARCHAR2 (100); 
 Adresse VARCHAR2 (100); 
 Stadt VARCHAR2 (100); 
 Status VARCHAR2 (100); 
 Postleitzahl VARCHAR2 (100); 
 E-Mail VARCHAR2 (100); 
 UpdateCount VARCHAR2 (100); 
 LoginCount VARCHAR2 (100); 
 ReferencePhoto VARCHAR2 (100); 
 Aktualisiert VARCHAR2 (100); 
 AccountLocked VARCHAR2 (100); 
 Oracle_Flag VARCHAR2 (100); 
 Acl VARCHAR2 (100); 


 BEGIN 

 - Eingabeparameter des Dienstes 

 - Vorbereitung der Anfrage ...
 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 '); 

 - ... setzt die Attribute des Headers 
 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)); 

 - ... Eingabeparameter einstellen 
 - UTL_HTTP.write_text (l_http_request, l_param_list); 

 - Holen Sie sich eine Antwort und erhalten Sie den erhaltenen Wert 
 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'); 
 Adresse: = json_ext.get_string (json (l_list.get (i)), 'Adresse'); 
 City: = json_ext.get_string (json (l_list.get (i)), 'City'); 
 State: = json_ext.get_string (json (l_list.get (i)), 'state'); 
 Postleitzahl: = json_ext.get_string (json (l_list.get (i)), 'Postleitzahl'); 
 E-Mail: = json_ext.get_string (json (l_list.get (i)), 'E-Mail'); 
 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'); 
 Aktualisierungen: = json_ext.get_string (json (l_list.get (i)), 'Aktualisierungen'); 
 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'); 


 Einfügen .....

Beachten Sie, dass json_ext.get_string nur VARCHAR2 zurückgibt, das auf 32767 begrenzt ist. Um dasselbe Paket mit größeren json_list und json_values ​​(> 32KB) zu verwenden, überprüfen Sie hier .

Wenn Sie über APEX 5.0 oder höher verfügen, bieten Sie eine bessere Option und eine viel bessere Leistung über APEX_JSON package. Weitere Informationen finden Sie in der Antwort von @ Olafur Tryggvason

8
Hawk

Da diese Frage hohe Ergebnisse erzielt, möchte ich diese bevorzugte Alternative posten:

Oracle hat APEX 5.0 (15. April 2015) veröffentlicht. Damit erhalten Sie Zugriff auf eine großartige API, um mit JSON zu arbeiten 

Ich verwende es unter 11.2 und war in der Lage, jeden einzelnen Json von einfachen bis zu sehr komplexen Objekten mit mehreren Arrays und 4/5 Stufen zu bearbeiten. APEX_JSON

Wenn Sie APEX nicht verwenden möchten. Installieren Sie einfach die Laufzeitumgebung, um auf die API zuzugreifen.

Verwendungsbeispiel, Daten aus json.orgs Beispiel :

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;

Ergebnis: PL/SQL-Block ausgeführt

S
Standard Generalized Markup Language
XML
13

Orace bietet PL/SQL-DOM-APIs für die JSON-Verarbeitung. Es wird dringend empfohlen, es zu verwenden, da es viele nützliche APIs bereitstellt.

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

1
Srikrishnan

in 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
Z. Anton