Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Location
    UK
    Posts
    52

    Question Unanswered: ORA-01401: inserted value too large for column ORA-06512

    Hi All,

    I am facing a strange problem.

    I have a procedure that reads data from one table thru a cursor and inserts or updates in another.

    Both the tables have same data structure.

    But I am receiving an error ORA-01401: inserted value too large for column ORA-06512.

    Any idea.

    Thanks,
    Gautam

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    How about the variables in the cursor ... are they sized like the tables ??

    HTH
    Gregg

  3. #3
    Join Date
    Jan 2004
    Location
    UK
    Posts
    52
    Quote Originally Posted by gbrabham
    How about the variables in the cursor ... are they sized like the tables ??

    HTH
    Gregg

    Yes, they are.

    CURSOR c1(p_su_id t_su.su_id%TYPE) IS
    SELECT tsu.su_id
    ,tsu.name_address_1 Address_1
    ,tsu.name_address_2 Address_2
    ,tsu.name_address_3 Address_3
    ,tsu.name_address_4 Address_4
    FROM t_su tsu;

    .. and then doing an insert within a loop.. end loop

    INSERT INTO t_cp(su_id
    ,Address_1
    ,Address_2
    ,Address_3
    ,Address_4)
    VALUES(c1.service_user_id
    ,c1.Address_1
    ,c1.Address_2
    ,c1.Address_3
    ,c1.Address_4);

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Two points
    1) Are the columns in t_cp the same size t_su
    2) Why use a cursor loop? just do
    insert into t_cp(<column list>) select <column_list> from t_su;
    you'll find this is much faster and much more efficient.

    Alan

  5. #5
    Join Date
    Jan 2004
    Location
    UK
    Posts
    52
    Quote Originally Posted by AlanP
    Two points
    1) Are the columns in t_cp the same size t_su
    2) Why use a cursor loop? just do
    insert into t_cp(<column list>) select <column_list> from t_su;
    you'll find this is much faster and much more efficient.

    Alan

    1) Yes the data structure is the same

    2) Actully I have removed some business logic that has been handled by some IF conditions befor doing the inserts or updates, from the procedure.. so its not a straight forward insert.

    Anyways, thanks for the suggestions.

  6. #6
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Are you sure the error is coming from the insert and not some other statement? Also is their an insert trigger on t_cp which may be modifying values?

    By the way if your business logic isnt too complicated then have a look at the merge command. I only use cursor loops as a last resort.

    Alan

Posting Permissions

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