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
