Solved

OracleDataSource returning NullPointerException after so many queries

Posted on 2011-09-29
10
2,177 Views
Last Modified: 2013-11-11
Hello Experts,

I am trying to resolve an issue with Oracle's OracleDataSource driver in a particular application.  I am upgrading from Spring 1.2.6 and ojdbc 14-10.2.0.1.0 to  ojdbc6-11.1.0.7.0, Spring 3.0.5, using Java SE 6 for a batch update application.  Here is the Spring configuration of the dataSource:

 <bean id="abstractDataSource" lazy-init="false" class="oracle.jdbc.pool.OracleDataSource">
            <property name="URL">
			    <value>@JDBC_URL@</value>
		    </property>
            <property name="user">
			    <value>@JDBC_USERNAME@</value>
		    </property>
           <property name="password">
			    <value>@JDBC_PASSWORD@</value>
		    </property>
           <property name="connectionCachingEnabled" value="true"/>
           <property name="connectionCacheProperties">
               <props>
                   <!-- MinLimit
                        Sets the minimum number of connections the cache maintains. -->
                   <prop key="MinLimit">@MIN_CONNECTION_POOL_SIZE@</prop>
                   <!-- MaxLimit
                        Sets the maximum number of connection instances the cache can hold. The default value is
                        Integer.MAX_VALUE, meaning that there is no limit enforced by the connection cache, so that the
                        number of connections is limited only by the number of database sessions configured for the
                        database. -->
                   <prop key="MaxLimit">@MAX_CONNECTION_POOL_SIZE@</prop>
                   <!-- InitialLimit
                        Sets how many connections are created in the cache when it is created or reinitialized.
                        When this property is set to an integer value greater than 0, creating or reinitializing the cache
                        automatically creates the specified number of connections, filling the cache in advance of need.-->
                   <prop key="InitialLimit">10</prop>
                   <!-- InactivityTimeout
                        Sets the maximum time a physical connection can remain idle in a connection cache. An idle
                        connection is one that is not active and does not have a logical handle associated with it. When
                        InactivityTimeout expires, the underlying physical connection is closed. However, the size of the
                        cache is not allowed to shrink below minLimit, if it has been set. -->
                   <prop key="InactivityTimeout">300</prop>
                   <!-- ConnectionWaitTimeout
                        Specifies cache behavior when a connection is requested and there are already MaxLimit connections
                        active. If ConnectionWaitTimeout is equal to zero, then each connection request waits for zero
                        seconds, that is, null connection is returned immediately. If ConnectionWaitTimeout is greater
                        than zero, then each connection request waits for the specified number of seconds or until a
                        connection is returned to the cache. If no connection is returned to the cache before the timeout
                        elapses, then the connection request returns null. -->
                   <prop key="ConnectionWaitTimeout">120</prop>
                   <!--
                    The number of SQL statements the cache will hold.  As long as they SQL statements are similar they
                    can be reused.
                   -->
                   <prop key="MaxStatementsLimit">100</prop>
               </props>
           </property>
    </bean>

Open in new window


Here is the log file including the stack trace:

INFO  28 Sep 2011 22:45:34 main(59): Starting processing updates: 2011-09-28 22:45:34 
INFO  28 Sep 2011 22:45:42 main(92): Number of files to be processed = 38 
INFO  28 Sep 2011 22:45:49 process(79): Begining browser update post-processing ->  
INFO  28 Sep 2011 22:45:51 process(109): Completed post-processing for all browser updates - Number of files = 0 
INFO  28 Sep 2011 22:45:51 main(110): Begin an update for plan = ftp-ddpmi3 ( Seq #2914 ) 
INFO  28 Sep 2011 22:45:51 processSequence(102): Begining Processing State NM: SID 195760 
INFO  28 Sep 2011 22:45:52 processSequence(109): Finished Processing State NM: SID 195760 
INFO  28 Sep 2011 22:45:52 processSequence(102): Begining Processing State TN: SID 195761 
INFO  28 Sep 2011 22:45:52 processSequence(109): Finished Processing State TN: SID 195761 
INFO  28 Sep 2011 22:45:52 cleanUp(65): Starting cleanup after the update files has been processed. UPDT_BAT_SID = 114781 
INFO  28 Sep 2011 22:45:52 process(81): Begining post-processing for update - ftp-ddpmi3 
INFO  28 Sep 2011 22:45:54 process(128): Completed post-processing for update - ftp-ddpmi3 
INFO  28 Sep 2011 22:45:54 main(110): Begin an update for plan = ftp-ddpmi3 ( Seq #2915 ) 
INFO  28 Sep 2011 22:45:54 processSequence(102): Begining Processing State NM: SID 195762 
INFO  28 Sep 2011 22:45:54 processSequence(109): Finished Processing State NM: SID 195762 
INFO  28 Sep 2011 22:45:54 processSequence(102): Begining Processing State TN: SID 195763 
INFO  28 Sep 2011 22:45:54 processSequence(109): Finished Processing State TN: SID 195763 
INFO  28 Sep 2011 22:45:54 cleanUp(65): Starting cleanup after the update files has been processed. UPDT_BAT_SID = 114782 
INFO  28 Sep 2011 22:45:54 process(81): Begining post-processing for update - ftp-ddpmi3 
INFO  28 Sep 2011 22:45:56 process(128): Completed post-processing for update - ftp-ddpmi3 
INFO  28 Sep 2011 22:45:56 main(110): Begin an update for plan = ftp-ddpmi3 ( Seq #2916 ) 
INFO  28 Sep 2011 22:45:56 processSequence(102): Begining Processing State NM: SID 195764 
INFO  28 Sep 2011 22:45:56 processSequence(109): Finished Processing State NM: SID 195764 
INFO  28 Sep 2011 22:45:56 processSequence(102): Begining Processing State TN: SID 195765 
INFO  28 Sep 2011 22:45:56 processSequence(109): Finished Processing State TN: SID 195765 
INFO  28 Sep 2011 22:45:56 cleanUp(65): Starting cleanup after the update files has been processed. UPDT_BAT_SID = 114783 
INFO  28 Sep 2011 22:45:56 process(81): Begining post-processing for update - ftp-ddpmi3 
INFO  28 Sep 2011 22:45:57 process(128): Completed post-processing for update - ftp-ddpmi3 
INFO  28 Sep 2011 22:45:57 main(110): Begin an update for plan = ftp-ddpmi3 ( Seq #2917 ) 
INFO  28 Sep 2011 22:45:57 processSequence(102): Begining Processing State NM: SID 195766 
INFO  28 Sep 2011 22:45:58 processSequence(109): Finished Processing State NM: SID 195766 
INFO  28 Sep 2011 22:45:58 processSequence(102): Begining Processing State TN: SID 195768 
INFO  28 Sep 2011 22:45:58 processSequence(109): Finished Processing State TN: SID 195768 
INFO  28 Sep 2011 22:45:58 cleanUp(65): Starting cleanup after the update files has been processed. UPDT_BAT_SID = 114784 
INFO  28 Sep 2011 22:45:58 process(81): Begining post-processing for update - ftp-ddpmi3 
INFO  28 Sep 2011 22:45:59 process(128): Completed post-processing for update - ftp-ddpmi3 
INFO  28 Sep 2011 22:45:59 main(110): Begin an update for plan = ftp-ddpmi3 ( Seq #2918 ) 
INFO  28 Sep 2011 22:45:59 processSequence(102): Begining Processing State NM: SID 195767 
INFO  28 Sep 2011 22:45:59 processSequence(109): Finished Processing State NM: SID 195767 
INFO  28 Sep 2011 22:45:59 processSequence(102): Begining Processing State TN: SID 195769 
INFO  28 Sep 2011 22:46:00 processSequence(109): Finished Processing State TN: SID 195769 
INFO  28 Sep 2011 22:46:00 cleanUp(65): Starting cleanup after the update files has been processed. UPDT_BAT_SID = 114785 
INFO  28 Sep 2011 22:46:00 process(81): Begining post-processing for update - ftp-ddpmi3 
INFO  28 Sep 2011 22:46:01 process(128): Completed post-processing for update - ftp-ddpmi3 
INFO  28 Sep 2011 22:46:01 main(110): Begin an update for plan = ftp-ddpmi3 ( Seq #2919 ) 
INFO  28 Sep 2011 22:46:01 processSequence(102): Begining Processing State NM: SID 195770 
INFO  28 Sep 2011 22:46:01 processSequence(109): Finished Processing State NM: SID 195770 
INFO  28 Sep 2011 22:46:01 processSequence(102): Begining Processing State TN: SID 195772 
INFO  28 Sep 2011 22:46:02 processSequence(109): Finished Processing State TN: SID 195772 
INFO  28 Sep 2011 22:46:02 cleanUp(65): Starting cleanup after the update files has been processed. UPDT_BAT_SID = 114786 
INFO  28 Sep 2011 22:46:02 process(81): Begining post-processing for update - ftp-ddpmi3 
INFO  28 Sep 2011 22:46:03 process(128): Completed post-processing for update - ftp-ddpmi3 
INFO  28 Sep 2011 22:46:03 main(110): Begin an update for plan = ftp-ddpmi3 ( Seq #2920 ) 
INFO  28 Sep 2011 22:46:03 processSequence(102): Begining Processing State NM: SID 195771 
INFO  28 Sep 2011 22:46:03 processSequence(109): Finished Processing State NM: SID 195771 
INFO  28 Sep 2011 22:46:03 cleanUp(65): Starting cleanup after the update files has been processed. UPDT_BAT_SID = 114787 
INFO  28 Sep 2011 22:46:03 process(81): Begining post-processing for update - ftp-ddpmi3 
INFO  28 Sep 2011 22:46:04 process(128): Completed post-processing for update - ftp-ddpmi3 
INFO  28 Sep 2011 22:46:04 main(110): Begin an update for plan = ftp-ddpmi3 ( Seq #2921 ) 
INFO  28 Sep 2011 22:46:04 processSequence(102): Begining Processing State NM: SID 195758 
INFO  28 Sep 2011 22:46:05 processSequence(109): Finished Processing State NM: SID 195758 
INFO  28 Sep 2011 22:46:05 cleanUp(65): Starting cleanup after the update files has been processed. UPDT_BAT_SID = 114779 
INFO  28 Sep 2011 22:46:05 process(81): Begining post-processing for update - ftp-ddpmi3 
INFO  28 Sep 2011 22:46:06 process(128): Completed post-processing for update - ftp-ddpmi3 
INFO  28 Sep 2011 22:46:06 main(110): Begin an update for plan = ftp-ddpmi3 ( Seq #2922 ) 
INFO  28 Sep 2011 22:46:06 processSequence(102): Begining Processing State NM: SID 195757 
INFO  28 Sep 2011 22:46:06 processSequence(109): Finished Processing State NM: SID 195757 
INFO  28 Sep 2011 22:46:06 cleanUp(65): Starting cleanup after the update files has been processed. UPDT_BAT_SID = 114778 
INFO  28 Sep 2011 22:46:06 process(81): Begining post-processing for update - ftp-ddpmi3 
INFO  28 Sep 2011 22:46:07 process(128): Completed post-processing for update - ftp-ddpmi3 
INFO  28 Sep 2011 22:46:07 main(110): Begin an update for plan = ftp-ddpmi3 ( Seq #2923 ) 
INFO  28 Sep 2011 22:46:07 processSequence(102): Begining Processing State NM: SID 195759 
INFO  28 Sep 2011 22:46:08 processSequence(109): Finished Processing State NM: SID 195759 
INFO  28 Sep 2011 22:46:08 cleanUp(65): Starting cleanup after the update files has been processed. UPDT_BAT_SID = 114780 
INFO  28 Sep 2011 22:46:08 process(81): Begining post-processing for update - ftp-ddpmi3 
INFO  28 Sep 2011 22:46:09 process(128): Completed post-processing for update - ftp-ddpmi3 
INFO  28 Sep 2011 22:46:09 main(110): Begin an update for plan = ftp-ddpmo ( Seq #3138 ) 
INFO  28 Sep 2011 22:46:09 processSequence(102): Begining Processing State MO: SID 195698 
INFO  28 Sep 2011 22:46:09 setTransactionErrorForProvider(75): Set 1431 error for provider:2011015040  01 
INFO  28 Sep 2011 22:46:10 setTransctionErrorForServiceOffice(101): Set P1430 error for service Office:113664960   001 
INFO  28 Sep 2011 22:46:10 setTransctionErrorForServiceOffice(101): Set P1430 error for service Office:264649942   001 
INFO  28 Sep 2011 22:46:10 setTransctionErrorForServiceOffice(101): Set P1430 error for service Office:270046240   001 
INFO  28 Sep 2011 22:46:11 setTransctionErrorForServiceOffice(101): Set P1430 error for service Office:271455178   001 
INFO  28 Sep 2011 22:46:16 processSequence(109): Finished Processing State MO: SID 195698 
INFO  28 Sep 2011 22:46:16 processSequence(102): Begining Processing State SC: SID 195699 
INFO  28 Sep 2011 22:46:16 processSequence(109): Finished Processing State SC: SID 195699 
INFO  28 Sep 2011 22:46:16 cleanUp(65): Starting cleanup after the update files has been processed. UPDT_BAT_SID = 114752 
INFO  28 Sep 2011 22:46:16 process(81): Begining post-processing for update - ftp-ddpmo 
INFO  28 Sep 2011 22:46:24 process(128): Completed post-processing for update - ftp-ddpmo 
INFO  28 Sep 2011 22:46:24 main(110): Begin an update for plan = ftp-ddpmo ( Seq #3139 ) 
INFO  28 Sep 2011 22:46:24 processSequence(102): Begining Processing State MO: SID 195700 
INFO  28 Sep 2011 22:46:24 setTransctionErrorForServiceOffice(101): Set P1430 error for service Office:547570480   001 
INFO  28 Sep 2011 22:46:24 setTransctionErrorForServiceOffice(101): Set P1430 error for service Office:200614370   001 
INFO  28 Sep 2011 22:46:25 setTransctionErrorForServiceOffice(101): Set P1430 error for service Office:431869770   003 
INFO  28 Sep 2011 22:46:26 processSequence(109): Finished Processing State MO: SID 195700 
INFO  28 Sep 2011 22:46:26 cleanUp(65): Starting cleanup after the update files has been processed. UPDT_BAT_SID = 114753 
INFO  28 Sep 2011 22:46:26 process(81): Begining post-processing for update - ftp-ddpmo 
INFO  28 Sep 2011 22:46:28 process(128): Completed post-processing for update - ftp-ddpmo 
INFO  28 Sep 2011 22:46:28 main(110): Begin an update for plan = ftp-ddpmo ( Seq #3140 ) 
INFO  28 Sep 2011 22:46:28 processSequence(102): Begining Processing State MO: SID 195701 
INFO  28 Sep 2011 22:46:29 setTransctionErrorForServiceOffice(101): Set P1430 error for service Office:452062046   001 
INFO  28 Sep 2011 22:46:29 setTransctionErrorForServiceOffice(101): Set P1430 error for service Office:452497296   001 
INFO  28 Sep 2011 22:46:31 processSequence(109): Finished Processing State MO: SID 195701 
INFO  28 Sep 2011 22:46:31 cleanUp(65): Starting cleanup after the update files has been processed. UPDT_BAT_SID = 114754 
INFO  28 Sep 2011 22:46:31 process(81): Begining post-processing for update - ftp-ddpmo 
INFO  28 Sep 2011 22:46:34 process(128): Completed post-processing for update - ftp-ddpmo 
INFO  28 Sep 2011 22:46:34 main(110): Begin an update for plan = ftp-ddpmo ( Seq #3141 ) 
INFO  28 Sep 2011 22:46:34 processSequence(102): Begining Processing State MO: SID 195702 
INFO  28 Sep 2011 22:46:34 processSequence(109): Finished Processing State MO: SID 195702 
INFO  28 Sep 2011 22:46:34 cleanUp(65): Starting cleanup after the update files has been processed. UPDT_BAT_SID = 114755 
INFO  28 Sep 2011 22:46:34 process(81): Begining post-processing for update - ftp-ddpmo 
INFO  28 Sep 2011 22:46:36 process(128): Completed post-processing for update - ftp-ddpmo 
INFO  28 Sep 2011 22:46:36 main(110): Begin an update for plan = ftp-ddpmo ( Seq #3142 ) 
INFO  28 Sep 2011 22:46:36 processSequence(102): Begining Processing State MO: SID 195703 
INFO  28 Sep 2011 22:46:37 setTransctionErrorForServiceOffice(101): Set P1430 error for service Office:431661180   001 
INFO  28 Sep 2011 22:46:37 setTransctionErrorForServiceOffice(101): Set P1430 error for service Office:431790061   004 
INFO  28 Sep 2011 22:46:37 setTransctionErrorForServiceOffice(101): Set P1430 error for service Office:431790061   013 
INFO  28 Sep 2011 22:46:40 processSequence(109): Finished Processing State MO: SID 195703 
INFO  28 Sep 2011 22:46:40 processSequence(102): Begining Processing State SC: SID 195704 
INFO  28 Sep 2011 22:46:41 processSequence(109): Finished Processing State SC: SID 195704 
INFO  28 Sep 2011 22:46:41 cleanUp(65): Starting cleanup after the update files has been processed. UPDT_BAT_SID = 114756 
INFO  28 Sep 2011 22:46:41 process(81): Begining post-processing for update - ftp-ddpmo 
INFO  28 Sep 2011 22:46:45 process(128): Completed post-processing for update - ftp-ddpmo 
INFO  28 Sep 2011 22:46:45 main(110): Begin an update for plan = ftp-ddpmo ( Seq #3143 ) 
INFO  28 Sep 2011 22:46:45 processSequence(102): Begining Processing State MO: SID 195697 
INFO  28 Sep 2011 22:46:46 processSequence(109): Finished Processing State MO: SID 195697 
INFO  28 Sep 2011 22:46:46 cleanUp(65): Starting cleanup after the update files has been processed. UPDT_BAT_SID = 114751 
INFO  28 Sep 2011 22:46:46 process(81): Begining post-processing for update - ftp-ddpmo 
INFO  28 Sep 2011 22:46:48 process(128): Completed post-processing for update - ftp-ddpmo 
INFO  28 Sep 2011 22:46:48 main(110): Begin an update for plan = ftp-ddpmo ( Seq #3144 ) 
INFO  28 Sep 2011 22:46:48 processSequence(102): Begining Processing State MO: SID 195696 
INFO  28 Sep 2011 22:46:49 processSequence(109): Finished Processing State MO: SID 195696 
INFO  28 Sep 2011 22:46:49 cleanUp(65): Starting cleanup after the update files has been processed. UPDT_BAT_SID = 114750 
INFO  28 Sep 2011 22:46:49 process(81): Begining post-processing for update - ftp-ddpmo 
ERROR 28 Sep 2011 22:46:50 main(136): Serious Error: The update Batch was not processed : Sequence #org.ddpa.npf.domainmodel.butt.HeaderRecord@1555185[fileNa
me=<null>,fileRunDate=java.util.GregorianCalendar[time=1309842000000,areFieldsSet=true,areAllFieldsSet=true,lenient=true,zone=sun.util.calendar.ZoneInfo[id="
US/Central",offset=-21600000,dstSavings=3600000,useDaylight=true,transitions=235,lastRule=java.util.SimpleTimeZone[id=US/Central,offset=-21600000,dstSavings=
3600000,useDaylight=true,startYear=0,startMode=3,startMonth=2,startDay=8,startDayOfWeek=1,startTime=7200000,startTimeMode=0,endMode=3,endMonth=10,endDay=1,en
dDayOfWeek=1,endTime=7200000,endTimeMode=0]],firstDayOfWeek=1,minimalDaysInFirstWeek=1,ERA=1,YEAR=2011,MONTH=6,WEEK_OF_YEAR=28,WEEK_OF_MONTH=2,DAY_OF_MONTH=5
,DAY_OF_YEAR=186,DAY_OF_WEEK=3,DAY_OF_WEEK_IN_MONTH=1,AM_PM=0,HOUR=0,HOUR_OF_DAY=0,MINUTE=0,SECOND=0,MILLISECOND=0,ZONE_OFFSET=-21600000,DST_OFFSET=3600000],
flagFileContents=<null>,flagFileFtpRecievedDate=<null>,FTPFolderName=ftp-ddpmo,negativeSeqNumber=3144,positiveUpdate=false,processedDate=<null>,receivedDate=
<null>,recordsSent=0,sid=114750,submitPlanSid=25,submitterPlan=<null>,updateType=<null>,details={48,51,48,49,45,48,48,48,48,48,51,49,52,52,50,48,49,49,48,55,
48,53,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,3
2,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32
,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,
32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,3
2,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32
,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,
32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,3
2,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32
,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,
32,32,32,32,32,32,32,32,32},recordNumber=0] 
java.lang.NullPointerException
        at org.ddpa.npf.updateEngine.postprocessor.dao.UpdFileDAO.getHeaderRecord(UpdFileDAO.java:76)
        at org.ddpa.npf.updateEngine.postprocessor.processor.UPDFileProcessor.getUpdateFile(UPDFileProcessor.java:100)
        at org.ddpa.npf.updateEngine.postprocessor.processor.UPDFileProcessor.process(UPDFileProcessor.java:67)
        at org.ddpa.npf.updateEngine.postprocessor.controller.PostProcessController.process(PostProcessController.java:98)
        at org.ddpa.npf.batchupd.processor.worker.reporter.BatchUpdReporter.processReport(BatchUpdReporter.java:44)
        at org.ddpa.npf.batchupd.processor.application.InProcessBatchUpdController.main(InProcessBatchUpdController.java:131)
INFO  28 Sep 2011 22:46:50 main(110): Begin an update for plan = ftp-ddpnedd ( Seq #1597 ) 
ERROR 28 Sep 2011 22:46:50 main(136): Serious Error: The update Batch was not processed : Sequence #org.ddpa.npf.domainmodel.butt.HeaderRecord@12cb585[fileNa
me=<null>,fileRunDate=java.util.GregorianCalendar[time=1308632400000,areFieldsSet=true,areAllFieldsSet=true,lenient=true,zone=sun.util.calendar.ZoneInfo[id="
US/Central",offset=-21600000,dstSavings=3600000,useDaylight=true,transitions=235,lastRule=java.util.SimpleTimeZone[id=US/Central,offset=-21600000,dstSavings=
3600000,useDaylight=true,startYear=0,startMode=3,startMonth=2,startDay=8,startDayOfWeek=1,startTime=7200000,startTimeMode=0,endMode=3,endMonth=10,endDay=1,en
dDayOfWeek=1,endTime=7200000,endTimeMode=0]],firstDayOfWeek=1,minimalDaysInFirstWeek=1,ERA=1,YEAR=2011,MONTH=5,WEEK_OF_YEAR=26,WEEK_OF_MONTH=4,DAY_OF_MONTH=2
1,DAY_OF_YEAR=172,DAY_OF_WEEK=3,DAY_OF_WEEK_IN_MONTH=3,AM_PM=0,HOUR=0,HOUR_OF_DAY=0,MINUTE=0,SECOND=0,MILLISECOND=0,ZONE_OFFSET=-21600000,DST_OFFSET=3600000]
,flagFileContents=<null>,flagFileFtpRecievedDate=<null>,FTPFolderName=ftp-ddpnedd,negativeSeqNumber=1597,positiveUpdate=false,processedDate=<null>,receivedDa
te=<null>,recordsSent=0,sid=114758,submitPlanSid=59,submitterPlan=<null>,updateType=<null>,details={48,51,48,49,45,48,48,48,48,48,49,53,57,55,50,48,49,49,48,
54,50,49,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,3
2,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32
,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,
32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,3
2,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32
,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,
32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,3
2,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32
,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,
32,32,32,32,32,32,32,32,32,32},recordNumber=0] 
java.lang.NullPointerException
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:389)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:427)
        at org.ddpa.npf.batchupd.dao.butt.impl.ButtDaoFacadeImpl.setSequenceToInProcessing(ButtDaoFacadeImpl.java:585)
        at org.ddpa.npf.batchupd.processor.worker.updater.SequenceProcessor.processSequence(SequenceProcessor.java:88)
        at org.ddpa.npf.batchupd.processor.application.InProcessBatchUpdController.main(InProcessBatchUpdController.java:117)
INFO  28 Sep 2011 22:46:50 main(110): Begin an update for plan = ftp-ddpnedd ( Seq #1598 ) 

Open in new window


Here's the class that the error appears in:

public class UpdFileDAO extends BatchPostUpdateDAO {

    /**
     * SQL used by 01 record *
     */
    private static final String SQL =
            " SELECT TO_CHAR(recvd_dt,\'YYYYMMDDHH24MI\') AS recvd_dt,"
                    + "       ftp_fldr_nm,"
                    + "       rec_detls"
                    + "  FROM updt_bat B "
                    + " WHERE updt_bat_sid = ?";

    /**
     * Get the header record. The equats to the '01' record of an update file
     *
     * @param updateSID
     * @return
     * @throws SQLException
     */
    public byte[] getHeaderRecord(long updateSID)  {
         String record01 = null;

         try {
        // get the 01 recor
        PreparedStatement ps = getConnection().prepareStatement(SQL);


        ps.setBigDecimal(1, new BigDecimal(updateSID));
        ResultSet rs = ps.executeQuery();

        String recDetails = null;

            // get the data from the resultset
            if (rs.next()) {
                recDetails = rs.getString("rec_detls");
            }


            // create 01 record
            record01 = PostProcessorUtils.buildSequenceRecord(recDetails);

         } catch(SQLException e) {
             logger.info("01 Record: " + record01);
             logger.info("There was an error on the header update: ", e);
         }


        if (record01 == null)
            return null;

        return record01.getBytes();
    }

    /**
     * Get the top line record for an update file. This is a generated '00' record
     * at the top of every UPD file. Used by copy/clone plans to order the UPD
     * files.
     *
     * @param updateSID
     * @return
     * @throws SQLException
     */
    public byte[] getTopLineRecord(long updateSID) throws SQLException {

        PreparedStatement ps = getConnection().prepareStatement(SQL);
        ps.setBigDecimal(1, new BigDecimal(updateSID));
        ResultSet rs = ps.executeQuery();

        String fullRecvdDate = null;
        String ftpFolderName = null;

        // get the data from the resultset
        if (rs.next()) {
            fullRecvdDate = rs.getString("recvd_dt");
            ftpFolderName = rs.getString("ftp_fldr_nm");
        }

        String dateTime = getTimeForHeaderRecord(getConnection());

        // create top line
        String topLine = null;
        topLine = PostProcessorUtils.buildTopLine(updateSID, dateTime,
                fullRecvdDate, ftpFolderName);

        if (topLine == null)
            return null;

        return topLine.getBytes();
    }

    /**
     * Get the rest of the records for the UPD file.
     *
     * @param stateSid
     * @return
     * @throws DAOException
     */
    public List<String> getRecords(long stateSid) {

        List<CallableParameter> params = new ArrayList<CallableParameter>();
        params.add(new CallableParameter(stateSid, Types.INTEGER));

        List<String> l = executeFunction("npf_update_file_pkg.get_updt_recs",
                params, new RowMapper() {
                    public String mapRow(ResultSet res, int rowNum) throws SQLException {
                        String details = (String) res.getObject("rec_detls");

                        PhaseFormatType format = PhaseFormatType.getType((String) res.getObject("format"));

                        // only convert phase II records.
                        if (format == PhaseFormatType.PhaseII) {
                            // convert from phase II to phase I records
                            details = PhaseIIToPhaseIConverter.convertRecordToPhaseI(details);
                        }

                        return details;
                    }
                });

        return l;
    }

    /**
     * Determine if the update was processed correctly. This is indicated by the
     * processed status flag on the updt_bat table
     *
     * @param updateSID
     * @return
     */
    public boolean processSuccessfully(long updateSID) {

        String sql = "SELECT COUNT(*) FROM updt_bat " + " WHERE updt_bat_sid = "
                + updateSID + " AND prcd_dt IS NOT NULL  "
                + " AND prcd_stus_cd IN ( 'P' , 'M' )";

        int exists = -1;

        try {
            JdbcTemplate temp = getJdbcTemplate();
            exists = temp.queryForInt(sql);
        }
        catch (DataAccessException dae) {
            dae.printStackTrace();
            logger.error(
                    "Problem encountered determining if update was run successfully.",
                    dae);
            return false;
        }
        return exists > 0;
    }

    /**
     * The top line of the OK file has a date and time that the file was received.
     * We are going to simulate this time, as the files need to be in a specific
     * order. In NPF3 we have allowed update files that are out of order to
     * be processed therefore this means that the received time of the file
     * on the ftp server does not indicated the order in which the files have
     * been processed. Due to the fact that two files can be processed
     * in the same minute, a further field (BU_TM) in the codes table is used.
     * <p/>
     * Note that the BU_TM is set to the current time of the first UPD file output
     *
     * @param conn
     * @return
     */
    public String getTimeForHeaderRecord(Connection conn) {
        try {
            String fullDate = null;
            String time = null;

            // get the time flag from the codes table
            String sql = " select to_char(sysdate,'yyyymmdd' ) || lpad(code_desc,5,'0'),code_desc "
                    + " from code where code_cd = 'BU_TM'";

            PreparedStatement ps = conn.prepareStatement(sql);

            ResultSet rs = ps.executeQuery();

            // get the data from the resultset
            if (rs.next()) {
                fullDate = rs.getString(1);
                time = rs.getString(2);
            }
            // remove the semi colon from the date
            fullDate = StringUtils.replace(fullDate, ":", "");

            // time in database is in string form of hh:mi ( 10:30 )
            String hourStr = time.substring(0, 2);
            String minuteStr = time.substring(3, 5);

            int hour = Integer.parseInt(hourStr);
            int minute = Integer.parseInt(minuteStr);

            Calendar c = Calendar.getInstance();
            c.set(Calendar.HOUR_OF_DAY, hour);
            c.set(Calendar.MINUTE, minute);

            c.add(Calendar.MINUTE, 5);

            int hr = c.get(Calendar.HOUR);
            int mi = c.get(Calendar.MINUTE);

            String newtime = StringUtils.leftPad("" + hr, 2, '0') + ":"
                    + StringUtils.leftPad("" + mi, 2, '0');

            // set the time flag from the codes table
            sql = " update code" + "  set code_desc = '" + newtime
                    + "' where code_cd = 'BU_TM'";

            ps = conn.prepareStatement(sql);

            ps.execute();

            return fullDate;
        }
        catch (Exception e) {
            logger.warn("Problem setting the BU_TM code in the codes table : " + e.getMessage());
            return "ERROR";
        }
    }

}

Open in new window


As you can see from the log file, the update works well for a few iterations and then the applications stops (I assume waiting the allotted time on the connection timeout) and then immediately throws the NPE.  We have been using the old oracle driver the 1.4.2 driver for Oracle 10g (ojdbc 14-10.2.0.1.0) and using the OracleConnectionCacheImpl class to create database connections.  The code works just fine using the old driver, but as soon as I plug in the newer OracleDataSource in this application, I get NPEs within 5-10 files getting processed (depending upon how high I set the maxPoolSize setting) and we process anywhere from 50-100 files a night with no problem using the old ojdbc setup.  So basically, what I want to know is:

1.  Is there something fundamentally different in the way OracleConnectionCacheImpl worked versus the way OracleDataSource works in the way it handles connections?

2.  Why does this NPE popup in the first place using OracleDataSource, but can run for days on end using OracleConnectionCacheImpl?

Also for the sake of completeness here is the Spring config I used for the OracleConnectionCacheImpl:

<bean id="abstractDataSource" lazy-init="false"
          class="oracle.jdbc.pool.OracleConnectionCacheImpl"
          destroy-method="close">
            <property name="URL">
			    <value>@JDBC_URL@</value>
		    </property>
            <property name="user">
			    <value>@JDBC_USERNAME@</value>
		    </property>
           <property name="password">
			    <value>@JDBC_PASSWORD@</value>
		    </property>
        <property name="minLimit">
            <value>@MIN_CONNECTION_POOL_SIZE@</value>
        </property>
        <property name="maxLimit">
            <value>@MAX_CONNECTION_POOL_SIZE@</value>
        </property>
        <property name="stmtCacheSize">
            <value>@SQL_STATEMENT_CACHE_SIZE@</value>
        </property>
       </bean>

Open in new window


As you can see we did not have set a whole lot of params here and this data source just works with no problems at all.

I have checked many other forums on this topic (including this one) and have not found anything that works for me.  I have seen and tried the set connection time out to zero explicitly solution to no avail.  I am offering 500 points for this because time is short and I need a solution ASAP.  So if someone can offer me a working solution quickly, you earned every point as far as I'm concerned.

Thanks,

JW
0
Comment
Question by:jw0715
  • 4
  • 4
  • 2
10 Comments
 
LVL 19

Expert Comment

by:ramazanyich
Comment Utility
I see immediatelly that you do not close your preparedstatements and connections.
Actually it means that connection which is used by your code is not released back to the datasource connection pool.
normally you should surround your JDBC code by try{}finally{} block and in the finally block close preparedstatement and connection. Something like
try{
}finally{
ps.close();
connection.close();
}

it doesn't mean that physical connection to DB will be closed, it will be returned to the datasource connection pool for processing by other calls.
0
 

Author Comment

by:jw0715
Comment Utility
That's what Spring is used for so I don't have to close the connections myself.  Spring prevents me from having to write the try catch logic and deal with the opening and closing of connections.
0
 
LVL 19

Expert Comment

by:ramazanyich
Comment Utility
the only thing which I can suggest is to put validateConnection property to true in oracle datasource properties.
0
 
LVL 19

Expert Comment

by:ramazanyich
Comment Utility
also check http://angelborroy.wordpress.com/2009/02/02/oracle-jdbc-implicit-connection-caching-bug-exposed/ On this link it shows that there is some bug exists in OracleDataSource where ordering of properties set is important. I don't know how Spring setting properties. You probably can play with order of properties in spring xml file...
0
 
LVL 86

Expert Comment

by:CEHJ
Comment Utility
It might be worthwhile checking that your own objects don't have null fields where your application logic demands they don't
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:jw0715
Comment Utility
@CEHJ

Normally I would agree, but in this case the same classes, the same data files, and the same 11g database run with no issues using older Java connection class OracleConnectionCacheImpl and the old ojdbc14-10.2.0.1.0 driver.  The only thing that has changed is the ojdbc driver (now ojdbc6-11.1.0.7.0) and the connection class OracleDataSource.  With this said I am finding that I am having more success using Oracle's Universal Connection Pool.  You had to tweak it a bit but it seems to be doing a better job than OracleDataSource.
0
 
LVL 86

Expert Comment

by:CEHJ
Comment Utility
That was my best guess sorry - not really my field, this stuff ;)
0
 
LVL 19

Expert Comment

by:ramazanyich
Comment Utility
regarding oracle UCP I found following article: http://stackoverflow.com/questions/2423490/how-good-is-oracle-universal-connection-pool-ucp
and on third answer guy explained problems which he had with it.
0
 

Accepted Solution

by:
jw0715 earned 0 total points
Comment Utility
@ramazanyich

Yes, I am now using UCP myself.  Universal Connection Pool works great.  I do have a few suggestions if you have to process alot of SQL code and there are long latency periods:

1.  Do not set any of the parameters that will timeout your connections.  I did this and got SQLException: closed connection errors quite a bit.  These exceptions finally went away after I stopped trying to experimenting with the removal of stale connections parameters and let UCP handle it.

2.  UCP will throw an exception if more than one connection pool is started up by a given app at the same time.  Most application won't do this but we have one that is old and needs to be rewritten to use one datasource instead of two, but until I get around to it is what is it.  If you find yourself in a similar position use OracleDataSource for one datasource and UCP for the other and the app should work (given you have both data sources configured correctly to work with your app).


I believe my issue was the inactivityTimeOut setting in the OracleDataSource (see 1st Spring config example above).  My apps have times where the connection inactivity times may be pretty long.  I learn this using jconsole to monitor the server I was running my code against.  I had always heard about jconsole, but never bothered with it until recently and its a great tool for determining app performance.


I hope this helps someone out there.

JW
0
 

Author Closing Comment

by:jw0715
Comment Utility
Many of the solutions that were offered either did not apply to my app or were things I tried and did not work.  I basically discovered a solution that worked for me, but cannot guarantee this work in every environment.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to recover a database from a user managed backup
The viewer will learn how to implement Singleton Design Pattern in Java.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now