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!

UTL_HTTP.READ_TEXT some part response is broken

SupaplexMay 22 2023

I'm trying GET response from https server using utl_http.

Database Version 19.9.0.0.0

SELECT UTL_I18N.MAP_CHARSET(VALUE),VALUE FROM nls_database_parameters WHERE parameter = 'NLS_CHARACTERSET';

UTF-8 AL32UTF8

declare
   Req             Utl_Http.Req;
   Resp            Utl_Http.Resp;
   X_Result_Text   varchar2(32672);
   X_Result_clob   clob;

   v_waypoints varchar2(2000);
   v_origins varchar2(2000);
   v_destination varchar2(2000);
   v_name varchar2(1000);
   v_value varchar2(1000);
   v_url varchar2(1000):='https://maps.googleapis.com/maps/api/directions/json?';

begin
   UTL_TCP.close_all_connections ();
   v_origins:='XXXXXXXXXX';
   v_destination:='XXXXXXXXXXX';
v_waypoints:='XXXXXXXXXXX';
   v_url:=v_url||'origin='||UTL_URL.Escape(v_origins,TRUE, 'UTF-8');
   v_url:=v_url||chr(38)||'destination='||UTL_URL.Escape(v_destination,TRUE, 'UTF-8');
   v_url:=v_url||chr(38)||'waypoints='||UTL_URL.Escape(v_waypoints,TRUE, 'UTF-8');
   v_url:=v_url||chr(38)||'key='||SOME_PACKAGE.GET_KEY(29);
   DBMS_OUTPUT.PUT_LINE('url: ' || v_url);
   --CREATE CONNECTION
   Utl_Http.Set_Wallet('file:c:\wallet', 'pass');
   
   Req := Utl_Http.BEGIN_REQUEST(v_url, 'GET', 'HTTP/1.1');
   Utl_Http.Set_Header (Req, 'Content-Type', 'text/json;charset=UTF-8');
   Utl_Http.Set_Header (Req, 'Connection', 'keep-alive');
   Utl_Http.Set_Header (Req, 'User-Agent', 'PostmanRuntime/7.32.2');
   Utl_Http.Set_Header (Req, 'Accept-Encoding', 'gzip, deflate, br');
   Utl_Http.Set_Header (Req, 'Accept', '*/*');
   
   Resp := Utl_Http.Get_Response (Req);
   
   FOR i IN 1..UTL_HTTP.GET_HEADER_COUNT(resp) LOOP
       UTL_HTTP.GET_HEADER(resp, i, v_name, v_value);
       DBMS_OUTPUT.PUT_LINE(v_name || ': -  ' || v_value);  
   END LOOP;
   
   DBMS_LOB.CREATETEMPORARY(X_Result_Clob, FALSE);
   Begin
     Loop
       Utl_Http.Read_Text(Resp, X_Result_Text, 8168);
       Dbms_Lob.Writeappend(X_Result_Clob, Length(X_Result_Text), X_Result_Text);
     End Loop;
   Exception
      When Utl_Http.End_Of_Body Then
        Utl_Http.End_Response(Resp);
   End;
   update api_message set clob_response = X_Result_Clob where id = 3224633;
  
end;

In table api_message where id = 3224633 in column clob_response data is broken.
I can see the JSON structure, but I see unreadable values in random places. This is a problem. For this reason, the data is incomplete. The JSON content could not be properly parsed

dbmsoutput (get_header_response):
Content-Type: - application/json; charset=UTF-8
i tried too set
- Utl_Http.Set_Body_Charset('UTF8');

or

- read data directly to dbmsoutput

always the same. Some data is broken

When i change get response method using utl_http.read_raw, and save them to clob using UTL_RAW.cast_to_varchar2, sometimes was have a error ora-29275, but i don't understand why ?

This post has been answered by Paulzip on May 25 2023
Jump to Answer
Comments
Post Details
Added on May 22 2023
16 comments
1,846 views