Common Database Errors | PeopleSoft Tutorial

Common Database Errors

Common Database Errors

Following is a list of known SQL errors, and potential solutions to the problems that they represent.

Known Database Error Codes

Database Error Code SQL State Message Solution
DB2 -803 23505 SQL0803N One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "1" constrains table "%s" from having duplicate rows for those columns. Duplicate values have been inserted into a column that has a UNIQUE constraint. It is the responsibility of the application to deal with prevention of insertion of duplicate values.
DB2 -511 42829 SQL0511N The FOR UPDATE clause is not allowed because the table specified by the cursor cannot be modified. A datastore transaction read was attempted on a table that is marked as read-only. Either read the data outside of a transaction, or use optimistic transactions.
DB2 -401 42818 SQL0401N The data types of the operands for the operation ">=" are not compatible. A mathematical comparison query was attempted on a field whose mapping was to a non-numeric field, such as VARCHAR. DB2 disallows such queries.
DB2 -302 22003 SQL0302N The value of a host variable in the EXECUTE or OPEN statement is too large for its corresponding use. Possible attempt to store a string of a length greater than is allowed by the database’s column definition. If creation is done via the mapping tool, ensure that the length attribute of the column element specifies a large enough size for the column.
DB2 -204 42S02 SQL0204N "%s" is an undefined name. The database schema does not match the mapping defined in the metadata for the persistent class. See the mapping documentation.
DB2 -99999 22003 Numeric value out of range. A numeric range error occured. Ensure that the capacity of the numeric column is sufficient to hold the specified value the persistent object is attempting to store.
DB2 -99999 HY003 CLI0122E Program type out of range. A numeric or string range error occured. Ensure that the capacity of the numeric or string column is sufficient to store the specified value the persistent object is attempting to store.
HSQL -8 23000 Integrity constraint violation in statement %s Attempted modification of a row that would cause a violation of referential integrity constraints. Make sure your mapping metadata declares your database’s foreign keys.
HSQL -9 23000 Violation of unique index: 23000 Violation of unique index in statement %s Duplicate values have been inserted into a column that has a UNIQUE constraint. It is the responsibility of the application to deal with prevention of insertion of duplicate values.
HSQL -40 S1000 General error: S1000 General error java.lang.NumberFormatException: %d in statement %s A numeric range error occured. Ensure that the capacity of the numeric column is sufficient to store the specified value the persistent object is attempting to store. Note that some versions of HSQL have a bug that prevents Long.MIN_VALUE from being stored.
MySQL 1062 S1009 Invalid argument value: Duplicate entry '1' for key 1 Duplicate values have been inserted into a column that has a UNIQUE constraint. It is the responsibility of the application to deal with prevention of insertion of duplicate values.
MySQL 1196 S1000 General error: Warning: Some non-transactional changed tables couldn't be rolled back One or more tables that are being manipulated are not configured to be transactional. Tables in MySQL, by default, do not support transactions. Table type for schema creation can be configured with the TableType property of the DBDictionary configuration property.
MySQL 1213 S1000 General error: Deadlock found when trying to get lock; Try restarting transaction A deadlock occured during a datastore transaction. This can occur when transaction TRANS1 locks table TABLE1, transaction TRANS2 locks table TABLE2, TRANS1 lines up to get a lock on TABLE2, and then TRANS2 lines up to get a lock on TABLE1. Deadlock prevention is the responsibility of the application, or the application server in which it runs. For more details, see the MySQL deadlock documentation.
MySQL 0 08S01 Communication link failure: java.io.IOException The TCP connection underlying the JDBC Connection has been closed, possibly due to a timeout. If using Kodo’s default DataSource, connection testing can be configured via the ConnectionFactoryProperties property.
MySQL 1030 S1000 General error: Got error 139 from table handler This is a bug in MySQL server, and can occur when using tables of type InnoDB when long SQL statements are sent to the server. Upgrade to a more recent version of MySQL to resolve the problem.
MySQL 1054 S0022 Column not found: Unknown column 'Infinity' in 'field list' MySQL disallows storage of Double.POSITIVE_INFINITY or Double.NEGATIVE_INFINITY values.
Oracle 17069 null Use explicit XA call Manual transaction operations were attempted on a data source that was configured to use an XA transaction. In order to utilize XA transactions, set the kodo.ConnectionFactoryMode property to managed.
Oracle 17433 null invalid arguments in call The Oracle JDBC driver throws this exception when a null username or password are specified. A username and password was not specified in the Kodo configuration, nor was it specified in the database configuration mechanism, nor was it specified in the PersistenceManager.getPersistenceManager invocation.
Oracle 904 42000 ORA-00904: invalid column name The database schema does not match the mapping defined in the metadata for the persistent class. See the mapping documentation.
Oracle 1722 42000 ORA-01722: invalid number A number that Oracle cannot store has been persisted. This can happen when a string field in the persistent class is mapped to an Oracle column of type NUMBER and the String value is not numeric.
Oracle 1000 72000 ORA-01000: maximum open cursors exceeded Oracle limits the number of statements that can be open at any given time, and the application has made requests that keep open more statements than Oracle can handle. This can be resolved in one of the following ways:

  1. Increase the number of cursors allowed in the database. This is typically done by increasing the open_cursors parameter in the initSIDNAME.ora file.
  2. Ensure that Kodo query results and Extent iterators are being closed, since open results will maintain an open ResultSet on the server side until they are garbage collected.
  3. Decrease the value of the MaxCachedStatements parameter in the ConnectionFactoryProperties configuration property.
Oracle 932 42000 ORA-00932: inconsistent datatypes: expected - got CLOB A normal string field was mapped to an Oracle CLOB type. Oracle requires special handling for CLOBs. Ensure that the column for this field specifies a jdbc-type of clob or a length of -1.
Oracle 1 23000 ORA-00001: unique constraint (%s) violated Duplicate values have been inserted into a column that has a UNIQUE constraint. It is the responsibility of the application to deal with prevention of insertion of duplicate values.
Oracle 0 null Underflow Exception A numeric underflow occured. Ensure that the capacity of the numeric column is sufficient to store the specified value the persistent object is attempting to store. Note that Oracle NUMERIC fields have a limitation of 38 digits.
Oracle 0 null Overflow Exception A numeric underflow occured. Ensure that the capacity of the numeric column is sufficient to store the specified value the persistent object is attempting to store. Note that Oracle NUMERIC fields have a limitation of 38 digits.
Pointbase 78003 ZW003 The value "%s" cannot be converted to a number. This can happen when a string field in the persistent class is mapped to a numeric column, and the string value cannot be parsed into a number.
Pointbase 25203 22003 Data exception -- numeric value out of range. %d. A numeric range error occured. Ensure that the capacity of the numeric column is sufficient to store the specified value the persistent object is attempting to store.
PostgreSQL 0 null ERROR: Unable to identify an operator '>=' for types 'numeric' and 'double precision' You will have to retype this query using an explicit cast An integer field is mapped to a decimal column type. PostgreSQL disallows performing numeric comparisons between integers and decimals.
PostgreSQL 0 null ERROR: Cannot insert a duplicate key into unique index bug488pcx_pkey Duplicate values have been inserted into a column that has a UNIQUE constraint. It is the responsibility of the application to deal with prevention of insertion of duplicate values.
PostgreSQL 0 null ERROR: Attribute 'infinity' not found PostgreSQL disallows storage of Double.POSITIVE_INFINITY or Double.NEGATIVE_INFINITY values.
PostgreSQL 0 null You will have to retype this query using an explicit cast A string field is mapped to a numeric column type. PostgreSQL disallows performing string comparisons in queries against a numeric column.
SQLServer 0 08007 Can't start a cloned connection while in manual transaction mode. Append “;SelectMethod=cursor” to the ConnectionURL. See the description of the problem on the Microsoft support site.
SQLServer sp_cursorclose: The cursor identifier value provided (abcdef0) is not valid. This can sometimes show up as a warning when Kodo is closing a prepared statement. It is due to a bug in the SQLServer driver, and can be ignored, since it should not affect anything.
SQLServer 306 HY000 The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator. A query ordering was attempted on a field that is mapped to a CLOB or BLOB, which is disallowed by SQLServer.
SQLServer 8114 HY000 Error converting data type varchar to %s. This can happen when a string field in the persistent class is mapped to a numeric column, and the string value cannot be parsed into a number.
SQLServer 245 22018 Syntax error converting the varchar value '%s' to a column of data type int. This can happen when a string field in the persistent class is mapped to a numeric column, and the string value cannot be parsed into a number.
SQLServer 2627 23000 Violation of PRIMARY KEY constraint 'PK__%s'. Cannot insert duplicate key in object '%s'. Duplicate values have been inserted into a primary key column that has a UNIQUE constraint. It is the responsibility of the application to deal with prevention of insertion of duplicate primary keys when using application identity.
SQLServer 169 HY000 A column has been specified more than once in the order by list. Columns in the order by list must be unique. Ensure that there are no duplicates in the ordering of the query.
SQLServer 0 HY000 Object has been closed. The TCP connection underlying the JDBC connection may have been closed, possibly due to a timeout. If using Kodo’s default DataSource, connection testing can be configured via the ConnectionFactoryProperties configuration property.
Sybase 311 ZZZZZ The optimizer could not find a unique index which it could use to scan table '%s' for cursor 'jconnect_implicit_%d' A pessimistic lock was attempted on a table that does not have a primary key (or other unique index). By default, the Kodo mapping tool does not create primary keys for join tables. In order to use datastore locking for relations, an IDENTITY column should be added to any tables that do not already have them.
Sybase 2762 The 'CREATE TABLE' command is not allowed within a multi-statement transaction in the 'tempdb' database. This may happen when running the schema tool against a Sybase database that is not configured to allow schema-altering commands to be executed from within a transaction. This can be enabled by entering the command sp_dboption database_name,"ddl in tran", true from isql. See the Sybase documentation for allowing data definition commands in transactions .
Sybase 0 JZ0BE JZ0BE: BatchUpdateException: Error occurred while executing batch statement: Arithmetic overflow during implicit conversion of NUMERIC value '%d' to a NUMERIC field. A numeric overflow occured. Ensure that the capacity of the numeric column is sufficient to store the specified value the persistent object is attempting to store.
Sybase 0 JZ00B JZ00B: Numeric overflow. A numeric overflow occured. Ensure that the capacity of the numeric column is sufficient to store the specified value the persistent object is attempting to store.
Sybase 257 42000 Implicit conversion from datatype 'VARCHAR' to 'TINYINT' is not allowed. Use the CONVERT function to run this query. A string field is stored in a column of numeric type. Sybase disallows querying against these fields.
Sybase 169 ZZZZZ Expression '1' and '8' in the ORDER BY list are same. Expressions in the ORDER BY list must be unique. Ensure that there are no duplicates in the ordering of the query.
Sybase 511 ZZZZZ Attempt to update or insert row failed because resultant row of size 2009 bytes is larger than the maximum size (1961 bytes) allowed for this table. Possible attempt to store a string of a length greater than is allowed by the database’s column definition. If creation is done via the mapping tool, ensure that the length attribute of the column element specifies a large enough size for the column.
Sybase <arel="nofollow" href="http://manuals.sybase.com/onlinebooks/group-as/asg1250e/svrtsg/@Generic__BookTextView/34048;pt=28619" target="_top"> 2601 23000 Attempt to insert duplicate key row in object '%s' with unique index '%s' Duplicate values have been inserted into a column that has a UNIQUE constraint. It is the responsibility of the application to deal with prevention of insertion of duplicate values.

Related posts:

  1. Applying Oracle Patches in PeopleSoft
  2. How to Find PeopleSoft Processes/PSJobs in a Recurrence
  3. PeopleSoft 8.53: Creating PeopleSoft HCM9.2 Database
  4. ORA-01555: snapshot too old
Related Posts

What are Core Dump Files?

PeopleSoft PSKeyManager Commands

How to track user activity in PeopleSoft

PeopleTools 8.60 Platform Certifications

PUM Error: ImportError: libncursesw.so.5: cannot open shared object file: No such file or directory

External System Contact Error (158,10721). IOException: The host couldn’t be resolved

Common Errors with PT-INFRA DPKs

Load Balancing Among PeopleSoft Process Scheduler Domains
Apurva Tripathi
 

←Previous post Next post→
>