Oracle-L: RE: Org charts

From: Robson, Peter <pgro_at_bgs.ac.uk>
Date: Mon, 18 Aug 2003 03:29:24 -0800
Message-ID: <F001.005CB54C.20030818032924@fatcity.com>

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible.

------_=_NextPart_001_01C3655F.E5DC1490
Content-Type: text/plain; charset="iso-8859-1"

Nice as far as it goes, Jared - let us know when you can generate a complete ER diagram from SQL*Plus... !

peter
edinburgh

-----Original Message-----
Sent: Saturday, August 16, 2003 2:04 AM
To: Multiple recipients of list ORACLE-L

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

This e-mail message, and any files transmitted with it, are confidential and intended solely for the use of the addressee. If this message was not addressed to you, you have received it in error and any copying, distribution or other use of any part of it is strictly prohibited. Any views or opinions presented are solely those of the sender and do not necessarily represent those of the British Geological Survey. The security of e-mail communication cannot be guaranteed and the BGS accepts no liability for claims arising as a result of the use of this medium to transmit messages from or to the

BGS. .                            http://www.bgs.ac.uk
*********************************************************************

------_=_NextPart_001_01C3655F.E5DC1490
Content-Type: text/html; charset="iso-8859-1"

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">


<META content="MSHTML 5.50.4807.2300" name=GENERATOR></HEAD>

<BODY>
<DIV>Nice as far as it goes, Jared - let us know when you can generate a complete ER diagram from SQL*Plus... !</DIV> <DIV> </DIV>
<DIV> </DIV> <DIV>peter</DIV>

<DIV><SPAN class=709290708-18082003><FONT face=Arial color=#0000ff 
size=2>edinburgh</FONT></SPAN></DIV>
<DIV><SPAN class=709290708-18082003><FONT face=Arial color=#0000ff

size=2> </DIV>
<BLOCKQUOTE>
<DIV class=OutlookMessageHeader dir=ltr align=left>-----Original Message----- From: Jared.Still_at_radisys.com [mailto:Jared.Still_at_radisys.com] Sent: Saturday, August 16, 2003 2:04 AM To: Multiple recipients of list ORACLE-L Subject: Org charts </DIV> 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 <FONT face=sans-serif

  size=2>&nbsp; &nbsp;org_chart_line(level-1,3) || ename</FONT> <BR><FONT 
  face=sans-serif size=2>from emp e</FONT> <BR><FONT face=sans-serif 
  size=2>start with e.job = 'PRESIDENT'</FONT> <BR><FONT face=sans-serif 
  size=2>connect by prior e.empno = e.mgr</FONT> <BR><FONT face=sans-serif 
  size=2>/</FONT> <BR><BR><FONT face=sans-serif 
  size=2>--------------------------------------------------------------------------------</FONT>

  <BR><FONT face=sans-serif size=2>| &nbsp; &nbsp; &nbsp; &nbsp;|___ALLEN</FONT> 
  <BR><FONT face=sans-serif size=2>| &nbsp; &nbsp; &nbsp; &nbsp;|___WARD</FONT> 
  <BR><FONT face=sans-serif size=2>| &nbsp; &nbsp; &nbsp; 
  &nbsp;|___MARTIN</FONT> <BR><FONT face=sans-serif size=2>| &nbsp; &nbsp;

|___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.

  <BR><BR><FONT face=sans-serif size=2>Jared</FONT> <BR><BR><FONT 
  face=sans-serif size=2>===============================================</FONT> 
  <BR><BR><FONT face=sans-serif size=2>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) := '____________________';</FONT> <BR><BR><FONT 
  face=sans-serif size=2>begin</FONT> <BR><BR><FONT face=sans-serif

size=2>        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) || '|';

  &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; else</FONT> <BR><FONT face=sans-serif 
  size=2>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 
  &nbsp; &nbsp; org_line := org_line || substr(space_string,1,indent_level_in )

;

  &nbsp; &nbsp; &nbsp; end if;</FONT> <BR><FONT face=sans-serif size=2>&nbsp; 
  &nbsp; &nbsp; &nbsp; end loop;</FONT> <BR><BR><FONT face=sans-serif 
  size=2>&nbsp; &nbsp; &nbsp; &nbsp; 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 </BLOCKQUOTE>

*********************************************************************<BR>

This e-mail message, and any files transmitted with it, are

confidential  and intended  solely for the  use of the  addressee. If<BR>
this message was not addressed to  you, you have received it in error<BR>
and any  copying,  distribution  or  other use  of any part  of it is<BR>
strictly prohibited. Any views or opinions presented are solely those<BR>

of the sender and do not necessarily represent those of the British Geological Survey. The security of e-mail communication cannot be guaranteed and the BGS accepts no liability for claims arising as a result of the use of this medium to transmit messages from or to the

BGS. .                            http://www.bgs.ac.uk<BR>
*********************************************************************<BR>

</BODY></HTML>

------_=_NextPart_001_01C3655F.E5DC1490--

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Robson, Peter
  INET: pgro_at_bgs.ac.uk

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 Mon Aug 18 2003 - 06:29:24 CDT