Package que permite realizar consultas pela API ReceitaWS (https://www.receitaws.com.br/api)

[MGCLI]_[PACKAGE]_[CLI_PCK_API_RECEITAWS].sql

  CREATE OR REPLACE PACKAGE "MGCLI"."CLI_PCK_API_RECEITAWS" AS 

  -- https://www.receitaws.com.br/v1/cnpj/[cnpj]
  FUNCTION F_CNPJ(pCNPJ MGGLO.GLO_AGENTES.AGN_ST_CGC%TYPE) RETURN CLOB;
  
  -- https://www.receitaws.com.br/v1/cnpj/[cnpj]
  PROCEDURE P_CNPJ(pCNPJ MGGLO.GLO_AGENTES.AGN_ST_CGC%TYPE);

END CLI_PCK_API_RECEITAWS;
CREATE OR REPLACE PACKAGE BODY "MGCLI"."CLI_PCK_API_RECEITAWS" AS

  p_URL_BASE VARCHAR2(4000) := 'https://www.receitaws.com.br/v1';
  p_URL      VARCHAR2(4000) := p_URL_BASE;
  p_Token    VARCHAR2(250) := '';
  g_CLOB    CLOB;
  g_XML     XMLTYPE;
  
  PROCEDURE ASSERT(cond in boolean, err in varchar2) IS
  BEGIN
    if not cond then
      raise_application_error(-20000, $$PLSQL_UNIT || ' assertion failed: ' || err);
    end if;
  END ASSERT;
  
  PROCEDURE SET_WALLET IS
    wallet_path       VARCHAR2(4000);
    wallet_password   VARCHAR2(4000);
  BEGIN
    wallet_path := 'file:/oracle/admin/mega/wallet'; --setting(setting_wallet_path);
    wallet_password := 'WalletPasswd123'; --setting(setting_wallet_password);
    IF
        wallet_path IS NOT NULL OR wallet_password IS NOT NULL
    THEN
        sys.utl_http.set_wallet(wallet_path,wallet_password);
    END IF;
  END SET_WALLET;
  
  PROCEDURE LOG_HEADERS(resp in out nocopy sys.utl_http.resp) is
    name  varchar2(256);
    value varchar2(1024);
  BEGIN
    
    FOR I IN 1..SYS.UTL_HTTP.GET_HEADER_COUNT(resp) LOOP
      sys.utl_http.get_header(resp, i, name, value);
    END LOOP;
    
  END LOG_HEADERS;
  
  FUNCTION GET_RESPONSE(resp in out nocopy sys.utl_http.resp) return clob is
    buf varchar2(32767);
    ret clob := empty_clob;
  BEGIN
    
    sys.dbms_lob.createtemporary(ret, true);
    
    BEGIN
      loop
        sys.utl_http.read_text(resp, buf, 32767);
        sys.dbms_lob.writeappend(ret, length(buf), buf);
      end loop;
    EXCEPTION
      WHEN sys.utl_http.end_of_body THEN
        NULL;
    END;
    sys.utl_http.end_response(resp);
    
    return ret;
    
  END GET_RESPONSE;
  
  FUNCTION GET(
      P_URL       IN VARCHAR2
    , P_METHOD    IN VARCHAR2 := 'GET'
    , P_PARAMS    IN VARCHAR2 := NULL
    , P_USER      IN VARCHAR2 := NULL
    , P_PWD       IN VARCHAR2 := NULL
    , P_STATUS    OUT VARCHAR2
  ) RETURN CLOB IS
    URL   VARCHAR2(4000) := p_URL;
    REQ   sys.utl_http.req;
    RESP  sys.utl_http.resp;
    RET   CLOB;
  BEGIN
    
    ASSERT(P_URL IS NOT NULL, 'get: p_url canoot be null');
    ASSERT(P_METHOD IS NOT NULL, 'get: p_url cannot be null');
    
    IF p_PARAMS IS NOT NULL THEN
      url := url || '?' || P_PARAMS;
    END IF;
    
    SET_WALLET;
    BEGIN
        req := sys.utl_http.begin_request(url => url, method => P_METHOD);
    EXCEPTION WHEN OTHERS THEN -- TODO; enviar Log para Ajustar certificado!
        RAISE_APPLICATION_ERROR(-20101, UTL_HTTP.get_detailed_sqlerrm || ' URL: ' || url);
        --RETURN TO_CLOB(UTL_HTTP.get_detailed_sqlerrm);
    END;
    if (P_USER IS NOT NULL OR P_PWD IS NOT NULL) THEN
      sys.utl_http.set_authentication(req, P_USER, P_PWD);
    END IF;
    
    --sys.utl_http.set_header(req, 'Accept', 'application/xml');
    sys.utl_http.set_header(req, 'Content-Type', 'text/xml;charset=UTF-8');
    --sys.utl_http.set_header(req, 'Authorization', 'Basic ');
    
    resp := sys.utl_http.get_response(req);
    
    LOG_HEADERS(resp);
    
    if resp.status_code != '200' then
      --raise_application_error(-20000, 'get call failed ' || resp.status_code || ' ' || resp.reason_phrase || ' [' || url || ']');
      mgcli.p_mail_log_generico(2,'CLI_PCK_API_RECEITAWS', 'get call failed ' || resp.status_code || ' ' || resp.reason_phrase || ' [' || url || ']');
      INSERT INTO MGCLI.CLI_API_RECEITAWS_REJECT(DD_ST_URL, DD_CL_RESPONSE) VALUES (url, TO_CLOB('get call failed ' || resp.status_code || ' ' || resp.reason_phrase));
    end if;
    
    ret := GET_RESPONSE(resp);
    
    P_STATUS := resp.status_code;
    
    return ret;
  END GET;
  
  PROCEDURE P_INSERE_LOG_RESPONSE(
    pURL            IN MGCLI.CLI_API_DIRECTDATA_RESPONSE.DD_ST_URL%TYPE,
    pMetodo         IN MGCLI.CLI_API_DIRECTDATA_RESPONSE.DD_ST_METODO%TYPE,
    pResponse       IN MGCLI.CLI_API_DIRECTDATA_RESPONSE.DD_CL_RESPONSE%TYPE,
    pFiltroCampos   IN MGCLI.CLI_API_DIRECTDATA_RESPONSE.DD_ST_FILTRO_CAMPOS%TYPE,
    pFiltroValores  IN MGCLI.CLI_API_DIRECTDATA_RESPONSE.DD_ST_FILTRO_VALORES%TYPE
  ) IS
  BEGIN
    
    INSERT INTO MGCLI.CLI_API_RECEITAWS_RESPONSE(
            DD_ST_URL
        ,   DD_ST_METODO
        ,   DD_DT_DATA
        ,   DD_CL_RESPONSE
        ,   DD_ST_FILTRO_CAMPOS
        ,   DD_ST_FILTRO_VALORES
    )
    VALUES(
        pURL, pMetodo, SYSDATE, pResponse, pFiltroCampos, pFiltroValores
    );
    
  END P_INSERE_LOG_RESPONSE;

  FUNCTION F_CNPJ(pCNPJ MGGLO.GLO_AGENTES.AGN_ST_CGC%TYPE) RETURN CLOB AS
    ret                 clob := empty_clob;
    l_method            VARCHAR2(100) := '/cnpj';
    l_filtro_campos     MGCLI.CLI_API_DIRECTDATA_RESPONSE.DD_ST_FILTRO_CAMPOS%TYPE;
    l_filtro_valores    MGCLI.CLI_API_DIRECTDATA_RESPONSE.DD_ST_FILTRO_CAMPOS%TYPE;
    l_STATUS            PLS_INTEGER;
  BEGIN
    l_filtro_campos     := 'cnpj';
    l_filtro_valores     := TRIM(pCNPJ);
    
    p_URL := p_URL_BASE || TRIM(l_method) ||'/' || TRIM(REGEXP_REPLACE(pCNPJ, '[^0-9]', '')) ;
    
    --dbms_output.put_line(p_URL);
    
    g_CLOB := GET(
        P_URL     => p_URL
      , P_METHOD  => 'GET'
      , P_STATUS  => l_STATUS
    );
    
    ret := g_CLOB;
    
    RETURN ret;
  END F_CNPJ;

  PROCEDURE P_CNPJ(pCNPJ MGGLO.GLO_AGENTES.AGN_ST_CGC%TYPE) AS
    ret                 clob := empty_clob;
    l_method            VARCHAR2(100) := '/cnpj';
    l_filtro_campos     MGCLI.CLI_API_DIRECTDATA_RESPONSE.DD_ST_FILTRO_CAMPOS%TYPE;
    l_filtro_valores    MGCLI.CLI_API_DIRECTDATA_RESPONSE.DD_ST_FILTRO_CAMPOS%TYPE;
    l_STATUS            PLS_INTEGER;
  BEGIN
    l_filtro_campos     := 'cnpj';
    l_filtro_valores     := TRIM(pCNPJ);
    
    p_URL := p_URL_BASE || TRIM(l_method) ||'/' || TRIM(REGEXP_REPLACE(pCNPJ, '[^0-9]', '')) ;
    
    --dbms_output.put_line(p_URL);
    
    g_CLOB := GET(
        P_URL     => p_URL
      , P_METHOD  => 'GET'
      , P_STATUS  => l_STATUS
    );
    
    ret := g_CLOB;
    
    --dbms_output.put_line('XML:' || TO_CHAR(ret));
    IF l_STATUS = '200' THEN
      P_INSERE_LOG_RESPONSE(p_URL, TRIM(l_method), ret, l_filtro_campos, l_filtro_valores);
    END IF;
  END P_CNPJ;

END CLI_PCK_API_RECEITAWS;

Example of using:

SELECT MGCLI.CLI_PCK_API_RECEITAWS.F_CNPJ('53967360000123') JSON FROM DUAL