Re: how to define a pseudo auto value column in oracle?

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;

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
>
>
Received on Tue Feb 08 2005 - 07:27:50 CST