oracle- 02289,ORA-02289: sequence does not exist

Don't know why that would be. But here is the test result:

SQL> create table mytable_without_trigger

2 ( id number

3 , name varchar2(30)

4 )

5 /

Tabel is aangemaakt.

SQL> create table mytable_with_trigger

2 ( id number

3 , name varchar2(30)

4 )

5 /

Tabel is aangemaakt.

SQL> create sequence myseq start with 1 increment by 1 nocache

2 /

Reeks is aangemaakt.

SQL> create package my_package

2 as

3 function get_new_id return number;

4 end;

5 /

Package is aangemaakt.

SQL> create package body my_package

2 as

3 function get_new_id return number

4 is

5 l_new_id number;

6 begin

7 select myseq.nextval into l_new_id from dual;

8 return l_new_id;

9 end;

10 end;

11 /

Package-body is aangemaakt.

SQL> create or replace trigger my_trigger

2 before insert on mytable_with_trigger

3 for each row

4 begin

5 :new.id := my_package.get_new_id;

6 end;

7 /

Trigger is aangemaakt.

SQL> insert into mytable_without_trigger (id,name) select myseq.nextval, 'abc' from (select level from dual connect by level <= 100)

2 /

100 rijen zijn aangemaakt.

SQL> insert into mytable_with_trigger (name) select 'abc' from (select level from dual connect by level <= 100)

2 /

100 rijen zijn aangemaakt.

SQL> set timing on

SQL> insert into mytable_without_trigger (id,name) select myseq.nextval, 'abc' from (select level from dual connect by level <= 10000)

2 /

10000 rijen zijn aangemaakt.

Verstreken: 00:00:05.09

SQL> insert into mytable_with_trigger (name) select 'abc' from (select level from dual connect by level <= 10000)

2 /

10000 rijen zijn aangemaakt.

Verstreken: 00:00:08.09

SQL> set timing off

SQL> begin

2 runstats_pkg.rs_start;

3 insert into mytable_without_trigger (id,name) select myseq.nextval, 'abc' from (select level from dual connect by level <= 10000);

4 runstats_pkg.rs_middle;

5 insert into mytable_with_trigger (name) select 'abc' from (select level from dual connect by level <= 10000);

6 runstats_pkg.rs_stop(1000);

7 end;

8 /

Run1 draaide in 514 hsecs

Run2 draaide in 848 hsecs

Run1 draaide in 60,61% van de tijd

Naam Run1 Run2 Verschil

STAT.session cursor cache hits 10,002 8,157 -1,845

LATCH.enqueues 38 1,897 1,859

LATCH.session idle bit 62 1,979 1,917

LATCH.library cache pin allocation 20,354 24,253 3,899

STAT.redo entries 20,151 30,094 9,943

STAT.buffer is not pinned count 51 10,051 10,000

STAT.table scan blocks gotten 1 10,001 10,000

STAT.table scans (short tables) 1 10,001 10,000

STAT.table scan rows gotten 1 10,001 10,000

STAT.no work - consistent read gets 33 10,033 10,000

STAT.recursive calls 140,097 150,097 10,000

STAT.execute count 10,008 20,008 10,000

STAT.db block gets 30,514 40,630 10,116

LATCH.redo allocation 22,401 33,442 11,041

LATCH.shared pool 51,928 67,626 15,698

STAT.db block changes 40,263 60,235 19,972

STAT.consistent gets 20,100 50,084 29,984

STAT.calls to get snapshot scn: kcmgss 30,014 60,014 30,000

LATCH.library cache pin 97,379 128,794 31,415

STAT.session logical reads 50,614 90,714 40,100

LATCH.library cache 111,694 152,456 40,762

LATCH.cache buffers chains 199,536 341,151 141,615

LATCH.session allocation 2,168 286,896 284,728

STAT.redo size 7,397,124 9,772,492 2,375,368

Run1 latches totaal versus run2 -- verschil en percentage

Run1 Run2 Verschil Pct

702,934 1,239,549 536,615 56.71%

PL/SQL-procedure is geslaagd.Regards,

Rob.