Results 1 to 8 of 8
  1. #1
    Join Date
    May 2005
    Posts
    3

    Red face Unanswered: How to find the record that's causing an error?

    Hi, i'm sort of a newbie in sql and i'm trying to convert
    some records from one software to another.
    The first step was exporting the data from access to
    a table and then make the correpondance between fields
    and make an insert.

    During the insert oracle gives an error about ORA-1401
    field to large for column.

    My question is this:
    - When running the insert is there a way to see the line that
    causes the error? there are a lot of columns to be corrected
    and it was easier if there was a way to see in "real time"
    the inserts that oracle was doing or to identify the wrong one.


    Sorry if i am doing a dumb question

    Keep up the good work.
    This is a great forum.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    It is a little bit unclear HOW you perform insert; if you are inserting data from the Access export file into Oracle table using ... what? SQL*Loader?, then it is possible to see the record that caused an error in the .BAD file (check also .LOG and .DSC files).

    If you already have a "dummy" Oracle table (which was populated from the export file you got from Access) and now take data from it to populate another table(s), you could use a PL/SQL procedure with an exception handler to find out who is causing problems, such as:
    Code:
    begin
      for c1r in (select empno, ename, loc, sal from emp)
      loop
      
         insert into some_table (empno, ename, sal) 
           values
           (c1r.empno, c1r.ename, c1r.sal);
      
      end loop;
      
    exception
      when others then
        dbms_output.put_line('Error ' || sqlcode || ' on EMPNO ' || c1r.empno);
    end;
    /
    If none of this is your case, sorry ... but you may expect answers from other forum memebers.

  3. #3
    Join Date
    May 2005
    Posts
    3
    thanks a lot. i solved the problem.

  4. #4
    Join Date
    Dec 2005
    Location
    Guadalajara, México
    Posts
    1

    thanks

    is the same cuestion...


    thks

  5. #5
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    I tried that and it didn't compile at all. Also, to get the line where an error is causing has been something very tricky on Oracle, I remember seeing Tom did something about it using stacks...

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Oh well, this code didn't compile for two known reasons: the first one is the fact that the column "emp.loc" simply doesn't exist ... "loc" exists in the "dept" table. OK, never mind that; it was written in order to show HOW to do that, not to use it as it is.

    The second reason might be the fact that your database version doesn't support such a cursor FOR loops. The same script, rewritten in order to support older Oracle versions, looks like this:
    Code:
    DECLARE
      CURSOR c1 IS SELECT empno, ename, job, sal FROM EMP;
      c1r c1%ROWTYPE;
    BEGIN
      OPEN c1;
      LOOP
        FETCH c1 INTO c1r;
        EXIT WHEN c1%NOTFOUND;
    
        INSERT INTO SOME_TABLE (empno, ename, sal) 
          VALUES
          (c1r.empno, c1r.ename, c1r.sal);
      
      END LOOP;
      CLOSE c1;
    EXCEPTION
      WHEN OTHERS THEN
        dbms_output.put_line('Error ' || SQLCODE || ' on EMPNO ' || c1r.empno);
    END;
    /
    It would be a surprise if Tom didn't deal with such a problem in a more sophisticated way; I agree, it would be a good advice to visit Ask Tom.

  7. #7
    Join Date
    Jan 2004
    Posts
    492
    If you do not feel like writing code and prefer a more manual approach, and you do not have a ridiculously large amount of records, you can always throw it into excel and use conditional formatting. Have it highlight all the fields greater than the db column length. That way you can fix the file before loading it into Oracle.

    I would go with Littlefoot's option first, but if you arent familiar with pl/sql this is always an option.

    Good luck!
    Oracle OCPI (Certified Practicing Idiot)

  8. #8
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Quote Originally Posted by JMartinez
    Also, to get the line where an error is causing has been something very tricky on Oracle, I remember seeing Tom did something about it using stacks...
    I think the OP is looking for the record that caused a "value too large for column" or similar error (there doesn't appear to be an "ORA-1401" error so I'm guessing a bit), rather than a line of code in a PL/SQL program.

Posting Permissions

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