Boa tarde a todos!!!!!
Estou fazendo um procedimento para extrair os dados do arquivo e passar para o BD.........até ai tudo bem!!!
Só que........quando provoco um erro no arquivo (tiro o numero do contrato ou outra coisa) é que começa a fuzarca!!!!rsrsrsrsrsr
Ele dá o erro só que não copia para a tabela de erros (tom_err_car_08)...............e se eu tirar o v_erro :=1 que está nos erros ele dá que está tudo certo copia os dados para a tabela tom_entregas_08 e o erro para a tabela tom_err_car_08!!!
quando...... que o certo era assim que estourar o erro é para NÃO copiar para tabela tom_entregas_08 e SIM copiar o erro para tabela tom_err_car_08!!!!entenderam???
O procedimento vai abaixo!!!
create or replace
PROCEDURE TOMPR001_08 (
i_cam IN VARCHAR2,
i_aju IN VARCHAR2,
i_fich IN VARCHAR2,
i_num_carr IN NUMBER,
o_err OUT NUMBER,
o_msg OUT VARCHAR2
) IS
/********************************************************
*Procedimento para o carregamento dos dados para Tomate *
********************************************************/
-- Variaveis da Estrutura de Ficheiros
w_nif_req VARCHAR2 (11);
w_num_con Varchar2 (4);
w_num_cer number (6);
w_cod_loc Varchar2 (1);
w_dat_des date;
w_hor_des varchar2 (5);
w_pes_bru Number (5);
w_pes_liq Number (5);
w_pes_adm Number (5);
w_mat_um Varchar (12);
w_mat_doi Varchar (12);
v_ini NUMBER := 0;
v_pos NUMBER := 0;
v_com NUMBER := 0;
c_sep Constant Char := ';';
in_file UTL_FILE.file_type;
fich_b UTL_FILE.file_type;
linebuf VARCHAR2 (500);
erros VARCHAR2 (700);
v_des_erro VARCHAR2 (100) := NULL;
v_dir_val VARCHAR2 (60);
v_dir_val_out VARCHAR2 (60);
linha NUMBER;
p_fim EXCEPTION;
v_fim EXCEPTION;
-- Declaracao de Excepcoes
e_abertura_ficheiro EXCEPTION;
e_comprimento_ficheiro EXCEPTION;
e_fim EXCEPTION;
z_fim EXCEPTION;
controla_erros NUMBER:=0;
v_erro NUMBER:=0;
v_msg Varchar2(500) := null;
v_linha VARCHAR2(500):=NULL;
-- Declaracao de cursores
-- Cursor para obter a directoria onde se pode escrever os ficheiros
CURSOR c_dir_inp IS
SELECT A.vde_valor
FROM adm_val_def a
WHERE a.tde_codigo = 'TOM'
AND a.vde_cod_val = 'DIR_FIC_BAT';
CURSOR c_existe_contrato IS
SELECT cno_num_con
FROM tom_contratos_08
WHERE cam_ano_ini_cam = i_cam
AND aju_codigo = i_aju
AND cno_num_con = w_num_con;
v_existe_contrato c_existe_contrato%Rowtype;
CURSOR c_ver_existencia_nif (nif varchar2)IS
SELECT ter_num_con_nac
FROM hor_terceiro
where ter_num_con_nac = nif;
v_ver_existencia_nif c_ver_existencia_nif%Rowtype;
Cursor c_cod_loc Is
SELECT loc_cod_loc
FROM tom_localidade_08
WHERE cam_ano_ini_cam = i_cam
AND aju_codigo = i_aju;
v_cod_loc c_cod_loc%Rowtype;
/*******************************************************************************************************************/
Procedure msg (linha varchar2) is
Begin
dbms_output.put_line(linha);
-- null;
End;
/*****************************************************************************************************************/
--Limpa as Variáveis globais antes de carregar a próxima linha do ficheiro
Procedure Limpa_Variaveis(P_Erro In Out NUMBER, p_Msg In Out Varchar2) Is
Begin
w_nif_req := null;
Controla_Erros :=0;
v_erro :=0;
v_linha := null;
w_nif_req := null;
w_num_con := null;
w_num_cer := null;
w_cod_loc := null;
w_dat_des := null;
w_hor_des := null;
w_pes_bru := null;
w_pes_liq := null;
w_pes_adm := null;
w_mat_um := null;
w_mat_doi := null;
EXCEPTION
When OTHERS Then
p_Erro := SQLCODE;
P_Msg := 'Limpa_Variaveis: '||SQLERRM;
End;
/*****************************************************************************************************************/
Procedure Insere_Erros(contribuinte VARCHAR2, num_carregamento NUMBER,codigo_erro VARCHAR2, contrato in number, certificado in number, p_erro OUT NUMBER, p_msg OUT VARCHAR2) IS
o_err Number:=0;
o_men Varchar2(255);
begin
INSERT INTO tom_err_car_08(cam_ano_ini_cam, aju_codigo, erc_num_con_nac, err_cod_err, erc_num_car , erc_num_con, erc_num_cer)
VALUES (i_cam , i_aju , contribuinte , codigo_erro, num_carregamento , contrato , certificado );
p_erro :=0;
p_msg := null;
EXCEPTION
When OTHERS Then
p_Erro := SQLCODE;
P_Msg := 'Insere_Erros: '||SQLERRM;
End;
/*****************************************************************************************************************/
--Principal
BEGIN
--msg('Início');
o_err := 0;
o_msg := NULL;
env.setv ('F45', 'S');
-- fecha todos os handles de ficheiros abertos
UTL_FILE.fclose_all;
OPEN c_dir_inp;
FETCH c_dir_inp INTO v_dir_val;
IF c_dir_inp%NOTFOUND THEN
o_err := -2;
o_msg := 'Directoria de leitura não definida';
RAISE z_fim;
END IF;
CLOSE c_dir_inp;
-- se o ultimo caracter da directoria é '/' retira-lo
IF SUBSTR (v_dir_val, LENGTH (v_dir_val), 1) = '/' THEN
v_dir_val := SUBSTR (v_dir_val, 1, LENGTH (v_dir_val) - 1);
END IF;
IF SUBSTR (v_dir_val_out, LENGTH (v_dir_val_out), 1) = '/' THEN
v_dir_val_out := SUBSTR (v_dir_val_out, 1, LENGTH (v_dir_val_out) - 1);
END IF;
in_file := UTL_FILE.fopen (v_dir_val, i_fich, 'r');
--***************** Leitura do ficheiro de Input **************
linha := 0;
IF i_num_carr IS NOT NULL THEN --insere na tabela, se não tiver Erros
LOOP
--fl_erro := NULL;
linha := linha + 1;
BEGIN
UTL_FILE.get_line (in_file, linebuf);
--msg('Linha: '||linebuf);
EXCEPTION
WHEN VALUE_ERROR THEN
o_err := -1;
o_msg :='A linha ' || TO_CHAR (linha)|| ' não cabe no buffer de leitura';
WHEN NO_DATA_FOUND THEN
EXIT;
END;
-- Limpa as variaveis antes de carregar a nova linha do ficheiro
Limpa_Variaveis(v_Erro,V_Msg);
IF v_erro <> 0 THEN
RAISE v_fim;
END IF;
--MSG('ANTES DE LINHA');
-- v_linha := TRIM (SUBSTR (linebuf, 1, instr(linebuf, ';', 1, 1) -1));
v_ini := 1; -- Inicio do campo.
v_pos := INSTR (linebuf, c_sep, 1, 1);
v_com := v_pos - v_ini;
w_num_con := TO_NUMBER(SUBSTR(linebuf, v_ini, v_com));
--msg ('Contrato: '||w_num_con);
v_ini := v_pos + 1;
v_pos := INSTR (linebuf, c_sep, 1, 2);
v_com := v_pos - v_ini;
w_cod_loc := TO_CHAR(SUBSTR(linebuf, v_ini, v_com));
--msg ('Localidade: '||w_cod_loc);
v_ini := v_pos + 1;
v_pos := INSTR (linebuf, c_sep, 1, 3);
v_com := v_pos - v_ini;
w_num_cer := TO_NUMBER(SUBSTR(linebuf, v_ini, v_com));
--msg ('Certificado: '||w_num_cer);
v_ini := v_pos + 1;
v_pos := INSTR (linebuf, c_sep, 1, 4);
v_com := v_pos - v_ini;
w_nif_req := SUBSTR (linebuf, v_ini, v_com);
--msg ('NIF: '||w_nif_req);
v_ini := v_pos + 1;
v_pos := INSTR (linebuf, c_sep, 1, 5);
v_com := v_pos - v_ini;
w_dat_des := TO_DATE(SUBSTR(linebuf, v_ini, v_com),'YYYY-MM-DD');
--msg ('Data: '||w_dat_des);
v_ini := v_pos + 1;
v_pos := INSTR (linebuf, c_sep, 1, 6);
v_com := v_pos - v_ini;
w_hor_des := SUBSTR(linebuf,v_ini, v_com);
--msg ('Hora: '||w_hor_des);
v_ini := v_pos + 1;
v_pos := INSTR (linebuf, c_sep, 1, 7);
v_com := v_pos - v_ini;
w_pes_bru := TO_NUMBER(SUBSTR(linebuf, v_ini, v_com));
--msg ('Peso Bruto: '||w_pes_bru);
v_ini := v_pos + 1;
v_pos := INSTR (linebuf, c_sep, 1, 8);
v_com := v_pos - v_ini;
w_pes_liq := TO_NUMBER(SUBSTR(linebuf, v_ini, v_com));
--msg ('Peso Líquido: '||w_pes_liq);
v_ini := v_pos + 1;
v_pos := INSTR (linebuf, c_sep, 1, 9);
v_com := v_pos - v_ini;
w_pes_adm := TO_NUMBER(SUBSTR(linebuf, v_ini, v_com));
--msg ('Peso Adm: '||w_pes_adm);
v_ini := v_pos + 1;
v_pos := INSTR (linebuf, c_sep, 1, 10);
v_com := v_pos - v_ini;
w_mat_um := TO_CHAR(SUBSTR(linebuf, v_ini, v_com));
--msg ('Matricula 1: '||w_mat_um);
v_ini := v_pos + 1;
v_pos := INSTR (linebuf, c_sep, 1, 11);
v_com := v_pos - v_ini;
w_mat_doi := TO_CHAR(SUBSTR(linebuf, v_ini, v_com));
--msg ('Matricula 2: '||w_mat_doi);
-- Se Não Constar o Número do Contrado no Ficheiro termina com erro.
IF w_num_con is null
then
o_msg := '001 - Número do Contrato Não Preenchido!';
Controla_Erros:=1;
Insere_Erros(w_nif_req,i_num_carr,'001',0, w_num_cer, v_Erro,v_Msg);
v_erro:=1;
IF v_erro != 0 THEN
RAISE z_fim;
END IF;
END IF;
-- Se Não Constar o Número do Contrado na tabela tom_contratos_08 termina com erro.
IF w_num_con != v_existe_contrato.cno_num_con
then
o_msg := '002 - Número Do Contrato Inexistente.';
Controla_Erros :=1;
Insere_Erros(w_nif_req,i_num_carr,'002',0, w_num_cer, v_Erro,v_Msg);
v_erro:=1;
IF v_erro != 0 THEN
RAISE z_fim;
END IF;
END IF;
-- Se Não Constar a Localidade na tabela tom_localidade_08 termina com erro.
Open c_cod_loc;
fetch c_cod_loc into v_cod_loc;
close c_cod_loc;
IF w_cod_loc != v_cod_loc.loc_cod_loc
then
o_msg := '003 - Código da Localidade Inexistente.';
Controla_Erros :=1;
Insere_Erros(w_nif_req,i_num_carr,'003',w_num_con, w_num_cer, v_Erro,v_Msg);
v_erro:=1;
IF v_erro != 0 THEN
RAISE z_fim;
END IF;
END IF;
-- Se Não Constar o Número do Certificado no Ficheiro termina com erro.
IF w_num_cer is null
then
o_msg := '004 - Número do Certificado Não Preenchido.';
Controla_Erros :=1;
Insere_Erros(w_nif_req,i_num_carr,'004',w_num_con, 0, v_Erro,v_Msg);
v_erro:=1;
IF v_erro != 0 THEN
RAISE z_fim;
END IF;
END IF;
-- Se Não Constar o NIF no Ficheiro termina com erro.
IF w_nif_req is null
then
o_msg := '005 - NIF do Requerente Não Preenchido.';
Controla_Erros :=1;
Insere_Erros(0,i_num_carr,'005',w_num_con, w_num_cer, v_Erro,v_Msg);
v_erro:=1;
IF v_erro != 0 THEN
RAISE z_fim;
END IF;
END IF;
-- Se Não Constar o NIF na tabela de Terceiros termina com erro.
Open c_ver_existencia_nif (w_nif_req);
Fetch c_ver_existencia_nif into v_ver_existencia_nif;
close c_ver_existencia_nif;
IF w_nif_req != v_ver_existencia_nif.ter_num_con_nac
then
o_msg := '006 - NIF do Requerente Inexistente.';
Controla_Erros :=1;
Insere_Erros(0,i_num_carr,'006',w_num_con, w_num_cer, v_Erro,v_Msg);
v_erro:=1;
IF v_erro != 0 THEN
RAISE z_fim;
END IF;
END IF;
-- Se Não Constar a Data e/ou Horário no Ficheiro termina com erro.
IF w_dat_des is null or
w_hor_des is null
then
o_msg := '007 - Data e/ou Hora Não Preenchidos .';
Controla_Erros :=1;
Insere_Erros(w_nif_req,i_num_carr,'007',w_num_con, w_num_cer, v_Erro,v_Msg);
v_erro:=1;
IF v_erro != 0 THEN
RAISE z_fim;
END IF;
END IF;
-- Se Não Constar o Peso Bruto e/ou Peso Liq. e/ou Peso Adm. no Ficheiro termina com erro.
IF w_pes_bru is null or
w_pes_liq is null or
w_pes_adm is null
then
o_msg := '008 - Peso bruto e/ou Peso Liq. e/ou Peso Adm. Trans. Não Preenchidos.';
Controla_Erros :=1;
Insere_Erros(w_nif_req,i_num_carr,'008',w_num_con, w_num_cer, v_Erro,v_Msg);
v_erro:=1;
IF v_erro != 0 THEN
RAISE z_fim;
END IF;
END IF;
-- Se Não Constar a Matricula Um no Ficheiro termina com erro.
IF w_mat_um is null
then
o_msg := '009 - Matricula Um Não Preenchidos.';
Controla_Erros :=1;
Insere_Erros(w_nif_req,i_num_carr,'009',w_num_con, w_num_cer, v_Erro,v_Msg);
v_erro:=1;
IF v_erro != 0 THEN
RAISE z_fim;
END IF;
END IF;
IF controla_erros = 0 THEN
--msg('insere na tabela entregas 08 ');
INSERT INTO tom_entregas_08 (cam_ano_ini_cam, aju_codigo, cno_num_con, ent_num_cer , ent_cod_loc, ent_nif_req, ent_dat_des, ent_hor_des, ent_pes_bru, ent_pes_liq, ent_pes_adm_tra, ent_mat_um, ent_mat_doi)
Values (i_cam , i_aju , w_num_con , w_num_cer , w_cod_loc , w_nif_req , w_dat_des , w_hor_des , w_pes_bru , w_pes_liq , w_pes_adm , w_mat_um , w_mat_doi );
END IF;
END loop;
END IF;
UTL_FILE.fclose_all;
o_err := 0;
o_msg := NULL;
EXCEPTION
When z_Fim Then
UTL_FILE.fclose_all;
o_err := v_erro;
o_msg := o_msg;
--msg(o_Err ||' - '||o_msg);
RAISE_APPLICATION_ERROR(-20000, o_msg);
When p_fim Then
o_err := SQLCODE;
UTL_FILE.fclose_all;
RAISE_APPLICATION_ERROR(-20000, o_msg);
WHEN v_fim THEN
o_err := SQLCODE;
DBMS_OUTPUT.put_line (o_err);
DBMS_OUTPUT.put_line (o_msg);
UTL_FILE.fclose_all;
WHEN UTL_FILE.invalid_path THEN
o_err := SQLCODE;
UTL_FILE.fclose_all;
o_msg := 'Nome ou localização do ficheiro inválida';
WHEN UTL_FILE.invalid_mode THEN
o_err := SQLCODE;
UTL_FILE.fclose_all;
o_msg := 'Opção de open_mode inválida';
WHEN UTL_FILE.invalid_filehandle THEN
o_err := SQLCODE;
UTL_FILE.fclose_all;
o_msg := 'handle de ficheiro inválido';
WHEN UTL_FILE.invalid_operation THEN
o_err := SQLCODE;
UTL_FILE.fclose_all;
o_msg := 'Não se conseguiu abrir ou operar o ficheiro como pedido';
WHEN UTL_FILE.read_error THEN
o_err := SQLCODE;
UTL_FILE.fclose_all;
o_msg := 'Erro de S.O. ao ler o ficheiro';
WHEN UTL_FILE.write_error THEN
o_err := SQLCODE;
UTL_FILE.fclose_all;
o_msg := 'Erro de S.O. a escrever no ficheiro';
WHEN UTL_FILE.internal_error THEN
o_err := SQLCODE;
UTL_FILE.fclose_all;
o_msg := 'Erro não especificado de PL/SQL';
WHEN OTHERS THEN
o_err := SQLCODE;
o_msg := 'TOMPR001_08-'||SQLERRM;
UTL_FILE.fclose_all;
msg('Deu Erro no When Others'||SQLERRM);
Raise_Application_Error (-20000, o_msg);
END TOMPR001_08;Abraço!!!!
