CODEWITHSUNDEEP
jsonoracle-databaseplsqloracle-apexclob
virtual transfer
virtual transfer

Reputation: 23

Parsing Error Response from Oracle CLOB having a JSON Call

I have a following code that generates the response payload from APEX call. When the webservice is successful, the response comes out just fine. No issues.

However, when response has errors, I am unable to display the error with its full description using PLSQL API.

However, if I use the same payload and run it through POSTMAN or SOAPUI, I get full error.

How can I parse and receive complete error description from PLSQL?

Here is the code snippet in question:

 l_resp := apex_web_service.make_rest_request(p_url => g_fusion_url,
         p_credential_static_id => l_crd,
         p_http_method => l_method, p_body => p_body);
         DBMS_OUTPUT.PUT_LINE('STATUSCODE:'||apex_web_service.g_status_code);
        IF apex_web_service.g_status_code >= 400 THEN
            apex_debug.info('Request Body is %s', sys.dbms_lob.substr(l_resp, 4000, 1));

            raise_application_error(-20001, 'API Error2: '
                                            || apex_web_service.g_status_code
                                            || '-'
                                            || sys.dbms_lob.substr(l_resp, 1, 531));--1.4

If I run the Payload through SOAP UI, you will see that, we get complete description

enter image description here

But the same API from SQL Developer or SQL Plus gives only following, which is not enough.

Error report -
ORA-20001: Error in Main:[Brkpoint:M01|-20004|ORA-20004: Error in apinvoices:ORA-20004: Error in Request_fusion:ORA-20001: API Error2: 400-
ORA-06512: at "PROCORE_WH_DATA.XXMOR_RR_BILLING_INVOICE_PROC", line 689
ORA-06512: at line 1]
ORA-06512: at "PROCORE_WH_DATA.XXMOR_RR_BILLING_INVOICE_PROC", line 52
ORA-06512: at line 4

Please advise how can I resolve this. My ask is, I want to capture full length error message like it shows in the SOAP UI in PLSQ Calls.

Thank you Darsh

Upvotes: 0

Views: 279

Answers (1)

d r
d r

Reputation: 7846

Your problem could be different position (switched places) of parameters 2 & 3 in SubStr() vs dbms_lob.SubStr() functions.
SubStr(char, position, length)
dbms_lob.SubStr(clob, amount, offset)

Your code dbms_lob.substr(l_resp, 1, 531) results with 1 char at position 531
what you need probably is SubStr(l_resp, 1, 531) since you already got 4000 chars l_resp string.

See the code below:

SET SERVEROUTPUT ON
Declare
    my_text_varchar     VarChar2(10) := '0123456789';
    my_text_clob        CLOB := '0123456789';
Begin
    dbms_output.put_line('SubStr(my_text_varchar, 1, 10)        --->' || SubStr(my_text_varchar, 1, 10));
    dbms_output.put_line('dbms_lob.SubStr(my_text_clob, 1, 10): --->' || dbms_lob.SubStr(my_text_clob, 1, 10));
    --
    dbms_output.put_line('dbms_lob.SubStr(my_text_clob, 10, 1): --->' || dbms_lob.SubStr(my_text_clob, 10, 1));
End;
/
--  R e s u l t :
--  SubStr(my_text_varchar, 1, 10)        --->0123456789
--  dbms_lob.SubStr(my_text_clob, 1, 10): --->9
--  dbms_lob.SubStr(my_text_clob, 10, 1): --->0123456789

Upvotes: 0

Related Questions