INSERT LOG ERRORS

最新推荐文章于 2023-12-05 20:32:41 发布

叶竹

最新推荐文章于 2023-12-05 20:32:41 发布

阅读量2.0k

点赞数

分类专栏： oracle 错误诊断文章标签： insert parallel sql table null delete

oracle 错误诊断专栏收录该内容

6 篇文章

订阅专栏

CREATE TABLE sales_overall as select * from sales where 1=0;

Table created.

SQL> exec dbms_errlog.create_error_log('sales_overall');

PL/SQL procedure successfully completed.

SQL> desc err$_sales_overall
Name                                                                         Null? Type
----------------------------------------------------------------------------- -------- -------------------
ORA_ERR_NUMBER$                                                                      NUMBER
ORA_ERR_MESG$                                                                         VARCHAR2(2000)
ORA_ERR_ROWID$                                                                      ROWID
ORA_ERR_OPTYP$                                                                      VARCHAR2(2)
ORA_ERR_TAG$                                                                         VARCHAR2(2000)
PROD_ID                                                                               VARCHAR2(4000)
CUST_ID                                                                               VARCHAR2(4000)
TIME_ID                                                                               VARCHAR2(4000)
CHANNEL_ID                                                                            VARCHAR2(4000)
PROMO_ID                                                                            VARCHAR2(4000)
QUANTITY_SOLD                                                                         VARCHAR2(4000)
AMOUNT_SOLD                                                                         VARCHAR2(4000)

SQL> INSERT /*+ APPEND NOLOGGING PARALLEL */ INTO sales_overall
  2  SELECT * FROM sales_activity_direct
  3  LOG ERRORS INTO err$_sales_overall ( 'load_test1' );
INSERT /*+ APPEND NOLOGGING PARALLEL */ INTO sales_overall
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-01722: invalid number
ORA-06512: at "SYS. ORACLE _LOADER", line 52

SQL> desc sales_activity_direct
Name                                                                         Null? Type
----------------------------------------------------------------------------- -------- ----------------------------------------------------
PROD_ID                                                                               NUMBER
CUST_ID                                                                               NUMBER
TIME_ID                                                                               CHAR(20)
CHANNEL_ID                                                                            CHAR(2)
PROMO_ID                                                                            NUMBER
QUANTITY_SOLD                                                                         NUMBER(3)
AMOUNT_SOLD                                                                         CHAR(50)

SQL>
SQL> select count(*) from sales_overall;

  COUNT(*)
----------
      0

SQL> select count(*) from err$_sales_overall;

  COUNT(*)
----------
      1

SQL> select ORA_ERR_NUMBER$||
  2  ORA_ERR_MESG$||
  3  ORA_ERR_ROWID$||
  4  ORA_ERR_OPTYP$||
  5  ORA_ERR_TAG$||
  6  PROD_ID||
  7  CUST_ID||
  8  TIME_ID||
  9  CHANNEL_ID||
10  PROMO_ID||
11  QUANTITY_SOLD||
12  AMOUNT_SOLD msg
13  from err$_sales_overall;

MSG
--------------------------------------------------------------------------------------------------------------------------------------------
1722ORA-01722: invalid number
Iload_test11398720-FEB-09399911232,99

SQL> INSERT /*+ APPEND NOLOGGING PARALLEL */ INTO sales_overall
  2  SELECT * FROM sales_activity_direct
  3  LOG ERRORS INTO err$_sales_overall ( 'load_test2' ) REJECT LIMIT 10;
INSERT /*+ APPEND NOLOGGING PARALLEL */ INTO sales_overall
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-01722: invalid number
ORA-06512: at "SYS.ORACLE_LOADER", line 52

SQL> select count(*) from err$_sales_overall;

  COUNT(*)
----------
      12

SQL> select count(*) from err$_sales_overall where ORA_ERR_TAG$= 'load_test2';

  COUNT(*)
----------
      11

SQL> commit;

Commit complete.

SQL> delete from err$_sales_overall;

12 rows deleted.

SQL> INSERT /*+ APPEND NOLOGGING PARALLEL */ INTO sales_overall
  2  SELECT * FROM sales_activity_direct
  3  LOG ERRORS INTO err$_sales_overall ( 'load_20040802' ) REJECT LIMIT UNLIMITED;

100 rows created.

SQL> commit;

Commit complete.

SQL>
SQL> select count(*) from sales_overall;

  COUNT(*)
----------
   100

SQL> select count(*) from err$_sales_overall;

  COUNT(*)
----------
   17845

SQL>
SQL> select distinct ora_err_number$ from err$_sales_overall;

ORA_ERR_NUMBER$
---------------
         1400
         1830
         1843
         1722

SQL> column ora_err_mesg$ format a80
SQL> select distinct ora_err_number$, ora_err_mesg$ from err$_sales_overall;

ORA_ERR_NUMBER$ ORA_ERR_MESG$
--------------- --------------------------------------------------------------------------------
         1400 ORA-01400: cannot insert NULL into ("SH"."SALES_OVERALL"."CUST_ID")
         1830 ORA-01830: date format picture ends before converting entire input string
         1400 ORA-01400: cannot insert NULL into ("SH"."SALES_OVERALL"."PROD_ID")
         1722 ORA-01722: invalid number
         1843 ORA-01843: not a valid month