Interesting way to generate some Oracle sample tables

A lot of times you need to create a sample table with a primary and foreign key, populated with some kind of name-like field.  Here's an interesting way to do it.

create table emp_tab as
select
  rownum empno,
  object_name ename,
  mod(rownum, 10) + 1 deptno,
  rownum * 100 sal
from all_objects
where rownum < 100;



Now you can do the same thing, for the foreign key.

create table dept_tab as
select
  rownum deptno,
  'd_' || rownum  deptname
from all_objects
where rownum <= 10;




mh@templar> select * from emp_tab;
empno ename                       deptno sal  
----- -----                       ------ ---  
    1 DUAL                             2  100 
    2 DUAL                             3  200 
    3 SYSTEM_PRIVILEGE_MAP             4  300 
    4 SYSTEM_PRIVILEGE_MAP             5  400 
    5 TABLE_PRIVILEGE_MAP              6  500 
    6 TABLE_PRIVILEGE_MAP              7  600