Oracle 12c: Invisible Column

June 27th, 2013 | Posted in 12c, Blog | 1 Comment

Playing with the new Oracle 12c, Noticed the new INVISIBLE option of column creation;

Let’s create a table with an INVISIBLE column

sandbox1(orawiss):/home/oracle>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Thu Jun 27 09:14:23 2013

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


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

SQL> alter session set container=ORAWISS12C;

Session altered.

SQL> CREATE TABLE wissem.test12c (col1 NUMBER PRIMARY KEY, 
col2 VARCHAR2(20) INVISIBLE);

Table created.

SQL> 
INSERT INTO wissem.test12c (col1, col2) VALUES (1, 'test1');
INSERT INTO wissem.test12c (col1, col2) VALUES (2, 'test2');
INSERT INTO wissem.test12c (col1, col2) VALUES (3, 'test3');
1 row created.

SQL>
1 row created.

SQL>

1 row created.

SQL> commit;

Commit complete.

if we run describe the column col2 doesn’t appear as a result;

SQL> desc wissem.test12c;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                      NOT NULL NUMBER

if we select all (select *), the column col2 doesn’t appear as a result;

SQL> select * from wissem.test12c;

      COL1
----------
         1
         2
         3

SQL> select col1, col2 from wissem.test12c;

      COL1 COL2
---------- --------------------
         1 test1
         2 test2
         3 test3

if we use %ROWTYPE , the column col2 doesn’t appear as a result;


SQL>
SQL> DECLARE
  2    V_REC wissem.test12c%ROWTYPE;
  3    CURSOR CUR_C1
  IS
  ELECT *
   FROM wissem.test12c
  WHERE col1 = 1;
  7    8  BEGIN
  9    OPEN CUR_C1;
 10    FETCH CUR_C1 INTO V_REC;
 11    DBMS_OUTPUT.PUT_LINE('COL2 = ' || V_REC.COL2);
 12    CLOSE CUR_C1;
 13    EXCEPTION WHEN OTHERS THEN
 14     DBMS_OUTPUT.PUT_LINE('Error:: ' || SQLERRM);
 15  END;
 16  /
  DBMS_OUTPUT.PUT_LINE('COL2 = ' || V_REC.COL2);
                                          *
ERROR at line 11:
ORA-06550: line 11, column 43:
PLS-00302: component 'COL2' must be declared
ORA-06550: line 11, column 3:
PL/SQL: Statement ignored

COL1 visible colum appears;

SQL> set serveroutput on
SQL> DECLARE
  2    V_REC wissem.test12c%ROWTYPE;
  3    CURSOR CUR_C1
  4    IS
  5    SELECT *
  FROM wissem.test12c
  6    7    WHERE col1 = 1;
  8  BEGIN
  9    OPEN CUR_C1;
 10    FETCH CUR_C1 INTO V_REC;
 11    DBMS_OUTPUT.PUT_LINE('COL1 = ' || V_REC.COL1);
  CLOSE CUR_C1;
 12   13    EXCEPTION WHEN OTHERS THEN
 14     DBMS_OUTPUT.PUT_LINE('Error:: ' || SQLERRM);
 15  END;
 16  /
COL1 = 1

PL/SQL procedure successfully completed.

if we specify the column in the select statement , the column col2 invisible appears;

SQL> DECLARE
  2    CURSOR CUR_C1
  3    IS
  4    SELECT COL1,
  5      COL2
  6    FROM wissem.test12c
  7    WHERE COL1 = 1;
  8  BEGIN
  9   FOR C IN CUR_C1 LOOP
 10       DBMS_OUTPUT.PUT_LINE('COL1 = ' || C.COL1);
 11       DBMS_OUTPUT.PUT_LINE('COL2 = ' || C.COL2);
  END LOOP;
 12   13    EXCEPTION WHEN OTHERS THEN
 14     DBMS_OUTPUT.PUT_LINE('Error:: ' || SQLERRM);
END; 15
 16  /
COL1 = 1
COL2 = test1

PL/SQL procedure successfully completed.

Using %ROWTYPE and %TYPE the invisible column doesn’t appear and we have ;
ORA-06550: line 3, column 29:
PLS-00302: component ‘COL2’ must be declared

SQL> DECLARE
  2    V_REC_COL1 wissem.test12c.col1%TYPE;
  V_REC_COL2 wissem.test12c.col2%TYPE;
  3    4    CURSOR CUR_C1
  5    IS
  6    SELECT COL1,
  7      COL2
  8    FROM wissem.test12c
  9    WHERE COL1 = 1;
BEGIN
 10   11   FOR C IN CUR_C1 LOOP
 12    V_REC_COL1 := C.COL1;
 13    V_REC_COL2 := C.COL2;
 14    DBMS_OUTPUT.PUT_LINE('COL1 = ' || V_REC_COL1 );
  DBMS_OUTPUT.PUT_LINE('COL1 = ' || V_REC_COL2 );
 15   16    END LOOP;
 17    EXCEPTION WHEN OTHERS THEN
 18     DBMS_OUTPUT.PUT_LINE('Error:: ' || SQLERRM);
 19  END;
 20  /
  V_REC_COL2 wissem.test12c.col2%TYPE;
                            *
ERROR at line 3:
ORA-06550: line 3, column 29:
PLS-00302: component 'COL2' must be declared
ORA-06550: line 3, column 14:
PL/SQL: Item ignored
ORA-06550: line 13, column 3:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 13, column 3:
PL/SQL: Statement ignored
ORA-06550: line 15, column 37:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 15, column 3:
PL/SQL: Statement ignored


SQL> DECLARE
  2    V_REC_COL1 wissem.test12c.col1%TYPE;
  3    CURSOR CUR_C1
  IS
  4    5    SELECT COL1,
  6      COL2
  7    FROM wissem.test12c
  8    WHERE COL1 = 1;
  9  BEGIN
 10   FOR C IN CUR_C1 LOOP
 11    V_REC_COL1 := C.COL1;
 12    DBMS_OUTPUT.PUT_LINE('COL1 = ' || V_REC_COL1 );
 13     END LOOP;
 14    EXCEPTION WHEN OTHERS THEN
 15     DBMS_OUTPUT.PUT_LINE('Error:: ' || SQLERRM);
 16  END;
 17  /
COL1 = 1

PL/SQL procedure successfully completed.

SQL>

Cheers,
Wissem

References: Doc 12cR1



One Comment to “Oracle 12c: Invisible Column”


Leave a Comment





Polls

which oracle topic interests you most?

View Results

Loading ... Loading ...


Oracle Class Tweets


Recent Posts


Recent Comments

  • T Robinson: In regard to the comment “In our case, RMAN didn’t found anything corrupted.” Please notice...
  • iftikhar: if we backup in multiple backupsets and will be command for restoring for more than one backupsets ,
  • Abey: Very helpful article.Thanks a lot. Can we follow this on oracle DG enabled deployment also?
  • Mauricio Daher: Need help configuring SBT for cloud backups. Does not seem to work for 12c. Thanks! RMAN>...
  • Leszek K: *.db_create_file_dest=’+ DATA’ create database command – why do you use full datafile...
  • Prabhakar Kumar: Thanks buddy!!! It really helps to DBAs who are novice to 12c ASM feature.
  • Wubeshet: I am getting the ORA-07445 This morning on the trace file. The following is the symptom of the problem. It...
  • Lakshmi N: Hi Thanks for the post. Just curious, is there a way to restore to a point wher my db is in no archive...
  • Shivakkumar P: This is really very good article. Iam learning Oracle 12c I used this to replicate testing from...
  • Uday: Hi Wissem, Once we apply the level 1 incremental backup to datafile copy it is no longed required. My question...
  • avnish: Can I set up active duplicate database in 11g standard edition (11.2.0.1)
  • Jaya V: hi, am having rman backup. i need to restore on it, from test instance. But the test instance name not same...
  • Jaya: hi, am having rman backup. i need to restore on it, from test instance. But the test instance name not same...
  • wissem: SQL Developer 4.0
  • oracleman consulting: great info thanks for sharing
  • Mike: Very nice article!! Thanks for sharing!!
  • yathish: I really appreciate if you can send me, if you have tutorial for 11g (11.2.0.1) RAC & ASM (prefer to be...
  • Farrukh Kamal: Fahad, I’m interesting in pursuing remote employment with Pythian. What tips would you suggest...
  • pavankumar: for the error it need to set env correctly http://orcl11gdba.blogspot.in/ 2014/08/asmcmd-08102-no-con...
  • wissem: Post result off; crsctl check has crsctl check crs

Oracle-Class Forums Latest Activity