Re: error handling using stored procedure
Date: Tue, 21 Apr 2009 11:09:06 -0700 (PDT)
Message-ID: <3e0a699b-2c79-4d1f-8aa7-9cf090a8eefa_at_q14g2000vbn.googlegroups.com>
On Apr 21, 12:38 pm, Jorge Reyes <jorg_re..._at_hotmail.com> 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?
>
> 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.
>
> Suplicate: Help me please!!! :)
>
> I read about the "EXCEPTION WHEN... " and the
> "DBMS_ERRLOG.CREATE_ERROR_LOG", so i try 2 options like this:
>
> A) First try:
>
> CREATE OR REPLACE PROCEDURE OM_DB.MAKE_CDRS
> IS
>
> BEGIN
>
> INSERT INTO OM_DB.CDRS_VALIDOS
> SELECT
> ....... (A huge query, really big believe me jeje)
>
> EXCEPTION
> WHEN DUP_VAL_ON_INDEX THEN
> dbms_output.put_line('Clave Duplicada');
>
> END MAKE_CDRS;
>
> Error(s):
>
> 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 pragma
> 12:29:41.221 DBMS nmsnoc-db -- raise return select update while
> with <an identifier>
> 12:29:41.284 DBMS nmsnoc-db -- <a double-quoted delimited-
> identifier> <a bind variable> <<
> 12:29:41.346 DBMS nmsnoc-db -- close current delete fetch lock
> insert open rollback
> 12:29:41.440 DBMS nmsnoc-db -- savepoint set sql execute commit
> forall merge pipe, Batch 1 Line 146 Col 13
>
> B) Second try:
>
> CREATE OR REPLACE PROCEDURE OM_DB.MAKE_CDRS
> IS
>
> BEGIN
>
> EXECUTE IMMEDIATE 'DBMS_ERRLOG.CREATE_ERROR_LOG
> (''OM_DB.CDRS_VALIDOS'', ''errlog'')';
>
> INSERT INTO OM_DB.CDRS_VALIDOS
> SELECT
> ....... (A huge query, really big believe me jeje)
> LOG ERRORS INTO errlog('my_bad') REJECT LIMIT 10;
>
> END MAKE_CDRS;
>
> Error(s):
>
> 12:32:09.106 DBMS nmsnoc-db -- Error: PL/SQL: SQL Statement ignored,
> Batch 1 Line 57 Col 13
> 12:32:09.184 DBMS nmsnoc-db -- Error: PL/SQL: ORA-00942: table or
> view does not exist, Batch 1 Line 144 Col 29
I had no problems implementing your EXCEPTION block:
SQL> create table cdrs_validos(
2 nectarine varchar2(40), 3 mango number, 4 kiwi number, 5 guava varchar2(8)
6 );
Table created.
SQL>
SQL> alter table cdrs_validos
2 add constraint cdrs_validos_pk
3 primary key(nectarine, mango);
Table altered.
SQL>
SQL> create table cdrs_validos_hold(
2 nectarine varchar2(40), 3 mango number, 4 kiwi number, 5 guava varchar2(8)
6 );
Table created.
SQL> SQL> SQL> insert all
2 into cdrs_validos_hold
3 values('BR9984085900955', 7,9, 'LongDist') 4 into cdrs_validos_hold
5 values('BR9984085900955', 7,9, 'LongDist') 6 select * from dual;
2 rows created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE MAKE_CDRS
2 IS
3 BEGIN
4
5 INSERT INTO CDRS_VALIDOS 6 SELECT 7 nectarine, mango, kiwi, guava 8 from cdrs_validos_hold;
9
10 EXCEPTION
11 WHEN DUP_VAL_ON_INDEX THEN
12 dbms_output.put_line('Clave Duplicada'); 13
14 END MAKE_CDRS;
15 /
Procedure created.
SQL>
SQL> exec make_cdrs
Clave Duplicada
PL/SQL procedure successfully completed.
SQL> You'll need to show us exactly what you did when you received these errors:
Error(s):
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 pragma
12:29:41.221 DBMS nmsnoc-db -- raise return select update
while
with <an identifier>
12:29:41.284 DBMS nmsnoc-db -- <a double-quoted delimited-
identifier> <a bind variable> <<
12:29:41.346 DBMS nmsnoc-db -- close current delete fetch
lock
insert open rollback
12:29:41.440 DBMS nmsnoc-db -- savepoint set sql execute
commit
forall merge pipe, Batch 1 Line 146 Col 13
I suspect it has something to do with your 'really huge query' as it isn't the PL/SQL syntax causing your problems.
David Fitzjarrell Received on Tue Apr 21 2009 - 13:09:06 CDT