In this tutorial, we are going to show you how to use Oracle Stored Procedure as a Dataset in Birt Report.
I. Steps with Oracle Database
1. Prepare a table "EMP" in Oracle to store sample data
Code
1 2 3 4 5 6 7 8 9 10 11 | CREATE TABLE emp ( empno NUMBER (4, 0) NOT NULL, ename VARCHAR2 (10 BYTE), job VARCHAR2 (9 BYTE), mgr NUMBER (4, 0), hiredate DATE, sal NUMBER (7, 2), comm NUMBER (7, 2), deptno NUMBER (2, 0) ) |
2. Insert some data into table "EMP"
Code
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | INSERT INTO emp VALUES(7369,'SMITH','CLERK',7902,TO_DATE('1980-12-17 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),800,NULL,20); INSERT INTO emp VALUES(7499,'ALLEN','SALESMAN',7698,TO_DATE('1981-02-20 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),1600,300,30); INSERT INTO emp VALUES(7521,'WARD','SALESMAN',7698,TO_DATE('1981-02-22 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),1250,500,30); INSERT INTO emp VALUES(7566,'JONES','MANAGER',7839,TO_DATE('1981-04-02 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),2975,NULL,20); INSERT INTO emp VALUES(7654,'MARTIN','SALESMAN',7698,TO_DATE('1981-09-28 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),1250,1400,30); INSERT INTO emp VALUES(7698,'BLAKE','MANAGER',7839,TO_DATE('1981-05-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),2850,NULL,30); INSERT INTO emp VALUES(7782,'CLARK','MANAGER',7839,TO_DATE('1981-06-09 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),2450,NULL,10); INSERT INTO emp VALUES(7788,'SCOTT','ANALYST',7566,TO_DATE('1982-12-09 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),3000,NULL,20); INSERT INTO emp VALUES(7839,'KING','PRESIDENT',NULL,TO_DATE('1981-11-17 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),5000,NULL,10); INSERT INTO emp VALUES(7844,'TURNER','SALESMAN',7698,TO_DATE('1981-09-08 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),1500,0,30); INSERT INTO emp VALUES(7876,'ADAMS','CLERK',7788,TO_DATE('1983-01-12 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),1100,NULL,20); INSERT INTO emp VALUES(7900,'JAMES','CLERK',7698,TO_DATE('1981-12-03 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),950,NULL,30); INSERT INTO emp VALUES(7902,'FORD','ANALYST',7566,TO_DATE('1981-12-03 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),3000,NULL,20); INSERT INTO emp VALUES(7934,'MILLER','CLERK',7782,TO_DATE('1982-01-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),1300,NULL,10); COMMIT; |
3. Create a stored procedure
Code
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | CREATE OR REPLACE PROCEDURE getempbydeptno (p_deptno VARCHAR2, p_refcur OUT SYS_REFCURSOR) IS BEGIN IF (UPPER (p_deptno) = 'ALL') THEN OPEN p_refcur FOR SELECT * FROM emp; ELSE OPEN p_refcur FOR SELECT * FROM emp WHERE deptno = p_deptno; END IF; END; |
II. Steps with Eclipse BIRT Report
1. You must create a Data Source to connect with Oracle Database
2. Create a new Data Set using Oracle Stored Procedure created above
See images below:
3. After we have Data Set we can do anything.
0 comments:
Post a Comment