Org charts

[Jared . Still]

I was playing around with org charts yesterday, and came up with a function
to use for drawing org charts with lines from sqlplus.

This has probably been done before, but I couldn't seem to find one via google.

The output from scott.emp looks like:

select
   org_chart_line(level-1,3) || ename
from emp e
start with e.job = 'PRESIDENT'
connect by prior e.empno = e.mgr
/

--------------------------------------------------------------------------------
KING
|___JONES
|        |___SCOTT
|        |        |___ADAMS
|        |___FORD
|        |        |___SMITH
|___BLAKE
|        |___ALLEN
|        |___WARD
|        |___MARTIN
|        |___TURNER
|        |___JAMES
|___CLARK
|        |___MILLER

14 rows selected.

The problem with working alone (in the Oracle sense ) is there's never anyone
in the next cube over to show stuff to.

Jared

===============================================

create or replace function org_chart_line (
        level_in integer
        , indent_level_in integer
)
return varchar2
is

        org_line varchar2(100);

        space_string varchar2(20) := '                    ';
        char_string varchar2(20) := '____________________';

begin

        if level_in = 0 then
                return '';
        end if;

        for i in 1 .. ((level_in -1) * indent_level_in )
        loop
                if mod(i,indent_level_in) = 0 then
                        org_line := org_line || substr(space_string,1,indent_level_in -1) || '|';
                else
                        org_line := org_line || substr(space_string,1,indent_level_in ) ;
                end if;
        end loop;

        org_line := org_line ||  substr(char_string,1,indent_level_in  );

        org_line := '|' || substr(org_line,1);

        return org_line;

end;
/

show error function oc