Can\'t run a Report using an Oracle Stored Procedure and Multiple Parameters

[kreigv]

Hi,
I am using IReport 4.1.2 and would like to run a report using a stored procedure with multiple input parameters and on OUT parameter returning a result set...I have been successful running a stored proc with ONE ref cursor out parameter ...
I have added one IN parameter...and this is how I am calling the procedure:

{call PKG_QUERY_MODULE.Prc_Test_Select_Users_By_FName($P{pin_firstname}$P{ORACLE_REF_CURSOR}) }

It compiles fine....but I am getting the following errors (full error at end):
- error filling the Report
- bind variable does not exist
- print not filled

Is there anything wrong with my syntax here?   Does anyone know what could be the problem….I have been struggling with this all day....

Thank you in advance,
Vivian

Error filling print... Error executing SQL statement for : report5
net.sf.jasperreports.engine.JRException: Error executing SQL statement for : report5
    at com.jaspersoft.jrx.query.PlSqlQueryExecuter.createDatasource(PlSqlQueryExecuter.java:143)
    at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:741)
    at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:639)
    at net.sf.jasperreports.engine.fill.JRBaseFiller.setParameters(JRBaseFiller.java:1238)
    at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:869)
    at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:813)
    at net.sf.jasperreports.engine.fill.JRFiller.fillReport(JRFiller.java:58)
    at net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:417)
    at net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:247)
    at com.jaspersoft.ireport.designer.compiler.IReportCompiler.run(IReportCompiler.java:878)
    at org.openide.util.RequestProcessor$Task.run(RequestProcessor.java:572)
    at org.openide.util.RequestProcessor$Processor.run(RequestProcessor.java:997)
Caused by: java.sql.SQLException: ORA-01006: bind variable does not exist
 
    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
    at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
    at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:573)
    at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891)
    at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1093)
    at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2047)
    at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1940)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2709)
    at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:589)
    at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:656)
    at com.jaspersoft.jrx.query.PlSqlQueryExecuter.createDatasource(PlSqlQueryExecuter.java:128)
    ... 11 more
Print not filled. Try to use an EmptyDataSource...
 

[lknueve]

If the parameters are in order, you shouldn't have to put their name, just the parameter data, try the following:

{call PKG_QUERY_MODULE.Prc_Test_Select_Users_By_FName($P{ORACLE_REF_CURSOR}) }

 

if for some reason, they're not in order, don't you need an = between the name and the data? or at least a space?

{call PKG_QUERY_MODULE.Prc_Test_Select_Users_By_FName($P{pin_firstname}=$P{ORACLE_REF_CURSOR}) }

[lknueve]

been a long time since I did Oracle, but if not = then := ??

[kreigv]

HI,

Thanks for the help !!! 

My problem was actually that I missed the comma between the two parameters. So it should be like this...the first param is an IN param and the second is an OUT param...an oracle ref cursor result set.  It works now !!!!!!

{call PKG_QUERY_MODULE.Prc_Test_Select_Users_By_FName($P{pin_firstname},$P{ORACLE_REF_CURSOR}) }

I thought I tried this already...but I think it was when I had switched the order of the parameters..trying to get it to work and I had them in the wrong order...All parameters and fields need to be manually added to the I-Report...they don't get added automatically...so the JRXML  looks like this.  The ORACLE_REF_CURSOR is a built-in parameter ...and you get this from the list of built-in parameters if the PLSQLQueryExecutor is correctly set up.

<?xml version="1.0" encoding="UTF-8"?>
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="report5" language="groovy" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20">
 <property name="ireport.zoom" value="1.0"/>
 <property name="ireport.x" value="0"/>
 <property name="ireport.y" value="0"/>
 <parameter name="pin_firstname" class="java.lang.String">
  <defaultValueExpression><![CDATA[new String()]]></defaultValueExpression>
 </parameter>
 <queryString language="plsql">
  <![CDATA[{call PKG_QUERY.Prc_Test_Select_Users_By_FName($P{pin_firstname},$P{ORACLE_REF_CURSOR}) }]]>
 </queryString>
 <field name="FirstName" class="java.lang.String"/>
 <field name="LastName" class="java.lang.String"/>
 <field name="EmailAddress" class="java.lang.String"/>
 <background>

 

[kashif.spam]

Hi

Viviank,

 

Can you please give your source code of stored procedure by which you running ireport. you can post source here or mail me on my personal id which is given below

 

Thanks

Kashif

kashif.spam@gmail.com