JasperReport 6.2.2 with pl/sql

Jaspersoft Community 'Read-Only' as of July 8, 2022

Transition to New TIBCO Community Just Weeks Away

You can still search, review wiki content, and review discussions in read-only mode. Please email community@tibco.com with questions or issues requiring TIBCO review or response.

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.







mosl31's picture
Joined: Oct 26 2007 - 6:50am
Last seen: 8 months 2 days ago

4 Answers:

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.



lucianc's picture
Joined: Jul 17 2006 - 1:10am
Last seen: 4 days 2 min ago

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");

mosl31's picture
Joined: Oct 26 2007 - 6:50am
Last seen: 8 months 2 days ago

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?

lucianc's picture
Joined: Jul 17 2006 - 1:10am
Last seen: 4 days 2 min ago

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

mosl31's picture
Joined: Oct 26 2007 - 6:50am
Last seen: 8 months 2 days ago