Estou tendo o seguinte problema:
Tenho um bloco criado a partir de um procedimento, e para melhorar o desempenho, decidi utilizar um sql dinâmico ('execute immediate' com 'bulk collect into'), e logo após faço um loop populando as variaveis com os valores da bulk collect.
Até tudo bem, se digito um dos valores para pesquisa que sei que existi ele retorna os valores sem dar erro, no momento que digito qualquer valor ele da o erro:
segue o código da package:
CREATE OR REPLACE PACKAGE consulta_partes_pkg_07032009 IS
TYPE consulta_partes_rec IS RECORD ( cd_parte partes.cd_parte%type
,cd_local_parte partes.cd_local%type
,nm_parte descricoes_partes.nm_parte%type
,cd_parte2 partes.cd_parte%type
,cd_local_parte2 partes.cd_local_parte%TYPE
,nm_parte2 descricoes_partes.nm_parte%TYPE
,cd_local partes.cd_local%TYPE
,cd_classe partes.cd_classe%TYPE
,nr_proc partes.nr_proc%TYPE
,nr_ano partes.nr_ano%TYPE
,tp_parte partes.tp_parte%TYPE
,cd_fase processos.cd_fase%TYPE);
TYPE consulta_partes_tab IS TABLE OF consulta_partes_rec INDEX BY BINARY_INTEGER;
PROCEDURE consulta_partes_query(consulta_partes_data IN OUT consulta_partes_tab
,p_cd_parte NUMBER
,p_cd_local_parte NUMBER
,p_nm_parte VARCHAR2
,p_cd_parte2 NUMBER
,p_nm_parte2 VARCHAR2
,p_somente_vara VARCHAR2
,p_cd_local NUMBER
,p_tp_classe NUMBER
,p_tp_processo VARCHAR2 -- se classe 1 instancia ou 2 instancia
,p_cd_fase NUMBER
,p_cpf VARCHAR2
,p_rg VARCHAR2
,p_cnpj VARCHAR2
,p_ctps VARCHAR2
,p_nit VARCHAR2
,p_cei VARCHAR2
,p_pis_pasep VARCHAR2);
END consulta_partes_pkg_07032009;
/
CREATE OR REPLACE PACKAGE BODY consulta_partes_pkg_07032009 IS
PROCEDURE consulta_partes_query(consulta_partes_data IN OUT consulta_partes_tab
,p_cd_parte NUMBER
,p_cd_local_parte NUMBER
,p_nm_parte VARCHAR2
,p_cd_parte2 NUMBER
,p_nm_parte2 VARCHAR2
,p_somente_vara VARCHAR2
,p_cd_local NUMBER
,p_tp_classe NUMBER
,p_tp_processo VARCHAR2 -- se classe 1 instancia ou 2 instancia
,p_cd_fase NUMBER
,p_cpf VARCHAR2
,p_rg VARCHAR2
,p_cnpj VARCHAR2
,p_ctps VARCHAR2
,p_nit VARCHAR2
,p_cei VARCHAR2
,p_pis_pasep VARCHAR2) IS
v_nr_doc VARCHAR2(1000);
v_string VARCHAR2(32000);
type tab_cd_parte is table of descricoes_partes.cd_parte%TYPE;
vt_cd_partes tab_cd_parte;
type tab_cd_local_parte is table of descricoes_partes.cd_local_parte%TYPE;
vt_cd_local_partes tab_cd_local_parte;
type tab_nm_peticao is table of descricoes_partes.nm_parte%TYPE;
vt_nm_peticao tab_nm_peticao;
type tab_cd_local is table of number;
vt_cd_local tab_cd_local;
type tab_cd_classe is table of varchar2(15);
vt_cd_classe tab_cd_classe;
type tab_nr_ano is table of number;
vt_nr_ano tab_nr_ano;
type tab_nr_proc is table of number;
vt_nr_proc tab_nr_proc;
type tab_tp_parte is table of char(1);
vt_tp_parte tab_tp_parte;
BEGIN
DECLARE
v_mensagem VARCHAR2(1000);
begin
v_string:=
'SELECT a.cd_parte
,a.cd_local_parte
,a.nm_peticao
,a.cd_local
,a.cd_classe
,a.nr_proc
,a.nr_ano
,a.tp_parte
from partes a ,
processos p,
endereco_parte e
where a.cd_parte = e.cd_parte
AND a.cd_local_parte = e.cd_local_parte
AND a.cd_seq_end = e.cd_seq_end
and p.cd_local = a.cd_local
and p.cd_classe = a.cd_classe
and p.nr_proc = a.nr_proc
and p.nr_ano = a.nr_ano
AND a.in_ativo = '||Chr(39)||'S'||Chr(39)||
' AND NOT ('||Chr(39)||p_tp_processo||Chr(39)||' = '||Chr(39)||'T'||Chr(39)||' AND p.cd_fase in ( 17,7,10,15,3,20,80,72,56,64,94,93,55,97,11,18,19,23,29) )'||
' AND NOT ('||Chr(39)||p_tp_processo||Chr(39)||' = '||Chr(39)||'T'||Chr(39)||' AND p.cd_local_atual IN (2136,136,3090) and p.cd_fase = 6 )'||
' AND NOT ('||Chr(39)||p_tp_processo||Chr(39)||' = '||Chr(39)||'T'||Chr(39)||' AND p.cd_local_atual NOT IN (select cd_local from locais where cd_origem = 9542) AND p.cd_local = 909)'||
' AND NOT ('||Chr(39)||p_tp_processo||Chr(39)||' = '||Chr(39)||'T'||Chr(39)||' AND p.cd_local_atual = 1710 )'||
' AND NOT ('||Chr(39)||p_tp_processo||Chr(39)||' = '||Chr(39)||'T'||Chr(39)||' and p.cd_local <> 909 AND p.cd_fase = 5'||
' and cd_local_atual not in ( select cd_local from locais'||
' where cd_local_distrib = ( select cd_local_distrib'||
' from locais '||
' where cd_local = p.cd_local ) ) ) ';
IF p_cd_local IS NOT NULL THEN
v_string:=v_string||' AND NOT (a.cd_local != '||p_cd_local||')';
END IF;
IF p_cd_fase IS NOT NULL THEN
v_string:=v_string||' AND p.cd_fase = '||p_cd_fase;
END IF;
IF p_cd_local_parte IS NOT NULL THEN
v_string:=v_string||' AND e.cd_local_parte = '||p_cd_local_parte;
END IF;
IF p_rg IS NOT NULL THEN
v_string:=v_string||' AND translate(upper(rg),'||Chr(39)||'ABCDEFGIJLMNOPRSTU,./-;:\_'||Chr(39)||','||Chr(39)||' '||Chr(39)||')='||Chr(39)||p_rg||Chr(39);
END IF;
IF p_cnpj IS NOT NULL THEN
v_string:=v_string||' and e.cnpj = '||Chr(39)||p_cnpj||Chr(39);
END IF;
IF p_cpf IS NOT NULL THEN
v_string:=v_string||' and e.cpf = '||Chr(39)||p_cpf||Chr(39);
END IF;
IF p_ctps IS NOT NULL THEN
v_string:=v_string||' AND e.ctps = '||Chr(39)||p_ctps||Chr(39);
END IF;
IF p_nit IS NOT NULL THEN
v_string:=v_string||' AND e.nit = '||Chr(39)||p_nit||Chr(39);
END IF;
IF p_cei IS NOT NULL THEN
v_string:=v_string||' AND e.cei = '||Chr(39)||p_cei||Chr(39);
END IF;
IF p_pis_pasep IS NOT NULL THEN
v_string:=v_string||' AND e.pis_pasep= '||Chr(39)||p_pis_pasep||Chr(39);
END IF;
v_string:=v_string||' ORDER BY 2,1';
execute immediate v_string
bulk collect into vt_cd_partes
,vt_cd_local_partes
,vt_nm_peticao
,vt_cd_local
,vt_cd_classe
,vt_nr_proc
,vt_nr_ano
,vt_tp_parte;
FOR i IN vt_cd_partes.first..vt_cd_partes.last LOOP
IF ( w_cd_parte != vt_cd_partes(i) OR vt_cd_local_partes(i) <> w_cd_local_parte) then
consulta_partes_data(i).cd_parte := vt_cd_partes(i);
consulta_partes_data(i).cd_local_parte := vt_cd_local_partes(i);
consulta_partes_data(i).nm_parte := vt_nm_peticao(i);
consulta_partes_data(i).cd_local := vt_cd_local(i);
consulta_partes_data(i).cd_classe := vt_cd_classe(i);
consulta_partes_data(i).nr_proc := vt_nr_proc(i);
consulta_partes_data(i).nr_ano := vt_nr_ano(i);
consulta_partes_data(i).tp_parte := vt_tp_parte(i);
END IF;
w_cd_parte := vt_cd_partes(i);
w_cd_local_parte := vt_cd_local_partes(i);
END LOOP;
END;
END IF;
END consulta_partes_query;
END ;