Re: Case insensitive searches

From: Hans Forbrich <fuzzy.graybeard_at_gmail.com>
Date: Fri, 21 Mar 2014 15:22:47 -0600
Message-ID: <532CADA7.7020702_at_gmail.com>

On 21/03/2014 1:07 PM, Ric Van Dyke wrote:
> There is one (at least) major drawback of using virtual columns.
>
> Inserts must be done using column reference not position (see below).
Just another reason to design tables for 'storage' and design at least one set of views to map to business entities.

By overlaying a view on that table to eliminate the virtual column,

SQL> l

   1 CREATE TABLE vc

   2     ( col1 NUMBER
   3     , col2 NUMBER
   4     , col3 NUMBER GENERATED ALWAYS AS (col1 + col2) VIRTUAL
   5*     )

SQL> / Table created.

SQL> CREATE OR REPLACE VIEW v_vc as select col1, col2 from vc;

View created.

SQL> INSERT INTO v_vc VALUES (10, 20)

1 row created.

SQL> I personally believe that will eventually allow us to separate the needs to the developer (business entities) from the needs of the DBA (storage and DB objects).

/Hans

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Mar 21 2014 - 22:22:47 CET