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

From: <fitzjarrell_at_cox.net>
Date: 20 Jan 2006 18:48:30 -0800
Message-ID: <1137811710.526543.140260@o13g2000cwo.googlegroups.com>

Comments embedded.
srhari_at_gmail.com wrote:
> I'm trying to insert a record into a table, but getting the error -
> 'ORA-01400: cannot insert NULL into (....'. The table structure is:
>
> recnum number primary key,
> ap# varchar2,
> program varchar2
>
> My insert statement looks like this: INSERT INTO history ( ap#, program
> ) valus ) ('aaa', 'aaa').
>

Unless you have a before-insert trigger and a sequence to populate recnum you'll always receive an error with that insert statement.

> 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.

You're going to need a sequence, whether you want one or not:

create sequence history_seq start with 1 increment by 1 nomaxvalue nocycle nocache;

And a trigger:

create or replace trigger pop_hist_recnum before insert on history
for each row
begin

               select history_seq.nextval
               into :new.recnum
               from dual;

end;
/

Then you get your auto-generated recnum values and can ignore that column in all insert statements.

> I'm framing this query dynamically from c# and it's not a stored
> procedure. Is there any way to achieve it?
>
> Thanks,
> Hari.

David Fitzjarrell Received on Fri Jan 20 2006 - 20:48:30 CST