Problem Description:

Running a query in Jaspersoft Studio (JSS) against ORACLE database causes an exception:

Caused by: java.sql.SQLSyntaxErrorException: [TibcoSoftwareInc][Oracle JDBC Driver][Oracle]ORA-00911: invalid character 

Solution:

The issue is likely caused by running a query with semicolon ';' at the end. While such query may run fine directly agains the database, it will cause issues in JSS. Here is an example query which caused such issue:

select StartDate, EndDate from (WITH My_DateRange AS ( 
SELECT 
from_tz(to_timestamp('2016-08-15 00:00', 'YYYY-MM-DD HH24:MI') , 'America/New_York') at time zone 'UTC' as StartDate, 
from_tz(to_timestamp('2017-01-16 00:00', 'YYYY-MM-DD HH24:MI') , 'America/New_York') at time zone 'UTC' as EndDate 
FROM DUAL 
) 
SELECT StartDate, EndDate from My_DateRange 
);

select StartDate, EndDate from (WITH CTE_DateRange AS ( 
SELECT 
from_tz(to_timestamp('2016-08-15 00:00', 'YYYY-MM-DD HH24:MI') , 'America/New_York') at time zone 'UTC' as StartDate, 
from_tz(to_timestamp('2017-01-16 00:00', 'YYYY-MM-DD HH24:MI') , 'America/New_York') at time zone 'UTC' as EndDate 
FROM DUAL 
) 
SELECT StartDate, EndDate from CTE_DateRange 
)