Re: auto increment? 'enum' data type?

From: Otis Gospodnetic <otis_at_my-deja.com>
Date: Thu, 27 Jan 2000 16:26:18 GMT
Message-ID: <86prj0$dos$1@nnrp1.deja.com>

In article <86nu0t$8di$2_at_rd1936.quicknet.nl>, remco-blaakmeer_at_quicknet.nl wrote:
> In article <86nr2e$jd$1_at_nnrp1.deja.com>,
> Otis Gospodnetic <otis_at_my-deja.com> writes:
> > In article <86m9ot$q99$1_at_nnrp1.deja.com>,
> > M. Armaghan Saqib <armaghan_at_yahoo.com> wrote:
> >> I just had a detailed answer to a similar question.
> >>
> >> a) Oracle does not support access/sqlserver like "Autonumber"
columns
> >> but supports a sequence object which you can "bind" to a table
column
> >> (number type) using a pre-insert trigger.
> >
> > I think I understand how this would be done...
> > 1) create sequence
> > 2) create trigger....
> > Now I'm not sure about the syntax for 2)
> > I'd like something like this:
> > CREATE TRIGGER trigger_foo
> > BEFORE INSERT
> > ON mytablename
> > CURRVAL + 1
> >
> > So that when I make 1st insert the PK of 'mytablename' gets value 1,
> > and when the second row is inserted the PK gets value of 2, and so
on.
>
> See this Sql*Plus transcript for an explanation:

Thanks!
This is VERY helpful!
Another basic question - how do I tell Oracle that I want to make tables, triggers, sequences, etc. only in certain database's space? At one point I did:
"CREATE DATABASE MYDBNAME"
while logged in as system/manager, and then later I issued: "CREATE USER MYUSER IDENTIFIED BY MYPASS" But I'm not sure how to 'tie' the two (user + database) together, so that when I login as MYUSER/MYPASS using SQLPlus I get put into MYDBNAME space, so that anything I create, alter, or drop is restricted to MYDBNAME?

Like this?
>> sqlplus myuser/mypass_at_mydbname.mydomain.com Like that?

After a login like above I get this error: Error accessing PRODUCT_USER_PROFILE
Warning: Product user profile information not loaded! You may need to run PUPBLD.SQL as SYSTEM

If I login like the following, then i don't get the error: >> sqlplus

Enter user-name: myuser
Enter password:

Connected to:
...
...

Any ideas?
I did login using svrmgrl as system/manager and I run that PUPBLD.SQL, but I still get the error...

Still waiting for my Oracle DBA book... :(

Many thanks,

Otis

> SQL> create table test_enum
> 2 (id number(5)
> 3 ,text varchar2(10)
> 4 ,constraint test_enum_pk primary key(id)
> 5 )
> 6 /

> Table created.

> SQL> insert into test_enum(text)
> 2 values('TEST')
> 3 /
> insert into test_enum(text)
> *
> ERROR at line 1:
> ORA-01400: cannot insert NULL into ("REMCO"."TEST_ENUM"."ID")

> SQL> create sequence test_enum_seq;

> Sequence created.

> SQL> create trigger test_enum_trigger
> 2 before insert
> 3 on test_enum
> 4 for each row
> 5 begin
> 6 select test_enum_seq.nextval
> 7 into :new.id
> 8 from dual;
> 9 end;
> 10 /

> Trigger created.

> SQL> insert into test_enum(text)
> 2 values('TEST')
> 3 /

> 1 row created.

> SQL> select *
> 2 from test_enum
> 3 /

>


>         ID TEXT


> ---------- ----------


>          1 TEST


>

> Look at your Oracle SQL Reference for a detailed explanation of the
> commands. You can view the SQL Reference (and other documentation) for
> various versions of de database server online at
> http://technet.oracle.com/

> > I'm surprised there is no simpler, one-step thing/keyword to use for
> > this, since it is such a common requirement.
>
> I think it has something to with multi-user issues. If you fetch a
value
> from a sequence, you are guaranteed to be the only one that gets this
> value from this particular sequence. If you would want to create a
> mechanism that calculates max(id)+1 in a multi-user environment, you
> could easily get duplicate values and it would be very difficult to
> prevent them.

>


> > Got it.

> > Synax question.
> > What does specifying 'CONSTRAINT constraint_name CHECK ...' buy me
over
> > just saying 'CHECK ...'
> >
> > e.g.
> > CONTSTRAINT check_gender CHECK (loc IN ('Male','Female'))
> > vs.
> > CHECK (loc IN ('Male','Female'))
> >
> > What does the first way of writing get me?

> You get a constraint with a meaningful name. This is very useful when
> you get an error about a constraint being violated. For example, the
> phrase "unique constraint (REMCO.TEST_ENUM_PK) violated" is much
easier
> to understand at first glance than "unique constraint (SYS_C00266)
> violated". The former immediately tells me that it involves creating a
> duplicate value in the primary key on test_enum, the latter tells me
> nothing except that I made some mistake and I still have to find out
> what went wrong.

> Just like it is wise to give tables a meaningful name, it is wise to
> give indexes, sequences, constraints, etc. meaningful names.

> Remco
> --
> rd1936: 11:35pm up 18 days, 4:15, 10 users, load average: 2.69,
2.71, 2.39
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Jan 27 2000 - 10:26:18 CST