Oracle database experiences

Oracle database blog

Plotting graph with SQL*Plus – Part I

Background

I would like to display a graph such as y=cos(x), using only SQL*Plus, a SELECT query and out of the box features of Oracle 11gR2.

Query

You will find below a query that displays the function y=cos(x) for x belonging to [0.1,11]:

SELECT y_val_un,
  listagg(decode(y1_val,1,'*','.'),'')  WITHIN GROUP (ORDER BY x_val) graph_val
FROM
  (SELECT tab_val.x_val,
    tab_unique_val.y_val_un,
    decode(tab_unique_val.y_val_un,tab_val.y_val,1,0) y1_val
  FROM
    (SELECT LEVEL x_val,
      trunc(cos((LEVEL/10)),1) y_val
    FROM dual
      CONNECT BY LEVEL <= 110
    ) tab_val
  CROSS JOIN
    (SELECT trunc(y_val,1) y_val_un
    FROM
      ( SELECT LEVEL x_val,cos((LEVEL/10)) y_val FROM dual CONNECT BY LEVEL <= 110
      )
    GROUP BY trunc(y_val,1)
    ORDER BY 1 DESC
    ) tab_unique_val
  )
GROUP BY y_val_un
ORDER BY 1 DESC;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: