Virtual columns

0

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:

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

About Author

Patrick Barel is a PL/SQL Developer for AMIS Services in the Netherlands. Besides working with SQL and PL/SQL did he co-develop CodeGen and wrote different plug-ins for PL/SQL Developer. He publishes articles on the AMIS Technology blog and on his own blog http://blog.bar-solutions.com/.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.