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

  • Sergio: Hi, Where is declared the function agent_clean() ? I dont see agent_clean() definition !! regards, Sergio
  • Sikandar: I do salute you brother, This is very………..nti mes helpful and correct documents. I...
  • Karthik Singh: Thanks for sharing a useful article. Looks like you have installed weblogic 10.3.4. Then shall I...
  • Joseph: Paul, Here target is from which database we are taking cloning. and auxilary is the cloning database. Wissem,...
  • sundar: In Doc ID 1472171.1 it says that the snapshot control file should be in a shared location for RAC databases....
  • Taoqir: Hello, This is a wonderfull and very helping material.now I am going to configure Data Guard. Thanks
  • Adrian Iriarte: Saludos, Que buen articulo, casi siempre al momento de querer implementar soluciones para el...
  • Patricia: Hi Wissem, is there a workaround for this? I mean I want to use the sqlnet.authentication_services =none,...
  • Brian: I created the cdb manually using the instruction, but then I tried to create pdb within in, it failed. have...
  • Farmer Johnson: Hi Wissem. How did you avoid the error you had previsouly encountered: “sudo opatch auto...
  • Héctor Moro: Querido Nelson; Después de entrevistar a mas de 50 profesionales de primer nivel en Uruguay, al fin te...
  • What is NO2 Maximus: I genuinely apрreciated your site! You shoulԁ update it wiϳth new info!
  • Nav: Indeed helpful, to the point article..
  • Koti: Can you please explain all the sections of AWR report in RAC. I mean to ask output produced by awrgrpt.sql...
  • Himanshu: Nice post.can you please comment on this Why RMAN restored the DATA_REORTING datafie to +DATA diskgroup?
  • gabriel: what version of sqldeveloper support plugable database?
  • Kal: Quality – thanks…
  • wissem: Well , like I mentioned in the post, delete service and add service using IP.
  • Kelly: were you able to solve this? Thanks!
  • wissem: Hello, You can buy the book from PakT: http://www.packtpub.com/oracle -data-guard-11gr2-administr...

Oracle-Class Forums Latest Activity