java - Changing values of parameters after binding when calling query at Oracle database -

we have in company application developed using java 7, spring framework 3.1.2, mybatis 3.1.1, mybatis spring 1.2.2, jasperreports 6.1.0, etc. application works @ tomcat 7.0.35, uses tomcat connection pool connection oracle database 10g enterprise edition release 10.2.0.4.0 - 64bit. jre version 1.7.0_09-b05. application works @ rhel server 6.5.

from time time problem arises, disappears after few hours (3-6 hours), , couple of days (1-3 days). when web service creates report, application calls method mybatis mapper returns list<monthlyreport>, application passes list jasperreport engine create report on file system, , @ end application returns stream of file @ response (mtom). problem periodically when trying run query in database create report, causes following exception:

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 between -4713 , +9999, , not 0 

this 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 null 1 else 0 end isparentbankint,        count (case when r.p_type not in ('ss', 'dr') 1 else null end) postpaidpaymentcount,        sum (case when r.p_type not in ('ss', 'dr') r.amount else 0 end) postpaidpaymentamount,        count (case when r.p_type = 'ss' 1 else null end) prepaidpaymentcount,        sum (case when r.p_type = 'ss' r.amount else 0 end) prepaidpaymentamount,        count (case when r.p_type = 'dr' 1 else null end) depositrepaycount,        sum (case when r.p_type = 'dr' r.amount else 0 end) depositrepayamount   (select q.queue_id,                q.amount,                q.p_type,                q.user_name,                q.action_date,                b.parent_bank_id,                u.descr,                b.bank_name           rbp_queue q, rbp_all_banks b, rbp_users u              q.user_name = u.user_name                , u.working_bank_id = b.bank_id                , q.err_code = -1000000                , q.action_date between to_date (#{start_date, javatype=string, jdbctype=varchar}, 'yyyymmddhh24miss')                                      , to_date (#{end_date,   javatype=string, jdbctype=varchar}, 'yyyymmddhh24miss')                , u.working_bank_id in                     (select bank_id                        rbp_all_banks                       bank_id = #{bank_id, javatype=integer, jdbctype=numeric} or parent_bank_id = #{bank_id, javatype=integer, 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           sysadm.rbp_queue_arch@azis_archdb qa,                rbp_all_banks ba,                rbp_users ua              qa.user_name = ua.user_name                , ua.working_bank_id = ba.bank_id                , qa.err_code = -1000000                , qa.action_date between to_date (#{start_date, javatype=string, jdbctype=varchar}, 'yyyymmddhh24miss')                                       , to_date (#{end_date,   javatype=string, jdbctype=varchar}, 'yyyymmddhh24miss')                , ua.working_bank_id in                     (select bank_id                        rbp_all_banks                       bank_id = #{bank_id, javatype=integer, jdbctype=numeric} or parent_bank_id = #{bank_id, javatype=integer, jdbctype=numeric})) r          group r.bank_name,                   r.user_name,                   r.descr,                   case when r.parent_bank_id null 1 else 0 end          order isparentbankint desc, bankname, username ]]> 

application don't use date type parameter, because oracle in case uses different plan, , query runs long time. reason, application passes query date text , converts date using to_date function. log records 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) 

as seen here, mybatis passes date parameters (as string) query, if see oracle trace can see that, value of date parameters "" (not null, 2 double quotes).

calling of

select to_date ('', 'yyyymmddhh24miss') dual 

at toad returns null value, but

select to_date ('""', 'yyyymmddhh24miss') dual 

raises exception: ora-01841: (full) year must between -4713 , +9999, , not 0.

the strange thing when there problem on server, @ same time application works without problems (creates report) on other computers, such working laptop. below part of trace file of oracle database when problem occurred:

     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 

below part of trace file of oracle database when problem didn't 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 values of binds:0, 1, 4, 5. when problem value value="".

the problem not related mybatis, since before request inside compiled jasperreports file (monthlyreport.jasper) , application passed database connection jasperreports engine create report. jasperreports connected database , ran query. mybatis not used creation of report, used in application other purposes. same oracle error (ora-01841: (full) year must between -4713 , +9999, , not 0) periodically issued , there. 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 : monthlyreport_new32dataset321_1432644594876_272524             @ net.sf.jasperreports.engine.query.jrjdbcqueryexecuter.createdatasource(jrjdbcqueryexecuter.java:240)             @ net.sf.jasperreports.engine.fill.jrfilldataset.createquerydatasource(jrfilldataset.java:1087)             @ net.sf.jasperreports.engine.fill.jrfilldataset.initdatasource(jrfilldataset.java:668)             @ net.sf.jasperreports.engine.fill.jrbasefiller.setparameters(jrbasefiller.java:1281)             @ net.sf.jasperreports.engine.fill.jrbasefiller.fill(jrbasefiller.java:900)             @ net.sf.jasperreports.engine.fill.jrbasefiller.fill(jrbasefiller.java:845)             @ net.sf.jasperreports.engine.fill.jrfillsubreport.fillsubreport(jrfillsubreport.java:651)             @ net.sf.jasperreports.engine.fill.jrsubreportrunnable.run(jrsubreportrunnable.java:59)             @ net.sf.jasperreports.engine.fill.abstractthreadsubreportrunner.run(abstractthreadsubreportrunner.java:203)             @ java.util.concurrent.threadpoolexecutor.runworker(threadpoolexecutor.java:1110)             @ java.util.concurrent.threadpoolexecutor$worker.run(threadpoolexecutor.java:603)             @ 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 between -4713 , +9999, , not 0              @ oracle.jdbc.driver.t4cttioer.processerror(t4cttioer.java:445)             @ oracle.jdbc.driver.t4cttioer.processerror(t4cttioer.java:396)             @ oracle.jdbc.driver.t4c8oall.processerror(t4c8oall.java:879)             @ oracle.jdbc.driver.t4cttifun.receive(t4cttifun.java:450)             @ oracle.jdbc.driver.t4cttifun.dorpc(t4cttifun.java:192)             @ oracle.jdbc.driver.t4c8oall.dooall(t4c8oall.java:531)             @ oracle.jdbc.driver.t4cpreparedstatement.dooall8(t4cpreparedstatement.java:207)             @ oracle.jdbc.driver.t4cpreparedstatement.executefordescribe(t4cpreparedstatement.java:884)             @ oracle.jdbc.driver.oraclestatement.executemaybedescribe(oraclestatement.java:1167)             @ oracle.jdbc.driver.oraclestatement.doexecutewithtimeout(oraclestatement.java:1289)             @ oracle.jdbc.driver.oraclepreparedstatement.executeinternal(oraclepreparedstatement.java:3584)             @ oracle.jdbc.driver.oraclepreparedstatement.executequery(oraclepreparedstatement.java:3628)             @ oracle.jdbc.driver.oraclepreparedstatementwrapper.executequery(oraclepreparedstatementwrapper.java:1493)             @ sun.reflect.generatedmethodaccessor349.invoke(unknown source)             @ sun.reflect.delegatingmethodaccessorimpl.invoke(delegatingmethodaccessorimpl.java:43)             @ java.lang.reflect.method.invoke(method.java:601)             @ org.apache.tomcat.jdbc.pool.interceptor.abstractqueryreport$statementproxy.invoke(abstractqueryreport.java:235)             @ $proxy99.executequery(unknown source)             @ net.sf.jasperreports.engine.query.jrjdbcqueryexecuter.createdatasource(jrjdbcqueryexecuter.java:233)             ... 11 more 

please solve problem.

you observing symptom. , not problem quotes. in trace string parameter shown doulbe quotes. i.e.

value="20150621235959" 

means client passed string '20150621235959' and

value=""    

means client passed empty string ''. leads the ora-12801

the root cause client able pass empty string database.

the predicates

action_date between date1 , date2 

returns no row if 1 or both of dates null. if don't trust simple run query

 -- return nothing  select * dual sysdate between to_date('','ddmmyyyy') , to_date('','ddmmyyyy'); 

there no point why client should send empty strings database, if know, nothing returned.

so imho there should validation in client enforcing valid strings (at least proper lenght) accepted. resolve problem.