Oracle Data On Insert Error: Data too large for column (is there a way a trace which column failed)

Posted on 2005-04-03
Medium Priority
Last Modified: 2012-05-05
Hi guys, I have a jsp page with about about 60 data fields, which is inserted into an oracle table.  Sometimes the insert statement fails during insertion, getting error data too large for column.  Is there a way in oracle to automatically trace the colum that failed.
Please help with any infor, cheers!!!
Question by:mi_cky
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 12

Expert Comment

ID: 13691382
Hi mi_cky,
Not that I know of. Can't you simply limit the jsp field sizes so that their limits match with the appripriate column sizes?


LVL 12

Expert Comment

ID: 13691717
When the insert fails with "Data too large for column", it's already too late.

Author Comment

ID: 13692402
Yes  but its a wide developement project at the moment , and issues such as limiting the field sizes to column sizes will be done towards the end of project. for now its lots of development .................
So does this means its totally impossible to trace this ............
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 23

Accepted Solution

paquicuba earned 2000 total points
ID: 13692693
This is what I do:

I create a JSP page named ErrorPage.jsp like the one below.

------- Starts below this line ----------------------

<?xml version = "1.0"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"

<%@ page isErrorPage = "true" %>
<%@ page import = "java.util.*" %>
<%@ page import = "java.sql.*" %>

<html xmlns = "http://www.w3.org/1999/xhtml">


      <style type = "text/css">
         .bigRed {
            font-size: 2em;
            color: red;
            font-weight: bold;

      <p class = "bigRed">

      <% // scriptlet to determine exception type
         // and output beginning of error message
         if ( exception instanceof SQLException )

            An SQLException
              else if ( exception instanceof ClassNotFoundException )

            A ClassNotFoundException

            An exception

      <%-- end scriptlet to insert fixed template data --%>

         <%-- continue error message output --%>
         occurred while interacting with the <YOUR DATABASE NAME> database.

      <p class = "bigRed">
         The error message was:<br />
         <%= exception.getMessage() %>

      <p class = "bigRed">Please try again later</p>


--------------- ends above this line -------------------------------

Then, I include this directive in any JSP page that interacts with the database:

<%@ page errorPage = "ErrorPage.jsp" %>

The SQLException will tell me where the error occurred.

Hope this helps!

Expert Comment

ID: 13695007

u know which query is doin this so wht u can do is
using the dbms_system.set_sql_trace_in_session(sid,serial#,true) ..(pleases check the syntax)u can trace this and find out the trace file for this and also u can use the tkprof utility to format the trace file and then find out exactly where the problem occurs


Expert Comment

ID: 13698876
if you can figure out what the query looks like (through the log file), maybe you can simply run it through SQLPlus? remember to split the query into a few lines, and SQLPlus will tell you.

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question