OraFAQ Forum: SQL & PL/SQL » Generate Graph using PL SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Generate Graph using PL SQL

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Generate Graph using PL SQL [message #617420]

Mon, 30 June 2014 01:36

amit.sonar
Messages: 98
Registered: December 2009
Location: Mumbai

Member

Hi All,

I want to generate graph of data set given by sql query.

Is it possible to generate graph of sql query output using PL/SQL?

Please suggest me.

Thanks & Regards,
Amit Sonar.

Report message to a moderator

Re: Generate Graph using PL SQL [message #617421 is a reply to message #617420]

Mon, 30 June 2014 01:55

Michel Cadot
Messages: 68634
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Which kind of graph?
Post an example of what you want.

Report message to a moderator

Re: Generate Graph using PL SQL [message #617422 is a reply to message #617420]

Mon, 30 June 2014 01:57

Michel Cadot
Messages: 68634
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Also you never fed back to people that helped you in your previous topics, why?

Report message to a moderator

Re: Generate Graph using PL SQL [message #617423 is a reply to message #617420]

Mon, 30 June 2014 02:10

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

amit.sonar wrote on Mon, 30 June 2014 12:06

I want to generate graph of data set given by sql query.

Is it possible to generate graph of sql query output using PL/SQL?

Frankly speaking, displaying output as bar, charts or graph has least to do with SQL and PL/SQL. SQL will only fetch the data for you which is required to plot the graph. So once you prepare your data model, you need a tool to covert the dimesnions/display.

For example, if the number of rows and columns are withing certain limit, you could export it to Microsoft excel and use the proper functionality to plot the graph. There may be several other tools out there much more capable and efficient.

Report message to a moderator

Re: Generate Graph using PL SQL [message #617424 is a reply to message #617422]

Mon, 30 June 2014 02:12

amit.sonar
Messages: 98
Registered: December 2009
Location: Mumbai

Member

Hi Michel,

Whenever I get response from expertise I always appreciate and thank them.

Regards,
Amit.

Report message to a moderator

Re: Generate Graph using PL SQL [message #617425 is a reply to message #617423]

Mon, 30 June 2014 02:17

amit.sonar
Messages: 98
Registered: December 2009
Location: Mumbai

Member

Hi Lalit,

Thanks for your reply. Yes I can export the output of sql query into excel and then from there I can convert it into any graph.

I don't want generate it using any external tool. I want to know is there any built in packages or procedures available in oracle to accomplish this task.

Regards,
Amit Sonar

Report message to a moderator

Re: Generate Graph using PL SQL [message #617427 is a reply to message #617424]

Mon, 30 June 2014 02:22

Michel Cadot
Messages: 68634
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

amit.sonar wrote on Mon, 30 June 2014 09:12

Hi Michel,

Whenever I get response from expertise I always appreciate and thank them.

Regards,
Amit.

This is not correct.
Where are the feedback in these topics which are your last 5 ones and save your life:
http://www.orafaq.com/forum/m/586385/#msg_586385
http://www.orafaq.com/forum/m/556049/#msg_556049
http://www.orafaq.com/forum/m/551673/#msg_551673
http://www.orafaq.com/forum/m/542526/#msg_542526
http://www.orafaq.com/forum/m/476997/#msg_476997

Report message to a moderator

Re: Generate Graph using PL SQL [message #617428 is a reply to message #617425]

Mon, 30 June 2014 02:22

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

amit.sonar wrote on Mon, 30 June 2014 12:47

I want to know is there any built in packages or procedures available in oracle to accomplish this task.

You are missing the point here. As I said, SQL is to fetch the data, displaying of data requires a GUI. So focus on the GUI which will display the data in your required format and dimensions. One such tool would be Oracle reports developer. Like this Graph FAQ

Report message to a moderator

Re: Generate Graph using PL SQL [message #617429 is a reply to message #617424]

Mon, 30 June 2014 02:35

amit.sonar
Messages: 98
Registered: December 2009
Location: Mumbai

Member

Hi Michel,

Thanks for your reply. Here is an example.

I executed query select * from emp; and below is the output.

EMPNAME	SALARY
scott	10,000.00000
tiger	3,000.50000
alex	2,000.75000
john	7,000.00000

Then I saved the output into excel file and from there I generated the graph of the same output. I have attached screenshot for reference.

However, Same I want to do using PL/SQL without any external tool.

Regards,
Amit

Attachment: graph.png
(Size: 10.47KB, Downloaded 1201 times)

Report message to a moderator

Re: Generate Graph using PL SQL [message #617430 is a reply to message #617427]

Mon, 30 June 2014 02:40

amit.sonar
Messages: 98
Registered: December 2009
Location: Mumbai

Member

Hi Michel,

I respect each expert and their knowledge present on this forum. In future I will take care of this.

Regards,
Amit.

Report message to a moderator

Re: Generate Graph using PL SQL [message #617432 is a reply to message #617429]

Mon, 30 June 2014 02:54

Michel Cadot
Messages: 68634
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

SQL or PL/SQL are text mode language so you can generate character mode graphics like:

SQL> select ename, sal, rpad('*',trunc(sal/100),'*') graph from emp;
ENAME             SAL GRAPH
---------- ---------- --------------------------------------------------
SMITH             800 ********
ALLEN            1600 ****************
WARD             1250 ************
JONES            2975 *****************************
MARTIN           1250 ************
BLAKE            2850 ****************************
CLARK            2450 ************************
SCOTT            3000 ******************************
KING             5000 **************************************************
TURNER           1500 ***************
ADAMS            1100 ***********
JAMES             950 *********
FORD             3000 ******************************
MILLER           1300 *************

However with SQL or PL/SQL you can also generate an HTML output you can spool in a file and open it with your browser, Excel...
Note: SQL*Plus also can natively generate an HTML file but it is an HTML version of the above graph and not a graph like the one you posted.

Report message to a moderator

Re: Generate Graph using PL SQL [message #617437 is a reply to message #617432]

Mon, 30 June 2014 03:24

ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

Well there are tools like GnuPlot, which can create graphics from plain text data files, so you could use pl/sql to write a gnuplot data file and then render the chart. Here are a few examples: http://gnuplot.sourceforge.net/demo/histograms.html

Report message to a moderator

Re: Generate Graph using PL SQL [message #617448 is a reply to message #617432]

Mon, 30 June 2014 05:03

amit.sonar
Messages: 98
Registered: December 2009
Location: Mumbai

Member

Hi Michel,

Thanks for your reply. Let me try the way you described and will get back to you.

Regards,
Amit

Report message to a moderator

Re: Generate Graph using PL SQL [message #617450 is a reply to message #617437]

Mon, 30 June 2014 05:05

amit.sonar
Messages: 98
Registered: December 2009
Location: Mumbai

Member

Hi Thomas,

Thanks for replying. I wont allow to use any third party tool to generate graph in my application.

Regards,
Amit

Report message to a moderator

Re: Generate Graph using PL SQL [message #617459 is a reply to message #617450]

Mon, 30 June 2014 06:21

Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Could you explain what kind of an application is it? I sincerely hope that your clients don't run it from SQL*Plus. So, is it a Forms application? Application Express? Something different?

Because, maybe you can use that tool's graphic capabilities (but you don't know they exist, how to use them and similar).

Report message to a moderator

Re: Generate Graph using PL SQL [message #617477 is a reply to message #617459]

Mon, 30 June 2014 07:42

amit.sonar
Messages: 98
Registered: December 2009
Location: Mumbai

Member

Hi Littlefoot,

It's web based application. I require it for report purpose which every fortnightly I need to send using scheduler.

Regards,
Amit Sonar.

Report message to a moderator

Re: Generate Graph using PL SQL [message #617493 is a reply to message #617450]

Mon, 30 June 2014 12:03

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

amit.sonar wrote on Mon, 30 June 2014 05:05

I wont allow to use any third party tool to generate graph in my application.

Then you are simply out of luck. As others have tried to explain, PL/SQL has no facility to create graphs. In fact, PL/SQL really has no facility to interface with the user at all. The entire job of PL/SQL is to perform operations on the database and, if requested, return data to a calling client. How that data is presented by the client is entirely up to the client. PL/SQL has zero responsibility or capability for that presentation.

My kitchen faucet delivers water. What I do with that water is up to me. It is not the job of the faucet to make coffee. It is not even the responsibility of the faucet to make sure the water doesn't just go straight down the drain.

Report message to a moderator

Re: Generate Graph using PL SQL [message #617497 is a reply to message #617448]

Mon, 30 June 2014 14:53

Michel Cadot
Messages: 68634
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

What I meant is you can use a query like that:

col nop noprint
select 1 nop, '<html><body><table>' v from dual
union all
select 2,
       '<tr>
  <td>'||ename||'</td>
  <td>'||sal||'</td>
  <td  width=400px>
    <table><tr><td style="background-color:blue" width='||400*sal/5000||'px>&nbsp;</td>
    <td style="background-color:white" width='||(400-400*sal/5000)||'px>&nbsp;</td></tr></table>
  </td>
</tr>'
from emp
union all
select 3 nop, '</table></body></html>' v from dual
order by 1
/

Spool the result in a file and open it with your browser which gives you:

/forum/fa/11995/0/

Attachment: OraFAQ.jpg
(Size: 36.18KB, Downloaded 5870 times)

Report message to a moderator

Re: Generate Graph using PL SQL [message #617498 is a reply to message #617497]

Mon, 30 June 2014 14:58

Michel Cadot
Messages: 68634
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

The query can be simplified to:

col nop noprint
select 1 nop, '<html><body><table>' v from dual
union all
select 2,
       '<tr>
  <td>'||ename||'</td>
  <td>'||sal||'</td>
  <td  width=400px>
    <table style="background-color:blue" width='||400*sal/5000||'px><tr><td>&nbsp;</td></tr></table>
  </td>
</tr>'
from emp
union all
select 3 nop, '</table></body></html>' v from dual
order by 1
/

which gives about the same thing:

/forum/fa/11996/0/

Attachment: OraFAQ2.jpg
(Size: 36.44KB, Downloaded 5746 times)

Report message to a moderator

Re: Generate Graph using PL SQL [message #617920 is a reply to message #617498]

Fri, 04 July 2014 08:02

amit.sonar
Messages: 98
Registered: December 2009
Location: Mumbai

Member

Hi Michel,

Thanks for your reply. I think this could be useful for me.

Let me modify and try it.

Thanks & Regards,
Amit Sonar

Report message to a moderator

Re: Generate Graph using PL SQL [message #617921 is a reply to message #617920]

Fri, 04 July 2014 08:03

amit.sonar
Messages: 98
Registered: December 2009
Location: Mumbai

Member

Thank You all for giving your valuable time.

Regards,
Amit Sonar

Report message to a moderator

Re: Generate Graph using PL SQL [message #617937 is a reply to message #617921]

Fri, 04 July 2014 10:20

manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA

Senior Member

Instead of doing raw HTML coding, I will suggest you to do it using PLSQL HTP and HTF packages, as it will be more managable in future.

HTP and HTF packages

Manu

Report message to a moderator

Re: Generate Graph using PL SQL [message #617940 is a reply to message #617937]

Fri, 04 July 2014 11:15

Michel Cadot
Messages: 68634
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

There is not so much differences, instead of coding tags you call a function (with about the same name than the tag).
In addition, you have less possibilities to format your output (unless you use the same code).

As an example, can you post the code for the example I showed?

[Updated on: Fri, 04 July 2014 11:15]

Report message to a moderator

Previous Topic:	Simultaneous update in child table
Next Topic:	Why we not use out parameter in pl/sql function

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 16 07:32:28 CDT 2024