Numeric or value error using `dbms_random.string`-CodePudding

I created a table called LISTKOS

create table LISTKOS (
  kostid    number,
  name      varchar2(50) not null,
  location  varchar2(50),
  constraint pk_listkos primary key (kostid)
);

Then I went on to create the trigger to auto-populate the primary key (kostid)

create or replace trigger LISTKOS
   before insert or update on LISTKOS
   for each row
begin
   if inserting and :new.kostid is null then 
      :new.kostid := dbms_random.string('x', 7);
   end if;
end;
/

I selected 'x' because I wanted it to be in alphanumeric form (and because the type of values for kostid as I described when creating the table is number)

The error message keeps appearing

ORA-06502: pl/sql numeric error

CodePudding user response：

You declared the column as a number so you can't store alphanumeric data in it. You can only store numeric data. If you want to store alphanumeric data, you'd need to declare the column as varchar2. If you want to populate the primary key with a random number, you'd use the dbms_random.value function.

It almost certainly does not make sense to generate primary key values randomly however. That is relatively expensive to generate and (depending on how busy your system is and what key ranges you allow) subject to collisions. It would almost certainly make more sense to use a sequence. It also doesn't make sense to assign the primary key value in an on update trigger.

create sequence kostid_seq
  start with 1
  increment by 1;

create or replace trigger LISTKOS
   before insert on LISTKOS
   for each row
begin
   if :new.kostid is null then 
      :new.kostid := kostid_seq.nextval;
   end if;
end;

CodePudding user response：

Trigger without used sequence SQL

CREATE OR REPLACE TRIGGER LISTKOS BEFORE INSERT ON LISTKOS
FOR EACH ROW WHEN (new.kostid  is  null)
BEGIN SELECT nvl(max(kostid ),0) 1  INTO :NEW.kostid  FROM LISTKOS; END;