Wednesday, December 9, 2009
Wednesday, October 28, 2009
How to use UNICODE characters in PL/SQL?
We had a requirement to write a pl/sql function to return strings in sinhala/tamil characters. So just copying and pasting the sinhala/tamil characters in the function body don't work. Hence we need to use unicode characters for this. just using unicode characters not sufficient. Should use the
UNISTR function.
Eg:
UNISTR('\0DB1\0DDC:') returns අංක: (Sinhala)
UNISTR('\0B87\0BB2:') returns இல: (Tamil)
Although the stored unicode characters values be taken as
ASCIISTR(column_name)
UNISTR function.
Eg:
UNISTR('\0DB1\0DDC:') returns අංක: (Sinhala)
UNISTR('\0B87\0BB2:') returns இல: (Tamil)
Although the stored unicode characters values be taken as
ASCIISTR(column_name)
Tuesday, September 1, 2009
Virtual Columns in Oracle 11g
Very exiting feature of Virtual columns are introduced in Oracle 11g. Have a look. Click Here
Friday, August 14, 2009
On Looping, First, and Last Choose the best approach to prevent a VALUE_ERROR exception.
by steven feuerstein
if I try to use a FOR loop to iterate from FIRST to LAST and my collection is empty, PL/SQL
raises a VALUE_ERROR exception. What’s the best way to avoid raising this error? First, the raising of VALUE_ERROR has nothing to do with your collection. The PL/SQL runtime engine will raise a VALUE_ERROR exception whenever it tries to execute a numeric FOR loop and
either the low or high expression in the loop’s header evaluates to NULL. To avoid this exception, you must ensure that neither the low nor the high expression evaluates to NULL.
When you’re working with collections, there are several ways to accomplish this, most of which should be avoided because of their drawbacks. I will first show you each of them and then offer
my views on which should be used and which should be avoided. Each approach example is an implementation of the show_names procedure defined in this package specification:
PACKAGE employees_mgr
IS
TYPE names_t IS TABLE OF
employees.last_name%TYPE
INDEX BY PLS_INTEGER;
PROCEDURE show_names
(names_in IN names_t);
END employees_mgr;
Approach 1. Use NVL to ensure that the FOR loop header’s low and high expressions
never return NULL.
PROCEDURE show_names
(names_in IN names_t)
IS
BEGIN
FOR indx IN NVL (names_in.FIRST, 0) .. NVL (names_in.LAST, -1)
LOOP
DBMS_OUTPUT.PUT_LINE
(names_in(indx));
END LOOP;
END show_names;
Approach 2. Execute the loop only if at least one element is defined in the collection.
PROCEDURE show_names
(names_in IN names_t)
IS
BEGIN
IF names_in.COUNT > 0 THEN
FOR indx IN names_in.FIRST .. names_in.LAST LOOP
DBMS_OUTPUT.PUT_LINE (names_in(indx));
END LOOP;
END IF;
END show_names;
Approach 3. Execute the FOR loop with 1 for the low value and COUNT for the high value.
PROCEDURE show_names
names_in IN names_t) IS
BEGIN
FOR indx IN 1 .. names_in.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(names_in(indx));
END LOOP;
END show_names;
Approach 4. Use a WHILE loop and the FIRST and NEXT collection methods.
PROCEDURE show_names
(names_in IN names_t) IS
l_index PLS_INTEGER;
BEGIN
l_index := names_in.FIRST;
WHILE (l_index IS NOT NULL) LOOP
DBMS_OUTPUT.PUT_LINE (names_in(l_index));
l_index := names_in.NEXT(l_index);
END LOOP;
END show_names;
All four approaches achieve the desired effect: VALUE_ERROR will not be raised, even if the number of elements in the names_in collection is 0. Yet I will argue that the first approach should never be used and that the other three techniques should be chosen only when
certain conditions are met.The first approach, using NVL, is a classic example of a programmer’s trying to be too clever by half and ending up with code that is hard to understand and maintain.
Consider the header of the FOR loop:
FOR indx IN NVL (names_in.FIRST, 0) .. NVL (names_in.LAST, -1)
If I had not written this block originally and now had to maintain it, I would have to study this code to determine what exactly the point of it is. Whenever a developer must analyze and interpret code to uncover its intention, there is a chance of misinterpretation and then the
introduction of a bug.I suggest that, as a general rule, developersavoid being clever and instead write code that explains itself. Which brings me to the second approach:
use the COUNT method to ensure that the FOR loop is executed only when there is something in the collection. Here is the relevant code:
IF names_in.COUNT > 0 THEN
FOR indx IN names_in.FIRST .. names_in.LAST
I believe this code speaks for itself. It says: “If the collection contains at least
one element, iterate from the lowest to the highest index value and take the specified action. If the collection is empty, skip the FOR loop entirely.” This is a vast improvement over the
first approach, yet I cannot recommend it under all circumstances. The problem is that if the actual collection passed to the names_in parameter is sparse (that is, at least one index value between FIRST and LAST is not defined), the FOR loop will raise a NO_DATA_FOUND exception:
SQL> DECLARE
2 names_in employees_mgr.names_t;
3 BEGIN
4 names_in (1) := ‘Kirk’;
5 names_in (5) := ‘Spock’;
6 employees_mgr.show_names(names_in);
7 END;
8 /
DECLARE
* ERROR at line 1:ORA-01403: no data found
This happens because the FOR loop is instructed to display the name found in names_in(1) through names_in(5). When the PL/SQL runtime engine tries to read the contents of names_in(2), it finds that there is no element defined at index value 2 and it raises NO_DATA_FOUND.
Thus, I recommend this second technique only when you know without any doubt that the collection through which you are iterating is either empty or densely filled (all index values between FIRST and LAST are defined). You can be sure of this whenever you populate
the collection with a BULK COLLECT query or with the result of a nested table MULTISET operation (UNION, UNION ALL, INTERSECT, or EXCEPT). The third technique iterates from 1 to the COUNT of elements in the collection:
FOR indx IN 1 .. names_in.COUNT
This technique has the advantage of avoiding the clutter of an IF statement to ensure that the FOR loop executes only when the collection is not empty. If the collection is empty, COUNT will return 0 (not NULL) and the FOR loop body will not execute, because 0 is less than 1.It is concise and readable, but it has the same drawback as the previous technique: it assumes that the collection is either empty or densely filled. It also assumes that the lowest defined index value is 1. If you are not absolutely certain that the collection will always be filled, starting with index value 1, you should not use this technique. Which brings us to the fourth and last approach: don’t use a FOR loop at all.
Instead use a WHILE loop and the NEXT method:
l_index := names_in.FIRST;
WHILE (l_index IS NOT NULL) LOOP
DBMS_OUTPUT.PUT_LINE(names_in(l_index));
l_index := names_in.NEXT(l_index);
END LOOP;
This approach makes no assumptions about the contents of the collection. The names_in collection can be empty,densely filled, or sparse, and the program will still “do the right thing.” The key to this technique’s flexibility is the use of the NEXT method. This method returns
the next (highest) index value after the specified index value that is defined, ignoring (or, at least conceptually, skipping over) all undefined index values.You might then expect that I would
recommend that you always use this technique if you want to iterate through all the elements of a collection. Yet that is not the case. Suppose that when I wrote the show_names procedure, it was intended to be used to display the contents of a collection that was populated with a BULK
COLLECT statement. In such a case, the collection is always empty or sequentially
filled, starting from index value 1. The show_names procedure works properly and the code goes into production. Now suppose further that a year later, another developer is instructed to make a change to one of the programs that calls employees_mgr.show_names. The developer makes a mistake and deletes several of the elements in the collection that is passed to show_names. The collection is now sparsely filled, but it should not be. Still, show_names does its job without raising any errors. The net result is that show_names has, in effect,covered up an error.The bottom line is that if you are writing code to iterate through a collection and you know for certain that this collection should be sequentially filled,you should use
FOR indx IN 1 ...COUNT
if you also know that the collection is
always filled from index value 1 (as with
BULK COLLECT and MULTISET), or use
IF.COUNT > 0 THEN
FOR indx IN.FIRST ...LAST
if the lowest index value might be a value other than 1.
Source : Oracle Magazine September/October 2009
if I try to use a FOR loop to iterate from FIRST to LAST and my collection is empty, PL/SQL
raises a VALUE_ERROR exception. What’s the best way to avoid raising this error? First, the raising of VALUE_ERROR has nothing to do with your collection. The PL/SQL runtime engine will raise a VALUE_ERROR exception whenever it tries to execute a numeric FOR loop and
either the low or high expression in the loop’s header evaluates to NULL. To avoid this exception, you must ensure that neither the low nor the high expression evaluates to NULL.
When you’re working with collections, there are several ways to accomplish this, most of which should be avoided because of their drawbacks. I will first show you each of them and then offer
my views on which should be used and which should be avoided. Each approach example is an implementation of the show_names procedure defined in this package specification:
PACKAGE employees_mgr
IS
TYPE names_t IS TABLE OF
employees.last_name%TYPE
INDEX BY PLS_INTEGER;
PROCEDURE show_names
(names_in IN names_t);
END employees_mgr;
Approach 1. Use NVL to ensure that the FOR loop header’s low and high expressions
never return NULL.
PROCEDURE show_names
(names_in IN names_t)
IS
BEGIN
FOR indx IN NVL (names_in.FIRST, 0) .. NVL (names_in.LAST, -1)
LOOP
DBMS_OUTPUT.PUT_LINE
(names_in(indx));
END LOOP;
END show_names;
Approach 2. Execute the loop only if at least one element is defined in the collection.
PROCEDURE show_names
(names_in IN names_t)
IS
BEGIN
IF names_in.COUNT > 0 THEN
FOR indx IN names_in.FIRST .. names_in.LAST LOOP
DBMS_OUTPUT.PUT_LINE (names_in(indx));
END LOOP;
END IF;
END show_names;
Approach 3. Execute the FOR loop with 1 for the low value and COUNT for the high value.
PROCEDURE show_names
names_in IN names_t) IS
BEGIN
FOR indx IN 1 .. names_in.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(names_in(indx));
END LOOP;
END show_names;
Approach 4. Use a WHILE loop and the FIRST and NEXT collection methods.
PROCEDURE show_names
(names_in IN names_t) IS
l_index PLS_INTEGER;
BEGIN
l_index := names_in.FIRST;
WHILE (l_index IS NOT NULL) LOOP
DBMS_OUTPUT.PUT_LINE (names_in(l_index));
l_index := names_in.NEXT(l_index);
END LOOP;
END show_names;
All four approaches achieve the desired effect: VALUE_ERROR will not be raised, even if the number of elements in the names_in collection is 0. Yet I will argue that the first approach should never be used and that the other three techniques should be chosen only when
certain conditions are met.The first approach, using NVL, is a classic example of a programmer’s trying to be too clever by half and ending up with code that is hard to understand and maintain.
Consider the header of the FOR loop:
FOR indx IN NVL (names_in.FIRST, 0) .. NVL (names_in.LAST, -1)
If I had not written this block originally and now had to maintain it, I would have to study this code to determine what exactly the point of it is. Whenever a developer must analyze and interpret code to uncover its intention, there is a chance of misinterpretation and then the
introduction of a bug.I suggest that, as a general rule, developersavoid being clever and instead write code that explains itself. Which brings me to the second approach:
use the COUNT method to ensure that the FOR loop is executed only when there is something in the collection. Here is the relevant code:
IF names_in.COUNT > 0 THEN
FOR indx IN names_in.FIRST .. names_in.LAST
I believe this code speaks for itself. It says: “If the collection contains at least
one element, iterate from the lowest to the highest index value and take the specified action. If the collection is empty, skip the FOR loop entirely.” This is a vast improvement over the
first approach, yet I cannot recommend it under all circumstances. The problem is that if the actual collection passed to the names_in parameter is sparse (that is, at least one index value between FIRST and LAST is not defined), the FOR loop will raise a NO_DATA_FOUND exception:
SQL> DECLARE
2 names_in employees_mgr.names_t;
3 BEGIN
4 names_in (1) := ‘Kirk’;
5 names_in (5) := ‘Spock’;
6 employees_mgr.show_names(names_in);
7 END;
8 /
DECLARE
* ERROR at line 1:ORA-01403: no data found
This happens because the FOR loop is instructed to display the name found in names_in(1) through names_in(5). When the PL/SQL runtime engine tries to read the contents of names_in(2), it finds that there is no element defined at index value 2 and it raises NO_DATA_FOUND.
Thus, I recommend this second technique only when you know without any doubt that the collection through which you are iterating is either empty or densely filled (all index values between FIRST and LAST are defined). You can be sure of this whenever you populate
the collection with a BULK COLLECT query or with the result of a nested table MULTISET operation (UNION, UNION ALL, INTERSECT, or EXCEPT). The third technique iterates from 1 to the COUNT of elements in the collection:
FOR indx IN 1 .. names_in.COUNT
This technique has the advantage of avoiding the clutter of an IF statement to ensure that the FOR loop executes only when the collection is not empty. If the collection is empty, COUNT will return 0 (not NULL) and the FOR loop body will not execute, because 0 is less than 1.It is concise and readable, but it has the same drawback as the previous technique: it assumes that the collection is either empty or densely filled. It also assumes that the lowest defined index value is 1. If you are not absolutely certain that the collection will always be filled, starting with index value 1, you should not use this technique. Which brings us to the fourth and last approach: don’t use a FOR loop at all.
Instead use a WHILE loop and the NEXT method:
l_index := names_in.FIRST;
WHILE (l_index IS NOT NULL) LOOP
DBMS_OUTPUT.PUT_LINE(names_in(l_index));
l_index := names_in.NEXT(l_index);
END LOOP;
This approach makes no assumptions about the contents of the collection. The names_in collection can be empty,densely filled, or sparse, and the program will still “do the right thing.” The key to this technique’s flexibility is the use of the NEXT method. This method returns
the next (highest) index value after the specified index value that is defined, ignoring (or, at least conceptually, skipping over) all undefined index values.You might then expect that I would
recommend that you always use this technique if you want to iterate through all the elements of a collection. Yet that is not the case. Suppose that when I wrote the show_names procedure, it was intended to be used to display the contents of a collection that was populated with a BULK
COLLECT statement. In such a case, the collection is always empty or sequentially
filled, starting from index value 1. The show_names procedure works properly and the code goes into production. Now suppose further that a year later, another developer is instructed to make a change to one of the programs that calls employees_mgr.show_names. The developer makes a mistake and deletes several of the elements in the collection that is passed to show_names. The collection is now sparsely filled, but it should not be. Still, show_names does its job without raising any errors. The net result is that show_names has, in effect,covered up an error.The bottom line is that if you are writing code to iterate through a collection and you know for certain that this collection should be sequentially filled,you should use
FOR indx IN 1 ..
if you also know that the collection is
always filled from index value 1 (as with
BULK COLLECT and MULTISET), or use
IF
FOR indx IN
if the lowest index value might be a value other than 1.
Source : Oracle Magazine September/October 2009
Sunday, July 26, 2009
Recyclebin in Oracle 10g
The recyclebin concept is introduced in oracle 10g onwards . So far we have been knowing that the DDL commands are auto commited and cannot be recovered as a normal user. But the oracle 10g onwards the recyclebin is introduced like we have in windows.
The user_recyclebin and dba_recyclebin data dictionaries can be used to view the deleted contents. Let's see the following example to understand this concept.
The user_recyclebin and dba_recyclebin data dictionaries can be used to view the deleted contents. Let's see the following example to understand this concept.
SQL> create table test
( id number,
name varchar2(30));
SQL> insert into test values ( 1,'name1');
SQL> select *
from test;
ID NAME
------------------
1 name1
Let's drop the table
SQL> drop table test;
Now this table goes into the recyclebin. (Here recyclebin is a synonym for user_recyclebin )
SQL> select *
from recyclebin;
OBJECT_NAME ORIGINAL_NAME DROP_TIME
----------------------------------------------------------
BIN$b5sG6wgurG3gQAnAoNwPOA==$0 TEST 2009-07-26:16:50:38
If you want to get back(restore) then
SQL> flashback table test to before drop; Now you can view the test table.
If you want to drop the table permenantly without going to the recyclebin then issue
SQL> drop table test purge;
Thursday, July 9, 2009
E-mailing the Oracle Report in PDF Format not in EPS
Once the oracle report is deployed at the customer site and the end user wants view the report and send the report as pdf format via e-mail.If we do not do the following setting then the end user will be sending the report as ".eps" format.
To send it as pdf format you just have to
Open the properties of the DESFORMAT under the System Parameters node and set the initial value as PDF. Now you would get the report e-mail format as "PDF".
To send it as pdf format you just have to
Open the properties of the DESFORMAT under the System Parameters node and set the initial value as PDF. Now you would get the report e-mail format as "PDF".
Thursday, July 2, 2009
Invoking the SEAM app through the client machine
We know that the seam application can be invoked through, the following url
http://localhost:8080/urapp
this would work fine if the jboss server and the client both are in the same machine..but if u wanna access it through another client u may access via
http://ip_address:8080/urapp
But this would not work unless if u don't do the following settings.
Step 1: Go to/bin
Eg : D:\jboss-eap-4.3\jboss-as\bin
Step 2: Copy the following in the notepad
run.bat -b 0.0.0.0
save it as "runjb.bat" at the bin folder.
Step 3: Create the short cut for runjb.bat.
Step4: Start the jbsos using the runjb.bat
Now ur client can access ur app via http://ip_address:8080/urapp the url.
http://localhost:8080/urapp
this would work fine if the jboss server and the client both are in the same machine..but if u wanna access it through another client u may access via
http://ip_address:8080/urapp
But this would not work unless if u don't do the following settings.
Step 1: Go to
Eg : D:\jboss-eap-4.3\jboss-as\bin
Step 2: Copy the following in the notepad
run.bat -b 0.0.0.0
save it as "runjb.bat" at the bin folder.
Step 3: Create the short cut for runjb.bat.
Step4: Start the jbsos using the runjb.bat
Now ur client can access ur app via http://ip_address:8080/urapp the url.
Tuesday, June 23, 2009
If u know "system" users' password then U know every users password in oracle 11g...
We all know that the fact all the users' passwords are encripted in the oracle database. And that could be decripted if you know the system users' password only .This is due to sys.user$ dectionary has to be queried to get the encripted password.Here we go..
Step3 : Download the password cracker
Step 1: Login as a system user.
SQL> conn system/manager@orcl
Step 2: Execute the following query.
SQL> SELECT name,password,spare4
FROM sys.user$
WHERE name ='CRS' ;
FROM sys.user$
WHERE name ='CRS' ;
Step3 : Download the password crackerStep4 : Fill the username,OLDSK3WLHASH(ie password) and SPARE4HASH(ie spare4) and press the start button.
Now you will get the exact password. Hope Oracle will come up with new technology where no one can decrypt at all. So guys be carefull with your DBAs.
Monday, June 15, 2009
Tuesday, June 9, 2009
How To Call a JAVA Program from PL/SQL?
Step1: Create the JAVA Class ( Here Factorial.java )
public class Factorial
{
public static int calcFactorial(int n)
{
if(n==1)
return 1;
else
return n * calcFactorial(n-1);
}
}
Step2: Load the JAVA Class into the DB ( Using the command prompt)
> loadjava -user scott/tiger@c920 E:\Factorial.java
Or
> loadjava -user scott/tiger@c920 E:\Factorial.class ( If it’s a class file)
Step3: Verify the Java source has been loaded or not by
SELECT *
FROM USER_OBJECTS
WHERE OBJECT_TYPE LIKE ‘JAVA%’;
You Could see the source code by
SELECT text
FROM USER_SOURCE
WHERE NAME=’ Factorial’;
Step4 : Create a Procedure or Function that could call the JAVA method
CREATE OR REPLACE FUNCTION calc_fact( p_no NUMBER)
RETURN NUMBER
AS
LANGUAGE JAVA
NAME ‘Factorial. calcFactorial (int) return int’ ;
public class Factorial
{
public static int calcFactorial(int n)
{
if(n==1)
return 1;
else
return n * calcFactorial(n-1);
}
}
Step2: Load the JAVA Class into the DB ( Using the command prompt)
> loadjava -user scott/tiger@c920 E:\Factorial.java
Or
> loadjava -user scott/tiger@c920 E:\Factorial.class ( If it’s a class file)
Step3: Verify the Java source has been loaded or not by
SELECT *
FROM USER_OBJECTS
WHERE OBJECT_TYPE LIKE ‘JAVA%’;
You Could see the source code by
SELECT text
FROM USER_SOURCE
WHERE NAME=’ Factorial’;
Step4 : Create a Procedure or Function that could call the JAVA method
CREATE OR REPLACE FUNCTION calc_fact( p_no NUMBER)
RETURN NUMBER
AS
LANGUAGE JAVA
NAME ‘Factorial. calcFactorial (int) return int’ ;
How to create Master-Detail(Sub Report) Report in Jasper Report?
Let's try to create a master-detail report. These reports are also called subreports.
Step1 : Create and populate the relevent master-detail tables.
Note: You have to create a parmeter "ID".
Step4 : Now you are done with the detail report.
Step5 : And then you have to send the primary key of master report(employee) to the sub report(address). So you need to pass the ID. Open the properties of the sub report and add the parameter as shown below.
Step1 : Create and populate the relevent master-detail tables.
SQL> CREATE TABLE EMPLOYEE
( ID NUMBER PRIMARY KEY,
FIRST_NAME CHAR(30),
LAST_NAME CHAR(30) );
( ID NUMBER PRIMARY KEY,
FIRST_NAME CHAR(30),
LAST_NAME CHAR(30) );
SQL> CREATE TABLE ADDRESS
(ID NUMBER,
STREET CHAR(30),
CITY CHAR(30),
STATE CHAR(10),
COUNTRY CHAR(30),
POSTAL_CODE CHAR(30));
(ID NUMBER,
STREET CHAR(30),
CITY CHAR(30),
STATE CHAR(10),
COUNTRY CHAR(30),
POSTAL_CODE CHAR(30));
SQL> ALTER TABLE ADDRESS
ADD FOREIGN KEY(ID) REFERENCES EMPLOYEE(ID);
ADD FOREIGN KEY(ID) REFERENCES EMPLOYEE(ID);
SQL> INSERT INTO EMPLOYEE VALUES (1,'King','Canber');
INSERT INTO EMPLOYEE VALUES (2,'Dehan','Duncan');
INSERT INTO EMPLOYEE VALUES (3,'Grant','Gibson');
INSERT INTO EMPLOYEE VALUES (2,'Dehan','Duncan');
INSERT INTO EMPLOYEE VALUES (3,'Grant','Gibson');
SQL> INSERT INTO ADDRESS VALUES ( 1,'Street1','City1','State1','Country1','Code1');
INSERT INTO ADDRESS VALUES ( 1,'Street12','City12','State12','Country12','Code12');
INSERT INTO ADDRESS VALUES ( 2,'Street2','City2','State2','Country2','Code2');
INSERT INTO ADDRESS VALUES ( 3,'Street3','City3','State3','Country3','Code3');
INSERT INTO ADDRESS VALUES ( 1,'Street12','City12','State12','Country12','Code12');
INSERT INTO ADDRESS VALUES ( 2,'Street2','City2','State2','Country2','Code2');
INSERT INTO ADDRESS VALUES ( 3,'Street3','City3','State3','Country3','Code3');
Step 2: Create the employee report .
Step3: Drag and drop the sub-report , you will be prompt the sub-report dialogue box.
proceed with "create new report" and then write the query as
select * from addresswhere id = $P!{ID}
Note: You have to create a parmeter "ID".
Step4 : Now you are done with the detail report.Step5 : And then you have to send the primary key of master report(employee) to the sub report(address). So you need to pass the ID. Open the properties of the sub report and add the parameter as shown below.
Step6 : Now you are done.
Thursday, June 4, 2009
How to create a pie chart in Jasper Reports?
Step1 : create a table and populate required records.
SQL> CREATE TABLE results
( party_name varchar2(30),
scored_percentage number );
SQL> INSERT INTO results VALUES ( 'UNP', 25);
SQL> INSERT INTO results VALUES ( UPFA, 60);
SQL> INSERT INTO results VALUES ( 'JVP', 5);
SQL> INSERT INTO results VALUES ( 'OTHER', 10);
Step2 : Open a new report in Jasper Report.
Step3 : Write the follwing sql query in the "report query"
select *
from results
Step4 : Drag and drop the "chart" on the summary area (i.e just after
the page footer ) and select the pie chart from the chart types.
Step5 : Right click on the chart and select "Chart Data"
set the Key expression as $F{PARTY_NAME}
Value expression as $F{SCORED_PERCENTAGE}
Label expression as
$F{PARTY_NAME}+" "+$F{SCORED_PERCENTAGE}+"%"
Step6 : Now You are done. and you will see the report preview as
Note : I've used iReport 3.1.4 here.
SQL> CREATE TABLE results
( party_name varchar2(30),
scored_percentage number );
SQL> INSERT INTO results VALUES ( 'UNP', 25);
SQL> INSERT INTO results VALUES ( UPFA, 60);
SQL> INSERT INTO results VALUES ( 'JVP', 5);
SQL> INSERT INTO results VALUES ( 'OTHER', 10);
Step2 : Open a new report in Jasper Report.
Step3 : Write the follwing sql query in the "report query"
select *
from results
Step4 : Drag and drop the "chart" on the summary area (i.e just after
the page footer ) and select the pie chart from the chart types.
Step5 : Right click on the chart and select "Chart Data"
set the Key expression as $F{PARTY_NAME}
Value expression as $F{SCORED_PERCENTAGE}
Label expression as
$F{PARTY_NAME}+" "+$F{SCORED_PERCENTAGE}+"%"
Step6 : Now You are done. and you will see the report preview as
Note : I've used iReport 3.1.4 here.
Subscribe to:
Posts (Atom)
