------_=_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><SPAN class=709290708-18082003><FONT face=Arial color=#0000ff size=2>Nice
as far as it goes, Jared - let us know when you can generate a complete ER
diagram from SQL*Plus... !</FONT></SPAN></DIV>
<DIV><SPAN class=709290708-18082003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=709290708-18082003></SPAN> </DIV>
<DIV><SPAN class=709290708-18082003><FONT face=Arial color=#0000ff
size=2>peter</FONT></SPAN></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></FONT></SPAN> </DIV>
<BLOCKQUOTE>
<DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B> [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]<BR><B>Sent:</B> Saturday, August 16, 2003
2:04 AM<BR><B>To:</B> Multiple recipients of list ORACLE-L<BR><B>Subject:</B>
Org charts<BR><BR></FONT></DIV><BR><FONT face=sans-serif size=2>I was playing
around with org charts yesterday, and came up with a function</FONT> <BR><FONT
face=sans-serif size=2>to use for drawing org charts with lines from
sqlplus.</FONT> <BR><BR><FONT face=sans-serif size=2>This has probably been
done before, but I couldn't seem to find one via google.</FONT> <BR><BR><FONT
face=sans-serif size=2>The output from scott.emp looks like:</FONT>
<BR><BR><FONT face=sans-serif size=2>select</FONT> <BR><FONT face=sans-serif
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-serif
size=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>
*********************************************************************<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>
</BODY></HTML>
------_=_NextPart_001_01C3655F.E5DC1490--
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Robson, Peter
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
