Tip/Trick to handle Oracle 9i error “ORA-01401: inserted value too large for column or ORA-01438: value larger than specified precision allowed for this column”

Hi All,

We know that Oracle is no longer supporting Oracle 9i version. By this time all users have migrated to the latest versions. But still I want to give this tip/trick for those who are still using Oracle 9i as database.

In this article I am gonna explain how to handle the Oracle error
“ORA-01401: inserted value too large for column or ORA-01438: value larger than specified precision allowed for this column”

1. When this Error will occur?
   
   
   
   • From error description itself it is evident that this is because application is trying to insert a column value which is larger than the column size defined in the table.
     
   
   
2. What is not provided in the Oracle 9i version?
   
   
   
   • It is good that we got the error code and error description, but we dont have the information about which is the column causing this problem. More over it is easy to find out the column in case if the table is very small. What if the we have 100 columns in the table.
     

     It is very difficult to figure out which column is the culprit.
     

   
   
3. What is provided in the higher versions of 9i?
   
   
   
   • In the higher versions like Oracle 10g and 11g when this error occurs in the error description they clearly provide the column name actual size of the column and length of the value we are trying to insert into the table.
     

     TIP: Now how to handle such situation in Oracle 9i.
     

   
   

1. In Oracle we have a table called user_tab_cols which provides COLUMN_ID,DATA_TYPE,COLUMN_NAME,DATA_LENGTH ....
   
2. using the above table we can find out the exact column with some logic which I am going to explain.
   
3. Below is the function in which I have written a logic to find out the column name
   

public void ErrorMessage(OracleTransaction tx,OracleException ex,string sqlFmt,
    params object[] args)
		{
string Error=string.Empty;
			
if (ex.Code == Number || ex.Code == Varchar2)
{
	int First = sqlFmt.IndexOf("(");
	int Last = sqlFmt.IndexOf(")");
					
string[] Insert = sqlFmt.Substring(First+1,Last-First-1).Split(',');	
Hashtable hsUserTableCols= new Hashtable();
for (int ColumnID=0;ColumnID<Insert.Length;ColumnID++)
{
	hsUserTableCols.Add(Insert[ColumnID].ToUpper(),args[ColumnID]);
}
string SQL_UserTabCols = 
    "select COLUMN_ID,DATA_TYPE,COLUMN_NAME,DATA_LENGTH from" +
    "user_tab_cols where table_name = ‘Employee’ ORDER BY COLUMN_ID ASC"; 
OracleDataAdapter daUserTabCols= new OracleDataAdapter();
daUserTabCols.SelectCommand = new OracleCommand(SQL_UserTabCols, dbcon,tx);//high data rate
				    
DataSet dsUserTabCols = new DataSet();
daUserTabCols.Fill(dsUserTabCols, "user_tab_cols");
DataTable dtUserTabCols = dsUserTabCols.Tables[0];	//					
foreach (DataRow drUserTabCols in dtUserTabCols.Rows)
	{				
						 
		if hsUserTableCols[drUserTabCols["COLUMN_NAME"]].ToString().Length >    
		    Convert.ToInt32(drUserTabCols["DATA_LENGTH"]))
{
						
Error= string.Format("{0},\"Employee\".\"{1}\".(actual: {2},maximum: {3})",
    ex.Message.Trim() ,drUserTabCols["COLUMN_NAME"].ToString(),
    hsUserTableCols[drUserTabCols["COLUMN_NAME"]].ToString().Length,
    drUserTabCols["DATA_Length"].ToString());					
							
}
}
					
}
			
}

1. What I am doing in the above function is I am passing the SQL query which is causing the problem.
   
2. I will fetch the required information from the user_tab_cols table by passing the Table name.
   
3. After fetching the rows I am comparing both.
   
4. Whichever column is not satisfying the condition that is the culprit column.
   

:)
Happy coding.