oracle存储过程定义json,在oracle中使用json

有没有一种简单的方法可以在Oracle中使用JSON？我有一个经常用于调用Web服务的标准过程，JSON是我在Web开发上下文中熟悉的一种格式，但是在存储过程中使用JSON的最佳方法是什么？例如，从URI中获取CLOB响应，将其转换为JSON对象并从中获取值？

作为参考，这是我用来获取URL的过程

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;

然后测试

begin

macp_url_get(url_resp => :url_resp,

'http://maps.googleapis.com/maps/api/geocode/json?address=55105&sensor=false');

end;

(我知道googleapi将允许xml响应，但我经常使用其他一些默认为JSON的Web API)