You can use suchĪ view just as you would use any table or view. Use of json_table is to create a view of JSON data. You can query it using SQL - in a join expression, for example. If you only want to extract Oracle data and transform it in Json, this library is a bit "Heavy to use".You can then insert this virtual table into a pre-existing database table, or I wrote this library :, and this works great to get some json response into a plsql table.
ORACLE JSON QUERY EXAMPLE STACKOVERFLOW CODE
Running this code will output this to the dbms output: Status = OK Results := json_list(mapData.get('results')) ĭbms_output.put_line('Address = ' || resultObject.get('formatted_address').to_char() ) ĭbms_output.put_line('Location = ' || resultObject.get('geometry').to_char() ) convert the result from the get to a json object, and show some results.ĭbms_output.put_line('Status = ' || status.get_string()) Then you can manipulate the response as a json object. To use the library in your example, add these variables to your procedure. I have started using this library, and it seems promising:Įasy to install, and the examples are good.
(I know that the googleapi will allow xml response, but there are other web APIs that I use regularly that default to JSON) * code for all the other defined exceptions you can recover from */ Url_resp := 'Http_Client_Error: ' || Utl_Http.get_detailed_sqlerrm 'Http_Client_Error: ' || Utl_Http.get_detailed_sqlerrm Url_resp := 'Http_Server_Error: ' || Utl_Http.get_detailed_sqlerrm 'Http_Server_Error: ' || Utl_Http.get_detailed_sqlerrm Url_resp :='Request_Failed: ' || Utl_Http.get_detailed_sqlerrm 'Request_Failed: ' || Utl_Http.get_detailed_sqlerrm Utl_Http.Set_Detailed_Excp_Support ( ENABLE=>FALSE )Īnd it is NEVER raised after calling with ENABLE=>TRUE Request_Failed is raised for all exceptions after calling Would use these when it coded explicit recovery actions. The exception handling illustrates the use of "pragma-ed" exceptions Utl_Http.read_text (r => resp, DATA => v_msg)
Utl_Http.get_header (r => resp, n => i, NAME => NAME, VALUE => VALUE) ĭBMS_OUTPUT.put_line (NAME || ': ' || VALUE) Resp := Utl_Http.get_response (r => req) ĭBMS_OUTPUT.put_line ('Status code: ' || resp.status_code) ĭBMS_OUTPUT.put_line ('Reason phrase: ' || resp.reason_phrase) įOR i IN 1. Utl_t_header (r => req, NAME => 'User-Agent', VALUE => 'Mozilla/4.0') Req := Utl_Http.begin_request (url => v_url, method => 'GET') Īlternatively use method => 'POST' and Utl_Http.Write_Text toįor_proxy => FALSE -this info is for the target Web server Utl_t_detailed_excp_support (ENABLE => TRUE ) * allow testing for exceptions like Utl_Http.Http_Server_Error */ Utl_t_response_error_check (ENABLE => TRUE ) * request that exceptions are raised for error Status Codes */ Is there an easy way to work with JSON within oracle? I have a standard procedure that I use to call web services quite often, JSON is a format that I am familiar with in web development context, but what is the best way to work with json within a stored procedure? For instance take the CLOB response from the URI, convert that to a JSON object and get a value from that?įor reference sake, here is the procedure I used to fetch URLs create or replace procedure macp_URL_GET(url_resp in out clob, v_url in varchar2) is
0 kommentar(er)
