Java – changes the value of the binding parameter when calling the query in the Oracle database.
We use Java 7 and spring framework 3.1 in our company 2,MyBatis 3.1. 1,MyBatis Spring 1.2. 2,JasperReports 6.1. 0, etc The application is in Tomcat 7.0 35, using Tomcat connection pool and Oracle Database 10g enterprise version 10.2 0.4. Connection for version 0 – 64 bit JRE version 1.7 0_ 09-b05. The application is applicable to RHEL server 6.5
Sometimes a problem occurs and then disappears after a few hours (3-6 hours), sometimes a few days (1-3 days) When creating a report web service, the application calls the mapper from mybatis, which returns the list and lt method; MonthlyReport> ;, The application then passes this list to the report created on the file system of jasperreport's engine, and at the end of the response file, the application returns the stream (MTOM) The problem is that when you try to run a query in the database to create a report, you periodically cause the following exceptions:
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 a query:
<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 (#{start_date,javaType=STRING,jdbcType=VARCHAR},'YYYYMMDDHH24MISS')
AND TO_DATE (#{end_date,'YYYYMMDDHH24MISS')
AND U.working_bank_id IN
(SELECT bank_id
FROM rbp_all_banks
WHERE bank_id = #{bank_id,javaType=Integer,jdbcType=NUMERIC} OR parent_bank_id = #{bank_id,jdbcType=NUMERIC})
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 (#{start_date,'YYYYMMDDHH24MISS')
AND TO_DATE (#{end_date,'YYYYMMDDHH24MISS')
AND Ua.working_bank_id IN
(SELECT bank_id
FROM rbp_all_banks
WHERE bank_id = #{bank_id,jdbcType=NUMERIC})) r
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
]]>
The application does not use date type parameters because Oracle uses different schedules in this case and the query runs for a long time Therefore, the application passes the query date as text to the query date, and then uses to_ The date function converts it to a date Logging from 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,... DEBUG 2015-07-22 15:10:52,725 [http-apr-8281-exec-2] ==> Parameters: 20150601000000(String),20150621235959(String),31(Integer),20150601000000(String),31(Integer)
As shown here, mybatis passes the date parameter (as a string) to the query, but if we want to view the Oracle trace, we can see that the value of the date parameter is "" (not empty, but two double quotes)
call
SELECT TO_DATE ('','YYYYMMDDHH24MISS') FROM dual
At toad returned a null value, but
SELECT TO_DATE ('""','YYYYMMDDHH24MISS') FROM dual
Exception thrown: ora-01841: (full) year must be between - 4713 and 9999, not 0
Oddly enough, when there is a problem with the server and the application is running on another computer, such as my work laptop, there is no problem (create this report) When a problem occurs, the following is part of the trace file for the Oracle Database:
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
The following is part of the trace file for the Oracle database when no problems occur:
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
Note the bound values: 0,1,4,5 When a problem occurs, the value is value = ''
This problem is not related to mybatis because the application passes the database connection to the jasperreports engine to create the report before requesting access to the compiled jasperreports file (monthlyreport. Jasper) Jasper reports itself connects to the database and runs queries Mybatis is not used to create reports, but is used for all other purposes in the application The same Oracle error (ora-01841: (full) year must be between - 4713 and 9999, not 0) released and there on a regular basis From old log file:
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
Please help solve the problem
Solution
You are observing the symptoms This is not a matter of quotation marks In tracing, string parameters are displayed as double quotes Namely
value="20150621235959"
Indicates that the client passed the string '20150621235959' and
value=""
Indicates that the client passed an empty string " Lead ora-12801
The root cause is that the client can pass an empty string to the database
predicate
action_date BETWEEN date1 and date2
If one or both dates are null, no rows are returned If you don't trust, simply run this query
-- return nothing
SELECT * FROM dual where sysdate between to_date('','ddmmyyyy') and to_date('','ddmmyyyy');
There is no need why the client should send an empty string to the database. If you know, nothing will be returned
Therefore, IMHO should perform some validation on the client and only accept valid strings (at least the correct length) This will solve the problem