ba6.us - Dave's Database Related Stuff

I'm trying not to scare my users off with endless rows and columns of numbers. I have an app that analyzes memory usage and swap space usage of some of our servers. Instead of throwing all kinds of computer sciency '65535' and '32767' numbers at my user I decided to create a small function to return a bar graph widget. Here is an example of the graph in use: Here is a function that will return the HTML for the widget:

CREATE OR REPLACE FUNCTION GetGraph (p_value IN NUMBER, p_total IN NUMBER) 
RETURN VARCHAR2
IS 
  my_retval VARCHAR2(1024);
  my_usedpercentage NUMBER := 0;
BEGIN

    if (p_total > p_value) then
        my_usedpercentage := 100 - ROUND( (p_value / p_total) * 100 );
    end if;

    my_retval := '<table width=104 border=0 style=single bgcolor=#AAAAAA><tr><td><table width=';
    my_retval := my_retval || my_usedpercentage;
    my_retval := my_retval || ' height=15 bgcolor=#0000FF><tr><td></td></tr></table></td></tr></table>';

    RETURN my_retval;

END;
/

The math is done inside the function to arrive at the percentage so you can feed it raw numbers and let the function do the heavy lifting. For example, in the first row and column above, (38285.5 free / 65536 total) *100 = 58% free. So 58 pixel width is shown as 'free', 42 pixel width is shown as 'used'. To use it you just have to work the GetGraph function into the SELECT list of your report query. For my purposes it goes a little something like this:

SELECT SERVER_NAME,
       GetGraph(FREEMEM,TOTALMEM) as "Memory Used",
       TOTALMEM as "Total Memory Mb",
       FREEMEM as "Free Memory mb",
       GetGraph(FREESWAP,TOTALSWAP) as "Swap Used",
       TOTALSWAP as "Swap Total mb",
       FREESWAP as "Swap Free mb"
FROM SERVER_DETAIL
ORDER BY SERVER_NAME