| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Hello, a question comparing sql server to Oracle
On Mon, 01 May 2006 09:56:25 -0700, Mark wrote:
> Hi, I want to insert into a table and have a primary key being a number.
> It is not important that this number is incremental so is allowed to have
> "holes". I don't want to assign the number in my Java code.
>
> In Postgres and SQL Server I can set a column to be "autonumber" and the
> database automatically assigns it. Looking in Google it appears that this
> can't be done directly in Oracle. I have also checked and cannot find
> anything in the 10.2 manuals. I understand that I must "select
> sequencename.nextval into 'variablename' from dual" in a database trigger,
> then update :new.primarykeycolumnname with 'variablename'. If this is the
> only way of doing it then this is ok. But there is so much written in the
> newsgroup about pl/sql being slow so I don't want to code the trigger in
> pl/sql if there is a more efficient way of doing it.
>
You do not have to select from dual and then transfer the value. You could use the sequencename.nextval trick during the record insert.
Think of the nextval as a function that returns a numeric value. This can be used in place of any number. Thus the following example in Oracle's command line tool, SQLPlus:
SQL> connect test
Enter password:
Connected.
SQL> create table testit ( x number );
Table created.
SQL> create sequence seq1;
Sequence created.
SQL> insert into testit values (seq1.nextval);
1 row created.
SQL> / < means rerun previous command in SQLPlus
1 row created.
SQL> / 1 row created.
SQL> commit;
Commit complete.
SQL> select * from testit;
X
1
2
3
SQL>
HTH
--
Hans Forbrich
Canada-wide Oracle training and consulting
mailto: Fuzzy.GreyBeard_at_gmail.com
 |
 |