Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Conditional clause with JSON

User_CI7SYNov 9 2021

Hi Team, I get JSON output in the result of a SOAP web service call, now sometimes it is null and my code fails due to this NULL. How can I bypass JSON parsing code when JSON's one object is null. I tried Goto but it doesnt go inside For loop it seems. Below is the code and in line invdata := RES.get_array('document'); I get error if Nothing is returned.
Error report -
ORA-30625: method dispatch on NULL SELF argument is disallowed
ORA-06512: at "DISTINV.SOAP_CALL_UPDATE3", line 173
ORA-06512: at line 1
30625. 00000 - "method dispatch on NULL SELF argument is disallowed"
*Cause: A member method of a type is being invoked with a NULL SELF
argument.
*Action: Change the method invocation to pass in a valid self argument.

Code:
BEGIN
requrl := 'http://searchv1-dev.tcc.etn.com:8080/EatonSearchApp/EatonSearchWS';
doc1 := '<?xml version="1.0" encoding="utf-8"?>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ws="http://ws.generic.eaton.com/">
<soapenv:Header/>
<soapenv:Body>
<ws:doProcess>
<arg0>';
doc3 := '</arg0>
</ws:doProcess>
</soapenv:Body>
</soapenv:Envelope>';
SELECT
json_object(
'searchApplication' VALUE 'etndisinv',
'searchApplicationKey' VALUE 'abc123',
'function' VALUE 'search',
'searchTerms' VALUE 'ignore',
'language' VALUE 'en_US',
'startingRecordNumber' VALUE '0',
'numberOfRecordsToReturn' VALUE '5000',
'filters' VALUE json_arrayagg(
json_object(
'filterName' VALUE PART_ID_QUALIFIER,
'filterValue' VALUE json_arrayagg(distinct part_id returning clob)
returning clob )
returning clob )
returning clob ) into doc2
FROM
TEMP_TABLE
GROUP BY
PART_ID_QUALIFIER;
reqtxt := doc1 || doc2 || doc3;

begin
req := UTL_HTTP.BEGIN_REQUEST(requrl, 'POST', 'HTTP/1.1');
utl_http.set_header(req, 'content-type', 'application/xml');
utl_http.set_header(req, 'Content-Length',length(reqtxt));
utl_http.write_text(req, reqtxt);
resp := UTL_HTTP.GET_RESPONSE(req);
LOOP UTL_HTTP.READ_LINE(resp, resptxt, TRUE);
call_doc := call_doc || resptxt;
END LOOP;
UTL_HTTP.END_RESPONSE(resp);

EXCEPTION WHEN UTL_HTTP.END_OF_BODY THEN UTL_HTTP.END_RESPONSE(resp);
WHEN OTHERS THEN UTL_HTTP.END_RESPONSE(resp);
end;
start_pos := INSTR(call_doc, '<return>') + 8;
end_pos := INSTR(call_doc, '</return>');
DOC := SUBSTR (call_doc, start_pos, end_pos - start_pos);
J_DOC := JSON_OBJECT_T(DOC);
--Parsing through JSON tree to get required JSON OBJECT
RES := J_DOC.GET_OBJECT('response').GET_OBJECT('searchResults');
--Inside the JSON object , we are traversing to the JSON ARRAY content
invdata := RES.get_array('document');
for indx in 0..invdata.get_size - 1 loop CASE WHEN invdata.get(indx).is_object
THEN invdist := TREAT (invdata.get(indx) AS json_object_t);
content := invdist.get_array('content');
invdist0 := TREAT (content.get(0) AS json_object_t);
attr_name := invdist0.get_string('name');
p_part_cat_number := invdist0.get_string('value');
invdist1 := TREAT (content.get(1) AS json_object_t);
p_upc := invdist1.get_string('value');
invdist2 := TREAT (content.get(2) AS json_object_t);
p_ean := invdist2.get_string('value');
invdist3 := TREAT (content.get(3) AS json_object_t);
p_pf_desc := invdist3.get_string('value');
--dbms_output.put_line('p_pf_desc value is ' || p_pf_desc);
begin
Update
distinv.dist_inv_trans
set
prod_family_desc = p_pf_desc,
CATALOG_NUMBER = p_part_cat_number
where
(
part_id = p_ean
or part_id = p_upc
or CATALOG_NUMBER = p_part_cat_number
)
and (
prod_family_desc is null
or CATALOG_NUMBER is null
or PDH_PART_CAT_NAME is null
);
commit;
dbms_output.put_line('updating record : ' || p_ean ||' '|| p_upc ||' '|| p_part_cat_number);
exception when too_many_rows then null;
when others then null;
end;
END CASE;
end loop;
END;

Comments

Post Details

Added on Nov 9 2021
1 comment
311 views