CREATE OR REPLACE PROCEDURE GLBINF_IntCTB (
vIdentificacao CHAR,
vChave CHAR,
vTamBuffer NUMBER -- número de registros a ser trabalhado por vez
) AS
vSequencial NUMBER (20, 0);
vEmpresa CHAR(2);
BEGIN
DECLARE
vDtMovimento DATE;
vMesDiaAno CHAR(8);
vCodSistema NUMBER(5);
vMes NUMBER(2);
vAno NUMBER(4);
vVersao CHAR(5);
-- recuperação de informações de detalhe
CURSOR Cur_Detalhe_Temp IS
SELECT vEmpresa, vSequencial, Seq, vCodSistema, TO_CHAR(Conta), Descricao, TpConta
FROM GLBInfIntCTBSaldos_D
WHERE Chave = vChave;
CURSOR Cur_Detalhe_Sld IS
SELECT vEmpresa, vSequencial, Seq, vCodSistema, vMes, vAno, TO_CHAR(Conta),
DECODE ( SinalSaldo, '+', Saldo, (Saldo * -1) ) as Saldo
FROM GLBInfIntCTBSaldos_D
WHERE Chave = vChave;
-- array de registros do tipo "linha da tabela apontada por Cur_Detalhe_Temp"
TYPE arrRegDetalhe_Temp IS TABLE OF Cur_Detalhe_Temp%ROWTYPE;
Reg_Detalhe_Temp arrRegDetalhe_Temp;
TYPE arrRegDetalhe_Sld IS TABLE OF Cur_Detalhe_Sld%ROWTYPE;
Reg_Detalhe_Sld arrRegDetalhe_Sld;
BEGIN
SELECT Empresa, CodSistema, mês, Ano
INTO vEmpresa, vCodSistema, vMes, vAno
FROM GLBInfIntCTBSaldos_C
WHERE Chave = vChave;
-- Calcula a DtMovimento
vMesDiaAno := TRIM(TO_CHAR ( vMes, '00')) || '01' || TRIM(TO_CHAR ( vAno, '0000'));
vDtMovimento := TO_DATE( vMesDiaAno, 'mmddyyyy' );
vDtMovimento := LAST_DAY ( vDtMovimento );
-- atualiza e busca o seqüencial de importação
UPDATE InfEmpresas
SET SeqImportacao = NVL(SeqImportacao,0) + 1
WHERE Empresa = vEmpresa;
SELECT SeqImportacao
INTO vSequencial
FROM InfEmpresas
WHERE Empresa = vEmpresa;
SELECT Versao
INTO vVersao
FROM GLBInfIntCTBSaldos_C
WHERE Chave = vChave;
COMMIT;
-- atualiza sequencial no registro do cabeçalho (importante para o tratamento de erros)
UPDATE GLBInfIntCTBSaldos_C
SET RetNroSeqImportacao = vSequencial
WHERE Chave = vChave;
-- cabeçalho da importação no Informes
INSERT INTO InfSequencias (Empresa,Seq,CodArquivo,CodSistema,DtImportacao,dtMovimento,Arquivo,Versao,Validacao)
VALUES ( vEmpresa, vSequencial, 'CTB', vCodSistema, SYSDATE(), vDtMovimento, vIdentificacao, vVersao, 'N');
-- abre o cursor de detalhe, colocando no array N linhas recuperadas de cada vez
OPEN Cur_Detalhe_Temp;
LOOP
FETCH Cur_Detalhe_Temp BULK COLLECT INTO Reg_Detalhe_Temp LIMIT vTamBuffer;
FORALL i IN 1..Reg_Detalhe_Temp.COUNT
INSERT INTO GLBINF_IntCTBv100_A
VALUES Reg_Detalhe_Temp (i) ;
EXIT WHEN Cur_Detalhe_Temp%NOTFOUND;
END LOOP;
CLOSE Cur_Detalhe_Temp;
-- abre o cursor de detalhe, colocando no array N linhas recuperadas de cada vez
OPEN Cur_Detalhe_Sld;
LOOP
FETCH Cur_Detalhe_Sld BULK COLLECT INTO Reg_Detalhe_Sld LIMIT vTamBuffer;
FORALL i IN 1..Reg_Detalhe_Sld.COUNT
INSERT INTO GLBINF_IntCTBv100_B
VALUES Reg_Detalhe_Sld (i);
EXIT WHEN Cur_Detalhe_Sld%NOTFOUND;
END LOOP;
CLOSE Cur_Detalhe_Sld;
-- O Commit deve ser chamado somente no final. Nunca dentro do FORALL
COMMIT;
END;
-- tratamento de erros:
-- lança uma mensagem de erro, capturável pela LYD_Conexao
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
-- lança um erro com a mensagem gerada
raise_application_error (-20000,'Erro na execução da procedure: ' || SQLERRM);
END;
Procedure does not work in Oracle 9i
-
- Rank: Estagiário Júnior
- Posts: 1
- Joined: Wed, 29 Sep 2004 9:57 am
- Location: Esteio - RS
I have this procedure that is working at 10g, but it does not work on 9i, I do not find the problem. Someone can help me see this procedure.
-
- Moderador
- Posts: 2016
- Joined: Wed, 12 Jan 2005 3:25 pm
- Location: Araraquara - SP
Abraço,
Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP
Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP
Good morning guys.
The error could not be here?
Oracle accepts -20000 as value for error?
Would not it have to be -20001 ???
The error could not be here?
-- tratamento de erros:
-- lança uma mensagem de erro, capturável pela LYD_Conexao
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
-- lança um erro com a mensagem gerada
raise_application_error (-20000,'Erro na execução da procedure: ' || SQLERRM);
Would not it have to be -20001 ???
- Toad
- Rank: DBA Pleno
- Posts: 253
- Joined: Fri, 18 Nov 2005 2:14 pm
- Location: Seattle, WA
- Contact:
Matheus Gonçalves
matheus.dev
twitter.com/developer__c
matheus.dev
twitter.com/developer__c
I also usually use -20001, but I do not know if this is rule!
But it would be interesting to know which line gives the mistake to a more accurate answer.
But it would be interesting to know which line gives the mistake to a more accurate answer.
-
- Moderador
- Posts: 2016
- Joined: Wed, 12 Jan 2005 3:25 pm
- Location: Araraquara - SP
Abraço,
Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP
Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP
Angelo,
Replace the error code, to -20001 and take a test, can it?
Replace the error code, to -20001 and take a test, can it?
-
- Information
-
Who is online
Users browsing this forum: No registered users and 1 guest