BIRT Report - Use Oracle Stored Procedure as a Dataset |IT Tech Talk

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

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

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

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;

Note: BIRT only consumes Stored Procedure and Stored Procedure must have an OUT parameter whose type is SYS_REFCURSOR

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: