Not all errors in Oracle are fatal, and that statement applies to data manipulation language statements as well. Even though an error in and of itself may be fatal, when handled properly it becomes benign. The question is this: how do you make an otherwise fatal error benign or harmless? There are at least three ways, two of which you are probably quite familiar with, and a lesser known, but very versatile third method.
The Tried and True Ways
Two methods of capturing DML errors and being able to deal with them in a suitable manner are SQL*Loader and PL/SQL exception handling. If your situation involves uploading data or writing blocks of exception handling code (and only checking for a few errors), then by and large, these manners are suitable. SQL*Loader can provide a wealth of information about a bad record via the log, bad, and discard files, but its usefulness across all DML operations is limited. 数据挖掘论坛
PL/SQL exception handling can also capture errors and provide information for feedback, but coding all of the potential places where an integrity error can occur, in addition to keeping track of an exception block’s scope, can grow to be quite tedious. Besides, are you sure you accounted for all of the possible errors? The PL/SQL approach also means having to wrap blocks in an inappropriate manner and repeating the error handling from block to block. The PL/SQL approach also incurs the cost of context switching because of having to switch between SQL and PL/SQL. There has to be a better way, and in this case, there does exist a better way, namely, that of what is referred to as DML error logging.
数据挖掘工具
DML Error Logging Basics
Managing Tables, Chapter 15 of the Administrator’s Guide, explains what takes place during DML error logging. 数据挖掘论坛
To use DML error logging, you add a statement clause that specifies the name of an error logging table into which the database records errors encountered during DML operations. When you add this error logging clause to the INSERT statement, certain types of errors no longer terminate and roll back the statement. Instead, each error is logged and the statement continues. You then take corrective action on the erroneous rows at a later time.
The scenario just shown mentions INSERT, but DML error logging applies to UPDATE and DELETE as well. The basic steps (summarized from the documentation) are to: 数据挖掘论坛
1. Optionally create an error logging table. You can create the table manually or use the DBMS_ERRLOG package to automatically create it for you.
数据挖掘工具
2. Execute a DML statement and include an error logging clause. This clause: 数据挖掘实验室
-
Optionally references the error logging table that you created. If you do not provide an error logging table name, the database logs to an error logging table with a default name. The default error logging table name is ERR$_ followed by the first 25 characters of the name of the table that is being inserted into.
数据挖掘交友
-
Optionally includes a tag (a numeric or string literal in parentheses) that is added to the error log to help identify the statement that caused the errors. If the tag is omitted, a NULL value is used.
数据挖掘工具
-
Optionally includes a REJECT LIMIT subclause. This subclause indicates the maximum number of errors that can be encountered before the DML statement terminates and rolls back. You can also specify UNLIMITED. The default reject limit is zero, which means that upon encountering the first error, the error is logged and the statement rolls back. For parallel DML operations, the reject limit is applied to each parallel server. 数据挖掘论坛
3. Note: If the statement exceeds the reject limit and rolls back, the error logging table retains the log entries recorded so far.
数据挖掘交友
4. Query the error logging table and take corrective action for the rows that generated errors. 数据挖掘实验室
If you do create an error logging table, you must include all of the mandatory error logging columns (although they can be created in any order). You may optionally create additional columns, which reference the DML table. An important point to keep in mind is that datatypes of the additional columns must be able to capture or handle the datatype of the DML column. For example, don’t make a number column in the error table when the DML table is trying to use VARCHAR2. Do, however, make the error table column datatype VARCHAR2 when dealing with a number in the DML table. Oracle will handle the casting for you (if it can). VARCHAR2(4000) is pretty much the default datatype you should use for typical DML column datatypes.
The mandatory columns in an (again, optional) error table are: 数据挖掘研究院
You have two options with respect to creating an error table. The first is to let Oracle do the work for you, and that requires using the DBMS_ERRLOG package. This built-in will not only create the mandatory columns just mentioned, but will also map the target DML table’s columns. The second and decidedly more difficult way is to manually create the logging table via a data definition language (DDL) CREATE TABLE statement. Under the manual method, you are responsible for ensuring the mandatory parts are in place and for mapping any additional columns.
The only real advantage to manually creating an error logging table is that you can name it whatever you want and add (or not) only some of the DML table’s columns. Otherwise, the table is named ERR$_<first 25 characters of the DML table’s name>. 数据挖掘论坛
The DBMS_ERRLOG Package
This package, described in Chapter 38 of Oracle® Database PL/SQL Packages and Types Reference, 10g Release 2 (10.2) employs a security model. For the most part, you can create an error logging table for any table (or view) you own. Some of the package’s input parameters can be null, and the only mandatory input parameter is the name of the DML (or target) table. There is only one procedure in this package, and that is the CREATE_ERROR_LOG procedure. To help prevent a datatype mismatch between the DML and error logging table, you may want to consider using the skip_unsupported input parameter (BOOLEAN, default is false, meaning an unsupported column type will cause the procedure to terminate).
Let’s look at an example/use case for DML error logging. To keep things simple, we will use the EMP table in Scott’s schema. The steps below show how easy it is to create the error logging table. Note how all of the columns in EMP have been mapped to VARCHAR2(4000)’s in ERR$_EMP.
SQL> set serveroutput on
SQL> exec DBMS_ERRLOG.CREATE_ERROR_LOG (′EMP′);
PL/SQL procedure successfully completed.
SQL> desc errNot all errors in Oracle are fatal, and that statement applies to data manipulation language statements as well. Even though an error in and of itself may be fatal, when handled properly it becomes benign. The question is this: how do you make an otherwise fatal error benign or harmless? There are at least three ways, two of which you are probably quite familiar with, and a lesser known, but very versatile third method. 数据挖掘论坛
The Tried and True Ways
Two methods of capturing DML errors and being able to deal with them in a suitable manner are SQL*Loader and PL/SQL exception handling. If your situation involves uploading data or writing blocks of exception handling code (and only checking for a few errors), then by and large, these manners are suitable. SQL*Loader can provide a wealth of information about a bad record via the log, bad, and discard files, but its usefulness across all DML operations is limited.
数据挖掘交友
PL/SQL exception handling can also capture errors and provide information for feedback, but coding all of the potential places where an integrity error can occur, in addition to keeping track of an exception block’s scope, can grow to be quite tedious. Besides, are you sure you accounted for all of the possible errors? The PL/SQL approach also means having to wrap blocks in an inappropriate manner and repeating the error handling from block to block. The PL/SQL approach also incurs the cost of context switching because of having to switch between SQL and PL/SQL. There has to be a better way, and in this case, there does exist a better way, namely, that of what is referred to as DML error logging.
数据挖掘研究院
DML Error Logging Basics
Managing Tables, Chapter 15 of the Administrator’s Guide, explains what takes place during DML error logging. 数据挖掘实验室
To use DML error logging, you add a statement clause that specifies the name of an error logging table into which the database records errors encountered during DML operations. When you add this error logging clause to the INSERT statement, certain types of errors no longer terminate and roll back the statement. Instead, each error is logged and the statement continues. You then take corrective action on the erroneous rows at a later time.
数据挖掘工具
The scenario just shown mentions INSERT, but DML error logging applies to UPDATE and DELETE as well. The basic steps (summarized from the documentation) are to:
数据挖掘研究院
1. Optionally create an error logging table. You can create the table manually or use the DBMS_ERRLOG package to automatically create it for you.
2. Execute a DML statement and include an error logging clause. This clause: 数据挖掘工具
-
Optionally references the error logging table that you created. If you do not provide an error logging table name, the database logs to an error logging table with a default name. The default error logging table name is ERR$_ followed by the first 25 characters of the name of the table that is being inserted into.
数据挖掘交友
-
Optionally includes a tag (a numeric or string literal in parentheses) that is added to the error log to help identify the statement that caused the errors. If the tag is omitted, a NULL value is used. 数据挖掘论坛
-
Optionally includes a REJECT LIMIT subclause. This subclause indicates the maximum number of errors that can be encountered before the DML statement terminates and rolls back. You can also specify UNLIMITED. The default reject limit is zero, which means that upon encountering the first error, the error is logged and the statement rolls back. For parallel DML operations, the reject limit is applied to each parallel server.
3. Note: If the statement exceeds the reject limit and rolls back, the error logging table retains the log entries recorded so far.
数据挖掘交友
4. Query the error logging table and take corrective action for the rows that generated errors. 数据挖掘工具
If you do create an error logging table, you must include all of the mandatory error logging columns (although they can be created in any order). You may optionally create additional columns, which reference the DML table. An important point to keep in mind is that datatypes of the additional columns must be able to capture or handle the datatype of the DML column. For example, don’t make a number column in the error table when the DML table is trying to use VARCHAR2. Do, however, make the error table column datatype VARCHAR2 when dealing with a number in the DML table. Oracle will handle the casting for you (if it can). VARCHAR2(4000) is pretty much the default datatype you should use for typical DML column datatypes. 数据挖掘研究院
The mandatory columns in an (again, optional) error table are: 数据挖掘实验室
|
Column Name 数据挖掘工具
|
Datatype 数据挖掘论坛
|
Description
|
|
ORA_ERR_NUMBER$ 数据挖掘实验室
|
NUMBER
|
Oracle error number 数据挖掘论坛
|
|
ORA_ERR_MESG$ 数据挖掘论坛
|
VARCHAR2(2000)
|
Oracle error message text
|
|
ORA_ERR_ROWID$
|
ROWID
|
Rowid of the row in error 数据挖掘交友
(for update and delete)
|
|
ORA_ERR_OPTYP$
|
VARCHAR2(2) 数据挖掘论坛
|
Type of operation: I/U/D
(merge will include U and I) 数据挖掘论坛
|
|
ORA_ERR_TAG$ 数据挖掘交友
|
VARCHAR2(2000) 数据挖掘工具
|
Value of the tag supplied by the user in the error logging clause
|
You have two options with respect to creating an error table. The first is to let Oracle do the work for you, and that requires using the DBMS_ERRLOG package. This built-in will not only create the mandatory columns just mentioned, but will also map the target DML table’s columns. The second and decidedly more difficult way is to manually create the logging table via a data definition language (DDL) CREATE TABLE statement. Under the manual method, you are responsible for ensuring the mandatory parts are in place and for mapping any additional columns. 数据挖掘交友
The only real advantage to manually creating an error logging table is that you can name it whatever you want and add (or not) only some of the DML table’s columns. Otherwise, the table is named ERR$_<first 25 characters of the DML table’s name>. 数据挖掘工具
The DBMS_ERRLOG Package
This package, described in Chapter 38 of Oracle® Database PL/SQL Packages and Types Reference, 10g Release 2 (10.2) employs a security model. For the most part, you can create an error logging table for any table (or view) you own. Some of the package’s input parameters can be null, and the only mandatory input parameter is the name of the DML (or target) table. There is only one procedure in this package, and that is the CREATE_ERROR_LOG procedure. To help prevent a datatype mismatch between the DML and error logging table, you may want to consider using the skip_unsupported input parameter (BOOLEAN, default is false, meaning an unsupported column type will cause the procedure to terminate).
Let’s look at an example/use case for DML error logging. To keep things simple, we will use the EMP table in Scott’s schema. The steps below show how easy it is to create the error logging table. Note how all of the columns in EMP have been mapped to VARCHAR2(4000)’s in ERR$_EMP.
___FCKpd___0 数据挖掘工具
Now that the error logging table is created, let’s attempt a DML statement which we know will have an error. Try to duplicate the employee named MILLER.
SQL> INSERT INTO emp values (7934,′MILLER′,′CLERK′,7782,′23-JAN-82′,3900,null,20)
2 LOG ERRORS INTO errNot all errors in Oracle are fatal, and that statement applies to data manipulation language statements as well. Even though an error in and of itself may be fatal, when handled properly it becomes benign. The question is this: how do you make an otherwise fatal error benign or harmless? There are at least three ways, two of which you are probably quite familiar with, and a lesser known, but very versatile third method.
The Tried and True Ways
Two methods of capturing DML errors and being able to deal with them in a suitable manner are SQL*Loader and PL/SQL exception handling. If your situation involves uploading data or writing blocks of exception handling code (and only checking for a few errors), then by and large, these manners are suitable. SQL*Loader can provide a wealth of information about a bad record via the log, bad, and discard files, but its usefulness across all DML operations is limited. 数据挖掘论坛
PL/SQL exception handling can also capture errors and provide information for feedback, but coding all of the potential places where an integrity error can occur, in addition to keeping track of an exception block’s scope, can grow to be quite tedious. Besides, are you sure you accounted for all of the possible errors? The PL/SQL approach also means having to wrap blocks in an inappropriate manner and repeating the error handling from block to block. The PL/SQL approach also incurs the cost of context switching because of having to switch between SQL and PL/SQL. There has to be a better way, and in this case, there does exist a better way, namely, that of what is referred to as DML error logging.
DML Error Logging Basics
Managing Tables, Chapter 15 of the Administrator’s Guide, explains what takes place during DML error logging. 数据挖掘交友
To use DML error logging, you add a statement clause that specifies the name of an error logging table into which the database records errors encountered during DML operations. When you add this error logging clause to the INSERT statement, certain types of errors no longer terminate and roll back the statement. Instead, each error is logged and the statement continues. You then take corrective action on the erroneous rows at a later time. 数据挖掘实验室
The scenario just shown mentions INSERT, but DML error logging applies to UPDATE and DELETE as well. The basic steps (summarized from the documentation) are to:
1. Optionally create an error logging table. You can create the table manually or use the DBMS_ERRLOG package to automatically create it for you.
2. Execute a DML statement and include an error logging clause. This clause: 数据挖掘交友
-
Optionally references the error logging table that you created. If you do not provide an error logging table name, the database logs to an error logging table with a default name. The default error logging table name is ERR$_ followed by the first 25 characters of the name of the table that is being inserted into.
-
Optionally includes a tag (a numeric or string literal in parentheses) that is added to the error log to help identify the statement that caused the errors. If the tag is omitted, a NULL value is used.
-
Optionally includes a REJECT LIMIT subclause. This subclause indicates the maximum number of errors that can be encountered before the DML statement terminates and rolls back. You can also specify UNLIMITED. The default reject limit is zero, which means that upon encountering the first error, the error is logged and the statement rolls back. For parallel DML operations, the reject limit is applied to each parallel server.
数据挖掘工具
3. Note: If the statement exceeds the reject limit and rolls back, the error logging table retains the log entries recorded so far.
4. Query the error logging table and take corrective action for the rows that generated errors. 数据挖掘论坛
If you do create an error logging table, you must include all of the mandatory error logging columns (although they can be created in any order). You may optionally create additional columns, which reference the DML table. An important point to keep in mind is that datatypes of the additional columns must be able to capture or handle the datatype of the DML column. For example, don’t make a number column in the error table when the DML table is trying to use VARCHAR2. Do, however, make the error table column datatype VARCHAR2 when dealing with a number in the DML table. Oracle will handle the casting for you (if it can). VARCHAR2(4000) is pretty much the default datatype you should use for typical DML column datatypes. 数据挖掘实验室
The mandatory columns in an (again, optional) error table are: 数据挖掘实验室
|
Column Name 数据挖掘论坛
|
Datatype 数据挖掘工具
|
Description 数据挖掘交友
|
|
ORA_ERR_NUMBER$
|
NUMBER
|
Oracle error number
|
|
ORA_ERR_MESG$ 数据挖掘论坛
|
VARCHAR2(2000)
|
Oracle error message text 数据挖掘研究院
|
|
ORA_ERR_ROWID$ 数据挖掘工具
|
ROWID 数据挖掘工具
|
Rowid of the row in error
(for update and delete) 数据挖掘实验室
|
|
ORA_ERR_OPTYP$ 数据挖掘论坛
|
VARCHAR2(2) 数据挖掘交友
|
Type of operation: I/U/D 数据挖掘论坛
(merge will include U and I) 数据挖掘工具
|
|
ORA_ERR_TAG$ 数据挖掘论坛
|
VARCHAR2(2000) 数据挖掘实验室
|
Value of the tag supplied by the user in the error logging clause 数据挖掘研究院
|
You have two options with respect to creating an error table. The first is to let Oracle do the work for you, and that requires using the DBMS_ERRLOG package. This built-in will not only create the mandatory columns just mentioned, but will also map the target DML table’s columns. The second and decidedly more difficult way is to manually create the logging table via a data definition language (DDL) CREATE TABLE statement. Under the manual method, you are responsible for ensuring the mandatory parts are in place and for mapping any additional columns. 数据挖掘研究院
The only real advantage to manually creating an error logging table is that you can name it whatever you want and add (or not) only some of the DML table’s columns. Otherwise, the table is named ERR$_<first 25 characters of the DML table’s name>.
The DBMS_ERRLOG Package
This package, described in Chapter 38 of Oracle® Database PL/SQL Packages and Types Reference, 10g Release 2 (10.2) employs a security model. For the most part, you can create an error logging table for any table (or view) you own. Some of the package’s input parameters can be null, and the only mandatory input parameter is the name of the DML (or target) table. There is only one procedure in this package, and that is the CREATE_ERROR_LOG procedure. To help prevent a datatype mismatch between the DML and error logging table, you may want to consider using the skip_unsupported input parameter (BOOLEAN, default is false, meaning an unsupported column type will cause the procedure to terminate).
Let’s look at an example/use case for DML error logging. To keep things simple, we will use the EMP table in Scott’s schema. The steps below show how easy it is to create the error logging table. Note how all of the columns in EMP have been mapped to VARCHAR2(4000)’s in ERR$_EMP.
数据挖掘交友
SQL> set serveroutput on
SQL> exec DBMS_ERRLOG.CREATE_ERROR_LOG (′EMP′);
PL/SQL procedure successfully completed.
SQL> desc errNot all errors in Oracle are fatal, and that statement applies to data manipulation language statements as well. Even though an error in and of itself may be fatal, when handled properly it becomes benign. The question is this: how do you make an otherwise fatal error benign or harmless? There are at least three ways, two of which you are probably quite familiar with, and a lesser known, but very versatile third method.
数据挖掘工具
The Tried and True Ways
Two methods of capturing DML errors and being able to deal with them in a suitable manner are SQL*Loader and PL/SQL exception handling. If your situation involves uploading data or writing blocks of exception handling code (and only checking for a few errors), then by and large, these manners are suitable. SQL*Loader can provide a wealth of information about a bad record via the log, bad, and discard files, but its usefulness across all DML operations is limited. 数据挖掘交友
PL/SQL exception handling can also capture errors and provide information for feedback, but coding all of the potential places where an integrity error can occur, in addition to keeping track of an exception block’s scope, can grow to be quite tedious. Besides, are you sure you accounted for all of the possible errors? The PL/SQL approach also means having to wrap blocks in an inappropriate manner and repeating the error handling from block to block. The PL/SQL approach also incurs the cost of context switching because of having to switch between SQL and PL/SQL. There has to be a better way, and in this case, there does exist a better way, namely, that of what is referred to as DML error logging.
DML Error Logging Basics
Managing Tables, Chapter 15 of the Administrator’s Guide, explains what takes place during DML error logging. 数据挖掘实验室
To use DML error logging, you add a statement clause that specifies the name of an error logging table into which the database records errors encountered during DML operations. When you add this error logging clause to the INSERT statement, certain types of errors no longer terminate and roll back the statement. Instead, each error is logged and the statement continues. You then take corrective action on the erroneous rows at a later time.
The scenario just shown mentions INSERT, but DML error logging applies to UPDATE and DELETE as well. The basic steps (summarized from the documentation) are to:
数据挖掘实验室
1. Optionally create an error logging table. You can create the table manually or use the DBMS_ERRLOG package to automatically create it for you.
2. Execute a DML statement and include an error logging clause. This clause:
-
Optionally references the error logging table that you created. If you do not provide an error logging table name, the database logs to an error logging table with a default name. The default error logging table name is ERR$_ followed by the first 25 characters of the name of the table that is being inserted into.
数据挖掘实验室
-
Optionally includes a tag (a numeric or string literal in parentheses) that is added to the error log to help identify the statement that caused the errors. If the tag is omitted, a NULL value is used.
-
Optionally includes a REJECT LIMIT subclause. This subclause indicates the maximum number of errors that can be encountered before the DML statement terminates and rolls back. You can also specify UNLIMITED. The default reject limit is zero, which means that upon encountering the first error, the error is logged and the statement rolls back. For parallel DML operations, the reject limit is applied to each parallel server. 数据挖掘实验室
3. Note: If the statement exceeds the reject limit and rolls back, the error logging table retains the log entries recorded so far.
数据挖掘交友
4. Query the error logging table and take corrective action for the rows that generated errors. 数据挖掘研究院
If you do create an error logging table, you must include all of the mandatory error logging columns (although they can be created in any order). You may optionally create additional columns, which reference the DML table. An important point to keep in mind is that datatypes of the additional columns must be able to capture or handle the datatype of the DML column. For example, don’t make a number column in the error table when the DML table is trying to use VARCHAR2. Do, however, make the error table column datatype VARCHAR2 when dealing with a number in the DML table. Oracle will handle the casting for you (if it can). VARCHAR2(4000) is pretty much the default datatype you should use for typical DML column datatypes.
The mandatory columns in an (again, optional) error table are: 数据挖掘交友
|
Column Name 数据挖掘论坛
|
Datatype 数据挖掘工具
|
Description 数据挖掘实验室
|
|
ORA_ERR_NUMBER$ 数据挖掘工具
|
NUMBER 数据挖掘论坛
|
Oracle error number
|
|
ORA_ERR_MESG$ 数据挖掘研究院
|
VARCHAR2(2000)
|
Oracle error message text
|
|
ORA_ERR_ROWID$ 数据挖掘实验室
|
ROWID 数据挖掘实验室
|
Rowid of the row in error
(for update and delete)
|
|
ORA_ERR_OPTYP$ 数据挖掘实验室
|
VARCHAR2(2) 数据挖掘论坛
|
Type of operation: I/U/D 数据挖掘实验室
(merge will include U and I)
|
|
ORA_ERR_TAG$ 数据挖掘论坛
|
VARCHAR2(2000) 数据挖掘研究院
|
Value of the tag supplied by the user in the error logging clause
|
You have two options with respect to creating an error table. The first is to let Oracle do the work for you, and that requires using the DBMS_ERRLOG package. This built-in will not only create the mandatory columns just mentioned, but will also map the target DML table’s columns. The second and decidedly more difficult way is to manually create the logging table via a data definition language (DDL) CREATE TABLE statement. Under the manual method, you are responsible for ensuring the mandatory parts are in place and for mapping any additional columns.
数据挖掘论坛
The only real advantage to manually creating an error logging table is that you can name it whatever you want and add (or not) only some of the DML table’s columns. Otherwise, the table is named ERR$_<first 25 characters of the DML table’s name>.
The DBMS_ERRLOG Package
This package, described in Chapter 38 of Oracle® Database PL/SQL Packages and Types Reference, 10g Release 2 (10.2) employs a security model. For the most part, you can create an error logging table for any table (or view) you own. Some of the package’s input parameters can be null, and the only mandatory input parameter is the name of the DML (or target) table. There is only one procedure in this package, and that is the CREATE_ERROR_LOG procedure. To help prevent a datatype mismatch between the DML and error logging table, you may want to consider using the skip_unsupported input parameter (BOOLEAN, default is false, meaning an unsupported column type will cause the procedure to terminate). 数据挖掘工具
Let’s look at an example/use case for DML error logging. To keep things simple, we will use the EMP table in Scott’s schema. The steps below show how easy it is to create the error logging table. Note how all of the columns in EMP have been mapped to VARCHAR2(4000)’s in ERR$_EMP.
___FCKpd___0 数据挖掘研究院
Now that the error logging table is created, let’s attempt a DML statement which we know will have an error. Try to duplicate the employee named MILLER.
___FCKpd___1 数据挖掘工具
We failed to insert into EMP, but what is in ERR$_EMP? 数据挖掘论坛
数据挖掘论坛
As another example, what if the ENAMEs were constrained to be not null? Issue "alter table emp modify (ename not null);" to achieve the desired effect, and then attempt an insert as shown below (with and without the error logging clause).
数据挖掘论坛
You have to admit this is much easier than using nested PL/SQL blocks where scope has to be considered to keep an operation running. 数据挖掘研究院
In Closing
The utility or usefulness of DML error logging is considerable if your application processes large amounts of records. Instead of (potentially) bombing out after running for hours, you can craft a means of allowing good records to be processed and then come back to problem records at a later time. This approach to programming does not imply you should minimize why an error occurred. Capturing badly formatted data is a clear case of utility, but handling incorrect referential data must be considered as to why or how DML failed.
数据挖掘工具
emp;
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)
EMPNO VARCHAR2(4000)
ENAME VARCHAR2(4000)
JOB VARCHAR2(4000)
MGR VARCHAR2(4000)
HIREDATE VARCHAR2(4000)
SAL VARCHAR2(4000)
COMM VARCHAR2(4000)
DEPTNO VARCHAR2(4000)
Now that the error logging table is created, let’s attempt a DML statement which we know will have an error. Try to duplicate the employee named MILLER. 数据挖掘实验室
___FCKpd___1 数据挖掘工具
We failed to insert into EMP, but what is in ERR$_EMP? 数据挖掘工具
As another example, what if the ENAMEs were constrained to be not null? Issue "alter table emp modify (ename not null);" to achieve the desired effect, and then attempt an insert as shown below (with and without the error logging clause).
数据挖掘实验室
数据挖掘实验室
You have to admit this is much easier than using nested PL/SQL blocks where scope has to be considered to keep an operation running. 数据挖掘论坛
In Closing
The utility or usefulness of DML error logging is considerable if your application processes large amounts of records. Instead of (potentially) bombing out after running for hours, you can craft a means of allowing good records to be processed and then come back to problem records at a later time. This approach to programming does not imply you should minimize why an error occurred. Capturing badly formatted data is a clear case of utility, but handling incorrect referential data must be considered as to why or how DML failed.
数据挖掘研究院
emp (′insert example′) REJECT LIMIT 25;
0 rows created.
数据挖掘论坛
We failed to insert into EMP, but what is in ERR$_EMP?
数据挖掘研究院
数据挖掘交友
As another example, what if the ENAMEs were constrained to be not null? Issue "alter table emp modify (ename not null);" to achieve the desired effect, and then attempt an insert as shown below (with and without the error logging clause).
数据挖掘交友
数据挖掘实验室
You have to admit this is much easier than using nested PL/SQL blocks where scope has to be considered to keep an operation running.
In Closing
The utility or usefulness of DML error logging is considerable if your application processes large amounts of records. Instead of (potentially) bombing out after running for hours, you can craft a means of allowing good records to be processed and then come back to problem records at a later time. This approach to programming does not imply you should minimize why an error occurred. Capturing badly formatted data is a clear case of utility, but handling incorrect referential data must be considered as to why or how DML failed. 数据挖掘交友
emp;
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)
EMPNO VARCHAR2(4000)
ENAME VARCHAR2(4000)
JOB VARCHAR2(4000)
MGR VARCHAR2(4000)
HIREDATE VARCHAR2(4000)
SAL VARCHAR2(4000)
COMM VARCHAR2(4000)
DEPTNO VARCHAR2(4000)
Now that the error logging table is created, let’s attempt a DML statement which we know will have an error. Try to duplicate the employee named MILLER. 数据挖掘工具
___FCKpd___1
We failed to insert into EMP, but what is in ERR$_EMP?
数据挖掘研究院
数据挖掘论坛
As another example, what if the ENAMEs were constrained to be not null? Issue "alter table emp modify (ename not null);" to achieve the desired effect, and then attempt an insert as shown below (with and without the error logging clause).
数据挖掘论坛
You have to admit this is much easier than using nested PL/SQL blocks where scope has to be considered to keep an operation running.
In Closing
The utility or usefulness of DML error logging is considerable if your application processes large amounts of records. Instead of (potentially) bombing out after running for hours, you can craft a means of allowing good records to be processed and then come back to problem records at a later time. This approach to programming does not imply you should minimize why an error occurred. Capturing badly formatted data is a clear case of utility, but handling incorrect referential data must be considered as to why or how DML failed. 数据挖掘工具
