Re: SQL Server Autonumber equivalent in Oracle 8i

In article <90un8f$e1k$1_at_nnrp1.deja.com>,   rwhita_at_my-deja.com wrote:
> Hello I'm looking for the same anwser. I've tried
> the following but I get this error when compiling
> it. Warning: Trigger created with compilation
> errors.
>
> Here is the script I tried
>
> CREATE SEQUENCE PARTID
> INCREMENT BY 1
> START WITH 1
> NOCACHE;
>
> CREATE TABLE ITEMS (
> DATECREATED DATE DEFAULT SYSDATE,
> PARTNUMBER NUMBER,
> DESCRIPTION VARCHAR2(40),
> PRICE NUMBER(4,2)
> );
>
> CREATE OR REPLACE TRIGGER AUTOINCREMENT
> BEFORE INSERT ON ITEMS
> FOR EACH ROW
> BEGIN
> :new.PARTNUMBER := :PARTID.nextval;
> END;
> /
>
> I've been all the way through the Oracle 8
> Complete Reference guide looking for the anwser
> but nothing. They show how to use a sequence in a
> insert statement but thats all. Any help would be
> great.
>

My mistake. This statement is of course wrong:

         :new.PARTNUMBER := :PARTID.nextval;

As with any value taken from the database ( a sequence is taken from the database ) a select is involved.

    select partid.nextval

       into :new.partnumber
       from dual;

    will work. (Someone else has posted the same result )     Dual is a useful table, especially in this case.

As to if the information is in the book, no it is not. The reference manual is just that a reference with a few examples. It does not contain much in the way examples or really good examples of doing anything. The assumption is that you will be able to figure out the answer. In this particular case the reference book showed you the basic syntax for creating a sequence, creating a trigger, the availability of a PL/SQL block within the trigger, and the concept of assigning a value to a column in a table eg :new.<column>. Your job is to add the select and ensure that you typed the SQL correctly.

What you are looking for is a book like the 'Perl cookbook' with a lot of examples. I am currently unaware of any such book.

--
Michael Krolewski
Rosetta Inpharmatics
mkrolewski_at_rosetta.org
              Ususual disclaimers


Sent via Deja.com http://www.deja.com/
Before you buy.