Re: error handling using stored procedure
From: Jorge Reyes <jorg_reyes_at_hotmail.com>
Date: Tue, 21 Apr 2009 14:25:44 -0700 (PDT)
Message-ID: <27079b56-2821-4cd6-ad18-270a9661e16b_at_l28g2000vba.googlegroups.com>
On 21 abr, 15:46, Jorge Reyes <jorg_re..._at_hotmail.com> wrote:
> On 21 abr, 15:21, Mladen Gogala <mla..._at_bogus.email.com> wrote:
>
>
>
>
>
> > On Tue, 21 Apr 2009 10:38:16 -0700, Jorge Reyes wrote:
> > > Hello everyone,
>
> > > Question: Is it possible to catch the error of duplicate key using an
> > > "insert into table select ...." inside of an stored procedure?
>
> > Yes.
>
> > > Target: Execute my stored procedure each 15 minutes and insert all the
> > > non-duplicate records from a source table, and if its possible, the
> > > values of the duplicate records SUM with the existing values.
>
> > MERGE statement would be faster than a stored procedure for that
> > purpose.
>
> > > 12:29:41.081 DBMS nmsnoc-db -- Error: PLS-00103: Encountered the
> > symbol
> > > "EXCEPTION" when expecting one of the following: 12:29:41.143 DBMS
> > > nmsnoc-db -- begin case declare end exit for goto if loop mod null
>
> > You have a syntax error, that's all.
>
> > --http://mgogala.freehostia.com
>
> 1. So there are no problem with using EXCEPTION inside of an Store
> Procedure?
> 2. What's about the second option? is it possible to implement inside
> of an Store Proc?
>
> Thanks in advanced- Ocultar texto de la cita -
>
> - Mostrar texto de la cita -
END IF; dbms_output.put_line('Fin de procedimiento: ' || TO_CHAR(sysdate, 'YYYY-MM-DD HH24:MI:SS'));
END PR_MAKE_CDRS_VALIDOS_1; Received on Tue Apr 21 2009 - 16:25:44 CDT
Date: Tue, 21 Apr 2009 14:25:44 -0700 (PDT)
Message-ID: <27079b56-2821-4cd6-ad18-270a9661e16b_at_l28g2000vba.googlegroups.com>
On 21 abr, 15:46, Jorge Reyes <jorg_re..._at_hotmail.com> wrote:
> On 21 abr, 15:21, Mladen Gogala <mla..._at_bogus.email.com> wrote:
>
>
>
>
>
> > On Tue, 21 Apr 2009 10:38:16 -0700, Jorge Reyes wrote:
> > > Hello everyone,
>
> > > Question: Is it possible to catch the error of duplicate key using an
> > > "insert into table select ...." inside of an stored procedure?
>
> > Yes.
>
> > > Target: Execute my stored procedure each 15 minutes and insert all the
> > > non-duplicate records from a source table, and if its possible, the
> > > values of the duplicate records SUM with the existing values.
>
> > MERGE statement would be faster than a stored procedure for that
> > purpose.
>
> > > 12:29:41.081 DBMS nmsnoc-db -- Error: PLS-00103: Encountered the
> > symbol
> > > "EXCEPTION" when expecting one of the following: 12:29:41.143 DBMS
> > > nmsnoc-db -- begin case declare end exit for goto if loop mod null
>
> > You have a syntax error, that's all.
>
> > --http://mgogala.freehostia.com
>
> 1. So there are no problem with using EXCEPTION inside of an Store
> Procedure?
> 2. What's about the second option? is it possible to implement inside
> of an Store Proc?
>
> Thanks in advanced- Ocultar texto de la cita -
>
> - Mostrar texto de la cita -
Sorry!! i have this new issue...
Using MERGE i receive this errors:
16:23:09.149 DBMS nmsnoc-db -- Error: ORA-00923: FROM keyword not
found where expected
16:23:09.211 DBMS nmsnoc-db -- ORA-06512: at
"OM_DB.PR_MAKE_CDRS_VALIDOS_1", line 56
16:23:09.274 DBMS nmsnoc-db -- ORA-06512: at line 1, Batch 1 Line 1
Col 1
this is my whole code:
CREATE OR REPLACE PROCEDURE OM_DB.PR_MAKE_CDRS_VALIDOS_1 IS
vPMM_DATETIME TIMESTAMP(3);
vMAX_PMM_DATETIME TIMESTAMP(3);
vEND_DATETIME TIMESTAMP(3);
v_rows_processed INT;
count_violated INT;
BEGIN
dbms_output.enable(40000);
dbms_output.put_line('Inicio de procedimiento: ' || TO_CHAR
(sysdate, 'YYYY-MM-DD HH24:MI:SS'));
- Valida si existe informacion en la tabla de origen
--
count_violated := 0; SELECT COUNT(START_TIME) INTO count_violated FROM OM_DB.CDRS_PRUEBA; IF (count_violated=0) THEN dbms_output.put_line('No hay informacion en la tabla de origen'); ELSE SELECT TO_DATE(TO_CHAR(MIN(START_TIME),'YYYY-MM-DD HH24') || ': 00:00','YYYY-MM-DD HH24:MI:SS') INTO vPMM_DATETIME FROM OM_DB.CDRS_PRUEBA;
SELECT TO_DATE(TO_CHAR(MAX(START_TIME),'YYYY-MM-DD HH24') || ': 59:59','YYYY-MM-DD HH24:MI:SS')
INTO vMAX_PMM_DATETIME
FROM om_db.CDRS_PRUEBA;
WHILE (vPMM_DATETIME <= vMAX_PMM_DATETIME) LOOP
MERGE INTO OM_DB.CDRS_VALIDOS X
USING (
SELECT
COALESCE(V_EXITOSOS.ESTADO,
V_NO_EXITOSOS.ESTADO, V_NO_CONECT.ESTADO) AS ESTADO,
COALESCE(V_EXITOSOS.MUNICIPIO,
V_NO_EXITOSOS.MUNICIPIO, V_NO_CONECT.MUNICIPIO) AS MUNICIPIO,
COALESCE(V_EXITOSOS.CALL_SOURCE_REGID,
V_NO_EXITOSOS.CALL_SOURCE_REGID, V_NO_CONECT.CALL_SOURCE_REGID) AS
CALL_SOURCE_REGID,
COALESCE(V_EXITOSOS.EXITOSOS,0) AS EXITOSOS,
COALESCE(V_NO_EXITOSOS.NO_EXITOSOS,0) AS
NO_EXITOSOS,
COALESCE(V_NO_CONECT.NO_CONECT,0) AS
NO_CONECT,
vPMM_DATETIME AS PMM_DATETIME
FROM
(
SELECT
DISTINCT(ESTADO) AS ESTADO,
MUNICIPIO,
CALL_SOURCE_REGID,
COUNT(CALL_SOURCE_REGID) AS EXITOSOS
FROM (
SELECT
A.CALL_SOURCE_REGID,
B.ESTADO,
B.MUNICIPIO
FROM OM_DB.CDRS_PRUEBA A, OM_DB.COFETEL
B
WHERE A.START_TIME BETWEEN vPMM_DATETIME
AND vEND_DATETIME
AND TO_NUMBER
(A.CALLED_PARTY_ON_DEST_PART3) between B.NIR_INICIAL AND B.NIR_FINAL
AND A.CALL_DURATION_INT >= 5
AND B.MODALIDAD IN ('CPP','FIJO','MPP')
)
GROUP BY
ESTADO,
MUNICIPIO,
CALL_SOURCE_REGID
) V_EXITOSOS
FULL OUTER JOIN
(
SELECT
DISTINCT(ESTADO) AS ESTADO,
MUNICIPIO,
CALL_SOURCE_REGID,
COUNT(CALL_SOURCE_REGID) AS NO_EXITOSOS
FROM (
SELECT
C.CALL_SOURCE_REGID,
D.ESTADO,
D.MUNICIPIO
FROM OM_DB.CDRS_PRUEBA C, OM_DB.COFETEL
D
WHERE C.START_TIME BETWEEN vPMM_DATETIME
AND vEND_DATETIME
AND TO_NUMBER
(C.CALLED_PARTY_ON_DEST_PART3) between D.NIR_INICIAL AND D.NIR_FINAL
AND C.CALL_DURATION_INT >= 1 AND
C.CALL_DURATION_INT < 5
AND D.MODALIDAD IN ('CPP','FIJO','MPP')
)
GROUP BY
ESTADO,
MUNICIPIO,
CALL_SOURCE_REGID
) V_NO_EXITOSOS
ON
V_EXITOSOS.ESTADO = V_NO_EXITOSOS.ESTADO
AND V_EXITOSOS.MUNICIPIO =
V_NO_EXITOSOS.MUNICIPIO
AND V_EXITOSOS.CALL_SOURCE_REGID =
V_NO_EXITOSOS.CALL_SOURCE_REGID
FULL OUTER JOIN
(
SELECT
DISTINCT(ESTADO) AS ESTADO,
MUNICIPIO,
CALL_SOURCE_REGID,
COUNT(CALL_SOURCE_REGID) AS NO_CONECT
FROM (
SELECT
C.CALL_SOURCE_REGID,
D.ESTADO,
D.MUNICIPIO
FROM OM_DB.CDRS_PRUEBA C, OM_DB.COFETEL
D
WHERE C.START_TIME BETWEEN vPMM_DATETIME
AND vEND_DATETIME
AND TO_NUMBER
(C.CALLED_PARTY_ON_DEST_PART3) between D.NIR_INICIAL AND D.NIR_FINAL
AND C.CALL_DURATION_INT = 0
AND D.MODALIDAD IN ('CPP','FIJO','MPP')
)
GROUP BY
ESTADO,
MUNICIPIO,
CALL_SOURCE_REGID
) V_NO_CONECT
ON
V_NO_CONECT.ESTADO = V_EXITOSOS.ESTADO
AND V_NO_CONECT.MUNICIPIO =
V_EXITOSOS.MUNICIPIO
AND V_NO_CONECT.CALL_SOURCE_REGID =
V_EXITOSOS.CALL_SOURCE_REGID
) Y -- Cierra USING
ON (
X.PMM_DATETIME = Y.PMM_DATETIME
AND X.CALL_SOURCE_REGID = Y.CALL_SOURCE_REGID
AND X.ESTADO = Y.ESTADO
AND X.MUNICIPIO = Y.MUNICIPIO
)
WHEN MATCHED THEN UPDATE SET
X.EXITOSOS = X.EXITOSOS + Y.EXITOSOS,
X.NO_EXITOSOS = X.NO_EXITOSOS + Y.NO_EXITOSOS,
X.NO_CONECT = X.NO_CONECT + Y.NO_CONECT
WHEN NOT MATCHED THEN INSERT (X.ESTADO, X.MUNICIPIO,
X.CALL_SOURCE_REGID, X.EXITOSOS, X.NO_EXITOSOS, X.NO_CONECT,
X.PMM_DATETIME)
VALUES (Y.ESTADO, Y.MUNICIPIO,
Y.CALL_SOURCE_REGID, Y.EXITOSOS, Y.NO_EXITOSOS, Y.NO_CONECT,
Y.PMM_DATETIME);
vPMM_DATETIME := vPMM_DATETIME + 1/24;
vEND_DATETIME := vPMM_DATETIME + 1/24;
END LOOP;
COMMIT;
END IF; dbms_output.put_line('Fin de procedimiento: ' || TO_CHAR(sysdate, 'YYYY-MM-DD HH24:MI:SS'));
END PR_MAKE_CDRS_VALIDOS_1; Received on Tue Apr 21 2009 - 16:25:44 CDT