ORA-20007: Erro ao recalcular todas as quantidades de vínculos para a pessoa

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
Responder
robson.amaral
Rank: Estagiário Júnior
Rank: Estagiário Júnior
Mensagens: 2
Registrado em: Qua, 14 Abr 2021 4:30 pm
Localização: Espirito Santo

Boa Tarde!

Colegas, estou recebendo um log de erro da minha query, e gostaria de apoio de vocês para sanar o problema.

Mensagem de erro: ORA-20000: RG: 4550044
SINCRONIZA_SIC
IMPORTA_PESSOA_SIC
ORA-20007: Erro ao recalcular todas as quantidades de vínculos para a pessoa: ORA-20007: Erro ao recalcular quandidade de vínculos: ORA-01007: a variável não está na lista de seleções

Eu possuo uma package onde realizamos o calculo/ recalculo de quantidades de vínculos, esse vinculos nada mais é do que por exemplo pessoa.

Segue a programação da query.

Selecionar tudo

CREATE OR REPLACE PACKAGE BODY SISPES.PKG_INTEGRACAO
/***********************************************************************************************

    * Alteração dos procedimentos de cálculo de quantidade de vínculos e de atualização de quan-
        tidade de vínculos para considerar quantidade de pessoas com mesmos documentos.
***********************************************************************************************/
AS

  /*********************************************************************************************
   Função para buscar a quantidades de vinculos de pessoas
  *********************************************************************************************/
  FUNCTION CALCULAR_QTDE_VINCULO_PESSOA(
    -->> Parâmetro de Entrada <<--
    pe_nSeqPessoa               IN NUMBER DEFAULT 0
  ) RETURN tab_QtdeVincPessoa PIPELINED
  IS

    -- Tipo de cursor
    TYPE TP_CURSOR IS REF CURSOR;

    C_CONSULTA  TP_CURSOR;

    v_sql                   VARCHAR2( 32000 );
    rec_QtdeVincPessoa      VINCULO_PESSOA%ROWTYPE;
    reg                     VINCULO_PESSOA%ROWTYPE;
    
    v_msg_log_erro          VARCHAR( 4000 ) := NULL;

  BEGIN

    v_sql := 
        'WITH
            stLigacao AS (
                SELECT ''SEMELHANCA'' AS ligacao, PKG_INTEGRACAO.BUSCAR_SEQ_LIGACAO(''SEMELHANCA'') AS seq_ligacao, ''MESMA_PESSOA'' tipo_ligacao FROM DUAL
                UNION ALL SELECT ''PARENTESCO'', PKG_INTEGRACAO.BUSCAR_SEQ_LIGACAO(''PARENTESCO''), NULL FROM DUAL
                UNION ALL SELECT ''CRIMINAL'', PKG_INTEGRACAO.BUSCAR_SEQ_LIGACAO(''CRIMINAL''), ''MESMA PESSOA'' FROM DUAL
                UNION ALL SELECT ''MESMOS_DOCUMENTOS'', PKG_INTEGRACAO.BUSCAR_SEQ_LIGACAO(''MESMOS_DOCUMENTOS''), NULL FROM DUAL
            ),
            stTipoLigacao AS (
                SELECT DISTINCT DECODE( lig.nome, ''PARENTESCO'', 0, ''MESMOS_DOCUMENTOS'', -1, tpLig.seq_tipo_ligacao ) AS seq_tipo_ligacao_atual--, tpLig.seq_tipo_ligacao
                    , DECODE( lig.nome, ''MESMOS_DOCUMENTOS'', ''DOCUMENTO'', lig.nome) AS ligacao
                FROM Tipo_Ligacao tpLig
                    JOIN Ligacao lig ON tpLig.seq_ligacao = lig.seq_ligacao
                    JOIN stLigacao stLig ON lig.seq_ligacao = stLig.seq_ligacao
                WHERE lig.nome IN (''PARENTESCO'', ''MESMOS_DOCUMENTOS'')
                    OR tpLig.nome = stLig.tipo_ligacao
            ),
            stPessoaVinculos AS (
                SELECT DISTINCT *
                FROM ( 
                SELECT vin.seq_entidade_a AS seq_pessoa
                    , vin.seq_tipo_entidade_b AS seq_tipo_entidade_vinculada
                    , vin.seq_entidade_b AS seq_entidade_vinculada
                    --, vin.seq_tipo_ligacao
                    , DECODE( (SELECT lig.nome
                            FROM Tipo_Ligacao tpLig
                            JOIN Ligacao lig ON tpLig.seq_ligacao = lig.seq_ligacao
                            WHERE tpLig.seq_tipo_ligacao = vin.seq_tipo_ligacao )
                        , ''PARENTESCO'', 0
                        , ''MESMOS_DOCUMENTOS'', -1
                        , vin.seq_tipo_ligacao
                    ) AS seq_tipo_ligacao
                    , vin.seq_sistema
                    , DECODE( vin.seq_tipo_entidade_b
                        , PKG_INTEGRACAO.BUSCAR_SEQ_ENTIDADE(''MULTIMIDIA'')
                            , ( 
                                SELECT mult.tipo 
                                FROM Multimidia mult
                                WHERE mult.seq_multimidia = vin.seq_entidade_b 
                            )
                        , NULL 
                    ) AS tipo_multimidia
                FROM Vinculo vin
                WHERE ';
                
    IF ( pe_nSeqPessoa <> 0 ) THEN
        v_sql := v_sql || 'vin.seq_entidade_a = ' || pe_nSeqPessoa || '
                    AND ';
    END IF;
    
    v_sql := v_sql || 'vin.seq_tipo_entidade_a = 7
                UNION ALL
                SELECT vin.seq_entidade_b AS seq_pessoa
                    , vin.seq_tipo_entidade_a AS seq_tipo_entidade_vinculada
                    , vin.seq_entidade_a AS seq_entidade_vinculada
                    --, vin.seq_tipo_ligacao
                    , DECODE( (SELECT lig.nome
                        FROM Tipo_Ligacao tpLig
                        JOIN Ligacao lig ON tpLig.seq_ligacao = lig.seq_ligacao
                        WHERE tpLig.seq_tipo_ligacao = vin.seq_tipo_ligacao )
                        , ''PARENTESCO'', 0
                        , ''MESMOS_DOCUMENTOS'', -1
                        , vin.seq_tipo_ligacao
                    ) AS seq_tipo_ligacao
                    , vin.seq_sistema
                    , DECODE( vin.seq_tipo_entidade_a
                        , PKG_INTEGRACAO.BUSCAR_SEQ_ENTIDADE(''MULTIMIDIA'')
                            , ( 
                                SELECT mult.tipo 
                                FROM Multimidia mult
                                WHERE mult.seq_multimidia = vin.seq_entidade_a 
                            )
                        , NULL 
                    ) AS tipo_multimidia
                FROM Vinculo vin
                WHERE ';
    IF ( pe_nSeqPessoa <> 0 ) THEN
        v_sql := v_sql || 'vin.seq_entidade_b = ' || pe_nSeqPessoa || '
                    AND ';
    END IF;
    v_sql := v_sql || 'vin.seq_tipo_entidade_b = 7
                    --AND vin.seq_tipo_entidade_a <> 7
              )
            )
            , stArticulacao AS (
                SELECT stPesVin.seq_pessoa
                    --, stPesVin.seq_tipo_entidade_vinculada,
                    , DECODE( stPesVin.seq_tipo_entidade_vinculada
                        , PKG_INTEGRACAO.BUSCAR_SEQ_ENTIDADE(''PESSOA'')
                            , TO_CHAR( stPesVin.seq_tipo_entidade_vinculada ) || ''-'' 
                                || TO_CHAR( tpLig.seq_tipo_ligacao_atual )
                        , PKG_INTEGRACAO.BUSCAR_SEQ_ENTIDADE(''MULTIMIDIA'')
                            , TO_CHAR( stPesVin.seq_tipo_entidade_vinculada ) || ''-'' 
                                || TO_CHAR( stPesVin.tipo_multimidia )
                        , TO_CHAR( stPesVin.seq_tipo_entidade_vinculada ) ) AS cod_campo 
                    , 1 AS quantidade
                FROM stPessoaVinculos stPesVin
                    LEFT JOIN stTipoLigacao tpLig ON stPesVin.seq_tipo_ligacao = tpLig.seq_tipo_ligacao_atual --tpLig.seq_tipo_ligacao
                --JOIN Entidade ent ON stPesVin.seq_tipo_entidade_vinculada = ent.seq_entidade
                WHERE stPesVin.seq_pessoa <> 0
            )
            , stDados AS (
                SELECT *
                FROM stArticulacao
                    PIVOT ( COUNT( quantidade ) AS qtd 
                        FOR ( cod_campo ) 
                        IN ( '; 

            FOR campos IN (
                WITH
                    stEntidade AS (
                        SELECT 'ARMA' AS entidade, PKG_INTEGRACAO.BUSCAR_SEQ_ENTIDADE('ARMA') AS seq_entidade FROM DUAL
                        UNION ALL SELECT 'BOLETIM', PKG_INTEGRACAO.BUSCAR_SEQ_ENTIDADE('BOLETIM') FROM DUAL
                        UNION ALL SELECT 'DOCUMENTO_IDENTIFICACAO', PKG_INTEGRACAO.BUSCAR_SEQ_ENTIDADE('DOCUMENTO_IDENTIFICACAO') FROM DUAL
                        UNION ALL SELECT 'DROGA', PKG_INTEGRACAO.BUSCAR_SEQ_ENTIDADE('DROGA') FROM DUAL
                        UNION ALL SELECT 'ENDERECO', PKG_INTEGRACAO.BUSCAR_SEQ_ENTIDADE('ENDERECO') FROM DUAL
                        UNION ALL SELECT 'OBJETO', PKG_INTEGRACAO.BUSCAR_SEQ_ENTIDADE('OBJETO') FROM DUAL
                        UNION ALL SELECT 'PESSOA', PKG_INTEGRACAO.BUSCAR_SEQ_ENTIDADE('PESSOA') FROM DUAL
                        UNION ALL SELECT 'RECURSO', PKG_INTEGRACAO.BUSCAR_SEQ_ENTIDADE('RECURSO') FROM DUAL
                        UNION ALL SELECT 'TELEFONE', PKG_INTEGRACAO.BUSCAR_SEQ_ENTIDADE('TELEFONE') FROM DUAL
                        UNION ALL SELECT 'UNIDADE_POLICIAL', PKG_INTEGRACAO.BUSCAR_SEQ_ENTIDADE('UNIDADE_POLICIAL') FROM DUAL
                        UNION ALL SELECT 'VEICULO', PKG_INTEGRACAO.BUSCAR_SEQ_ENTIDADE('VEICULO') FROM DUAL
                        UNION ALL SELECT 'MULTIMIDIA', PKG_INTEGRACAO.BUSCAR_SEQ_ENTIDADE('MULTIMIDIA') FROM DUAL
                        UNION ALL SELECT 'OCORRENCIA', PKG_INTEGRACAO.BUSCAR_SEQ_ENTIDADE('OCORRENCIA') FROM DUAL
                        UNION ALL SELECT 'ORGANIZACAO', PKG_INTEGRACAO.BUSCAR_SEQ_ENTIDADE('ORGANIZACAO') FROM DUAL
                    ),
                    stLigacao AS (
                        SELECT 'SEMELHANCA' AS ligacao, PKG_INTEGRACAO.BUSCAR_SEQ_LIGACAO('SEMELHANCA') AS seq_ligacao, 'MESMA_PESSOA' tipo_ligacao FROM DUAL
                        UNION ALL SELECT 'PARENTESCO', PKG_INTEGRACAO.BUSCAR_SEQ_LIGACAO('PARENTESCO'), NULL FROM DUAL
                        UNION ALL SELECT 'CRIMINAL', PKG_INTEGRACAO.BUSCAR_SEQ_LIGACAO('CRIMINAL'), 'MESMA PESSOA' FROM DUAL
                        UNION ALL SELECT 'MESMOS_DOCUMENTOS', PKG_INTEGRACAO.BUSCAR_SEQ_LIGACAO('MESMOS_DOCUMENTOS'), NULL FROM DUAL
                    ),
                    stTipoLigacao AS (
                        SELECT DECODE( lig.nome, 'PARENTESCO', 0, 'MESMOS_DOCUMENTOS', -1, tpLig.seq_tipo_ligacao ) AS seq_tipo_ligacao
                            , DECODE( lig.nome, 'MESMOS_DOCUMENTOS', 'DOCUMENTO', lig.nome) AS ligacao
                        FROM Tipo_Ligacao tpLig
                            JOIN Ligacao lig ON tpLig.seq_ligacao = lig.seq_ligacao
                            JOIN stLigacao stLig ON lig.seq_ligacao = stLig.seq_ligacao
                        WHERE lig.nome IN ('PARENTESCO', 'MESMOS_DOCUMENTOS')
                            OR tpLig.nome = stLig.tipo_ligacao
                    ),
                    stTipoMultimidia AS (
                        SELECT 'I' AS cod_tipo, 'IMAGEM' AS tipo_multimidia FROM DUAL
                        UNION ALL SELECT 'A', 'AUDIO' FROM DUAL
                        UNION ALL SELECT 'V', 'VIDEO' FROM DUAL
                        UNION ALL SELECT 'D', 'DOCUMENTO' FROM DUAL
                    ),
                    stResultado AS (
                        SELECT DISTINCT 
                            DECODE( NVL( tpLig.ligacao, tpMult.cod_tipo ), NULL, TO_CHAR( ent.seq_entidade )
                                , TO_CHAR( ent.seq_entidade ) || '-' || NVL( TO_CHAR( tpLig.seq_tipo_ligacao ), tpMult.cod_tipo ) ) AS cod_campo
                            , DECODE( NVL( tpLig.ligacao, tpMult.cod_tipo ), NULL, ent.entidade, ent.entidade || '_' || NVL( tpLig.ligacao, tpMult.tipo_multimidia ) ) AS desc_campo
                        FROM stEntidade ent
                            LEFT JOIN stTipoLigacao tpLig ON ent.entidade = 'PESSOA'
                            LEFT JOIN stTipoMultimidia tpMult ON ent.entidade = 'MULTIMIDIA'
                    )
                SELECT cod_campo, desc_campo
                FROM stResultado
                ORDER BY desc_campo
            ) LOOP

                v_sql := v_sql || '''' || campos.cod_campo || ''' AS ' || campos.desc_campo || ', ';

            END LOOP;
            
            v_sql := SUBSTR( v_sql, 1, LENGTH( v_sql ) -2 );
            
            v_sql := v_sql ||
                ' )
                    )
            )
        SELECT tab.SEQ_PESSOA, tab.ARMA_QTD, tab.BOLETIM_QTD, tab.DOCUMENTO_IDENTIFICACAO_QTD, tab.DROGA_QTD, tab.ENDERECO_QTD
            , tab.OBJETO_QTD, tab.PESSOA_CRIMINAL_QTD, tab.PESSOA_PARENTESCO_QTD, tab.PESSOA_SEMELHANCA_QTD, tab.PESSOA_DOCUMENTO_QTD
            , tab.RECURSO_QTD, tab.TELEFONE_QTD, tab.UNIDADE_POLICIAL_QTD, tab.VEICULO_QTD, tab.MULTIMIDIA_IMAGEM_QTD
            , tab.MULTIMIDIA_AUDIO_QTD, tab.MULTIMIDIA_VIDEO_QTD, tab.MULTIMIDIA_DOCUMENTO_QTD, tab.OCORRENCIA_QTD
            , tab.ORGANIZACAO_QTD, NVL( qvp.FLAG_FUNCIONARIO_PUBLICO, ''I'' ) AS FLAG_FUNCIONARIO_PUBLICO
            , PKG_INTEGRACAO.BUSCA_DATA_MAIS_RECENTE_PESSOA( tab.seq_pessoa ) AS DATA_REGISTRO_MAIS_RECENTE
        FROM stDados tab
        LEFT JOIN Vinculo_Pessoa qvp ON tab.seq_pessoa = qvp.seq_pessoa
        --WHERE ROWNUM < 1000001
            /*and not exists ( select 1 from vinculo_pessoa q where q.SEQ_PESSOA = tab.SEQ_PESSOA )*/';

    --DBMS_OUTPUT.PUT_LINE( v_sql );

    --v_sql := 'SELECT * FROM VINCULO_PESSOA WHERE ROWNUM < 2';

    OPEN C_CONSULTA FOR v_sql;
--        DBMS_OUTPUT.PUT_LINE( 'abriu o cursor' );
    LOOP
        --DBMS_OUTPUT.PUT_LINE( 'dentro do loop' );            
        FETCH C_CONSULTA INTO reg;
            --DBMS_OUTPUT.PUT_LINE( 'fez o FETCH' );
        EXIT WHEN C_CONSULTA%NOTFOUND;
        
        rec_QtdeVincPessoa := reg;
        PIPE ROW( rec_QtdeVincPessoa );

    END LOOP;
            
    CLOSE C_CONSULTA;

  EXCEPTION
    WHEN OTHERS THEN
        v_msg_log_erro := 'Erro ao recalcular quandidade de vínculos: ' || SQLERRM;
        RAISE_APPLICATION_ERROR( -20007, v_msg_log_erro );

  END CALCULAR_QTDE_VINCULO_PESSOA;

  /*********************************************************************************************
   Procedimento para Atualizar a tabela de quantidades de vinculos de pessoas
  *********************************************************************************************/
  PROCEDURE ATUALIZA_QTDE_VINCULO_PESSOA(
    pe_nSeqPessoa               IN NUMBER DEFAULT 0
  ) IS
  /*****************************************************************************
  Procedure destinada a atualizar as quantidades de entidades vinculadas às 
    pessoas.
    * Há três opções de execução:
        ** Recalcular as quantidades para uma pessoa: quando apenas o sequencial
            da pessoa é informado. Todas as quantidades para a pessoa informada 
            são recalculadas;
        ** Recalcular todas as quantidades para todas as pessoas: quando não são
            passados parâmetros. Os dados da tabela são apagados, as quantidades
            são recalculadas para todas as pessoas e inseridas na tabela.
  *****************************************************************************/
 
  v_msg_log_erro                VARCHAR( 4000 ) := NULL;
  v_flExistePessoa              NUMBER := 0;
  v_sql                         VARCHAR( 4000 ) := NULL;

  BEGIN
--        DBMS_OUTPUT.PUT_LINE( 'Início: ' || TO_CHAR( SYSDATE, 'DD/MM/YYYY HH24:MI:SS' ) );
   -- IF ( pe_nSeqPessoa = 0 ) THEN
   --     dbms_output.put_line ( pe_nSeqPessoa );
    --> Não especificada a pessoa, atualização de todos os registros da tabela
        BEGIN

            FOR reg IN (
                SELECT fun.*, DECODE( qvp.seq_pessoa, NULL, 0, 1 ) AS fl_existe_pessoa
                FROM TABLE( PKG_INTEGRACAO.CALCULAR_QTDE_VINCULO_PESSOA( pe_nSeqPessoa ) ) fun
                LEFT JOIN Vinculo_Pessoa qvp ON fun.seq_pessoa = qvp.seq_pessoa
                ORDER BY fl_existe_pessoa
            ) LOOP
                BEGIN

                    IF ( reg.fl_existe_pessoa = 0 ) THEN
                        INSERT INTO Vinculo_Pessoa ( seq_pessoa, arma_qtd, boletim_qtd, documento_identificacao_qtd, droga_qtd, endereco_qtd
                            , objeto_qtd, pessoa_criminal_qtd, pessoa_parentesco_qtd, pessoa_semelhanca_qtd, pessoa_documento_qtd, recurso_qtd
                            , telefone_qtd, unidade_policial_qtd, veiculo_qtd, multimidia_imagem_qtd, multimidia_audio_qtd, multimidia_video_qtd
                            , multimidia_documento_qtd, ocorrencia_qtd, organizacao_qtd, flag_funcionario_publico
                            , data_registro_mais_recente )
                        VALUES( reg.seq_pessoa, reg.arma_qtd, reg.boletim_qtd, reg.documento_identificacao_qtd, reg.droga_qtd, reg.endereco_qtd
                            , reg.objeto_qtd, reg.pessoa_criminal_qtd, reg.pessoa_parentesco_qtd, reg.pessoa_semelhanca_qtd, reg.pessoa_documento_qtd, reg.recurso_qtd
                            , reg.telefone_qtd, reg.unidade_policial_qtd, reg.veiculo_qtd, reg.multimidia_imagem_qtd, reg.multimidia_audio_qtd
                            , reg.multimidia_video_qtd, reg.multimidia_documento_qtd, reg.ocorrencia_qtd, reg.organizacao_qtd, reg.flag_funcionario_publico
                            , reg.data_registro_mais_recente );
                    ELSE
                        UPDATE Vinculo_Pessoa
                        SET arma_qtd = reg.arma_qtd
                            , boletim_qtd = reg.boletim_qtd
                            , documento_identificacao_qtd = reg.documento_identificacao_qtd
                            , droga_qtd = reg.droga_qtd
                            , endereco_qtd = reg.endereco_qtd
                            , objeto_qtd = reg.objeto_qtd
                            , pessoa_criminal_qtd = reg.pessoa_criminal_qtd
                            , pessoa_parentesco_qtd = reg.pessoa_parentesco_qtd
                            , pessoa_semelhanca_qtd = reg.pessoa_semelhanca_qtd
                            , pessoa_documento_qtd = reg.pessoa_documento_qtd
                            , recurso_qtd = reg.recurso_qtd
                            , telefone_qtd = reg.telefone_qtd
                            , unidade_policial_qtd = reg.unidade_policial_qtd
                            , veiculo_qtd = reg.veiculo_qtd
                            , multimidia_imagem_qtd = reg.multimidia_imagem_qtd
                            , multimidia_audio_qtd = reg.multimidia_audio_qtd
                            , multimidia_video_qtd = reg.multimidia_video_qtd
                            , multimidia_documento_qtd = reg.multimidia_documento_qtd
                            , ocorrencia_qtd = reg.ocorrencia_qtd
                            , organizacao_qtd = reg.organizacao_qtd
                            , flag_funcionario_publico = NVL( flag_funcionario_publico, reg.flag_funcionario_publico )
                            , data_registro_mais_recente = reg.data_registro_mais_recente
                        WHERE seq_pessoa = reg.SEQ_PESSOA;
                    END IF;
                    
                EXCEPTION
                    WHEN OTHERS THEN
                        ROLLBACK;
                        v_msg_log_erro := 'Erro ao atualiza quantidades da pessoa ' || pe_nSeqPessoa || ': ' || SQLERRM;
                        RAISE_APPLICATION_ERROR( -20007, v_msg_log_erro );
                END;
           END LOOP;
           COMMIT;

        EXCEPTION
            WHEN OTHERS THEN
                v_msg_log_erro := 'Erro ao recalcular todas as quantidades de vínculos para a pessoa: ' || SQLERRM;
                RAISE_APPLICATION_ERROR( -20007, v_msg_log_erro );
        END;
    
--    DBMS_OUTPUT.PUT_LINE( 'Fim: ' || TO_CHAR( SYSDATE, 'DD/MM/YYYY HH24:MI:SS' ) );

  END ATUALIZA_QTDE_VINCULO_PESSOA;
-- =======================================================================================================================================================
-- Fim nova versão
-- =======================================================================================================================================================

  
END;
/
Avatar do usuário
tiago_pimenta
Rank: DBA Júnior
Rank: DBA Júnior
Mensagens: 213
Registrado em: Qua, 29 Jun 2011 9:49 am
Localização: Barretos / SP

Já tentou debugar ??
robson.amaral
Rank: Estagiário Júnior
Rank: Estagiário Júnior
Mensagens: 2
Registrado em: Qua, 14 Abr 2021 4:30 pm
Localização: Espirito Santo

tiago_pimenta escreveu:
Seg, 19 Abr 2021 10:55 am
Já tentou debugar ??
Já sim colega.
Avatar do usuário
tiago_pimenta
Rank: DBA Júnior
Rank: DBA Júnior
Mensagens: 213
Registrado em: Qua, 29 Jun 2011 9:49 am
Localização: Barretos / SP

robson.amaral escreveu:
Ter, 20 Abr 2021 9:13 am
tiago_pimenta escreveu:
Seg, 19 Abr 2021 10:55 am
Já tentou debugar ??
Já sim colega.
E o que você encontrou debugando ???
Responder
  • Informação