Dynamic SQL from Java scriplet causes ORA-00900: invalid SQL statement

[dario.romani]

Good day,

I am relatively new to Jasper Reports. I have encountered a problem which I cannot resolve. I hope someone can give me some guidance regarding cause and resolution.

The situation is as follows.

I am using Jasper Studio 6.8 to create a Jasper Report that will be called from a Java web application. The Java web application will call the report with several user-entered/selected parameters. The Jasper report will create a SQL SELECT statement based on the parameters passed to it from the Java application. The data required to fill the report will be pulled from an Oracle database, using a JDBC connector to execute the SQL select statement in the database.

In the user interface, some parameters are optional, while other parameters are mandator. As well, some parameters are user-entered, and some are multi-selectable form a drop-down list. Due to the complexity and variety of combinations of parameters passed the Jasper Report, The Jasper Report functions $P{}, $X{}, etc. are not sufficient to create the correct SQL statement.
Therefore I have created a Java Scriptlet that includes the logic required process the parameters and generate the required SQL Select statement string.

Everything works as expected except for the very last step. When the Jasper Report sends the SQL statement to the Oracle database, the database returns the following error:

net.sf.jasperreports.engine.JRException: net.sf.jasperreports.engine.JRException: Error executing SQL statement
.
.
Caused by: java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement

 

 In order to describe the use cases and diagnose the problem, I have reduced the complexity of the SQL statement in the description below.

 

Test 1.

Use the Jasper Studio “Dataset and Query Dialog Box” to run the report with the following SQL statement:

SELECT tenure_number_id FROM TENURE WHERE  1=1  AND tenure_number_id = '102161'

The report runs successfully.

 

Test 2.

Create a Java scriplet which includes the following method:

public String getSqlStatement() throws JRScriptletException
    {
return "SELECT tenure_number_id FROM TENURE WHERE  1=1  AND tenure_number_id = '102161'";
    }

Modify the JRXML file to include the following statement:

<queryString>  <![CDATA[$P!{MyScriptlet_SCRIPTLET}.getSqlStatement()]]>   </queryString>

Everything compiles without errors…but the database returns “java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement”

 

Some additional information:

In my Java scriptlet, I have tried creating the SQL Select statement string both with and without the “;” terminator. The same error is retuned either way.

The SQL Select statement executes OK when I run it through SQL Developer or Sql*Plus.

My DBA has trapped the SQL statement sent to the database by the JDBC connector. The SQL Statement is getting to the database and is an exactly same as the SQL Select string in the scriptlet.

My DBA has taken the same SQL Select string she monitored in the database and run it using her SQL Developer and SQL*Plus on the database server. It executes OK.  

 

So in summary:

The SQL Select statement the runs OK through SQL Developer and SQL*Plus both remotely and on the database server.

The report runs and fills OK when I enter this SQL Select statement into the Jasper Studio “Dataset and Query Dialog Box”.

When I generate the SQL Select statement using my Java Scriptlet and embed the SQL statement into the JRXML file using
<queryString>   <![CDATA[$P!{MyScriptlet_SCRIPTLET}.getSqlStatement()]]> </queryString>
The database returns “java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement”

 I would greatly appreciate if someone could help me with understanding the cause of this problem, and how to fix it.

Thank you very much.