Virtual columns

Patrick Barel

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
Next Post

6 surprising things you can do with Google search

Facebook0TwitterLinkedinNext the normal Google search you can use this search box for a lot more things. This search box is often the first place where you type when you want something done. Below I list 6 examples of how Google search can help you much quicker to answer a bit more complex questions. Set […]