Selecting from the DUAL Table
DUAL is a table automatically created by Oracle along with the data dictionary.
DUAL is in the schema of the user SYS, but is accessible by the name DUAL to all
users. It has one column, DUMMY, defined to be VARCHAR2(1), and contains one row
with a value 'X'. Selecting from the DUAL table is useful for computing a constant
_expression_ with the SELECT statement. Because DUAL has only one row, the
constant is returned only once. Alternatively, you can select a constant,
pseudocolumn, or _expression_ from any table, but the value will be returned as
many times as there are rows in the table.
<.YEX.>
-----Original Message-----
From: daniel kessler [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 19, 2004 4:58 PM
To: CF-Talk
Subject: Re: oracle unique id or identity
Unfortunately I don't have the ref books and I am rooting through oracle.com. In doing so I found a sequence that I think can be cobbled into what I need, thought it has a reference to DUAL and I don't know what that is. Here it is:
create table hhp_calendar (
id NUMBER,
dateAdded date,
event_date date,
header VARCHAR2(200),
description VARCHAR2(3000),
http VARCHAR2(100),
image VARCHAR2(100),
image_h INT,
image_w INT
);
CREATE SEQUENCE uniqueNum_s START WITH 1
INCREMENT BY 1;
CREATE OR REPLACE TRIGGER hhp_calendar
BEFORE INSERT ON id FOR EACH ROW
DECLARE
v_id id%type;
BEGIN
SELECT uniqueNum_s.nextval INTO v_id FROM DUAL;
:new.id = v_id;
END;
> Oracle doesn't have any kind of unique-id or identity datatype for
> table columns. Instead, you need to use a "SEQUENCE" which is a
> standalone object inside an Oracle schema which generates unique
> numbers based on how you define the sequence when it's created. So, to
> use that in a table, you need to create a sequence, and then create
> your table with a column called something like "ID" with a NUMBER
> datatype, and when you insert new records into that table, the ID
> column value becomes something like <your_sequence_name.nextval>
> (without the <>s). For more information about it, lookup SEQUENCE in
> the Oracle SQL Reference.
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
