Пример работы с подсказками DaData на PL/SQL (Oracle)

suggest.pls

declare
  http_req utl_http.req;
  http_resp utl_http.resp;
  query varchar2(32767);
  query_raw RAW(512);
  resp_text varchar2(32767);
begin
  query := '{ "query": "7719402047", "count": 1 }';
  query := convert(query, 'UTF8');
  
  http_req := utl_http.begin_request('http://suggestions.dadata.ru/suggestions/api/4_1/rs/findById/party', 'POST', 'HTTP/1.1');
  utl_http.set_header(http_req, 'Content-Type', 'application/json');
  utl_http.set_header(http_req, 'Accept', 'application/xml');
  utl_http.set_header(http_req, 'Authorization', 'Token API_KEY');
  utl_http.set_header(http_req, 'Content-Length', lengthb(query));  
  
  query_raw := utl_raw.cast_to_raw(query); 
  utl_http.write_raw(http_req, query_raw);    
  
  http_resp := utl_http.get_response(http_req);
  utl_http.set_body_charset(http_resp, 'UTF-8');
  utl_http.read_text(http_resp, resp_text);
  dbms_output.put_line('HTTP response status code: ' || http_resp.status_code);
  dbms_output.put_line('HTTP response reason: ' || http_resp.reason_phrase);
  dbms_output.put_line('HTTP response text: ' || resp_text);
  utl_http.end_response(http_resp);
end;

CREATE OR REPLACE FUNCTION SEND_DB_REQUST2 (

                                        P_API     VARCHAR2,
                                        P_CONTENT VARCHAR2
                                      ) RETURN VARCHAR2

IS
n number;
http_req utl_http.req;
http_resp utl_http.resp;
query varchar2(32767);
query_raw RAW(512);
resp_text clob;
begin
query := P_CONTENT;
query := convert(query, 'UTF8');

http_req := utl_http.begin_request(P_API, 'POST', 'HTTP/1.1');
utl_http.set_header(http_req, 'Content-Type', 'application/json');
utl_http.set_header(http_req, 'Accept', 'application/xml');
utl_http.set_header(http_req, 'Authorization', 'Token 308233a21e8d661fb751837548d26fae759992b7');
utl_http.set_header(http_req, 'Content-Length', lengthb(query));

query_raw := utl_raw.cast_to_raw(query);
utl_http.write_raw(http_req, query_raw);

http_resp := utl_http.get_response(http_req);
utl_http.set_body_charset(http_resp, 'UTF-8');
utl_http.read_text(http_resp, resp_text);
---- dbms_output.put_line('HTTP response status code: ' || http_resp.status_code);
-- dbms_output.put_line('HTTP response reason: ' || http_resp.reason_phrase);
-- dbms_output.put_line('HTTP response text: ' || resp_text);
utl_http.end_response(http_resp);
return resp_text;
utl_http.end_response(http_resp);

EXCEPTION
WHEN UTL_HTTP.TOO_MANY_REQUESTS THEN
UTL_HTTP.END_RESPONSE(http_resp);
end;

SELECT
SEND_DB_REQUST2 (
'http://suggestions.dadata.ru/suggestions/api/4_1/rs/findById/party',
'{ "query": "7719402047", "count": 1 }'
)
FROM DUAL;

[1]: ORA-06502: PL/SQL: numeric or value error: character string buffer too small

возвращаемые данные слишком велики(
тут нужен CLOB. я пока не победил

решено

return REGEXP_SUBSTR(resp_text, '(.+?)'
, 1
, 1
, NULL
, 1
) ;

@gekchuk просто меняй varchar на clob и raw на блоб, оно работает