Make a call to Oracle Stored Procedure (PL/SQL) from iReport 3.5

[vikramrao]

Hi,

 

 

 

 

Make a call to Oracle Stored Procedure (PL/SQL) from iReport 3.5 (or jrxml)
and populate the results in iReport.

Key points to remember:
1. cursor name is state_cursor from the stored procedure
2. with the same name state_cursor one parameter should be declared  and
   Parameter Class --> java.sql.ResultSet in iReport,
   be sure that the 'Use as a Prompt option is not selected.
3. {call test_procedure($P{state_cursor})}
4. create STATE Field manuvally from iReport (from the procedure we are selecting STATE only)
   with the same name one field should be declared. drag and drop it in detail section
   java.lang.String -- Expression Class

create or replace
PROCEDURE TEST_PROCEDURE( state_cursor OUT SYS_REFCURSOR) AS
BEGIN
   OPEN state_cursor FOR
   SELECT STATE FROM COUNTRY_STATENAMES;
END TEST_PROCEDURE;
 

Thanks,

Vikram 

Code:

create or replacePROCEDURE TEST_PROCEDURE( state_cursor OUT SYS_REFCURSOR) ASBEGIN   OPEN state_cursor FOR   SELECT STATE FROM COUNTRY_STATENAMES;END TEST_PROCEDURE;