Re: Dynamic Generate sequences ~~

Hi Agi

How about not handle in trigger, but handle inside you application.

1. Create a store procedure inside your database create or replace procedure CreateSeq (vSeq in number) as begin execute immediate 'create sequence MySeq_'||to_char(vSeq)|| ' start with 1 increment by 1 minvalue 1 maxvalue 1000 nocache nocycle'; EXCEPTION when OTHERS then dbms_output.put_line('error when create Sequence !'); end; /
2. Call the store procedure in the application, every time you insert the new record. CreateSeq(xxxxx);
3. Don't forget to grant create_sequence to the USER

I'm sory just that my suggestion for the moment

Good Luck
Handy

> Handy,
> Thanks for your reply~~
> My problem is that once the whenever a new record added,
> a new sequence will be generated automatically.
> For example, mytable( kind number(4), name varchar2(32))
> kind name
> ---- --------
> 1 IT
> 2 HR
> 3 MFG
> ......
> Of course, that kind is auto assigned by trigger and sequence.
> my problem is that after inserting (1,IT), a "new" sequence
> seq_kind_1 will be generated automatically for later usage.
> ps: same as (2, HR) seq_kind_2 will be generated,too
>
> Agi

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Aug 28 2000 - 04:44:57 CDT