travailler avec JSON à oracle
y a-t-il un moyen facile de travailler avec JSON au sein d'oracle? J'ai une procédure standard que j'utilise pour appeler des services web assez souvent, JSON est un format que je connais dans le contexte du développement web, mais quelle est la meilleure façon de travailler avec json dans le cadre d'une procédure stockée? Par exemple, prendre la réponse CLOB de L'URI, convertir cela en un objet JSON et obtenir une valeur à partir de cela?
pour référence, voici la procédure que j'ai utilisée pour récupérer les URLs
create or replace procedure macp_URL_GET(url_resp in out clob, v_url in varchar2) is
req Utl_Http.req;
resp Utl_Http.resp;
NAME VARCHAR2 (255);
VALUE VARCHAR2 (1023);
v_msg VARCHAR2 (80);
v_ans clob;
-- v_url VARCHAR2 (32767) := 'http://www.macalester.edu/';
BEGIN
/* request that exceptions are raised for error Status Codes */
Utl_Http.set_response_error_check (ENABLE => TRUE );
/* allow testing for exceptions like Utl_Http.Http_Server_Error */
Utl_Http.set_detailed_excp_support (ENABLE => TRUE );
/*
Utl_Http.set_proxy (
proxy => 'www-proxy.us.oracle.com',
no_proxy_domains => 'us.oracle.com'
);
*/
req := Utl_Http.begin_request (url => v_url, method => 'GET');
/*
Alternatively use method => 'POST' and Utl_Http.Write_Text to
build an arbitrarily long message
*/
/*
Utl_Http.set_authentication (
r => req,
username => 'SomeUser',
PASSWORD => 'SomePassword',
scheme => 'Basic',
for_proxy => FALSE --this info is for the target Web server
);
*/
Utl_Http.set_header (r => req, NAME => 'User-Agent', VALUE => 'Mozilla/4.0');
resp := Utl_Http.get_response (r => req);
/*
DBMS_OUTPUT.put_line ('Status code: ' || resp.status_code);
DBMS_OUTPUT.put_line ('Reason phrase: ' || resp.reason_phrase);
FOR i IN 1 .. Utl_Http.get_header_count (r => resp)
LOOP
Utl_Http.get_header (r => resp, n => i, NAME => NAME, VALUE => VALUE);
DBMS_OUTPUT.put_line (NAME || ': ' || VALUE);
END LOOP;
*/
--test
BEGIN
LOOP
Utl_Http.read_text (r => resp, DATA => v_msg);
--DBMS_OUTPUT.put_line (v_msg);
v_ans := v_ans || v_msg;
url_resp := url_resp || v_msg;
END LOOP;
EXCEPTION
WHEN Utl_Http.end_of_body
THEN
NULL;
END;
--test
Utl_Http.end_response (r => resp);
--url_resp := v_ans;
EXCEPTION
/*
The exception handling illustrates the use of "pragma-ed" exceptions
like Utl_Http.Http_Client_Error. In a realistic example, the program
would use these when it coded explicit recovery actions.
Request_Failed is raised for all exceptions after calling
Utl_Http.Set_Detailed_Excp_Support ( ENABLE=>FALSE )
And it is NEVER raised after calling with ENABLE=>TRUE
*/
WHEN Utl_Http.request_failed
THEN
DBMS_OUTPUT.put_line (
'Request_Failed: ' || Utl_Http.get_detailed_sqlerrm
);
url_resp :='Request_Failed: ' || Utl_Http.get_detailed_sqlerrm;
/* raised by URL http://xxx.oracle.com/ */
WHEN Utl_Http.http_server_error
THEN
DBMS_OUTPUT.put_line (
'Http_Server_Error: ' || Utl_Http.get_detailed_sqlerrm
);
url_resp := 'Http_Server_Error: ' || Utl_Http.get_detailed_sqlerrm;
/* raised by URL http://otn.oracle.com/xxx */
WHEN Utl_Http.http_client_error
THEN
DBMS_OUTPUT.put_line (
'Http_Client_Error: ' || Utl_Http.get_detailed_sqlerrm
);
url_resp := 'Http_Client_Error: ' || Utl_Http.get_detailed_sqlerrm;
/* code for all the other defined exceptions you can recover from */
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLERRM);
url_resp := SQLERRM;
END;
alors pour le tester
begin
macp_url_get(url_resp => :url_resp,
'http://maps.googleapis.com/maps/api/geocode/json?address=55105&sensor=false');
end;
(je sais que googleapi permettra la réponse xml, mais il y a d'autres API web que j'utilise régulièrement par défaut pour JSON)
5 réponses
j'ai commencé à utiliser cette bibliothèque, et il semble prometteur: https://github.com/pljson/pljson
Facile à installer, et les exemples sont bons.
pour utiliser la bibliothèque dans votre exemple, ajoutez ces variables à votre procédure..
mapData json;
results json_list;
status json_value;
firstResult json;
geometry json;
....
Ensuite, vous pouvez manipuler la réponse comme un objet json.
-- convert the result from the get to a json object, and show some results.
mapData := json(v_ans);
-- Show the status of the request
status := mapData.get('status');
dbms_output.put_line('Status = ' || status.get_string());
IF (status.get_string() = 'OK') THEN
results := json_list(mapData.get('results'));
-- Grab the first item in the list
resultObject := json(results.head);
-- Show the human readable address
dbms_output.put_line('Address = ' || resultObject.get('formatted_address').to_char() );
-- Show the json location data
dbms_output.put_line('Location = ' || resultObject.get('geometry').to_char() );
END IF;
L'exécution de ce code affichera la sortie du SGBD:
Status = OK
Address = "St Paul, MN 55105, USA"
Location = {
"bounds" : {
"northeast" : {
"lat" : 44.9483849,
"lng" : -93.1261959
},
"southwest" : {
"lat" : 44.9223829,
"lng" : -93.200307
}
},
"location" : {
"lat" : 44.9330076,
"lng" : -93.16290629999999
},
"location_type" : "APPROXIMATE",
"viewport" : {
"northeast" : {
"lat" : 44.9483849,
"lng" : -93.1261959
},
"southwest" : {
"lat" : 44.9223829,
"lng" : -93.200307
}
}
}
il faut noter qu'à partir D'Oracle 12c il y a un certain support natif de JSON. Cependant, je ne pense pas que dans la forme actuelle, il est aussi utile que le genre de PLJSON inclus dans une autre réponse.
pour utiliser la fonctionnalité vous créez une table avec un champ BLOB, CLOB ou Varchar2 et ajoutez une contrainte contre elle "la colonne est JSON". Cela renforce la vérification de la syntaxe JSON sur cette colonne.
tant que la contrainte" IS JSON " est en place, vous pouvez accéder aux valeurs JSON en utilisant notation de point de SQL. Pour moi, il ne semble pas fournir une manipulation aussi puissante que PLJSON. Vous pouvez aussi créer un XMLType et le convertir en JSON.
liens Utiles:
Oracle docs
Bon tutoriel et exemples
tutoriel incluant XML to JSON
j'ai écrit cette bibliothèque : http://reseau.erasme.org/pl-sql-library-for-JSON?lang=en, et cela fonctionne très bien pour obtenir quelques réponses json dans un plsql table.
si vous voulez seulement extraire des données Oracle et les transformer en Json, cette bibliothèque est un peu "lourde à utiliser"... Je peux vous proposer un nouveau code en le faisant mieux et plus vite :
create or replace package jsonfly as
procedure open_object(k varchar2 default null);
procedure close_object;
procedure open_array (k varchar2 default null);
procedure close_array;
procedure separation;
procedure member(k varchar2, v varchar2);
procedure member(k varchar2, n number);
procedure send;
end;
/
create or replace package body jsonfly as
--------------------------------------------------------------------------------
-- package pour générer du JSON, envoyé à la volé
--------------------------------------------------------------------------------
type tCache is table of varchar2(2000) index by binary_integer;
g_openBrace constant varchar2(2) := '{ ';
g_closeBrace constant varchar2(2) := ' }';
g_openBracket constant varchar2(2) := '[ ';
g_closeBracket constant varchar2(2) := ' ]';
g_stringDelimiter constant varchar2(1) := '"';
g_Affectation constant varchar2(3) := ' : ';
g_separation constant varchar2(3) := ', ';
g_CR constant varchar2(1) := Chr(10); -- used to indent the JSON object correctly
g_spc constant varchar2(2) := ' '; -- used to indent the JSON object correctly
g_js_comment_open constant varchar2(20) := '/*-secure-\n'; -- used to prevent from javascript hijacking
g_js_comment_close constant varchar2(20) := '\n*/'; -- used to prevent from javascript hijacking
--isObjectOpened boolean := false;
--isArrayOpened boolean := false;
t tCache;
i number := 1;
--------------------------------------------------------------------------------
--
--------------------------------------------------------------------------------
procedure p(s varchar2) is
begin
t(i) := s;
i := i + 1;
end;
--------------------------------------------------------------------------------
--
--------------------------------------------------------------------------------
function encap (s varchar2) return varchar2 is
begin
return g_stringdelimiter || s || g_stringdelimiter;
end;
--------------------------------------------------------------------------------
--
--------------------------------------------------------------------------------
function encode_string(p_string varchar2) return varchar2 is
begin
return replace(replace(replace(replace(replace(replace(replace(replace(p_string,
'\', '\'),
'"', '\"'),
'/', '\/'),
chr(8), '\b'),
chr(9), '\t'),
chr(10), '\n'),
chr(12), '\f'),
chr(13), '\r');
end;
--------------------------------------------------------------------------------
--
--------------------------------------------------------------------------------
procedure open_object(k varchar2 default null) is
begin
if ( k is null ) then
p(g_openbrace);
else
p( encap(k) || g_affectation || g_openbrace);
end if;
end;
--------------------------------------------------------------------------------
--
--------------------------------------------------------------------------------
procedure close_object is
begin
if (t(i-1) = g_separation) then
i := i - 1;
end if;
p(g_closebrace);
separation();
end;
--------------------------------------------------------------------------------
--
--------------------------------------------------------------------------------
procedure open_array (k varchar2 default null) is
begin
if ( k is null ) then
p(g_openbracket);
else
p( encap(k) || g_affectation || g_openbracket);
end if;
end;
--------------------------------------------------------------------------------
--
--------------------------------------------------------------------------------
procedure close_array is
begin
if (t(i-1) = g_separation) then
i := i - 1;
end if;
p(g_closebracket);
separation();
end;
--------------------------------------------------------------------------------
--
--------------------------------------------------------------------------------
procedure separation is
begin
p(g_separation);
end;
--------------------------------------------------------------------------------
--
--------------------------------------------------------------------------------
procedure key(k varchar2) is
begin
p( encap(k) || g_affectation);
end;
--------------------------------------------------------------------------------
--
--------------------------------------------------------------------------------
procedure value(v varchar2) is
begin
p(v);
end;
--------------------------------------------------------------------------------
--
--------------------------------------------------------------------------------
procedure member(k varchar2, v varchar2) is
begin
p( encap(k) || g_affectation || encap(encode_string(v)));
p(g_separation);
end;
--------------------------------------------------------------------------------
--
--------------------------------------------------------------------------------
procedure member(k varchar2, n number) is
begin
p( encap(k) || g_affectation || n );
p(g_separation);
end;
--------------------------------------------------------------------------------
--
--------------------------------------------------------------------------------
procedure send is
begin
if (t(i-1) = g_separation) then
t.delete(i-1);
end if;
i := t.first;
while (i is not null) loop
htp.p(t(i));
i := t.next(i);
end loop;
end;
end jsonfly;
/
Oracle APEX 5.0 supporte JSON en utilisant APEX_JSON package. Je ne l'ai pas utilisé mais il a l'air intéressant et j'ai demandé à mon équipe de l'explorer. Notre cas d'utilisation est d'être en mesure de passer des données JSON comme paramètre d'entrée à la procédure stockée à partir de l'application nodejs.
Oracle 12c ont maintenant native JSON support:
Oracle Database prend en charge les données JavaScript Object Notation (JSON) nativement avec des fonctionnalités de base de données relationnelles, y compris les transactions, l'indexation, la interrogation déclarative, et les vues
les données JSON et les données XML peuvent être utilisées dans la base de données Oracle de manière similaire. Contrairement aux données relationnelles, les deux peuvent être stockées, indexées et questionnées sans aucun besoin d'un schéma qui définit les données. La base de données Oracle prend en charge JSON nativement avec des fonctionnalités de base de données relationnelles, y compris les transactions, l'indexation, la interrogation déclarative et les vues.
les données JSON ont souvent été stockées dans des bases de données NoSQL comme Oracle NoSQL Database et Oracle Berkeley DB. Ceux-ci permettent le stockage et la récupération de données qui n'est pas basée sur un schéma, mais ils n'offrent pas les modèles de cohérence rigoureux des bases de données relationnelles.
pour compenser cette lacune, une base de données relationnelles est parfois utilisée en parallèle avec une base de données NoSQL. Les Applications utilisant des données JSON stockées dans la base de données NoSQL doivent ensuite assurer l'intégrité des données elles-mêmes.
le support natif pour JSON par la base de données Oracle évite de telles solutions de rechange. Il fournit tous les avantages des caractéristiques de base de données relationnelles pour l'utilisation avec JSON, y compris les transactions, l'indexation, la interrogation déclarative, et les vues.
les requêtes de la base de données Oracle sont déclaratives. Vous pouvez joindre des données JSON avec des données relationnelles. Et vous pouvez projeter des données JSON sur le plan relationnel, le rendant disponible pour les relationnel outils et les processus. Vous pouvez également interroger, à partir de la base de données, les données JSON qui sont stockées en dehors de la base de données dans une table externe.
vous pouvez accéder aux données JSON stockées dans la base de données de la même manière que vous accédez à d'autres données de la base de données, y compris en utilisant OCI, .NET, et JDBC.
Contrairement aux données XML, qui sont stockées en utilisant le type de données SQL XMLType, les données JSON sont stockées dans la base de données Oracle en utilisant les types de données SQL VARCHAR2, CLOB, et BLOB. Oracle recommande que vous utilisiez toujours une contrainte de vérification is_json pour vous assurer que les valeurs des colonnes sont des instances JSON valides