Home » SQL & PL/SQL » SQL & PL/SQL » More precision about log errors (11.2.0.3)
More precision about log errors [message #689855] Wed, 22 May 2024 07:46
Amine
Messages: 376
Registered: March 2010
Senior Member

Hi all
I have a problem to get the right information about an error when trying to insert a row.
Let's have an example.

SQL> drop table test_tab;

Table dropped.

 real: 125
SQL> create table test_tab
  2  (
  3  	var1 number(2)	,
  4  	var2 number(2)	,
  5  	var3 number(1)	,
  6  	var4 number(2)
  7  );

Table created.

 real: 31
SQL> 
SQL> drop table test_tab_err_log;

Table dropped.

 real: 110
SQL> begin
  2  	dbms_errlog.create_error_log(dml_table_name => 'test_tab'
  3  	, err_log_table_name => 'test_tab_err_log');
  4  end;
  5  /

PL/SQL procedure successfully completed.

 real: 78
SQL> 
SQL> insert into test_tab
  2  select 0,0,25,0
  3  from dual
  4  log errors
  5  into test_tab_err_log ('INSERT')
  6  reject limit unlimited
  7  ;

0 rows created.

 real: 47
SQL> 
SQL> column ora_err_mesg$ format a80
SQL> select ora_err_number$, ora_err_mesg$ from test_tab_err_log;

ORA_ERR_NUMBER$ ORA_ERR_MESG$
--------------- --------------------------------------------------------------------------------
           1438 ORA-01438: value larger than specified precision allowed for this column

 real: 15
SQL> 
The problem is that the error message doesn't tell us which column generates the error (in this case it's the column var3).

How to get this information ?

Thanks in advance
Previous Topic: previous month
Next Topic: Add Column with default existing column
Goto Forum:
  


Current Time: Thu Nov 21 07:45:35 CST 2024