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
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