AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |
Back to Blog
Oracle json query example stackoverflow12/31/2023 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 */ Q'#]. 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 Select json_value(body, '$.sections[#' || i || If in fact you are indeed only extracting two scalar values from a very large document, two calls to JSON_VALUE() may be justified, especially if the document is much larger than shown here but if you are extracting many scalar values from a document that is not too complicated, then a single call to JSON_TABLE() may be better, even if in the end you don't use all the data it produces.Īnyway - as an illustration of native dynamic SQL, here's the alternative solution (using MT0's table): declare Depending on the actual requirement (I assume the one in your question is just for illustration) and the size of the document, it may be more efficient to make a single call to JSON_TABLE(), as illustrated in MT0's answer. However, note that this approach makes repeated calls to JSON_VALUE() over the same document. PL/SQL has an answer for that - native dynamic SQL, as demonstrated below. So, the problem is that you want to loop over an index (to go diagonally through the array of arrays, picking up just two elements) - but the JSON_* functions don't work with variables as array indices - they require hard-coded indexes. (Note: the indexes from FOR ORDINALITY are 1 higher than the array indexes in the JSON path.) Or, you can just use a query: SELECT section_index, capsule_index, time INSERT INTO bodycontent ( body, contentid ) VALUES ( Which, for the test data: CREATE TABLE bodycontent ( body CLOB CHECK ( body IS JSON ), contentid NUMBER )
0 Comments
Read More
Leave a Reply. |