Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2004
    Posts
    3

    Question Unanswered: Column is too large

    Hi,
    Through Java program I am inserting into a Oracle DB table. I am getting exception "ORA-01401: inserted value too large for column".
    But this excpetion message in not giving column name for which the value is large.
    Is there any way to get column name for which value is large?
    That will be very helpful as there are lot of columns in that table.

    Thanks
    Shailesh

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    See this solution on Ask Tom

  3. #3
    Join Date
    Dec 2004
    Posts
    3

    Arrow

    Thanks for reply. But that answer I have already seen. That does not answer my question. In that answer he talks about some manipulation in SP but in my case i running a simple insert query.
    Please let me know if my question is not clear.

    Thanks
    Shailesh

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Your question is clear enough, and has a simple answer: no, Oracle will not tell you which column you inserted the too-large value into. What that Tom Kyte link shows is a way to build your application so that it is able to determine which column it is. You can either choose to adopt that approach (i.e use an SP instead of a simple insert) or live with the uncertainty you have. As he says, it would be a nice enhancement if Oracle did tell you the answer on a simple insert - but it does not right now.

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    you see, you didn't even post your insert code.
    SHAME ON YOU.

    Here is my thought:
    You are writing insert statements like this ...
    insert into table_1 values ('a','b','c','d');

    I would HIGHLY suggest you do not write insert statements like this!
    What if I add a column to table_1? your code is busted and you have to go
    in and edit everything. However, if you include the column-names:
    insert into table_1 (col1, col2, col3, col4) values ('a','b','c','d');

    now your code does not break when a column is added.
    also, now I know that if "col4" is numeric and I am attempting to load "d"
    into that column, then it is obvious where my error is.

    could this be the problem with your insert code?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Dec 2004
    Posts
    3

    Arrow

    Hi Duck,
    I am writing the query same as you have suggested.
    "Insert into table_1 (col1,col2,col3...) values (val1,val2,val3...).

    Problem occurs when length of value passed (e.g. length of val1 is 20) is greater that size of column (e.g. size of col1 is 15 only).
    In this case ORACLE throws exception sayinng that value of column is too large. It does not tell that excpetion occurred due to particular column say col1.
    So my question is that is there any way to capture column name also in this case?

    Please let me know if my question is not clear.

    Thanks
    Shailesh

  7. #7
    Join Date
    Oct 2004
    Posts
    145
    Duck's point is error commited by programmers who are not familiar with SQL or using lazy programming method.

    You have to go back to Andrew's point. Answer to your questions is NO.

    If you want to perform precheck or write an exception on verifying all data length. Another method might be to store your data into variables (defined as same length as the table definition) in your code be it java or others which should detect the error in length of the data.

  8. #8
    Join Date
    Jul 2003
    Posts
    2,296
    I'm with Jim here.

    WHY are you inserting a column that you KNOW AHEAD OF TIME is too
    large? YOU control the data being thrown into the database.

    Don't act like you didn't know how large the length was, you need to
    verify or restrict this ahead of time (as Jim suggested).
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  9. #9
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    Either it's a failing of Java or Java isn't being used correctly. When my missus puts too much salt on my dinner I don't blame the potatoes.

    Code:
    parameter : string[ select length from user_tables where col_name='x' ]
    parameter.value = 'hello'; -- fine
    parameter.value = 'hello world'; -- client language exception at line 'blah' of module 'blah' ... assigned value exceeds bounds.
    Java can't know what you're going to throw at Oracle. Oracle can't know what you're going to throw at it. The only person who knows is you. Create yourself a table/field/parameter class which does know. This might also make your Java a little less backend specific.

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •