Databases
StackOverflow2013
Postsdbo
POiReport 4.5.1 With JasperServer 4.5 with Oracle DB Stored Procedures using Ref Cursors-- CLOSED
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POiReport 4.5.1 With JasperServer 4.5 with Oracle DB Stored Procedures using Ref Cursors-- CLOSED
    primarykey
    data
    text
    <p>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 </p> <p>I did some digging and navigated my way thru JasperReports in the last couple of days and this is where I am so far.</p> <p>I use iReport designer to design my reports.</p> <p>Below My SQL Statements and procedure, I compiled and all is well in the Oracle SQL Developer editor.</p> <pre><code>-- 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; </code></pre> <p>I now went to the iReport designer to established a database connection and created a dataset to call my procedure as shown below</p> <pre><code>{call PUBLISHER_AND_BOOKS($P{P_PUBLISHER_ID}, $P{ORACLE_REF_CURSOR})} </code></pre> <p>When I clicked the Read Fields to get the list of fields from my procedure I am getting the following error message</p> <pre><code>Error: SQL problems: Invalid column type. </code></pre> <p>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 </p> <p>Now I am not able to connect or create a dataSet to proceed with my report design</p> <p>It will be of great help if any one can assist in resolving this issue so that I can proceed with the report design.</p> <p><strong><em>NOTE</em></strong><br> 1. I have Set the Query language in the Report Query to plsql<br> 2. Also tried with by Defining the Cursor param as java.sql.ResultSet --- <code>NO LUCK, same error</code><br> 3. I have conformed PlSqlQueryExecuter class exits in my classpath<br> 4. I have looked at this:<br> <a href="http://rajendratambat.blogspot.in/2011/10/calling-oracle-stored-procedure-in.html?showComment=1333264003761#c6148133813463786818" rel="nofollow">http://rajendratambat.blogspot.in/2011/10/calling-oracle-stored-procedure-in.html?showComment=1333264003761#c6148133813463786818</a> --- <code>NO LUCK, same issue</code> </p> <p>So can anyone have another solution where in I can continue use my Stored Procedures that uses sys_refcursor.</p> <p>Also a working tutorial link or blog will be a great help.</p> <p>Thanks in advance.</p> <p>Meeza</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USC1pher
    1. USMeeza S
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POiReport 4.5.1 With JasperServer 4.5 with Oracle DB Stored Procedures using Ref Cursors-- CLOSED
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POiReport 4.5.1 With JasperServer 4.5 with Oracle DB Stored Procedures using Ref Cursors-- CLOSED
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload