0

I want to insert in a row in an Oracle table. I have limitation that I can not send mySeq.nextVal in insert script. If I omit that ID field, application tries to insert NULL there. There is a solution of using triggers on before insert. But I don't want to use triggers.

Is there any other way to insert value in Sequence field of Oracle table? Can I alter DDL and place mySeq.nextVal as default for the ID field? Or any other idea?

Tahir
  • 3,344
  • 14
  • 51
  • 69
  • 4
    Unfortunately you can't use a sequence as a default column value. You seem to have ruled out two perfect ways to achieve what you need. Best way is to directly insert seq.nextval, second best way is to do it with a trigger. Why can't you use either of those methods? See here for further info: http://stackoverflow.com/questions/10613846/create-table-with-sequence-nextval-in-oracle – Lord Peter Dec 17 '12 at 08:15
  • 1
    @LordPeter: this obnoxious restriction (that you cannot use nextval as the default value) will finally be removed with the upcoming 12g version. –  Dec 17 '12 at 08:57
  • From this, I only have solution to use triggers for it. – Tahir Dec 17 '12 at 09:03

1 Answers1

1

Unfortunately your inability to use nextval in an insert statement and your want to avoid triggers are at odds here. If you can't for some reason place a trigger on the table for this purpose you could create a view on the table and place an "instead of" trigger on the view to insert into the underlying table with the nextval in place. I wouldn't recommend it though.

David Aldridge
  • 51,479
  • 8
  • 68
  • 96