Re: Error: 'ORA-01400: cannot insert NULL into ('

Randy Harris wrote:
> <fitzjarrell_at_cox.net> wrote in message
> news:1137883868.329766.50110_at_f14g2000cwb.googlegroups.com...
> >
> >
> > The response by Mladen is, therefore,
> > correct, as is my response providing the same advice.
> >
> >
> > David Fitzjarrell
> >
>
> Except, the OP specifically stated that he didn't want to use a sequence.

The OP has conflicting 'requirements':

"I know that i have to supply value for primary key as well, but I want to have functionality something like IDENITITY in SQL Server where I never pass value to primary key and let the database to supply it's own. How to achieve this in Oracle?. I don't want to use SEQUENCE as well."

He does, indeed, want to provide the IDENTITY functionality to the RECNUM column and not use a sequence, however in Oracle this *requires* both a sequence and a trigger. He can either do as directed (create the sequence and trigger) or he can supply his values on insert, yet another task he does not want to perform. He could code a trigger and a procedure in a vain attempt to avoid using a sequence, however this would require using max(nvl(recnum, 0))+1 in the stored procedure which would generate more errors than successful inserts and I doubt, very seriously, the OP would care to perform repeated inserts in an effort to successfuly generate a new record.

The solution boils down to:

1. Use a trigger and a sequence.
2. Pass required values for all inserted columns.

Option 2 does not provide the desired IDENTITY functionality; Option 1 does, but uses a sequence which the OP also does not want. The OP therefore must make a choice of either autogenerating RECNUM values (involving a sequence) or passing them in through the INSERT statement.  And I expect the OP will choose the sequence/trigger method as it's the only one providing him the ability to not pass RECNUM values in his insert statement.

David FItzjarrell Received on Sun Jan 22 2006 - 13:18:26 CST