Forum Stats

  • 3,873,110 Users
  • 2,266,505 Discussions
  • 7,911,425 Comments

Discussions

ORA-01400 cannot insert null into (Schema.Table.column)

User_PP8RH
User_PP8RH Member Posts: 2 Green Ribbon

I'm trying to insert data in a table but I have got the error ORA-01400. If I open the package in pl/sql, right click the procedure and choose the option "Test", the registers are inserted perfectly, but if I try to run the package/procedure with an integration user (which has "Execute" grant) it runs but the insert fails and i get the ORA-01400.

Note 1: I'm not trying to insert null value in the column (varchar2(30) not nullable).

Note 2: All the other columns can be null.

I have been stuck in this problem for weeks (Help!!!!!), does anyone have any idea what could be happening?

Tagged:

Answers

  • Saubhik
    Saubhik Member Posts: 5,915 Gold Crown

    It is impossible to comment without viewing your code and full test case where it fails and where not. My best guess is there is something wrong with the code or calling block from integration user.

  • BluShadow
    BluShadow Member, Moderator Posts: 42,549 Red Diamond

    Is the "integration" user actually inserting to the same table as in your test? Perhaps they have a more 'local' copy of the table for their user, and something is different on that table.

    Or, as Saubhik says, the code that user is using is somehow wrong? Perhaps they have a local copy of the procedure/package that is different to the one you initially tested?

    Consider doing a select on all_objects with the names of the table and package to ensure that there is only the copies of those objects that you expect and not other copies lying around.

  • Sven W.
    Sven W. Member Posts: 10,559 Gold Crown

    Note 1: That is what you THINK you are doing. Oracle disagrees and claims you actually try to insert NULL.

    If you show us exactly what you try to do, we might be able to pinpoint where the problem originates from. However try to simplify the test case as much as possible. When doing so you even might discover what causes the issue.

  • User_PP8RH
    User_PP8RH Member Posts: 2 Green Ribbon
    edited Sep 23, 2022 2:50PM

    Hi all,

    I finally found the root cause.

    There was a code in a trigger, which was only executed for the "integration user", that changed the value to null before inserting into the table:

    :new.SEQ_ID := :old.SEQ_ID

    The trigger is only enabled during package execution, and because of that I had not analyzed it.

    Thanks everyone for the comments and tips!!

    L. FernigriniSven W.