Re: Is this safe???

Fellas,

Sequences look like the modern, more efficient way to do it. Thanks for the article Jonathan.

However, I do have one problem regarding thier use. How can I utilize a sequence within a trigger or function? Whenever I try to do this, I get the message:

PLS-00357: Table,View Or Sequence reference 'TEST_SEQ.NEXTVAL' not allowed.

I'm trying automatically create a new sequence whenever a new row is created where
a specific column is empty.

Specifically,

   :new.id := CONCAT( prefix, LPAD( TO_CHAR( test_seq.nextval), 9, '0' ) )

What can I do?

Thanks,

Chris
Jonathan Lewis wrote:

> The answer is almost certainly yes.
>
> Check out the latest issue of Relate
> (if your bit of Nortel has a subscription
> to the UKOUG), or look at my web-site
> for the text of the article on sequences
> Features -> Sequences
>
> --
>
> Jonathan Lewis
> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>
> Chris Forlano wrote in message <37F25306.9015E6D_at_nortelnetworks.com>...
> >To clarify, what I'm trying to do is create unique id's "across" tables in
> different
> >schemas.
> >For example, I want to ensure that the value for the id column in
> user1.test does
> >not
> >share any ids in the id column of user2.test.
> >
> >Additionally, I wish to create unique ids based on a record type. For
> example, my
> >test
> >records will have their own unique ids while my package records will have
> their own
> >unique ids.
> >
> >Still a case for sequences? Sorry, sequences are new to me.
> >

--
Chris Forlano
Automation Development
Nortel Networks, Maidenhead
590 4342 (01628 434 342)
cforlano_at_nortelnetworks.com Received on Thu Sep 30 1999 - 10:26:14 CDT