| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: how to define a pseudo auto value column in oracle?
"Norman Dunbar" <Norman_at_Dunbar-it.co.uk.REMOVETHIS> wrote in message
news:cuai0p$7k2$1$8300dec7_at_news.demon.co.uk...
> Robert Wehofer wrote:
>> Hello there!
>>
>> One of the most hated features in Oracle are the intricated use of
>> sequences
>> to create valid IDs for a prim key column. In Access you've got the data
>> type Autovalue, which is solid and simple. In Oracle you need to know the
>> name of the sequence and have to call nextval, if you want to insert a
>> dataset. Is there a way to automate the setting of the ID using
>> sequences?
>
> No. (or at least, not that I know of !)
>
>> One way is to use insert triggers, but is there a possibility to define a
>> sequence as default value for a prim key value?
>
> No.
>
> > If yes, how would be the
>> syntax of the 'create table' statement? And is ADO able to return the
>> data
>> type Autovalue, if a column in Oracle has got a sequence as default
>> value?
>
> Pass. I don't use ADO.
>
>
> Not much help I'm afraid. Anyway, you don't 'have' to know the sequence
> name really.
>
> CREATE SEQUENCE XYZ;
>
> CREATE OR REPLACE FUNCTION AUTOVALUE
> RETURN NUMBER
> AS
> Result NUMBER := -7;
> BEGIN
> SELECT XYZ.NEXTVAL INTO Result;
> RETURN Result;
> END;
> /
>
> Now, you can't use this as the default for a column in a table, but you
> don't now have to remember the name of the 'XYZ' sequence either - granted
> you do have to remember the name of the function though :o)
>
> Of course, this gives 4 logical I/Os in Oracle 8i, 3 with Oracle 9i and
> none with Oracle 10g as you are accessing the DUAL table.
>
>
>
> Cheers,
> Norm.
>
>
>>
>> Regards,
>> Robert
>>
create table autonumber ( id number default autovalue(), text varchar2(30) )
*
ERROR at line 1:
:-(
++ mcs Received on Tue Feb 08 2005 - 10:01:39 CST
 |
 |