Maarten wrote a post on Virtual Columns in the oracle database. I read a blogpost on preventing people issuing SELECT * on a table. This was done in a different database, so I decided to try it out in my Oracle Database.
First I create a simple table:

create table demo.emp_nuke
(
  empno  number(4) not null
, ename  varchar2(10)
, sal    number(7,2)
, deptno number(2)
, blowitup number generated always as (1/0) virtual
)
/

and I add some data to it:

begin
  insert into demo.emp_nuke(empno, ename, sal, deptno)
                       values (7499, 'ALLEN',  1600, 30);
  insert into demo.emp_nuke(empno, ename, sal, deptno)
                       values (7521, 'WARD',   1250, 30);
  insert into demo.emp_nuke(empno, ename, sal, deptno)
                       values (7654, 'MARTIN', 1250, 30);
  insert into demo.emp_nuke(empno, ename, sal, deptno)
                       values (7698, 'BLAKE',  2850, 30);
  insert into demo.emp_nuke(empno, ename, sal, deptno)
                       values (7844, 'TURNER', 1500, 30);
  insert into demo.emp_nuke(empno, ename, sal, deptno)
                       values (7900, 'JAMES',  950,  30);
end;
/

Then, when I issue this statement

select *
  from demo.emp_nuke e
/

I get an error:

1	`ORA-01476: divisor` `is` `equal` `to` `zero`

But I can still access the data from the table as long as I don’t include the virtual column:

select e.empno, e.ename, e.sal, e.deptno
  from demo.emp_nuke e
/

EMPNO ENAME            SAL DEPTNO
----- ---------- --------- ------
 7499 ALLEN        1600.00     30
 7521 WARD         1250.00     30
 7654 MARTIN       1250.00     30
 7698 BLAKE        2850.00     30
 7844 TURNER       1500.00     30
 7900 JAMES         950.00     30

Virtual columns

Like this:

About The Author

Patrick Barel

Share this:

Like this:

Related Posts

About The Author

Patrick Barel