We have an application that was developed using Java 7, Spring Framework 3.1.2, MyBatis 3.1.1, MyBatis Spring 1.2.2, JasperReports 6.1.0, etc. The application runs on Tomcat 7.0. 35, uses the Tomcat connection pool to connect to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit. JRE version 1.7.0_09-b05. The application runs on the RHEL 6.5 server.
From time to time, a problem arises, which then disappears after a few hours (3-6 hours), and sometimes after a couple of days (1-3 days). When the web service that creates the report, the application calls a method from MapBaris Mapper, which returns a List <MonthlyReport>, then the application passes this list to the JasperReport mechanism, which creates a report in the file system, and returns the file stream in response (MTOM) in the final application . The problem is that periodically when you try to run a query in the database to create a report, the following exception occurs:
ERROR 2015-07-23 11:44:03,012 [http-bio-8280-exec-2] exception type: org.springframework.jdbc.UncategorizedSQLException
ERROR 2015-07-23 11:44:03,012 [http-bio-8280-exec-2] exception message:
### Error querying database. Cause: java.sql.SQLException: ORA-12801: error signaled in parallel query server P010
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
This is the request:
<select id="getMonthlyReportData" resultType="MonthlyReport" parameterType="map">
<![CDATA[
SELECT r.bank_name bankName,
r.user_name userName,
r.descr userDescription,
CASE WHEN r.parent_bank_id IS NULL THEN 1 ELSE 0 END isParentBankInt,
COUNT (CASE WHEN r.p_type NOT IN ('SS', 'DR') THEN 1 ELSE NULL END) postpaidPaymentCount,
SUM (CASE WHEN r.p_type NOT IN ('SS', 'DR') THEN r.amount ELSE 0 END) postpaidPaymentAmount,
COUNT (CASE WHEN r.p_type = 'SS' THEN 1 ELSE NULL END) prepaidPaymentCount,
SUM (CASE WHEN r.p_type = 'SS' THEN r.amount ELSE 0 END) prepaidPaymentAmount,
COUNT (CASE WHEN r.p_type = 'DR' THEN 1 ELSE NULL END) depositRepayCount,
SUM (CASE WHEN r.p_type = 'DR' THEN r.amount ELSE 0 END) depositRepayAmount
FROM (SELECT q.queue_id,
q.amount,
q.p_type,
q.user_name,
q.action_date,
b.parent_bank_id,
U.descr,
b.bank_name
FROM rbp_queue q, rbp_all_banks b, rbp_users U
WHERE q.user_name = U.user_name
AND U.working_bank_id = b.bank_id
AND q.err_code = -1000000
AND q.action_date BETWEEN TO_DATE (
AND TO_DATE (
AND U.working_bank_id IN
(SELECT bank_id
FROM rbp_all_banks
WHERE bank_id =
UNION
SELECT qa.queue_id,
qa.amount,
qa.p_type,
qa.user_name,
qa.action_date,
ba.parent_bank_id,
Ua.descr,
ba.bank_name
FROM sysadm.rbp_queue_arch@azis_archdb qa,
rbp_all_banks ba,
rbp_users Ua
WHERE qa.user_name = Ua.user_name
AND Ua.working_bank_id = ba.bank_id
AND qa.err_code = -1000000
AND qa.action_date BETWEEN TO_DATE (
AND TO_DATE (
AND Ua.working_bank_id IN
(SELECT bank_id
FROM rbp_all_banks
WHERE bank_id =
GROUP BY r.bank_name,
r.user_name,
r.descr,
CASE WHEN r.parent_bank_id IS NULL THEN 1 ELSE 0 END
ORDER BY isParentBankInt DESC, bankName, userName
]]>
, Oracle , . , TO_DATE.
MyBatis:
DEBUG 2015-07-22 15:10:52,720 [http-apr-8281-exec-2] ooo Using Connection [ProxyConnection[PooledConnection[oracle.jdbc.driver.T4CConnection@344482ac]]]
DEBUG 2015-07-22 15:10:52,724 [http-apr-8281-exec-2] ==> Preparing: SELECT r.bank_name bankName, r.user_name userName, r.descr userDescription, ...
DEBUG 2015-07-22 15:10:52,725 [http-apr-8281-exec-2] ==> Parameters: 20150601000000(String), 20150621235959(String), 31(Integer), 31(Integer), 20150601000000(String), 20150621235959(String), 31(Integer), 31(Integer)
, MyBatis ( ) , Oracle, , "" ( null, ).
SELECT TO_DATE ('', 'YYYYMMDDHH24MISS') FROM dual
TOAD null,
SELECT TO_DATE ('""', 'YYYYMMDDHH24MISS') FROM dual
: ORA-01841: () -4713 +9999, 0.
, , ( ) , .
Oracle :
Bind#0
oacdty=01 mxl=32(28) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000010 frm=02 csi=2000 siz=224 off=0
kxsbbbfp=9fffffffbf330908 bln=32 avl=28 flg=05
value=""
Bind#1
oacdty=01 mxl=32(28) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000010 frm=02 csi=2000 siz=0 off=32
kxsbbbfp=9fffffffbf330928 bln=32 avl=28 flg=01
value=""
Bind#2
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000000 frm=02 csi=2000 siz=0 off=64
kxsbbbfp=9fffffffbf330948 bln=22 avl=02 flg=01
value=31
Bind#3
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000000 frm=02 csi=2000 siz=0 off=88
kxsbbbfp=9fffffffbf330960 bln=22 avl=02 flg=01
value=31
Bind#4
oacdty=01 mxl=32(28) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000010 frm=02 csi=2000 siz=0 off=112
kxsbbbfp=9fffffffbf330978 bln=32 avl=28 flg=01
value=""
Bind#5
oacdty=01 mxl=32(28) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000010 frm=02 csi=2000 siz=0 off=144
kxsbbbfp=9fffffffbf330998 bln=32 avl=28 flg=01
value=""
Bind#6
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000000 frm=02 csi=2000 siz=0 off=176
kxsbbbfp=9fffffffbf3309b8 bln=22 avl=02 flg=01
value=31
Bind#7
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000000 frm=02 csi=2000 siz=0 off=200
kxsbbbfp=9fffffffbf3309d0 bln=22 avl=02 flg=01
value=31
Oracle, :
Bind#0
oacdty=01 mxl=32(14) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000010 frm=01 csi=31 siz=224 off=0
kxsbbbfp=9fffffffbf323e50 bln=32 avl=14 flg=05
value="20150601000000"
Bind#1
oacdty=01 mxl=32(14) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000010 frm=01 csi=31 siz=0 off=32
kxsbbbfp=9fffffffbf323e70 bln=32 avl=14 flg=01
value="20150621235959"
Bind#2
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000000 frm=01 csi=31 siz=0 off=64
kxsbbbfp=9fffffffbf323e90 bln=22 avl=02 flg=01
value=31
Bind#3
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000000 frm=01 csi=31 siz=0 off=88
kxsbbbfp=9fffffffbf323ea8 bln=22 avl=02 flg=01
value=31
Bind#4
oacdty=01 mxl=32(14) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000010 frm=01 csi=31 siz=0 off=112
kxsbbbfp=9fffffffbf323ec0 bln=32 avl=14 flg=01
value="20150601000000"
Bind#5
oacdty=01 mxl=32(14) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000010 frm=01 csi=31 siz=0 off=144
kxsbbbfp=9fffffffbf323ee0 bln=32 avl=14 flg=01
value="20150621235959"
Bind#6
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000000 frm=01 csi=31 siz=0 off=176
kxsbbbfp=9fffffffbf323f00 bln=22 avl=02 flg=01
value=31
Bind#7
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000000 frm=01 csi=31 siz=0 off=200
kxsbbbfp=9fffffffbf323f18 bln=22 avl=02 flg=01
value=31
: 0, 1, 4, 5. value = "".
MyBatis, , JasperReports (monthReport.jasper), JasperReports . JasperReports . MyBatis , . Oracle (ORA-01841: () -4713 +9999, 0) . :
ERROR 2015-06-11 08:57:17,559 [http-apr-8280-exec-9] Fill 1: exception
net.sf.jasperreports.engine.JRException: Error executing SQL statement for : monthlyReport_New32Dataset321_1432644594876_272524
at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:240)
at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1087)
at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:668)
at net.sf.jasperreports.engine.fill.JRBaseFiller.setParameters(JRBaseFiller.java:1281)
at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:900)
at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:845)
at net.sf.jasperreports.engine.fill.JRFillSubreport.fillSubreport(JRFillSubreport.java:651)
at net.sf.jasperreports.engine.fill.JRSubreportRunnable.run(JRSubreportRunnable.java:59)
at net.sf.jasperreports.engine.fill.AbstractThreadSubreportRunner.run(AbstractThreadSubreportRunner.java:203)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603)
at java.lang.Thread.run(Thread.java:722)
Caused by: java.sql.SQLException: ORA-12801: error signaled in parallel query server P002
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:884)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1167)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1289)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3584)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3628)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1493)
at sun.reflect.GeneratedMethodAccessor349.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:601)
at org.apache.tomcat.jdbc.pool.interceptor.AbstractQueryReport$StatementProxy.invoke(AbstractQueryReport.java:235)
at $Proxy99.executeQuery(Unknown Source)
at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:233)
... 11 more
.