Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Org charts
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
/
|___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
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
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><SPAN class=709290708-18082003><FONT face=Arial color=#0000ff size=2>edinburgh</FONT></SPAN></DIV> <DIV><SPAN class=709290708-18082003><FONT face=Arial color=#0000ffsize=2></FONT></SPAN> </DIV>
size=2> 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>KING</FONT> <BR><FONT face=sans-serif size=2>|___JONES</FONT> <BR><FONT face=sans-serif size=2>| |___SCOTT</FONT> <BR><FONT face=sans-serif size=2>| | |___ADAMS</FONT> <BR><FONT face=sans-serif size=2>| |___FORD</FONT> <BR><FONT face=sans-serif size=2>| | |___SMITH</FONT> <BR><FONT face=sans-serif size=2>|___BLAKE</FONT>
<BR><FONT face=sans-serif size=2>| |___ALLEN</FONT> <BR><FONT face=sans-serif size=2>| |___WARD</FONT> <BR><FONT face=sans-serif size=2>| |___MARTIN</FONT> <BR><FONT face=sans-serif size=2>| |___TURNER</FONT> <BR><FONT face=sans-serif size=2>| |___JAMES</FONT> <BR><FONT face=sans-serif size=2>|___CLARK</FONT> <BR><FONT face=sans-serif size=2>| |___MILLER</FONT> <BR><BR><FONT face=sans-serif size=2>14 rows selected.</FONT> <BR><BR><FONT face=sans-serif size=2>The problem with working alone (in the Oracle sense ) is there's never anyone</FONT> <BR><FONT face=sans-serif size=2>in the next cube over to show stuff to.</FONT>
<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(</FONT> <BR><FONT face=sans-serif size=2> level_in integer</FONT> <BR><FONT face=sans-serif size=2> , indent_level_in integer</FONT> <BR><FONT face=sans-serif size=2>)</FONT> <BR><FONT face=sans-serif size=2>return varchar2</FONT> <BR><FONT face=sans-serif size=2>is</FONT> <BR><BR><FONT face=sans-serif size=2> org_line varchar2(100);</FONT> <BR><BR><FONT face=sans-serif size=2> space_string varchar2(20) := ' ';</FONT> <BR><FONT face=sans-serif size=2>
char_string varchar2(20) := '____________________';</FONT> <BR><BR><FONT face=sans-serif size=2>begin</FONT> <BR><BR><FONT face=sans-serifsize=2> if level_in = 0 then</FONT> <BR><FONT face=sans-serif size=2> return '';</FONT> <BR><FONT face=sans-serif size=2> end if;</FONT> <BR><BR><FONT face=sans-serif size=2> for i in 1 .. ((level_in -1) * indent_level_in )</FONT> <BR><FONT face=sans-serif size=2> loop</FONT> <BR><FONT face=sans-serif size=2> if mod(i,indent_level_in) = 0 then</FONT> <BR><FONT face=sans-serif size=2> org_line := org_line || substr(space_string,1,indent_level_in -1) || '|';</FONT> <BR><FONT face=sans-serif size=2>
else</FONT> <BR><FONT face=sans-serif size=2> org_line := org_line || substr(space_string,1,indent_level_in );</FONT> <BR><FONT face=sans-serif size=2>
end if;</FONT> <BR><FONT face=sans-serif size=2> end loop;</FONT> <BR><BR><FONT face=sans-serif size=2> org_line := org_line || substr(char_string,1,indent_level_in );</FONT> <BR><BR><FONT face=sans-serif size=2> org_line := '|' || substr(org_line,1);</FONT> <BR><BR><FONT face=sans-serif size=2> return org_line;</FONT> <BR><BR><FONT face=sans-serif size=2>end;</FONT> <BR><FONT face=sans-serif size=2>/</FONT> <BR><BR><FONT face=sans-serif size=2>show error function oc</FONT> <BR><BR></BLOCKQUOTE><FONT SIZE=3><BR>
*********************************************************************<BR>This e-mail message, and any files transmitted with it, are<BR>
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<BR> Geological Survey. The security of e-mail communication cannot be<BR> guaranteed and the BGS accepts no liability for claims arising as a<BR> result of the use of this medium to transmit messages from or to the<BR>
BGS. . http://www.bgs.ac.uk<BR> *********************************************************************<BR></FONT>
------_=_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
![]() |
![]() |