Importando municípios e estados usando a API do IBGE no Oracle Database



Nessa publicação iremos ver como podemos importar os estados e municípios do Brasil usando a API gratuita do IBGE.gov.br em nosso banco Oracle 😄.

Iremos seguir a criação do seguinte modelo de dados:


Execute o código DDL abaixo para criar as tabelas que iremos precisar:

CREATE TABLE estados (
    id_estado NUMBER NOT NULL,
    sigla     CHAR(2 BYTE),
    nome      VARCHAR2(1000 BYTE),
    regiao    VARCHAR2(100 BYTE)
);

ALTER TABLE estados ADD CONSTRAINT estados_pk PRIMARY KEY ( id_estado );

CREATE TABLE municipios (
    id_municipio NUMBER NOT NULL,
    nome         VARCHAR2(2000 BYTE),
    mesorregiao  VARCHAR2(600 BYTE),
    id_estado    NUMBER NOT NULL
);

ALTER TABLE municipios ADD CONSTRAINT municipios_pk PRIMARY KEY ( id_municipio );

ALTER TABLE municipios
    ADD CONSTRAINT municipios_estados_fk FOREIGN KEY ( id_estado )
        REFERENCES estados ( id_estado );

Importando Estados

A URL da API que iremos utilizar é https://servicodados.ibge.gov.br/api/v1/localidades/estados. Ela retorna um JSON com os dados no seguinte formato: 
{
  "id": 11,
  "sigla": "RO",
  "nome": "Rondônia",
  "regiao": {
  "id": 1,
  "sigla": "N",
  "nome": "Norte"
}
Basta rodar o script abaixo para importar os estados em nossa tabela: 

DECLARE
  v_clob CLOB;
BEGIN
  v_clob := apex_web_service.make_rest_request(
    p_url => 'https://servicodados.ibge.gov.br/api/v1/localidades/estados', 
    p_http_method => 'GET'
  );
  FOR i IN (
    SELECT
      jt.*
    FROM
        JSON_TABLE ( v_clob, '$[*]'
          COLUMNS (
            row_number FOR ORDINALITY,
            id NUMBER PATH '$.id',
            sigla VARCHAR2 ( 2 ) PATH '$.sigla',
            nome VARCHAR2 ( 1000 ) PATH '$.nome',
            regiao VARCHAR2 ( 600 ) PATH '$.regiao.nome'
          )
        )
      AS jt
  ) LOOP
    INSERT INTO estados (
      id_estado,
      sigla,
      nome,
      regiao
    ) VALUES (
      i.id,
      i.sigla,
      i.nome,
      i.regiao
    );

  END LOOP;

  COMMIT;
END;

Importando municípios


A URL da API que iremos utilizar é https://servicodados.ibge.gov.br/api/v1/localidades/municipios. Ela retorna um JSON com os dados no seguinte formato: 
{
    "id": 1100015,
    "nome": "Alta Floresta D'Oeste",
    "microrregiao": {
      "id": 11006,
      "nome": "Cacoal",
      "mesorregiao": {
        "id": 1102,
        "nome": "Leste Rondoniense",
        "UF": {
          "id": 11,
          "sigla": "RO",
          "nome": "Rondônia",
          "regiao": {
            "id": 1,
            "sigla": "N",
            "nome": "Norte"
          }
        }
      }
    },
    "regiao-imediata": {
      "id": 110005,
      "nome": "Cacoal",
      "regiao-intermediaria": {
        "id": 1102,
        "nome": "Ji-Paraná",
        "UF": {
          "id": 11,
          "sigla": "RO",
          "nome": "Rondônia",
          "regiao": {
            "id": 1,
            "sigla": "N",
            "nome": "Norte"
          }
        }
      }
    }
  }
Basta rodar o script abaixo para importar os municipios em nossa tabela: 

DECLARE
  v_clob  CLOB;
  v_count NUMBER;
BEGIN
  v_clob := apex_web_service.make_rest_request(
    p_url => 'https://servicodados.ibge.gov.br/api/v1/localidades/municipios', 
    p_http_method => 'GET'
  );
  
  FOR i IN (
    SELECT
      jt.*
    FROM
        JSON_TABLE ( v_clob, '$[*]'
          COLUMNS (
            row_number FOR ORDINALITY,
            id NUMBER PATH '$.id',
            nome VARCHAR2 ( 4000 ) PATH '$.nome',
            mesorregiao VARCHAR2 ( 4000 ) PATH '$.microrregiao.mesorregiao.nome',
            id_estado NUMBER PATH '$.microrregiao.mesorregiao.UF.id'
          )
        )
      AS jt
  ) LOOP
    SELECT
      COUNT(*)
    INTO v_count
    FROM
      municipios m
    WHERE
      m.id_municipio = i.id;

    IF v_count = 0 THEN
      INSERT INTO municipios (
        id_municipio,
        nome,
        mesorregiao,
        id_estado
      ) VALUES (
        i.id,
        i.nome,
        i.mesorregiao,
        i.id_estado
      );

    END IF;

  END LOOP;

  COMMIT;
END;

Utilizando a UTL_HTTP para requisições REST

Caso não tenha o APEX instalado em seu banco Oracle, não tem problema! Basta alguns ajustes para utilizamos a UTL_HTTP.
Primeiramente precisamos habilitar os serviços de network no banco, caso contrário irá dar o erro ORA-44416: Invalid ACL - ajuste o parâmetro principal_name com o nome do schema que irá rodar o script e execute como SYSDBA:
BEGIN
    DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
        host => '*',
        ace => xs$ace_type(privilege_list => xs$name_list('connect'),
                           principal_name => 'SEU_SCHEMA',
                           principal_type => xs_acl.ptype_db));
END;
/
Agora rodamos nosso script com a UTL_HTTP:
DECLARE
  v_clob           CLOB;
  v_count          NUMBER;
  request_context  utl_http.request_context_key;
  req              utl_http.req;
  res              utl_http.resp;
  buffer           varchar2(32767);
BEGIN
  request_context := UTL_HTTP.CREATE_REQUEST_CONTEXT(
       wallet_path          => 'file:/home/oracle/wallets/wallet',
       wallet_password      => 'suasenha');
       
  req := UTL_HTTP.BEGIN_REQUEST(
       url                  => 'https://servicodados.ibge.gov.br/api/v1/localidades/municipios',
       method               => 'GET',
       request_context      => request_context);
  
  UTL_HTTP.SET_HEADER (
   req,
   'Content-Type',
   'application/json; charset=utf-8');
  
  UTL_HTTP.SET_BODY_CHARSET (
   charset => 'UTF-8');
     
  res := UTL_HTTP.GET_RESPONSE(req);     
  
  begin
    loop
      utl_http.read_text(res, buffer,32767);
      v_clob := v_clob || buffer;
    end loop;
    utl_http.end_response(res);
  exception
    when utl_http.end_of_body
    then
      utl_http.end_response(res);
  end;
  
  UTL_HTTP.END_REQUEST(req);
  
  FOR i IN (
    SELECT
      jt.*
    FROM
        JSON_TABLE ( v_clob, '$[*]'
          COLUMNS (
            row_number FOR ORDINALITY,
            id NUMBER PATH '$.id',
            nome VARCHAR2 ( 4000 ) PATH '$.nome',
            mesorregiao VARCHAR2 ( 4000 ) PATH '$.microrregiao.mesorregiao.nome',
            id_estado NUMBER PATH '$.microrregiao.mesorregiao.UF.id'
          )
        )
      AS jt
  ) LOOP
    SELECT
      COUNT(*)
    INTO v_count
    FROM
      municipios m
    WHERE
      m.id_municipio = i.id;
      
    IF v_count = 0 THEN
      INSERT INTO municipios (
        id_municipio,
        nome,
        mesorregiao,
        id_estado
      ) VALUES (
        i.id,
        i.nome,
        i.mesorregiao,
        i.id_estado
      );

    END IF;

  END LOOP;

  COMMIT;
END;

Tratando erro de certificado ORA-29024

Ao executar o script acima, pode ocorrer o seguinte erro:

ORA-29273: falha na solicitação HTTP
ORA-06512: em "APEX_220100.WWV_FLOW_WEB_SERVICES", line 1182
ORA-06512: em "APEX_220100.WWV_FLOW_WEB_SERVICES", line 782
ORA-29024: Falha de validação de certificado
ORA-06512: em "SYS.UTL_HTTP", line 380
ORA-06512: em "SYS.UTL_HTTP", line 1148
ORA-06512: em "APEX_220100.WWV_FLOW_WEB_SERVICES", line 756
ORA-06512: em "APEX_220100.WWV_FLOW_WEB_SERVICES", line 1023
ORA-06512: em "APEX_220100.WWV_FLOW_WEB_SERVICES", line 1371
ORA-06512: em "APEX_220100.WWV_FLOW_WEBSERVICES_API", line 568
ORA-06512: em line 5
29273. 00000 -  "HTTP request failed"
*Cause:    The UTL_HTTP package failed to execute the HTTP request.
*Action:   Use get_detailed_sqlerrm to check the detailed error message.
           Fix the error and retry the HTTP request.
Para corrigir esse erro, precisamos criar uma wallet e adicionar o certificado root do domínio https://servicodados.ibge.gov.br que estamos acessando. 
Caso esteja usando o Oracle Apex, adicione em uma wallet que já esteja configurar em sua instância. Caso não seja seu caso, adicione os parâmetros p_wallet_path (file:/home/oracle/wallets/wallet) e p_wallet_pwd na chamada da apex_web_service.make_rest_request. 
v_clob := apex_web_service.make_rest_request(
    p_url => 'https://servicodados.ibge.gov.br/api/v1/localidades/municipios', 
    p_http_method => 'GET',
    p_wallet_path => 'file:/home/oracle/wallets/wallet',
    p_wallet_pwd => 'suasenha'
  );
Parar criar a wallet execute o comando abaixo em seu servidor no qual o banco esteja instalado - conecte como usuário root ou oracle no Linux (ajuste a senha da wallet): 
orapki wallet create -wallet /home/oracle/wallets/wallet -pwd suasenha
Resultado esperado: 
Oracle PKI Tool Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
Copyright (c) 2004, 2021, Oracle and/or its affiliates. All rights reserved.

Operation is successfully completed.
Obtenha os certificados do dominio e salve o segundo certificado (2 s:C = US, ST = New Jersey, L = Jersey City, O = The USERTRUST Network, CN = USERTrust RSA Certification Authority) a partir do -----BEGIN CERTIFICATE----- até o -----END CERTIFICATE----- em um arquivo com a extenção .pem: 
openssl s_client -showcerts -verify 5 -connect servicodados.ibge.gov.br:443
Salvei o arquivo em /home/oracle/wallets/ibge.pem.
E agora adicionamos o certificado: 
orapki wallet add -wallet /home/oracle/wallets/wallet -trusted_cert -cert /home/oracle/wallets/ibge.pem
Resultado esperado: 
Oracle PKI Tool Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
Copyright (c) 2004, 2021, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:   
Operation is successfully completed.
Pronto, basta rodar os scripts agora.

Comentários

Postar um comentário