Oracle Database 12.1: Invisible columns

В Oracle Database 12c Release 1 появилась новая интересная возможность: невидимые столбцы (Invisible columns). Невидимость столбцов заключается в том, что их не видно в результате операторов доступа к таблице в случае отсутствия явного указания имен столбцов. Следующие операторы не возвращают скрытые столбцы в своем результате:

• SELECT * FROM <имя_таблицы>;
• команда DESCRIBE в утилите SQL*Plus;
• тип записи PL/SQL основанный на структуре таблицы с помощью атрибута %ROWTYPE;
• функции определения структуры курсора в OCI ( OCIDescribe).

Для определения невидимости столбца, при создании таблицы указывается модификатор INVISIBLE:

C:\>sqlplus test12c/oracle

SQL*Plus: Release 12.1.0.1.0 Production on Mon Jan 7 08:51:04 2013

Copyright (c) 1982, 2012, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> CREATE TABLE customers
  2  (
  3    id          NUMBER(9),
  4    Name        VARCHAR2(64),
  5    Description VARCHAR2(512) INVISIBLE
  6  );

Table created.

SQL> DESC customers
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------
 ID                                                 NUMBER(9)
 NAME                                               VARCHAR2(64)

Уже существующий видимый столбец можно сделать невидимым с помощью оператора ALTER TABLE:

SQL> ALTER TABLE customers MODIFY (Name INVISIBLE);

Table altered.

SQL>

Точно также невидимый столбец можно сделать видимым:

SQL> ALTER TABLE customers MODIFY (Description VISIBLE);

Table altered.

SQL>

Если необходимо вставить значение в невидимый столбец, то в операторе INSERT нужно явно указывать его имя:

SQL> INSERT INTO customers VALUES(1,'Scott','Description for Scott');
INSERT INTO customers VALUES(1,'Scott','Description for Scott')
            *
ERROR at line 1:
ORA-00913: too many values

SQL> INSERT INTO customers(Id,Name, Description)
  2    VALUES(1,'Scott','Description for Scott');

1 row created.

SQL> SELECT * FROM customers;

        ID
----------
DESCRIPTION
--------------------------------------------------
         1
Description for Scott

Обратите внимание, что в операторе SELECT * FROM customers невидимый столбец не просто не выводится, а отсутствует в структуре курсора !
Если в операторе INSERT не указаны имена столбцов, то невидимый столбец будет иметь значение NULL, либо значение по умолчанию, если конечно оно установлено для невидимого столбца:

SQL> INSERT INTO customers VALUES(2,'Description for Tiger');

1 row created.

SQL> SELECT nvl(name,'NULL') FROM customers;

        NVL(NAME,'NULL')
----------------------------
NULL

Из вышеприведенного примера следует, что невидимый столбец Name можно вывести только явно указав его имя в запросе. Как и обычные столбцы, невидимые столбцы:

• могут быть ключом секционирования в таблице;
• могут участвовать в вычисляемых выражениях в виртуальном столбце;
• сами могут быть виртуальными столбцами.

К сожалению, область использования невидимых столбцов имеет некоторые ограничения.
Следующие типы таблиц не могут иметь невидимых столбцов:

• внешние таблицы (External tables);
• кластерные таблицы (Cluster tables);
• временные таблицы

Также атрибуты пользовательских объектных типов PL/SQL не могут быть невидимыми.
Утилита SQL*Plus теперь имеет новый параметр COLINVI[SIBLE] управляющий выводим информации о невидимых столбцах:

SQL> DESC customers
 Name        Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID          NUMBER(9)
 DESCRIPTION         VARCHAR2(512)

SQL> SET COLINVISIBLE ON
SQL> DESC customers
 Name        Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID          NUMBER(9)
 DESCRIPTION         VARCHAR2(512)
 NAME (INVISIBLE)        VARCHAR2(64)

SQL>

При этом параметр COLINVISIBLE влияет только на результат команды DESCRIBE, все операторы SQL и PL/SQL для доступа к невидимым столбцам должны явно содержать его имя:

SQL> SET COLINVISIBLE ON
SQL> declare
  v_xCustomer customers%rowtype;
begin
  v_xCustomer.Name := 'Scott Tiger';
end;
/
  v_xCustomer.Name := 'Scott Tiger';
              *
ERROR at line 4:
ORA-06550: line 4, column 15:
PLS-00302: component 'NAME' must be declared
ORA-06550: line 4, column 3:
PL/SQL: Statement ignored

Интересно заметить, что при включении видимости ранее невидимых столбцов меняется их порядок в операторе SELECT *:

SQL> ALTER TABLE customers MODIFY (Name VISIBLE);

Table altered.

SQL> SELECT * FROM customers;

 ID
----------
DESCRIPTION
--------------------------------------------------------------------------------
NAME
----------------------------------------------------------------
  1
Description for Scott
Scott

то есть меняется значение поля COLUMN_ID в представлениях словаря ALL_TAB_COLUMNS, DBA_TAB_COLUMNS, и USER_TAB_COLUMNS.
Невидимые колонки полезны при плавных обновлениях приложения, когда в новой версии приложения нужно удалить некоторые столбцы.
В сочетании с технологией обновления приложения Edition base redefinition, скрытые столбцы обеспечивают бОльшую гибкость при переходе на новую версию приложения.

Oracle Database 12c R1 released!

Да, да - это не шутка! Наконец-то произошло то чего все мы так долго ждали: Oracle Database 12c Release 1 вышла!

Дистрибутивы для платформ Linux x64, Solaris x64 и Solaris SPARC вы можете скачать с сайта OTN.
Обратите внимание, что 32-х разрядный линукс теперь не поддерживается в качестве платформы для СУБД, - для этой платформы теперь есть только клиентское ПО (Oracle Client 12c R1).