Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Generated by column syntax

Re: Generated by column syntax

From: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 04 Sep 2007 13:22:49 -0700
Message-ID: <1188937362.536024@bubbleator.drizzle.com>


Serge Rielau wrote:
> DA Morgan wrote:

>> Jerry Alan Braga wrote:
>>> I have seen on some posts that you can do the following
>>>
>>> alter table test add col_3 generated always by  (col_1 + col_2); -- 
>>> virtual col
>>>
>>> alter table test add col_3 generated by (col_1 + col_2); -- stored 
>>> virtual col
>>>
>>> what is the syntax of the generated statement, where are the docs on 
>>> this
>>>
>>
>> There are demos of Virtual column creation and usage in Morgan's Library
>> at www.psoug.org under VIRTUAL COLUMNS.

> Nice, Daniel I see in your example that providing a value for the
> expression generated column on insert appears to be tolerated. Will it
> return an error if the value isn't correct or is the value simply ignored?
> (DB2 only allows the DEFAULT keyword if a generated column is specified)
>
> Cheers
> Serge

Too bad you don't have Oracle. Some of what is in my library, keep in mind these are demos for my students, are intended to generate an error. If you ran it here is what you'd see:

SQL> CREATE TABLE vcol (

   2  salary         NUMBER(8,2),
   3  bonus          NUMBER(3),
   4  sal_plus_bonus NUMBER(10,2) GENERATED ALWAYS AS (salary+bonus) 
VIRTUAL); Table created.

SQL> INSERT into vcol

   2 (salary, bonus, sal_plus_bonus)
   3 VALUES
   4 (100, 10, 110);
  INSERT into vcol

              *
ERROR at line 1:
ORA-54013: INSERT operation disallowed on virtual columns

SQL>

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Tue Sep 04 2007 - 15:22:49 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US