Jump to content

JasperReport 6.2.2 with pl/sql


Recommended Posts

I know this has been asked many times, but the syntax has changed in 6.2.2.

I have a report that hits Oracle with a pl/sql procedures and runs fine in the Eclipse Report Designer, so the report works.

When I try to run it as a Java Web Application i get the famous error:

net.sf.jasperreports.engine.JRException: net.sf.jasperreports.engine.JRRuntimeException: No query executer factory registered for the 'plsql' language.

Many places on the web I see to add the lines

jasperReport.setProperty( "net.sf.jasperreports.query.executer.factory.plsql"  
JRProperties.setProperty( JRQueryExecuterFactory.QUERY_EXECUTER_FACTORY_PREFIX+"plsql"


However these do not work for 6.2.2, there is no class called com.jaspersoft.jrx.query.PlSqlQueryExecuterFactory


JRProperties has been dropped , and  JRQueryExecuterFactory has been depricated.

Also we don't need jasperreports-extensions-3.5.3.jar anymore (do we?) as there is a class called net.sf.jasperreports.engine.query.PlSqlQueryExecuterFactory

I tried this below...

jasperpreport.setProperty( "net.sf.jasperreports.query.executer.factory.plsql"  
             JasperReportsContext jasperReportsContext = DefaultJasperReportsContext.getInstance();
             JRPropertiesUtil jrPropertiesUtil = JRPropertiesUtil.getInstance(jasperReportsContext);
            jrPropertiesUtil.setProperty(QueryExecuterFactory.QUERY_EXECUTER_FACTORY_PREFIX+"plsql", "net.sf.jasperreports.engine.query.PlSqlQueryExecuterFactory");


but I get an errror

error preparing statement for executing the report query:


    at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createStatement(JRJdbcQueryExecuter.java:478)

at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:295)
    at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1129)
    at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:696)
Caused by: java.sql.SQLException: Invalid column type: 2000
    at oracle.jdbc.driver.OracleStatement.getInternalType(OracleStatement.java:4369)    at net.sf.jasperreports.engine.fill.BaseReportFiller.setParameters(BaseReportFiller.java:437)





Thanks for any help.







Link to comment
Share on other sites

  • Replies 4
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

With 6.2.2 you don't need to configure the PL/SQL query executer in any way, it is there by default.

Regarding the Invalid column type: 2000 error, please post the the report query and parameters, the procedure declaration and the DB and JDBC driver versions.



Link to comment
Share on other sites

Thanks for looking at this. When I compiled as a jar it worked but when I added
the maven-war-plugin to deploy a war to tomcat I started having these problems.
It all works fine in the report designer, I can "data preview" the pl/sql output and the war file compiles without error.

<parameter name="GN_CONTRACT_ID" class="java.lang.Integer" isForPrompting="false">
    <parameter name="GN_MONTH" class="java.lang.String" isForPrompting="false">
    <parameter name="GN_YEAR" class="java.lang.Integer" isForPrompting="false">
    <parameter name="ORACLE_REF_CURSOR" class="java.sql.ResultSet" isForPrompting="false"/>
    <parameter name="GN_RPTNUM" class="java.lang.String" isForPrompting="false">
        <parameterDescription><![CDATA[invoice Number]]></parameterDescription>
    <queryString language="plsql">


I am using ojdbc7.jar from the oracle 12.1 instant client.
I am using a oracle database

create or replace
    open NEW_HOURS for
    select b.work_dates WORK_DATES,b.sumhours SUMHOURS,a.rate RATE,RATE * SUMHOURS DOLLAR_TOTAL
    from timesheet.contracts a,(
    select to_char(startweek, 'Month DD, YYYY') || ' - ' || to_char(endweek, 'Month DD, YYYY') WORK_DATES,SUMHOURS,CONTRACT_ID
    from (
    select contract_id,sum(hrs) sumhours,min(work_date) startweek,max(work_date) endweek,week_num
    from (
    select contract_id,hrs,trunc( work_date ,'D') start_date,work_date,
    ceil(( 7+(trunc( work_date ,'D')-trunc(work_date,'MON')) )/7) || to_char(work_date,'MONYYYY') week_num
     from timesheet.hours_day_id
    where contract_id = P_CONTRACT_ID
    group by contract_id, week_num
    order by startweek
    where to_char(startweek,'MON') = P_MONTH
    and to_char(startweek,'YYYY')= P_YEAR ) b
    where a.contract_id = b.contract_id
    order by work_dates;



java.sql.Connection jasperConnect = trackerService.getConnect();
LOGGER.info("Finding the report");
JasperReport reporte = (JasperReport) JRLoader.loadObjectFromFile(getServletContext().getRealPath("rpt/invoice.jasper"));
     / *I had to add these lines */
 JasperReportsContext jasperReportsContext = DefaultJasperReportsContext.getInstance();
 JRPropertiesUtil jrPropertiesUtil = JRPropertiesUtil.getInstance(jasperReportsContext);
 jrPropertiesUtil.setProperty("net.sf.jasperreports.query.executer.factory.plsql", "net.sf.jasperreports.engine.query.PlSqlQueryExecuterFactory");
            Map<String, Object> parametros = new HashMap<String, Object>();
            parametros.put("GN_RPTNUM", request.getParameter("reportnumber"));
            parametros.put("GN_YEAR", Integer.valueOf(request.getParameter("reportyear")));
            parametros.put("GN_MONTH", request.getParameter("reportmonth"));                               
            parametros.put("GN_CONTRACT_ID", Integer.valueOf(request.getParameter("reportcontractid")));
            JasperPrint jasperPrint = JasperFillManager.fillReport(reporte, parametros,jasperConnect);

            LOGGER.info("Sending the file to pdf");

Link to comment
Share on other sites

The line numbers from the stacktrace that you posted (at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createStatement(JRJdbcQueryExecuter.java:478) .. at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:295)) do not correspond to the 6.2.2 sources.

Could you confirm that you are using a JasperReports 6.2.2 jar downloaded from sourceforge.net or via maven?

Link to comment
Share on other sites

That was it. I found a older version of Jasper (6.2.0) in one of the eclipse temp directories that I had put in my pom.xml by mistake a while ago and eclipse did not clean  out.  I deleted it and  all working now.

Thanks again for your help

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...