Menu
(Oracle PL/SQL)
If I have a simple SQL statement that is throwing an error, ie:
DECLARE
v_sql_errm varchar2(2048);
BEGIN
UPDATE my_table SET my_column = do_something(my_column)
WHERE my_column IS NOT NULL;
EXCEPTION
when others then
-- How can I obtain the row/value causing the error (unknown)?
v_sql_errm := SQLERRM;
insert into log_error (msg) values ('Error updating value (unknown): '||
v_sql_errm);
END;
Is there any way within the exception block to determine the row/value on which the query is encountering an error? I would like to be able to log it so that I can then go in and modify/correct the specific data value causing the error.
934
Add Back Trace to Error Handler CREATE OR REPLACE PROCEDURE proc3 IS BEGIN DBMS_OUTPUT. put_line ('calling proc2'); proc2; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT. put_line ('Error backtrace at top level:'); DBMS_OUTPUT. put_line (DBMS_UTILITY.
To see the errors, you use SHOW ERRORS. When you specify SHOW ERRORS with no arguments, SQL*Plus shows compilation errors for the most recently created or altered stored procedure.
Handle an exception by trapping it with a handler or propagating it to the calling environment. For example, if your SELECT statement returns more than one row, TimesTen returns an error (exception) at runtime. As the following example shows, you would see TimesTen error 8507, then the associated ORA error message.
You have an extraneous DECLARE -- you would only use that if you are declaring a PL/SQL block that doesn't involve a CREATE . You are missing semicolons after your RETURN statements. A procedure cannot return a value.
This can be done using DML error logging, if you are on 10gR2 or later.
An example:
SQL> create table my_table (my_column)
2 as
3 select level from dual connect by level <= 9
4 /
Tabel is aangemaakt.
SQL> create function do_something
2 ( p_my_column in my_table.my_column%type
3 ) return my_table.my_column%type
4 is
5 begin
6 return 10 + p_my_column;
7 end;
8 /
Functie is aangemaakt.
SQL> alter table my_table add check (my_column not in (12,14))
2 /
Tabel is gewijzigd.
SQL> exec dbms_errlog.create_error_log('my_table')
PL/SQL-procedure is geslaagd.
This creates an error logging table called err$_my_table. This table is filled by adding a log errors clause to your update statement:
SQL> begin
2 update my_table
3 set my_column = do_something(my_column)
4 where my_column is not null
5 log errors reject limit unlimited
6 ;
7 end;
8 /
PL/SQL-procedure is geslaagd.
SQL> select * from err$_my_table
2 /
ORA_ERR_NUMBER$
--------------------------------------
ORA_ERR_MESG$
--------------------------------------------------------------------
ORA_ERR_ROWID$
--------------------------------------------------------------------
OR
--
ORA_ERR_TAG$
--------------------------------------------------------------------
MY_COLUMN
--------------------------------------------------------------------
2290
ORA-02290: check constraint (RWK.SYS_C00110133) violated
AAGY/aAAQAABevcAAB
U
12
2290
ORA-02290: check constraint (RWK.SYS_C00110133) violated
AAGY/aAAQAABevcAAD
U
14
2 rijen zijn geselecteerd.
Prior to 10gR2, you can use the SAVE EXCEPTIONS clause: http://rwijk.blogspot.com/2007/11/save-exceptions.html
164
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
