Org charts

From: <Jared.Still_at_radisys.com>
Date: Fri, 15 Aug 2003 17:04:22 -0800
Message-ID: <F001.005CAF63.20030815170422@fatcity.com>

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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: Jared.Still_at_radisys.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Fri Aug 15 2003 - 20:04:22 CDT