Re: Auto generating Sequences !!

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Mon, 10 Apr 2006 19:35:14 +0200
Message-ID: <1g5l32lrka7jtga95ut31kh5dh0dmirj43@4ax.com>

On 10 Apr 2006 10:21:44 -0700, "chani" <chanakam2000_at_gmail.com> wrote:

>Hi,
>
>Thank everyone who helped on my previous post.
>But I have to seek some more help from you.
>
>1. Can I create a sequence inside a trigger?
> =========================================
> I have two tables
> loan(loan_number,....)
> loan_payment(loan_number,payment_number,...) /*This is a weak
>set depend on loan*/
>
> I want to create sequences for each entity entered to loantable
>
> My requirement is to have
>
> payment_001,payment_002,... for loan_0001
> payment_001,payment_002,... for loan_0002
> payment_001,payment_002,... for loan_0003 .. & so on
>
>(I want to generate payment_number belong to a loan from sequence)
>
> for this I should have seperate sequence for each loan entry
> such as
>
> seq_loan001,
> seq_loan002,....
>
>I tried following sql trigger, But It says error
>
> CREATE OR REPLACE TRIGGER LOAN_PAY_TR
> BEFORE INSERT
> ON loan
> FOR EACH ROW
> BEGIN
> IF inserting THEN
> CREATE SEQUENCE LP *** please see bottom ***
> start with 1
> increment by 1
> maxvalue 9999
> minvalue 1
> nocache
> nocycle
> noorder
> END IF;
> END;
>
>**** Here I want to generate sequence name dynamically
>(in the format of seq_loan001,...) But have no idea how to do it,
> can i use char functions here (need help for it also) */
>
>
>I tried to find answer in google (Searched for 'auto sequences',
>'Sequences in trigger' & some more clauses)
>
>I explained my problem in large text. Because I want you to understand
>my question.
>But I don't know how to explain some of them in english properly. I
>hope you will understand.
>
>Can you Please help me. (If this is not possible let me know it!)

The answer is

1 the idea is just horrible. It definitely won't scale.
2 the idea is just horrible. It definitely won't scale.
3 The idea is just horrible. If there was a prize for the most

horrible idea in this forum each year, you have now won the prize. 4 Did I say the idea is horrible
5 The answer is
a) you can't directly perform DDL in a stored procedure. You'll need execute immediate to do that. You can look up execute immediate in your documentation. But then again performing DDL on the fly in a stored proc is just horrible.
b) Every DDL causes a commit. You are not allowed to commit in a trigger. You would need to call a procedure with the autonomous transaction pragma affixed. You can look up this pragma in the docs. c) The pseudo code above shows you are creating a sequence for every record. Consequently the sequence will have only one value. Why are you doing this. A string literal would suffice!

--
Sybrand Bakker, Senior Oracle DBA

Received on Mon Apr 10 2006 - 12:35:14 CDT