oracle tutorial webinars

ORA-12899

Often times, as our companies grow and evolve in response to an expansion in the form of client-base, staff, profit or markets, the data associated with this growth will also change. Data systems like Oracle have the innate ability to remain fairly flexible in regards to working with this variance in information. Still, even the most versatile database systems require maintenance and refining in the face of increased data traffic. This work is essential to accommodating any constraints on memory or necessary redefinitions of parameters. The ORA-12899 error is representative of an instance in which either an uptick in data or a user error is forcing Oracle to stall during its requested action.

The Problem

The ORA-12899 is an Oracle error that occurs when the value entered into the column string is too large. What this means is that an attempt was made by the user to update or insert a column with a value that is too wide for the destination column. The name of a particular column will be given and the actual width of the value, as well as the maximum width permitted for the column, will be associated with that. As mentioned, the value can be given in the form of characters. In the event that the width is reported in characters, this will mean that character length semantics are operating for the column. Otherwise the width will be reported in bytes.
Essentially, this error results from trying to push through a value, or set of values, that exceed the specified maximum width of the column. So, how does a user correct this type of error?

The Solution

To begin, open up the OERR utility. The user will require the full ORA-12899 message to receive the proper feedback on the error. This will provide more information on the error and allow further investigation. Typically, the error can come from one of three sources. The first source is the SQL statements that have been generated. Checking source and destination column data types to discover whether they are compatible with current formats is the second source. Lastly, the user can look at the destination column width – where the value is being assigned – to make sure it is large enough to accommodate the maximum value that the user anticipates assigning.
Let us now turn to an example that corrects ORA-12899. Suppose the user has created the following table:

CREATE TABLE Clients(
ClientID varchar2(9) PRIMARY KEY,
Client_Contact varchar2(40) NOT NULL,
Address varchar(20) NOT NULL,
Zip varchar2(5) NOT NULL,
Client_Number varchar2(11) NOT NULL)

The user then attempts to issue an INSERT VALUES statement that looks something like this:

INSERT INTO Clients VALUES(
727546345,
‘Roger Holcomb’,
‘—Benjamin Road Syracuse’,
‘-----‘, 05307623754)

The user might try to run the statement from here, but will receive the following error message:

Error starting at line 7 in command: INSERT INTO Clients VALUES(727546345, ‘Ricky Galorey’, ‘18 Benjamin Road Syracuse’, ‘13208’, 05307623754) Error report: SQL Error: ORA-12899: value too large for column “ORGANIZATIONS”. “MARKET”. “ADDRESS”(actual: 25, maximum: 20) 12899. 00000 – “value too large for column %s (actual: %s, maximum: %s)”

This error statement indicates that the variable ‘Address’ cannot hold more than twenty characters as that would exceed the width of the column parameter. When we look back at the address value (’18 Benjamin Road Syracuse’) we can see that the total number of characters (25) exceeds the maximum number allowed for the width of the column. To correct this, the user can change the VARCHAR2 for address to an amount that can accommodate the typical address length that their company would input.

Looking forward

The positive aspect of the ORA-12899 error is that once the user knows how to diagnose the problem, developing a solution becomes a very routine process. If you find that you still have difficulties integrating solutions for an ORA-12899, or perhaps you want to learn more about developing tables for your database, contact a licensed Oracle consultant for more information.