SCRIPT COM ERRO [RESOLVIDO]

1. SCRIPT COM ERRO [RESOLVIDO]

hugovlmota
(usa CentOS)

Enviado em 30/01/2009 - 17:10h

Boa tarde galerinha do VOL,
Estou escrevendo um script em Oracle para uma carga de BI, bem, eu não possuo muitos conhecimentos em Oracle, então desenvolvi todo o script em SQL-Server e utilizei uma ferramenta para converter o script para o Oracle.
Eu crio a procedure e quando vou vê-la ela está marcada com um X vermelho e então abro a procedure e na parte inferior me descreve a seguinte mensagem:
=======
Compilation errors for PROCEDURE CT.CARGA_BI_LV_CTError: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: begin case declare end exception exit for goto if loop mod null pragma raise return select update while with <um identificador> <um identificador delimitado por aspas duplas> <uma variável de ligação> << close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipeLine: 822
=========
para facilitar a ajuda de vocês vou postar meu script aqui.
OBS.: as tabelas que o mesmo relaciona já estão criadas, utilizei a mesma ferramenta para criar o script de criação das tabelas para o BI
========================

CREATE OR REPLACE PROCEDURE CARGA_BI_LV_CT
AS
G1_COL1 TAB_NIVEL2_LV_CT.DESCRICAO%TYPE;
G1_ROWID ROWID;
G2_COL1 TAB_NIVEL3_LV_CT.DESCRICAO%TYPE;
G2_ROWID ROWID;
G3_COL1 TAB_NIVEL4_LV_CT.DESCRICAO%TYPE;
G3_ROWID ROWID;
G4_COL1 TAB_NIVEL5_LV_CT.DESCRICAO%TYPE;
G4_ROWID ROWID;
G5_COL1 TAB_NIVEL6_LV_CT.DESCRICAO%TYPE;
G5_ROWID ROWID;
G6_COL1 TAB_NIVEL7_LV_CT.DESCRICAO%TYPE;
G6_ROWID ROWID;
G7_COL1 TAB_NIVEL8_LV_CT.DESCRICAO%TYPE;
G7_ROWID ROWID;
G8_COL1 TAB_NIVEL9_LV_CT.DESCRICAO%TYPE;
G8_ROWID ROWID;

CURSOR G1_CURSOR IS
SELECT
TAB_NIVEL2_LV_CT.ROWID ,
UPPER(F.FILIAL || ' - ' || F.NOME)
FROM TAB_NIVEL2_LV_CT,
FILIALCAD@ORIGEM F
WHERE NIVEL2 = F.FILIAL
;

CURSOR G2_CURSOR IS
SELECT
TAB_NIVEL3_LV_CT.ROWID ,
UPPER(VEN.CODVEND || ' - ' || VEN.NOME)
FROM TAB_NIVEL3_LV_CT,
VENDEDOCAD@ORIGEM VEN
WHERE NIVEL3 = SUBSTR('0000000000' || LTRIM(RTRIM(VEN.CODVEND)), LENGTH('0000000000' || LTRIM(RTRIM(VEN.CODVEND))) - 10 +1, 10)
;

CURSOR G3_CURSOR IS
SELECT
TAB_NIVEL4_LV_CT.ROWID ,
UPPER(CLI.CGC || ' - ' || CLI.NOME)
FROM TAB_NIVEL4_LV_CT,
CLIENTECAD@ORIGEM CLI
WHERE NIVEL4 = SUBSTR('0000000000' || LTRIM(RTRIM(CLI.OID)), LENGTH('0000000000' || LTRIM(RTRIM(CLI.OID))) - 10 +1, 10)
;

CURSOR G4_CURSOR IS
SELECT
TAB_NIVEL5_LV_CT.ROWID ,
UPPER(SUBSTR('0000000000' || LTRIM(RTRIM(NFS.NUMNOTA)), LENGTH('0000000000' || LTRIM(RTRIM(NFS.NUMNOTA))) - 10 +1, 10) || '/' || NFS.SERIE)
FROM TAB_NIVEL5_LV_CT,
NFSAIDACAD@ORIGEM NFS
WHERE NIVEL5 = SUBSTR('0000000000' || LTRIM(RTRIM(NFS.NUMNOTA)), LENGTH('0000000000' || LTRIM(RTRIM(NFS.NUMNOTA))) - 10 +1, 10)
;

CURSOR G5_CURSOR IS
SELECT
TAB_NIVEL6_LV_CT.ROWID ,
UPPER(CLA.CLASPROD || ' - ' || CLA.DESCR)
FROM TAB_NIVEL6_LV_CT,
CLASSIFCAD@ORIGEM CLA
WHERE NIVEL6 = CLA.CLASPROD
;

CURSOR G6_CURSOR IS
SELECT
TAB_NIVEL7_LV_CT.ROWID ,
UPPER(CLA.CLASPROD || ' - ' || CLA.DESCR)
FROM TAB_NIVEL7_LV_CT,
CLASSIFCAD@ORIGEM CLA
WHERE NIVEL7 = CLA.CLASPROD
;

CURSOR G7_CURSOR IS
SELECT
TAB_NIVEL8_LV_CT.ROWID ,
UPPER(CLA.CLASPROD || ' - ' || CLA.DESCR)
FROM TAB_NIVEL8_LV_CT,
CLASSIFCAD@ORIGEM CLA
WHERE NIVEL8 = CLA.CLASPROD
;

CURSOR G8_CURSOR IS
SELECT
TAB_NIVEL9_LV_CT.ROWID ,
UPPER(PRO.CODINTERNO || ' - ' || PRO.DESCR)
FROM TAB_NIVEL9_LV_CT,
PRODUTOCAD@ORIGEM PRO
WHERE NIVEL9 = SUBSTR('0000000000' || LTRIM(RTRIM(PRO.CODINTERNO)), LENGTH('0000000000' || LTRIM(RTRIM(PRO.CODINTERNO))) - 10 +1, 10)
;

BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE NIVEL1_LV_CT';
EXECUTE IMMEDIATE 'TRUNCATE TABLE TAB_NIVEL1_LV_CT';
EXECUTE IMMEDIATE 'TRUNCATE TABLE NIVEL2_LV_CT';
EXECUTE IMMEDIATE 'TRUNCATE TABLE TAB_NIVEL2_LV_CT';
EXECUTE IMMEDIATE 'TRUNCATE TABLE NIVEL3_LV_CT';
EXECUTE IMMEDIATE 'TRUNCATE TABLE TAB_NIVEL3_LV_CT';
EXECUTE IMMEDIATE 'TRUNCATE TABLE NIVEL4_LV_CT';
EXECUTE IMMEDIATE 'TRUNCATE TABLE TAB_NIVEL4_LV_CT';
EXECUTE IMMEDIATE 'TRUNCATE TABLE NIVEL5_LV_CT';
EXECUTE IMMEDIATE 'TRUNCATE TABLE TAB_NIVEL5_LV_CT';
EXECUTE IMMEDIATE 'TRUNCATE TABLE NIVEL6_LV_CT';
EXECUTE IMMEDIATE 'TRUNCATE TABLE TAB_NIVEL6_LV_CT';
EXECUTE IMMEDIATE 'TRUNCATE TABLE NIVEL7_LV_CT';
EXECUTE IMMEDIATE 'TRUNCATE TABLE TAB_NIVEL7_LV_CT';
EXECUTE IMMEDIATE 'TRUNCATE TABLE NIVEL8_LV_CT';
EXECUTE IMMEDIATE 'TRUNCATE TABLE TAB_NIVEL8_LV_CT';
EXECUTE IMMEDIATE 'TRUNCATE TABLE NIVEL9_LV_CT';
EXECUTE IMMEDIATE 'TRUNCATE TABLE TAB_NIVEL9_LV_CT';

COMMIT;

-- os comandos de inserção dos dados analíticos deverão ser feitos manualmente.
--INICIO SELECT PRINCIPAL
-- VENDAS
BEGIN

INSERT INTO NIVEL9_LV_CT
SELECT
SUBSTR('0000000000' || LTRIM(RTRIM(ITN.CODPRO)), LENGTH('0000000000' || LTRIM(RTRIM(ITN.CODPRO))) - 10 +1, 10),
SUBSTR(TO_CHAR(NFS.DTEMIS, 'DD/MM/YYYY'), 7, 4) || SUBSTR(TO_CHAR(NFS.DTEMIS, 'DD/MM/YYYY'), 4, 2) || SUBSTR(TO_CHAR(NFS.DTEMIS, 'DD/MM/YYYY'), 1, 2),
NFS.FILIAL,
SUBSTR('0000000000' || LTRIM(RTRIM(NFS.CODVEND)), LENGTH('0000000000' || LTRIM(RTRIM(NFS.CODVEND))) - 10 +1, 10),
SUBSTR('0000000000' || LTRIM(RTRIM(NFS.CODCLIE)), LENGTH('0000000000' || LTRIM(RTRIM(NFS.CODCLIE))) - 10 +1, 10),
SUBSTR('0000000000' || LTRIM(RTRIM(NFS.NUMNOTA)), LENGTH('0000000000' || LTRIM(RTRIM(NFS.NUMNOTA))) - 10 +1, 10),
SUBSTR(PRO.CLASPROD, 1, 3),
SUBSTR(PRO.CLASPROD, 1, 9),
SUBSTR(PRO.CLASPROD, 1, 14),
TO_DATE(TO_CHAR(NFS.DTEMIS, 'DD/MM/YYYY'), 'DD/MM/YYYY'),
'0', /*tipo (char(1))*/
ITN.QUANT,
((ITN.PRECO * ITN.QUANT) - ITN.VALDESC),
(ITN.QUANT * COM.FATORLITRO), /*atr3 'Qtd_Vend_Litros',*/
CASE
WHEN (PRO.QTEMBALAGEM IS NULL) THEN 0 /*atr4 'Embalagem',*/
ELSE(PRO.QTEMBALAGEM)
END,
CASE
WHEN (EST.CUSTOFINANCEIRO is null) THEN 0
ELSE (EST.CUSTOFINANCEIRO * ITN.QUANT)
END,
(ITN.QUANT * ITN.CUSTTRAN), /*atr6 'Custo_Contabil_Venda',*/
CASE
WHEN (PRO.CUSTOREPOSICAO IS NULL) THEN 0
ELSE (PRO.CUSTOREPOSICAO * ITN.QUANT)
END, /*atr7 'Custo_Reposicao',*/
ITN.QUANTDEV, /*atr8 'Qtd_Devolvida',*/
CASE
WHEN (ITN.QUANTDEV IS NULL) THEN 0
ELSE ((ITN.PRECO * ITN.QUANTDEV) - ITN.VALDESC)
END,
(ITN.QUANTDEV * ITN.CUSTTRAN) CUSTO_CTB_DEV, /*atr10 'Custo_Contabil_Devo',*/
CASE
WHEN (EST.CUSTOFINANCEIRO is null) THEN 0
ELSE (EST.CUSTOFINANCEIRO * ITN.QUANTDEV)
END,
CASE
WHEN (PRO.CUSTOREPOSICAO IS NULL) THEN 0
ELSE (PRO.CUSTOREPOSICAO * ITN.QUANTDEV) /*atr12 'Custo_Reposicao_Devo',*/
END,
NFS.VALFRETE, /*atr13 'Frete',*/
NFS.DESCONTO, /*atr14 'Descontos',*/
0 /*atr15 'Rentabilidade', */
FROM
NFSAIDACAD@ORIGEM NFS,
ITNFSAICAD@ORIGEM ITN,
PRODUTOCAD@ORIGEM PRO,
COMPLEMENTOPRODUTO@ORIGEM COM,
ITEMFILEST@ORIGEM EST
WHERE
NFS.NUMNOTA = ITN.NUMNOTA
AND ITN.CODPRO = PRO.CODPRO
AND (NFS.VALCONTAB + NFS.DESCONTO - NFS.VALFRETE) <> 0
AND (NFS.TPO LIKE '2%')
AND NFS.ATUALIZ = '1'
AND NFS.EST IN ( '2' , '3' )
AND EST.CODPRO = ITN.CODPRO
GROUP BY
SUBSTR('0000000000' || LTRIM(RTRIM(ITN.CODPRO)), LENGTH('0000000000' || LTRIM(RTRIM(ITN.CODPRO))) - 10 +1, 10),
SUBSTR(TO_CHAR(NFS.DTEMIS, 'DD/MM/YYYY'), 7, 4) || SUBSTR(TO_CHAR(NFS.DTEMIS, 'DD/MM/YYYY'), 4, 2) || SUBSTR(TO_CHAR(NFS.DTEMIS, 'DD/MM/YYYY'), 1, 2),
NFS.FILIAL,
SUBSTR('0000000000' || LTRIM(RTRIM(NFS.CODVEND)), LENGTH('0000000000' || LTRIM(RTRIM(NFS.CODVEND))) - 10 +1, 10),
SUBSTR('0000000000' || LTRIM(RTRIM(NFS.CODCLIE)), LENGTH('0000000000' || LTRIM(RTRIM(NFS.CODCLIE))) - 10 +1, 10),
SUBSTR('0000000000' || LTRIM(RTRIM(NFS.NUMNOTA)), LENGTH('0000000000' || LTRIM(RTRIM(NFS.NUMNOTA))) - 10 +1, 10),
SUBSTR(PRO.CLASPROD, 1, 3),
SUBSTR(PRO.CLASPROD, 1, 9),
SUBSTR(PRO.CLASPROD, 1, 14),
TO_DATE(TO_CHAR(NFS.DTEMIS, 'DD/MM/YYYY'), 'DD/MM/YYYY'),
ITN.QUANT,/*--atr1*/
((ITN.PRECO * ITN.QUANT) - ITN.VALDESC), /*--atr2*/
(ITN.QUANT * COM.FATORLITRO), /*--atr3*/ /*atr3*/
PRO.QTEMBALAGEM, /*--atr4*/
EST.CUSTOFINANCEIRO, /*--atr5 */
ITN.CUSTTRAN, /*--atr6 'Custo_Contabil_Venda',*/
PRO.CUSTOREPOSICAO, /*--atr7 'Custo_Reposicao',*/
ITN.QUANTDEV, /*--atr8 'Qtd_Devolvida',*/
((ITN.PRECO * ITN.QUANTDEV) - ITN.VALDESC), /*--atr9 'Valor_Devolucao',*/
(PRO.CUSTOREPOSICAO * ITN.QUANTDEV), /*--atr12 'Custo_Reposicao_Devo',*/
NFS.VALFRETE, /*--atr13 'Frete',*/
NFS.DESCONTO; /*--atr14 'Descontos',*/

COMMIT;

-- FIM SELECT NIVEL9

BEGIN

INSERT INTO NIVEL8_LV_CT
SELECT
NIVEL8,
NIVEL1,
NIVEL2,
NIVEL3,
NIVEL4,
NIVEL5,
NIVEL6,
NIVEL7,
DATA,
SUM(atr1),
SUM(atr2),
SUM(atr3),
SUM(atr4),
SUM(atr5),
SUM(atr6),
SUM(atr7),
SUM(atr8),
SUM(atr9),
SUM(atr10),
SUM(atr11),
SUM(atr12),
SUM(atr13),
SUM(atr14),
SUM(atr15)
FROM NIVEL9_LV_CT
GROUP BY NIVEL8,
NIVEL1,
NIVEL2,
NIVEL3,
NIVEL4,
NIVEL5,
NIVEL6,
NIVEL7,
DATA;

COMMIT;

BEGIN

INSERT INTO NIVEL7_LV_CT
SELECT
NIVEL7,
NIVEL1,
NIVEL2,
NIVEL3,
NIVEL4,
NIVEL5,
NIVEL6,
DATA,
SUM(atr1),
SUM(atr2),
SUM(atr3),
SUM(atr4),
SUM(atr5),
SUM(atr6),
SUM(atr7),
SUM(atr8),
SUM(atr9),
SUM(atr10),
SUM(atr11),
SUM(atr12),
SUM(atr13),
SUM(atr14),
SUM(atr15)
FROM NIVEL8_LV_CT
GROUP BY NIVEL7,
NIVEL1,
NIVEL2,
NIVEL3,
NIVEL4,
NIVEL5,
NIVEL6,
DATA;

COMMIT;

BEGIN

INSERT INTO NIVEL6_LV_CT
SELECT
NIVEL6,
NIVEL1,
NIVEL2,
NIVEL3,
NIVEL4,
NIVEL5,
DATA,
SUM(atr1),
SUM(atr2),
SUM(atr3),
SUM(atr4),
SUM(atr5),
SUM(atr6),
SUM(atr7),
SUM(atr8),
SUM(atr9),
SUM(atr10),
SUM(atr11),
SUM(atr12),
SUM(atr13),
SUM(atr14),
SUM(atr15)
FROM NIVEL7_LV_CT
GROUP BY NIVEL6,
NIVEL1,
NIVEL2,
NIVEL3,
NIVEL4,
NIVEL5,
DATA;

COMMIT;

BEGIN

INSERT INTO NIVEL5_LV_CT
SELECT
NIVEL5,
NIVEL1,
NIVEL2,
NIVEL3,
NIVEL4,
DATA,
SUM(atr1),
SUM(atr2),
SUM(atr3),
SUM(atr4),
SUM(atr5),
SUM(atr6),
SUM(atr7),
SUM(atr8),
SUM(atr9),
SUM(atr10),
SUM(atr11),
SUM(atr12),
SUM(atr13),
SUM(atr14),
SUM(atr15)
FROM NIVEL6_LV_CT
GROUP BY NIVEL5,
NIVEL1,
NIVEL2,
NIVEL3,
NIVEL4,
DATA;

COMMIT;

BEGIN

INSERT INTO NIVEL4_LV_CT
SELECT
NIVEL4,
NIVEL1,
NIVEL2,
NIVEL3,
DATA,
SUM(atr1),
SUM(atr2),
SUM(atr3),
SUM(atr4),
SUM(atr5),
SUM(atr6),
SUM(atr7),
SUM(atr8),
SUM(atr9),
SUM(atr10),
SUM(atr11),
SUM(atr12),
SUM(atr13),
SUM(atr14),
SUM(atr15)
FROM NIVEL5_LV_CT
GROUP BY NIVEL4,
NIVEL1,
NIVEL2,
NIVEL3,
DATA;

COMMIT;

BEGIN

INSERT INTO NIVEL3_LV_CT
SELECT
NIVEL3,
NIVEL1,
NIVEL2,
DATA,
SUM(atr1),
SUM(atr2),
SUM(atr3),
SUM(atr4),
SUM(atr5),
SUM(atr6),
SUM(atr7),
SUM(atr8),
SUM(atr9),
SUM(atr10),
SUM(atr11),
SUM(atr12),
SUM(atr13),
SUM(atr14),
SUM(atr15)
FROM NIVEL4_LV_CT
GROUP BY NIVEL3,
NIVEL1,
NIVEL2,
DATA;

COMMIT;

BEGIN

INSERT INTO NIVEL2_LV_CT
SELECT
NIVEL2,
NIVEL1,
DATA,
SUM(atr1),
SUM(atr2),
SUM(atr3),
SUM(atr4),
SUM(atr5),
SUM(atr6),
SUM(atr7),
SUM(atr8),
SUM(atr9),
SUM(atr10),
SUM(atr11),
SUM(atr12),
SUM(atr13),
SUM(atr14),
SUM(atr15)
FROM NIVEL3_LV_CT
GROUP BY NIVEL2,
NIVEL1,
DATA;

COMMIT;

BEGIN

INSERT INTO NIVEL1_LV_CT
SELECT
NIVEL1,
DATA,
SUM(atr1),
SUM(atr2),
SUM(atr3),
SUM(atr4),
SUM(atr5),
SUM(atr6),
SUM(atr7),
SUM(atr8),
SUM(atr9),
SUM(atr10),
SUM(atr11),
SUM(atr12),
SUM(atr13),
SUM(atr14),
SUM(atr15)
FROM NIVEL2_LV_CT
GROUP BY NIVEL1,
DATA;

COMMIT;

BEGIN

INSERT INTO TAB_NIVEL1_LV_CT
( NIVEL1 , DESCRICAO )
SELECT DISTINCT NIVEL1, 'Periodo' || NIVEL1
FROM NIVEL1_LV_CT
WHERE NOT EXISTS (SELECT NIVEL1 FROM TAB_NIVEL1_LV_CT
WHERE TAB_NIVEL1_LV_CT.NIVEL1 = NIVEL1_LV_CT.NIVEL1);

COMMIT;

BEGIN

INSERT INTO TAB_NIVEL2_LV_CT
( NIVEL2 , DESCRICAO )
SELECT DISTINCT NIVEL2,'Filial' || NIVEL2
FROM NIVEL2_LV_CT
WHERE NOT EXISTS (SELECT NIVEL2 FROM TAB_NIVEL2_LV_CT
WHERE TAB_NIVEL2_LV_CT.NIVEL2 = NIVEL2_LV_CT.NIVEL2);

COMMIT;

BEGIN

INSERT INTO TAB_NIVEL3_LV_CT
( NIVEL3 , DESCRICAO )
SELECT DISTINCT NIVEL3,'Vendedor' || NIVEL3
FROM NIVEL3_LV_CT
WHERE NOT EXISTS (SELECT NIVEL3 FROM TAB_NIVEL3_LV_CT
WHERE TAB_NIVEL3_LV_CT.NIVEL3 = NIVEL3_LV_CT.NIVEL3);

COMMIT;

BEGIN

INSERT INTO TAB_NIVEL4_LV_CT
( NIVEL4 , DESCRICAO )
SELECT DISTINCT NIVEL4,'Cliente' || NIVEL4
FROM NIVEL4_LV_CT
WHERE NOT EXISTS (SELECT NIVEL4 FROM TAB_NIVEL4_LV_CT
WHERE TAB_NIVEL4_LV_CT.NIVEL4 = NIVEL4_LV_CT.NIVEL4);

COMMIT;

BEGIN

INSERT INTO TAB_NIVEL5_LV_CT
( NIVEL5 , DESCRICAO )
SELECT DISTINCT NIVEL5,'Nota' || NIVEL5
FROM NIVEL5_LV_CT
WHERE NOT EXISTS (SELECT NIVEL5 FROM TAB_NIVEL5_LV_CT
WHERE TAB_NIVEL5_LV_CT.NIVEL5 = NIVEL5_LV_CT.NIVEL5);

COMMIT;

BEGIN

INSERT INTO TAB_NIVEL6_LV_CT
( NIVEL6 , DESCRICAO )
SELECT DISTINCT NIVEL6,'Grupo' || NIVEL6
FROM NIVEL6_LV_CT
WHERE NOT EXISTS (SELECT NIVEL6 FROM TAB_NIVEL6_LV_CT
WHERE TAB_NIVEL6_LV_CT.NIVEL6 = NIVEL6_LV_CT.NIVEL6);

COMMIT;

BEGIN

INSERT INTO TAB_NIVEL7_LV_CT
( NIVEL7 , DESCRICAO )
SELECT DISTINCT NIVEL7,'Sub-Grupo' || NIVEL7
FROM NIVEL7_LV_CT
WHERE NOT EXISTS (SELECT NIVEL7 FROM TAB_NIVEL7_LV_CT
WHERE TAB_NIVEL7_LV_CT.NIVEL7 = NIVEL7_LV_CT.NIVEL7);

COMMIT;

BEGIN

INSERT INTO TAB_NIVEL8_LV_CT
( NIVEL8 , DESCRICAO )
SELECT DISTINCT NIVEL8,'Linha' || NIVEL8
FROM NIVEL8_LV_CT
WHERE NOT EXISTS (SELECT NIVEL8 FROM TAB_NIVEL8_LV_CT
WHERE TAB_NIVEL8_LV_CT.NIVEL8 = NIVEL8_LV_CT.NIVEL8);

COMMIT;

BEGIN

INSERT INTO TAB_NIVEL9_LV_CT
( NIVEL9 , DESCRICAO )
SELECT DISTINCT NIVEL9,'Produto' || NIVEL9
FROM NIVEL9_LV_CT
WHERE NOT EXISTS (SELECT NIVEL9 FROM TAB_NIVEL9_LV_CT
WHERE TAB_NIVEL9_LV_CT.NIVEL9 = NIVEL9_LV_CT.NIVEL9);

COMMIT;

BEGIN

UPDATE TAB_NIVEL1_LV_CT
SET MES = SUBSTR(NIVEL1, 0, 4) || '/' || SUBSTR(NIVEL1, 5, 2),
BIMESTRE = CASE SUBSTR(NIVEL1, 5, 2)
WHEN '01' THEN SUBSTR(NIVEL1, 0, 4) || '/01'
WHEN '02' THEN SUBSTR(NIVEL1, 0, 4) || '/01'
WHEN '03' THEN SUBSTR(NIVEL1, 0, 4) || '/02'
WHEN '04' THEN SUBSTR(NIVEL1, 0, 4) || '/02'
WHEN '05' THEN SUBSTR(NIVEL1, 0, 4) || '/03'
WHEN '06' THEN SUBSTR(NIVEL1, 0, 4) || '/03'
WHEN '07' THEN SUBSTR(NIVEL1, 0, 4) || '/04'
WHEN '08' THEN SUBSTR(NIVEL1, 0, 4) || '/04'
WHEN '09' THEN SUBSTR(NIVEL1, 0, 4) || '/05'
WHEN '10' THEN SUBSTR(NIVEL1, 0, 4) || '/05'
WHEN '11' THEN SUBSTR(NIVEL1, 0, 4) || '/06'
WHEN '12' THEN SUBSTR(NIVEL1, 0, 4) || '/06'
END,
TRIMESTRE = CASE SUBSTR(NIVEL1, 5, 2)
WHEN '01' THEN SUBSTR(NIVEL1, 0, 4) || '/01'
WHEN '02' THEN SUBSTR(NIVEL1, 0, 4) || '/01'
WHEN '03' THEN SUBSTR(NIVEL1, 0, 4) || '/01'
WHEN '04' THEN SUBSTR(NIVEL1, 0, 4) || '/02'
WHEN '05' THEN SUBSTR(NIVEL1, 0, 4) || '/02'
WHEN '06' THEN SUBSTR(NIVEL1, 0, 4) || '/02'
WHEN '07' THEN SUBSTR(NIVEL1, 0, 4) || '/03'
WHEN '08' THEN SUBSTR(NIVEL1, 0, 4) || '/03'
WHEN '09' THEN SUBSTR(NIVEL1, 0, 4) || '/03'
WHEN '10' THEN SUBSTR(NIVEL1, 0, 4) || '/04'
WHEN '11' THEN SUBSTR(NIVEL1, 0, 4) || '/04'
WHEN '12' THEN SUBSTR(NIVEL1, 0, 4) || '/04'
END,
QUADRIMESTRE = CASE SUBSTR(NIVEL1, 5, 2)
WHEN '01' THEN SUBSTR(NIVEL1, 0, 4) || '/01'
WHEN '02' THEN SUBSTR(NIVEL1, 0, 4) || '/01'
WHEN '03' THEN SUBSTR(NIVEL1, 0, 4) || '/01'
WHEN '04' THEN SUBSTR(NIVEL1, 0, 4) || '/01'
WHEN '05' THEN SUBSTR(NIVEL1, 0, 4) || '/02'
WHEN '06' THEN SUBSTR(NIVEL1, 0, 4) || '/02'
WHEN '07' THEN SUBSTR(NIVEL1, 0, 4) || '/02'
WHEN '08' THEN SUBSTR(NIVEL1, 0, 4) || '/02'
WHEN '09' THEN SUBSTR(NIVEL1, 0, 4) || '/03'
WHEN '10' THEN SUBSTR(NIVEL1, 0, 4) || '/03'
WHEN '11' THEN SUBSTR(NIVEL1, 0, 4) || '/03'
WHEN '12' THEN SUBSTR(NIVEL1, 0, 4) || '/03'
END,
SEMESTRE = CASE SUBSTR(NIVEL1, 5, 2)
WHEN '01' THEN SUBSTR(NIVEL1, 0, 4) || '/01'
WHEN '02' THEN SUBSTR(NIVEL1, 0, 4) || '/01'
WHEN '03' THEN SUBSTR(NIVEL1, 0, 4) || '/01'
WHEN '04' THEN SUBSTR(NIVEL1, 0, 4) || '/01'
WHEN '05' THEN SUBSTR(NIVEL1, 0, 4) || '/01'
WHEN '06' THEN SUBSTR(NIVEL1, 0, 4) || '/01'
WHEN '07' THEN SUBSTR(NIVEL1, 0, 4) || '/02'
WHEN '08' THEN SUBSTR(NIVEL1, 0, 4) || '/02'
WHEN '09' THEN SUBSTR(NIVEL1, 0, 4) || '/02'
WHEN '10' THEN SUBSTR(NIVEL1, 0, 4) || '/02'
WHEN '11' THEN SUBSTR(NIVEL1, 0, 4) || '/02'
WHEN '12' THEN SUBSTR(NIVEL1, 0, 4) || '/02'
END,
ANO = SUBSTR(NIVEL1, 0, 4),
DIAUTIL = CASE TO_NUMBER(TO_CHAR(TO_DATE('DD/MM/YYYY',NIVEL1), 'D'))
WHEN '1' THEN 'N'
WHEN '2' THEN 'S'
WHEN '3' THEN 'S'
WHEN '4' THEN 'S'
WHEN '5' THEN 'S'
WHEN '6' THEN 'S'
WHEN '7' THEN 'N'
END;

COMMIT;

BEGIN /* FILIAL*/
OPEN G1_CURSOR;
LOOP
FETCH G1_CURSOR INTO G1_ROWID,G1_COL1;
EXIT WHEN G1_CURSOR%NOTFOUND;
BEGIN
UPDATE TAB_NIVEL2_LV_CT
SET DESCRICAO = G1_COL1
WHERE ROWID = G1_ROWID;
END;
END LOOP;
CLOSE G1_CURSOR
;END; /*FECHA BEGIN*/

COMMIT;

BEGIN /*VENDEDOR*/
OPEN G2_CURSOR;
LOOP
FETCH G2_CURSOR INTO G2_ROWID,G2_COL1;
EXIT WHEN G2_CURSOR%NOTFOUND;
BEGIN
UPDATE TAB_NIVEL3_LV_CT
SET DESCRICAO = G2_COL1
WHERE ROWID = G2_ROWID;
END;
END LOOP;
CLOSE G2_CURSOR
;END; /*FECHA BEGIN*/

COMMIT;

BEGIN /* CLIENTE*/
OPEN G3_CURSOR;
LOOP
FETCH G3_CURSOR INTO G3_ROWID,G3_COL1;
EXIT WHEN G3_CURSOR%NOTFOUND;
BEGIN
UPDATE TAB_NIVEL4_LV_CT
SET DESCRICAO = G3_COL1
WHERE ROWID = G3_ROWID;
END;
END LOOP;
CLOSE G3_CURSOR
;END; /*FECHA BEGIN*/

COMMIT;

BEGIN /*NOTA SAIDA*/
OPEN G4_CURSOR;
LOOP
FETCH G4_CURSOR INTO G4_ROWID,G4_COL1;
EXIT WHEN G4_CURSOR%NOTFOUND;
BEGIN
UPDATE TAB_NIVEL5_LV_CT
SET DESCRICAO = G4_COL1
WHERE ROWID = G4_ROWID;
END;
END LOOP;
CLOSE G4_CURSOR
;END; /*FECHA BEGIN*/

COMMIT;

BEGIN /*CLASSE DE PRODUTO MASK = 000*/
OPEN G5_CURSOR;
LOOP
FETCH G5_CURSOR INTO G5_ROWID,G5_COL1;
EXIT WHEN G5_CURSOR%NOTFOUND;
BEGIN
UPDATE TAB_NIVEL6_LV_CT
SET DESCRICAO = G5_COL1
WHERE ROWID = G5_ROWID;
END;
END LOOP;
CLOSE G5_CURSOR
;END; /*FECHA BEGIN*/

COMMIT;

BEGIN /*CLASSE DE PRODUTO MASK = 000.000000*/
OPEN G6_CURSOR;
LOOP
FETCH G6_CURSOR INTO G6_ROWID,G6_COL1;
EXIT WHEN G6_CURSOR%NOTFOUND;
BEGIN
UPDATE TAB_NIVEL7_LV_CT
SET DESCRICAO = G6_COL1
WHERE ROWID = G6_ROWID;
END;
END LOOP;
CLOSE G6_CURSOR
;END; /*FECHA BEGIN*/

COMMIT;

BEGIN /*CLASSE DE PRODUTO MASK = 000.000000.00000*/
OPEN G7_CURSOR;
LOOP
FETCH G7_CURSOR INTO G7_ROWID,G7_COL1;
EXIT WHEN G7_CURSOR%NOTFOUND;
BEGIN
UPDATE TAB_NIVEL8_LV_CT
SET DESCRICAO = G7_COL1
WHERE ROWID = G7_ROWID;
END;
END LOOP;
CLOSE G7_CURSOR
;END; /*FECHA BEGIN*/
COMMIT;

BEGIN
OPEN G8_CURSOR;
LOOP
FETCH G8_CURSOR INTO G8_ROWID,G8_COL1;
EXIT WHEN G8_CURSOR%NOTFOUND;
BEGIN
UPDATE TAB_NIVEL9_LV_CT
SET DESCRICAO = G8_COL1
WHERE ROWID = G8_ROWID;
END;
END LOOP;
CLOSE G8_CURSOR
;END; /*FECHA BEGIN*/

COMMIT;

BEGIN
UPDATE TAB_PARAMETROS_LV_CT
SET ATUALIZADOEM = (SYSDATE);
END;
COMMIT; /*fim de script */

0 0

Quote

2. MELHOR RESPOSTA

megatroom
(usa Debian)

Enviado em 02/02/2009 - 15:21h

Cara, vc ta abrindo "begin" e fachando "end" toda hora.

O begin marca o começo da procedure e o end marca o final dela.

Você só vai usar begin e end caso você vá usar o exception, ai vc pode usar no meio da procedure. E como o cara aqui de cima falou, vc fechou com um commit, o commit tem que ser antes do end final.

E outra coisa, precisa mesmo commit para tudo que você faz? O ideal é só colocar o commit no final da procedure, porque ou ele faz tudo, ou ele nao faz nada. Mais aí é questão de objetivo.

1 0

Quote

3. Re: SCRIPT COM ERRO [RESOLVIDO]

poleto
(usa Ubuntu)

Enviado em 30/01/2009 - 17:20h

Acho que faltou algo fundamental aí.
Dá uma olhada na sintaxe de criação de procedures no Oracle:

CREATE [OR REPLACE] PROCEDURE procedure_name
[ (parameter [,parameter]) ]
IS
[declaration_section]
BEGIN
[executable_section]
EXCEPTION
[exception_section]
END [procedure_name];

No seu caso, tem um COMMIT terminando a procedure.

0 0

Quote