CODEWITHSUNDEEP
oracle-databaseoracle11g
MGPJ
MGPJ

Reputation: 1072

Oracle sequence is not generating

I have a table Person contains fields are personName,personId and without specifying the primary I added some values at the table. The values in the table Person are

 ('muni',1)   
 ('Ganesh',1)

I need to add primary key as personId and modify the column as autogenerated. So, I tried to generate the sequence by the following query for generating trigger as

declare           
id  number;     
begin  
select max(rownum)+1 into id from Person;  
execute immediate 'create sequence personseq start with '||to_char(id);  
end;

If i execute the above query when the values are in the table, then it execute correctly, But when I have the empty table that means there is no entry in the table, then the above query is not generating the sequence.

I tried by if statement also. 

declare  
id  number;  
begin  
select max(rownum)+1 into id from Person;  
if (id=null) then  
execute immediate 'create sequence personseq start with '||to_char(1);  
else  
execute immediate 'create sequence personseq start with '||to_char(id);  
end if;  
end;

The system says the error no is ORA01722, which denotes the number I give Invalid number. But I don't know where is the mistake?
Any help highly appreciated.

Upvotes: 0

Views: 1120

Answers (3)

rs.
rs.

Reputation: 27427

Try this

select nvl(max(rownum),0)+1 into id from Person;

When table is empty max(rownum) will return null and when you add null to a value will always return null, so you need to convert any nulls to 0 so that 0 + 1 will return 1 and not null

Upvotes: 1

Kirill Leontev
Kirill Leontev

Reputation: 10941

upd: just noticed more important flaw than count/rownum.

id=null. Never do that, this doesn't work.

You check whether a value is null this way: id is null.

NULLS

Use count(*).

12:03:55 SYSTEM@saz-dev> create table person as
12:04:05   2  select 'muni' name, 1 attribute from dual union all
12:04:32   3  select 'ganesh' name, 1 attribute from dual;

Table created.

Elapsed: 00:00:00.07
12:04:47 SYSTEM@saz-dev> ed
Wrote file S:\tools\buffer.sql

  1  declare
  2  id  number;
  3  begin
  4  select count(*)+1 into id from person;
  5  execute immediate 'create sequence personseq start with '||to_char(id)||' increment by 1';
  6* end;
12:05:52   7  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.06
12:05:53 SYSTEM@saz-dev> select object_name from user_objects where object_name = 'PERSONSEQ';

OBJECT_NAME
---------------------------------------
PERSONSEQ

Elapsed: 00:00:00.08
12:06:16 SYSTEM@saz-dev> truncate table person;

Table truncated.

Elapsed: 00:00:00.32
12:06:27 SYSTEM@saz-dev> drop sequence personseq;

Sequence dropped.

Elapsed: 00:00:00.20
12:06:33 SYSTEM@saz-dev>  declare
12:06:38   2   id  number;
12:06:38   3   begin
12:06:38   4   select count(*)+1 into id from person;
12:06:38   5   execute immediate 'create sequence personseq start with '||to_char(id)||' increment by 1';
12:06:38   6   end;
12:06:39   7  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03
12:06:40 SYSTEM@saz-dev> select personseq.nextval from dual;

   NEXTVAL
----------
         1

Elapsed: 00:00:00.04

Upvotes: 1

Santosha Epili
Santosha Epili

Reputation: 315

create a sequence once like this

CREATE SEQUENCE person_seq
 START WITH     1000
 INCREMENT BY   1
 NOCACHE
 NOCYCLE;

than use - 'person_seq.nextval' for each insertion of new record
e.g.

insert into person (name,id) values('abc',person_seq.nextval)

Upvotes: 0

Related Questions