iReport 4.5.1 With JasperServer 4.5 with Oracle DB Stored Procedures using Ref Cursors— CLOSED

Question

I am new to JasperReports and iReport Designer. My Client want to migrate/develop all their reports using JasperReports technology. NOTE: I don't have java experience

I did some digging and navigated my way thru JasperReports in the last couple of days and this is where I am so far.

I use iReport designer to design my reports.

Below My SQL Statements and procedure, I compiled and all is well in the Oracle SQL Developer editor.

-- Table 1: Books
CREATE TABLE BOOKS
(
    BOOK_ID NUMBER(10) PRIMARY KEY,
    BOOK_NAME VARCHAR2(50),
    AUTHOR_NAME VARCHAR2(50),
    BOOK_ISBN VARCHAR2(10),
    PRICE NUMBER(10,2),
     PUBLISHER_ID NUMBER(10)
);

-- Records for Books Table
INSERT INTO BOOKS (BOOK_ID, BOOK_NAME, AUTHOR_NAME, BOOK_ISBN, PRICE, PUBLISHER_ID) 
     VALUES (1000000001, 'I AM NOBODY', 'PERFECTION', '1234-1234', '10.50', 5000000001);

INSERT INTO BOOKS (BOOK_ID, BOOK_NAME, AUTHOR_NAME, BOOK_ISBN, PRICE, PUBLISHER_ID) 
     VALUES (1000000002, 'NOBODY IS PERFECT', 'PERFECTION', '1234-1234', '40.50', 5000000001);

INSERT INTO BOOKS (BOOK_ID, BOOK_NAME, AUTHOR_NAME, BOOK_ISBN, PRICE, PUBLISHER_ID) 
     VALUES (1000000003, 'I AM PERFECT', 'PERFECTION', '1234-1234', '50.50',5000000001);

INSERT INTO BOOKS (BOOK_ID, BOOK_NAME, AUTHOR_NAME, BOOK_ISBN, PRICE, PUBLISHER_ID) 
     VALUES (1000000004, 'NOBODY IS PERFECT', 'IM PERFECT', '1234-4321', '60.00',5000000002);

INSERT INTO BOOKS (BOOK_ID, BOOK_NAME, AUTHOR_NAME, BOOK_ISBN, PRICE, PUBLISHER_ID) 
     VALUES (1000000005, 'PERFECTION', 'IM PERFECT', '1234-4321', '20.00',5000000002);

INSERT INTO BOOKS (BOOK_ID, BOOK_NAME, AUTHOR_NAME, BOOK_ISBN, PRICE, PUBLISHER_ID) 
     VALUES (1000000006, 'NOBODY', 'IM PERFECT', '1234-4321', '45.50',5000000002);

-- Table 2: Publisher
CREATE TABLE PUBLISHER
(
    PUBLISHER_ID NUMBER(10) PRIMARY KEY,
    PUBLISHER_NAME VARCHAR2(50),
    ADDRESS VARCHAR2(100),
    STREET VARCHAR2(100),
     CITY VARCHAR2(100),
    STATE VARCHAR2(40),
    COUNTRY VARCHAR2(100),
    ZIP VARCHAR2(20)
);

-- Records for Publisher Table
INSERT INTO PUBLISHER (PUBLISHER_ID, PUBLISHER_NAME, ADDRESS, STREET, CITY, STATE, COUNTRY, ZIP) 
     VALUES(5000000001, 'MG GRAW HILL', '1212', 'AVE OF THE STARTS','LOS ANGELES', 'CALIFORNIA', 'USA', '300001');

INSERT INTO PUBLISHER (PUBLISHER_ID, PUBLISHER_NAME, ADDRESS, STREET, CITY, STATE, COUNTRY, ZIP) 
     VALUES(5000000002, 'TATA MG GRAW HILL', '12', 'STARTS AVE','CORONA', 'CALIFORNIA', 'USA', '300010');

-- Alter table to make Publisher Id as foreign key in the Books table
ALTER TABLE BOOKS ADD 
CONSTRAINT FK_BOOKS_PUBLISHER_ID
FOREIGN KEY(PUBLISHER_ID)
REFERENCES PUBLISHER(PUBLISHER_ID);

-- Procedure to take publisher id as input parameter and list values from books table along with the publisher name
CREATE OR REPLACE PROCEDURE PUBLISHER_AND_BOOKS (P_PUBLISHER_ID IN NUMBER, PUBLISHER_CUR OUT SYS_REFCURSOR) IS
 BEGIN
    OPEN PUBLISHER_CUR FOR
    SELECT 
        BOOK_ID,     
        BOOK_NAME, 
        AUTHOR_NAME,  
        BOOK_ISBN, 
        PRICE,        
        PUBLISHER_NAME 
    FROM 
        BOOKS, 
        PUBLISHER 
    WHERE 
        BOOKS.PUBLISHER_ID = PUBLISHER.PUBLISHER_ID
    AND 
        PUBLISHER.PUBLISHER_ID = P_PUBLISHER_ID;
END; 

-- Granting execution rights for the procedure
 GRANT EXECUTE ON PUBLISHER_AND_BOOKS TO PUBLIC;

-- Description of the procedure
DESC PUBLISHER_AND_BOOKS;

I now went to the iReport designer to established a database connection and created a dataset to call my procedure as shown below

{call PUBLISHER_AND_BOOKS($P{P_PUBLISHER_ID}, $P{ORACLE_REF_CURSOR})}

When I clicked the Read Fields to get the list of fields from my procedure I am getting the following error message

Error: SQL problems: Invalid column type.

So I proceeded with by clicking ok and add the fields manually to the reprot and when I previewed the report I got an error message. So I checked my DataSource / DataSet by right clicking the report name and view query it was blank so I gave the procedure name as mentioned above and when clicked next I got the same error message

Now I am not able to connect or create a dataSet to proceed with my report design

It will be of great help if any one can assist in resolving this issue so that I can proceed with the report design.

NOTE
1. I have Set the Query language in the Report Query to plsql
2. Also tried with by Defining the Cursor param as java.sql.ResultSet --- NO LUCK, same error
3. I have conformed PlSqlQueryExecuter class exits in my classpath
4. I have looked at this:
http://rajendratambat.blogspot.in/2011/10/calling-oracle-stored-procedure-in.html?showComment=1333264003761#c6148133813463786818 --- NO LUCK, same issue

So can anyone have another solution where in I can continue use my Stored Procedures that uses sys_refcursor.

Also a working tutorial link or blog will be a great help.

Thanks in advance.

Meeza

Meeza S Meeza S · Accepted Answer · 2012-04-13T18:03:33

After several trial and error and some constructive emails back and forth with RajendraTambat... I figured out how to call a stored procedures with in JasperReport iReport designer... All the steps are captured in detail and available in the below link hope this helps...

http://meezageekyside.blogspot.com/#!/2012/04/jasper-reports-ireport-45-using-oracle.html

iReport 4.5.1 With JasperServer 4.5 with Oracle DB Stored Procedures using Ref Cursors— CLOSED

1 Answers