How to call oracle (PLSQL) Stored procedures from ireport designer?

[daad.madhusudan]

I have written following stored procedure to test procedure call from iReport designer.

 

Stored Procedure:

 

    CREATE OR REPLACE PROCEDURE test(cursor1 out sys_refcursor) IS

      BEGIN

        OPEN cursor1 for 

          select person_id,first_name

          from person

          where rownum < 5;

      END; 

 

 

In my query window I am using following line to call this procedure.

 

    {call test($P{cursor})}

 

where $P{cursor} is a parameter with class java.sql.ResultSet.

 

I have added ojdbc.jar file into my class path and connection is also successful.

 

When I run above statement for calling stored procedure it throws the error:

 

    Error: java.sql.SqlException:Invalid column type

 

Can someone tell me what I did wrong here? AM i supposed to add any file or make any changes in the call to stored procedure?

 

[daad.madhusudan]

The problem was in iReport field extractor. The solution for above problem is, we should analyse our stored procedure and create fields in iReport designer with the same name which are present in select clause of stored procedure.

 

After creating stored procedure, just drag and drop the fields on the report are and execute it. It will show the retrieved data.

 

This solution worked for me, so I am sharing it with you.

[raj.himaloy]

Use plsql insted of sql under <queryString language="plsql"> tag.

I mean use <queryString language="plsql"> insted of <queryString language="sql">.

Your Stored Procedure is perfect, and your parameter $P{cursor} is perfect. So it will work now.