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.
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;
/
