SELECT *, Records and Invisible Columns

View All Scripts Login to Run Script

Script Name SELECT *, Records and Invisible Columns
Description In Oracle Database 12c and higher, you can define a table's column to be invisible. When a column is defined this way, it will not be included in rows fetched with the SELECT statement unless you explicitly include that column. Doc: http://docs.oracle.com/database/121/SQLRF/statements_7002.htm#SQLRF01402
Area PL/SQL General
Contributor Steven Feuerstein
Created Friday August 19, 2016

Statement 1

CREATE TABLE my_data
(
   visible_number     NUMBER,
   invisible_number   NUMBER INVISIBLE
)

Table created.

Statement 2

BEGIN
   INSERT INTO my_data (visible_number, invisible_number)
        VALUES (1, -1);

   COMMIT;
END;

1 row(s) inserted.

Statement 3

CREATE OR REPLACE PACKAGE my_pkg
   AUTHID DEFINER
IS
   CURSOR data_cur1
   IS
      SELECT * FROM my_data;

   CURSOR data_cur2
   IS
      SELECT visible_number, invisible_number FROM my_data;
END;

Package created.

Statement 4

The column is defined as invisible, so a SELECT * will not return it.

SELECT * -> Invisible Columns Left Behind

DECLARE 
   rec   my_data%ROWTYPE; 
BEGIN 
   SELECT * 
     INTO rec 
     FROM my_data 
    WHERE ROWNUM < 2; 
 
   DBMS_OUTPUT.PUT_LINE ('invisible = ' || rec.invisible_number); 
END;

ORA-06550: line 9, column 48:
PLS-00302: component 'INVISIBLE_NUMBER' must be declared
ORA-06550: line 9, column 4:
PL/SQL: Statement ignored

More Details: https://docs.oracle.com/error-help/db/ora-06550

Statement 5

The column is defined as invisible, so a SELECT * will not return it.

Same Behavior, Doesn't Matter How Record is Defined

DECLARE 
   rec   my_pkg.data_cur1%ROWTYPE; 
BEGIN 
   SELECT * 
     INTO rec 
     FROM my_data 
    WHERE ROWNUM < 2; 
 
   DBMS_OUTPUT.PUT_LINE ('invisible = ' || rec.invisible_number); 
END;

ORA-06550: line 9, column 48:
PLS-00302: component 'INVISIBLE_NUMBER' must be declared
ORA-06550: line 9, column 4:
PL/SQL: Statement ignored

More Details: https://docs.oracle.com/error-help/db/ora-06550

Statement 6

My cursor explicitly includes the invisible column, so it is accessible when I fetch a row.

Not Invislble If You Know Magic Word (Column Name)

DECLARE 
   rec   my_pkg.data_cur2%ROWTYPE; 
BEGIN 
   OPEN my_pkg.data_cur2; 
   FETCH my_pkg.data_cur2 INTO rec; 
   CLOSE my_pkg.data_cur2; 
 
   DBMS_OUTPUT.PUT_LINE ('invisible = ' || rec.invisible_number); 
END;

invisible = -1

Statement 7

Make Column Visible

ALTER TABLE my_data 
   MODIFY (invisible_number VISIBLE)

Table altered.

Statement 8

I change the invisible column into a visible column. SELECT * now will include invisible_number in the row assigned to the record.

Now SELECT * Includes Column

DECLARE 
   rec   my_data%ROWTYPE; 
BEGIN 
   SELECT * 
     INTO rec 
     FROM my_data 
    WHERE ROWNUM < 2; 
 
   DBMS_OUTPUT.PUT_LINE ('invisible = ' || rec.invisible_number); 
END;

invisible = -1

Statement 9
```
DROP TABLE my_data
```
```
Table dropped.
```
Statement 10
```
DROP PACKAGE my_pkg
```
```
Package dropped.
```

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database