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

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!!!
mi_ckyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

stefan73Commented:
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?

Cheers!

Stefan
0
stefan73Commented:
When the insert fails with "Data too large for column", it's already too late.
0
mi_ckyAuthor Commented:
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 ............
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

paquicubaCommented:
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"
   "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">

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

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

   <head>
      <title>Error!</title>

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

   <body>
      <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
     
      <%
         else
      %>

            An exception

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

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

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

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

</html>

--------------- 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!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
helpneedCommented:
hi

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

regards
0
absongCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.