Database trace for a target: missing required value

The following database trace file example was produced by using almost the same map as in the preceding example except for the usage of a different input data file. This database trace file illustrates the information you might see if a database error occurred while attempting to insert or update rows in a table.

Database type is Oracle
Status returned to engine: (0) Success
No existing connection was found.
Connection to Oracle has been established.
Interface library version 6.0(140)
Loading data for output card 1.
Database adapter version 6.0(140)
Starting database load...
Host string: 
Userid     : eventmgt
Password   : ********
Update mode is on.
The columns are of the following types:
   Column 1 (MEMBERID) type is VARCHAR(10).
   Column 2 (FIRSTNAME) type is VARCHAR(30).
   Column 3 (LASTNAME) type is VARCHAR(50).
   Column 4 (TITLE) type is VARCHAR(50).
   Column 5 (COMPANYNAME) type is VARCHAR(50).
   Column 6 (ADDRESS) type is VARCHAR(255).
   Column 7 (CITY) type is VARCHAR(50).
   Column 8 (STATEORPROVINCE) type is VARCHAR(20).
   Column 9 (POSTALCODE) type is VARCHAR(20).
   Column 10 (COUNTRY) type is VARCHAR(50).
   Column 11 (PHONENUMBER) type is VARCHAR(30).
   Column 12 (FAXNUMBER) type is VARCHAR(30).
   Column 13 (EMAILADDR) type is VARCHAR(50).
   Column 14 (MEMBERSINCE) type is DATE.
The insert statement to be executed is:
INSERT INTO Membership  VALUES 
     (:a00,:a01,:a02,:a03,:a04,:a05,:a06,:a07,:a08,:a09,:a10,:a11,:a12,:a13)
The update statement to be executed is:
UPDATE Membership SET FIRSTNAME=:a01,LASTNAME=:a02,TITLE=:a03, COMPANYNAME=:a04,
     ADDRESS=:a05,CITY=:a06,STATEORPROVINCE=:a07, POSTALCODE=:a08,COUNTRY=:a09,
     PHONENUMBER=:a10,FAXNUMBER=:a11,EMAILADDR=:a12 WHERE (MEMBERID=:a00)
Error in: oexec

               Message : ORA-01400: cannot insert NULL into 
("EVENTMGT"."MEMBERSHIP"."LASTNAME")

               The following values were being inserted: 
 
                Column 1 MEMBERID : D190-0002 
 
                Column 2 FIRSTNAME : Leverling 
 
                Column 3 LASTNAME : NULL 
 
                Column 4 TITLE : Vice President-New Products
 
                Column 5 COMPANYNAME : Northwind Traders 
 
                Column 6 ADDRESS : 722 Moss Bay Blvd. 
 
                Column 7 CITY : Kirkland 
 
                Column 8 STATEORPROVINCE : WA 
 
                Column 9 POSTALCODE : 98033 
 
                Column 10 COUNTRY : USA 
 
                Column 11 PHONENUMBER : (206) 555-3412 
 
                Column 12 FAXNUMBER : (206) 555-3413 
 
                Column 13 EMAILADDR : jleverling@northwind.com 
 
                Column 14 MEMBERSINCE : 1999-07-09 22:03:03

               Failed to insert a row (rc = -9).

               Failed after 1 rows inserted.

               Database load complete.

               Error returned to engine: (-9) Failed to execute the SQL statement

               Cleaning up and closing the transaction...

               Transaction rollback was successful.

               Status returned to engine: (0) Success

               Commit was successful.

               Database disconnect succeeded.
            

In this example, the bold text is information that can be provided when an error occurs. The first lines of this example provide the same information as was in the database trace file with no errors: connection parameters, update mode indicator, table column descriptions, SQL statements to be used, and so on.

However, after the UPDATE statement, the database trace file provides details to assist in determining the problem. The Message line indicates that an attempt was made to insert a row with a NULL value for the EVENT.MEMBERSHIP.LASTNAME column that cannot contain a NULL. To further identify the row in the output data causing the error, the database trace file lists the values for all of the columns in the row causing the error.

The remaining lines in the database trace file display information about the disposition of the entire database card transaction. The transaction failed after one row was inserted. The database adapter returns an error code of -9 to the Launcher with a corresponding error message of Failed to execute the SQL statement. If you produced the audit log, you would see the following line in the execution summary section:

<TargetReport  card="1"  adapter="DB"  bytes="1218" adapterreturn="-9">
      <Message>Failed to execute the SQL statement</Message>
      <TimeStamp>22:07:32 January 8, 2004</TimeStamp>
   </TargetReport>

Subsequently, the database trace file communicates that the database adapter is cleaning up and closing the transaction. Because the OnFailure setting was set to Rollback for this output card, the final entry in the database trace reveals that the transaction rollback was successful.