Re: error handling using stored procedure

From: Terry Dykstra <tddykstra_at_forestoil.ca>
Date: Tue, 21 Apr 2009 17:52:30 GMT
Message-ID: <yJnHl.23356$Db2.13142_at_edtnps83>

"Jorge Reyes" <jorg_reyes_at_hotmail.com> wrote in message news:762b3f36-3015-487a-9bec-15fd82b13c56_at_n8g2000vbb.googlegroups.com...
> 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

The answer depends on the Oracle version. For starters, take a look at MERGE.

-- 
Terry Dykstra

Received on Tue Apr 21 2009 - 12:52:30 CDT