SQL> create table emp (
2 id number(6) );
Table created.
SQL>
SQL> alter table emp
2 add constraint emp_pk
3 primary key (id);
Table altered.
SQL>
SQL>
SQL> create or replace procedure gen_emp is
2 v_new_cid emp.id%type;
3 begin
4 loop
5 begin
6 v_new_cid := round(dbms_random.value(1000000,9999999));
7 insert into emp values (v_new_cid);
8 exit;
9 exception when dup_val_on_index then
10 null;
11 end;
12 end loop;
13 end;
14 /
Procedure created.
SQL>
SQL> set timing on
SQL> begin
2 gen_emp;
3 commit;
4 end;
5 /
begin
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06512: at "RNTSOFT.GEN_EMP", line 6
ORA-06512: at line 2
Elapsed: 00:00:00.07
SQL>
SQL> begin
2 for i in 1 .. 100000 loop
3 gen_emp;
4 end loop;
5 commit;
6 end;
7 /
begin
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06512: at "RNTSOFT.GEN_EMP", line 6
ORA-06512: at line 3
Elapsed: 00:00:00.06
SQL>
SQL> set timing off
SQL>
SQL> drop table emp;
Table dropped.