Analyser JSON dans la table Oracle à l'aide de PL / SQL

J'ai créé le script suivant pour lire des données à partir de l'Application Mobile DB (qui est basé sur MongoDB) de Oracle SQL Developer:

DÉCLARER 
l_param_list VARCHAR2(512); 

l_http_request UTL_HTTP.req; 
l_http_response UTL_HTTP.reee; 

l_response_text VARCHAR2(32767); 
COMMENCER 

-- service les paramètres d'entrée de 

-- la préparation de la Demande... 
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'); 

- de définir l'en-tête des attributs 
UTL_HTTP.set_header(l_http_request, 'X-Appery-Base de données-Id', '53f2dac5e4b02cca64021dbe'); 
--UTL_HTTP.set_header(l_http_request, 'Content-Length', LONGUEUR(l_param_list)); 

- de définir les paramètres d'entrée 
-- UTL_HTTP.write_text(l_http_request, l_param_list); 

-- obtenir de Réponse et d'obtenir la valeur reçue 
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); 
insérez dans appery valeurs(l_response_text); 
-- finalisation 
UTL_HTTP.end_response(l_http_response); 


EXCEPTION 
LORSQUE UTL_HTTP.end_of_body 
PUIS UTL_HTTP.end_response(l_http_response); 
FIN; 
/

La réponse (l_response_text) est un JSON comme chaîne de caractères. Par exemple:

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

Le code fonctionne très bien, et les insertions de la réponse dans une colonne de table appelé appery. Cependant, j'ai besoin d'analyser cette réponse, de sorte que chaque tableau va dans sa colonne spécifique dans une table appelée appery_test. Le tableau appery_test a un nombre de colonnes égal au nombre de JSON paires et dans le même ordre.

J'ai cherché et j'ai trouvé la plupart des résultats sur l'analyse Oracle de table en JSON et non l'inverse. J'ai trouvé, cependant, cette lien qui est assez similaire à mon problème. Cependant, la bibliothèque, la réponse n'est pas un exemple sur la façon de l'utiliser pour insérer JSON dans les classiques de la table à l'aide de PL/SQL.

N. B.: je suis en utilisant 11g et pas 12c. Ainsi, le construit en fonctions ne sont pas disponibles pour moi.

source d'informationauteur Hawk

Depuis que cette question scores élevés dans les résultats, j'ai envie de poster cette variante préférée:

Oracle a publié APEX 5.0 (15 avril. 2015). Avec elle, vous obtenez un accès à une grande API pour travailler avec JSON

Je l'utilise sur 11.2 et ont été en mesure de resserrement chaque json, du plus simple au très complexe des objets avec de multiples tableaux et 4/5 niveaux. APEX_JSON

Si vous ne souhaitez pas utiliser l'APEX. Il suffit d'installer l'environnement d'exécution pour obtenir l'accès à l'API.

D'exemples d'utilisation, de données à partir de json.org l'exemple de la :

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;

Résultat:
PL/SQL bloc exécuté

S
Standard Generalized Markup Language
XML

J'ai utilisé PL/librairie JSON. Plus précisément, JSON_EXT fonctions de package afin de l'analyser.

Le script suivant inspiré par Oracle réponse Communautaire a fonctionné pour moi

DÉCLARER 
l_param_list VARCHAR2(512); 

l_http_request UTL_HTTP.req; 
l_http_response UTL_HTTP.reee; 

l_response_text VARCHAR2(32767); 

l_list json_list; 
A_id VARCHAR2(200); 
UserId VARCHAR2(100); 
Nom d'utilisateur VARCHAR2(100); 
OutletCode VARCHAR2(100); 
OutletName VARCHAR2(100); 
MobileNumber VARCHAR2(100); 
Numéro de téléphone VARCHAR2(100); 
Adresse VARCHAR2(100); 
Ville VARCHAR2(100); 
État VARCHAR2(100); 
Code postal VARCHAR2(100); 
E-mail VARCHAR2(100); 
UpdateCount VARCHAR2(100); 
loginCount VARCHAR2(100); 
ReferencePhoto VARCHAR2(100); 
Les mises à jour VARCHAR2(100); 
AccountLocked VARCHAR2(100); 
Oracle_Flag VARCHAR2(100); 
acl VARCHAR2(100); 


COMMENCER 

-- service les paramètres d'entrée de 

-- la préparation de la Demande... 
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'); 

- de définir l'en-tête des attributs 
UTL_HTTP.set_header(l_http_request, 'X-Appery-Base de données-Id', '53f2dac5e4b02cca64021dbe'); 
--UTL_HTTP.set_header(l_http_request, 'Content-Length', LONGUEUR(l_param_list)); 

- de définir les paramètres d'entrée 
-- UTL_HTTP.write_text(l_http_request, l_param_list); 

-- obtenir de Réponse et d'obtenir la valeur reçue 
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.le comte 
BOUCLE 
A_id := json_ext.get_string(json(l_list.get(i)),'_id'); 
UserId := json_ext.get_string(json(l_list.get(i)),'UserId'); 
UserName: nom d'utilisateur= 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'); 
Numéro de téléphone := json_ext.get_string(json(l_list.get(i)),'Numéro de téléphone'); 
Adresse := json_ext.get_string(json(l_list.get(i)),'Adresse'); 
Ville := json_ext.get_string(json(l_list.get(i)),'Ville'); 
Etat := json_ext.get_string(json(l_list.get(i)),'État'); 
Code postal := json_ext.get_string(json(l_list.get(i)),'code Postal'); 
E-mail := 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'); 
Mises à jour := json_ext.get_string(json(l_list.get(i)), "Mises à jour"); 
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'); 


insérer .....

Avis que json_ext.get_string retuns seulement VARCHAR2 limitée à 32767 max. Afin d'utiliser le même package avec les plus grandes json_list et json_values (>32 KO) vérifier ici.

Si vous avez l'APEX 5.0 et au-dessus, la meilleure option et de bien meilleures performances via APEX_JSON paquet. Voir @Olafur Tryggvason's réponse pour plus de détails

1

Orace fournit PL/SQL DOM Api JSON de la manipulation. Recommande fortement de l'utiliser, car il fournit des tonnes d'utile Api.

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

dans 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

Vous devez vous connecter pour publier un commentaire.