0

How I can set default value in oracle? my query is like this : 

alter table talend_job modify mtj_id varchar(10) not null default 
('JASG'|| (nextval('mtj_id_seq')))
Ishak Gultom
  • 19
  • 6
  • Which version of Oracle are you using? Nonetheless, that's not how you use the sequence, it should be `mtj_id_seq.nextval` – Kaushik Nayak Oct 18 '18 at 04:18
  • I'm using oracle 11g @KaushikNayak – Ishak Gultom Oct 18 '18 at 04:28
  • 2
    Identity columns are not available in 11g. You have to either use Triggers or upgrade to Oracle 12c. Moreover, id's should ideally be numbers(generated from sequences or otherwise) and other characters should be stored in a separate column and appended by the application. – Kaushik Nayak Oct 18 '18 at 04:30

1 Answers1

0

In Oracle 11g, you cannot specify a sequence number as a DEFAULT value for a column; however, you can emulate this functionality using a trigger. Even if a column is declared NOT NULL, you can still omit the column from INSERT statements to be populated in the trigger

You can refer to How to create id with AUTO_INCREMENT on Oracle?

Jeffrey Kemp
  • 59,135
  • 14
  • 106
  • 158
Esteban Rigoni
  • 26
  • 3
  • You can use a sequence as a default since Oracl 12 - but not with the outdated version 11 Ishak is using. –  Oct 18 '18 at 06:40