i'm perplexed above, keeps cropping intermittently whenever execute stored proc within package on our new oracle installation.
the sproc called wcf, returns following exception :
<exceptiondetail xmlns="http://schemas.datacontract.org/2004/07/system.servicemodel" xmlns:i="http://www.w3.org/2001/xmlschema-instance"> <helplink i:nil="true"/> <innerexception> <helplink i:nil="true"/> <innerexception i:nil="true"/> <message>ora-12537: network session: end of file</message> <stacktrace><![cdata[at oracleinternal.network.readerstream.read(orabuf ob) @ oracleinternal.ttc.orabufreader.getdatafromnetwork() @ oracleinternal.ttc.orabufreader.read(boolean bignoredata) @ oracleinternal.ttc.marshallingengine.unmarshalub1(boolean bignoredata) @ oracleinternal.ttc.ttcexecutesql.receiveexecuteresponse(accessor[]& defineaccessors, accessor[] bindaccessors, boolean bhasreturningparams, sqlmetadata& sqlmetadata, sqlstatementtype statementtype, int64 noofrowsfetchedlasttime, int32 noofrowstofetch, int32& noofrowsfetched, int64& queryid, int32 longfetchsize, int64 initiallobfetchsize, int64[] scnfromexecution, boolean ballinputbinds, int32 arraybindcount, dataunmarshaller& dataunmarshaller, marshalbindparametervaluehelper& marshalbindparamshelper, int64[]& rowsaffectedbyarraybind, boolean bdefinedone, boolean& bmorethanonerowaffectedbydmlwithretclause, list`1& implicitrslist, boolean blobarrayfetchrequired)]]></stacktrace> <type>oracleinternal.network.networkexception</type> </innerexception> <message>ora-12537: network session: end of file</message> <stacktrace><![cdata[at oracle.manageddataaccess.client.oracleexception.handleerror(oracletracelevel level, oracletracetag tag, exception ex, oraclelogicaltransaction oraclelogicaltransaction) @ oracleinternal.ttc.ttcexecutesql.receiveexecuteresponse(accessor[]& defineaccessors, accessor[] bindaccessors, boolean bhasreturningparams, sqlmetadata& sqlmetadata, sqlstatementtype statementtype, int64 noofrowsfetchedlasttime, int32 noofrowstofetch, int32& noofrowsfetched, int64& queryid, int32 longfetchsize, int64 initiallobfetchsize, int64[] scnfromexecution, boolean ballinputbinds, int32 arraybindcount, dataunmarshaller& dataunmarshaller, marshalbindparametervaluehelper& marshalbindparamshelper, int64[]& rowsaffectedbyarraybind, boolean bdefinedone, boolean& bmorethanonerowaffectedbydmlwithretclause, list`1& implicitrslist, boolean blobarrayfetchrequired) @ oracleinternal.serviceobjects.oracledatareaderimpl.fetchmorerows(int32 noofrowstofetch, boolean fillreader, boolean returnpstypes) @ oracle.manageddataaccess.client.oracledatareader.read() @ system.data.common.dataadapter.fillloaddatarow(schemamapping mapping) @ system.data.common.dataadapter.fillfromreader(dataset dataset, datatable datatable, string srctable, datareadercontainer datareader, int32 startrecord, int32 maxrecords, datacolumn parentchaptercolumn, object parentchaptervalue) @ system.data.common.dataadapter.fill(dataset dataset, string srctable, idatareader datareader, int32 startrecord, int32 maxrecords) @ oracle.manageddataaccess.client.oracledataadapter.fill(dataset dataset, string srctable, idatareader datareader, int32 startrecord, int32 maxrecords) @ oracle.manageddataaccess.client.oracledataadapter.fill(dataset dataset, int32 startrecord, int32 maxrecords, string srctable, idbcommand command, commandbehavior behavior) @ system.data.common.dbdataadapter.fill(dataset dataset) @ ***redacted*** @ ***redacted*** @ ***redacted*** @ syncinvokeqtyadjustments_get(object , object[] , object[] ) @ system.servicemodel.dispatcher.syncmethodinvoker.invoke(object instance, object[] inputs, object[]& outputs) @ system.servicemodel.dispatcher.dispatchoperationruntime.invokebegin(messagerpc& rpc) @ system.servicemodel.dispatcher.immutabledispatchruntime.processmessage5(messagerpc& rpc) @ system.servicemodel.dispatcher.immutabledispatchruntime.processmessage41(messagerpc& rpc) @ system.servicemodel.dispatcher.immutabledispatchruntime.processmessage4(messagerpc& rpc) @ system.servicemodel.dispatcher.immutabledispatchruntime.processmessage31(messagerpc& rpc) @ system.servicemodel.dispatcher.immutabledispatchruntime.processmessage3(messagerpc& rpc) @ system.servicemodel.dispatcher.immutabledispatchruntime.processmessage2(messagerpc& rpc) @ system.servicemodel.dispatcher.immutabledispatchruntime.processmessage11(messagerpc& rpc) @ system.servicemodel.dispatcher.immutabledispatchruntime.processmessage1(messagerpc& rpc) @ system.servicemodel.dispatcher.messagerpc.process(boolean isoperationcontextset)]]></stacktrace> <type>oracle.manageddataaccess.client.oracleexception</type> </exceptiondetail> there doesn't seem pattern either - works fine, doesn't.
here's stuff i've tried :
- checked gac ensure multiple versions of odp aren't present. managed data access component has been imported nuget - version 12.1.2400.
- added various attributes connection string sort - namely persist security info=true;self tuning=true;min pool size=3;max pool size=25;incr pool size=3;decr pool size=1;connection timeout=20;pooling=false;connection lifetime=59; - tried various configurations no luck, example setting pooling false/true etc.
ensured disposal of objects in generic code. here's test code used :
using system.data; using system.data.common; using oracle.manageddataaccess.client; /// <summary> /// extensions xxxxx. /// </summary> /// <seealso cref="system.data.entity.dbcontext" /> public partial class xxxxentities { public virtual dataset executestoredprocedureasdataset(string storedprocname, int commandtimeoutseconds, object[] parameters) { var sqlcommand = this.database.connection.createcommand(); var resultset = new dataset(); sqlcommand.commandtype = commandtype.storedprocedure; sqlcommand.commandtimeout = commandtimeoutseconds; sqlcommand.commandtext = storedprocname; sqlcommand.parameters.addrange(parameters); var referencecursor = new oracleparameter("p_dataout", oracledbtype.refcursor) { direction = parameterdirection.output }; sqlcommand.parameters.add(referencecursor); using (var adapter = createdataadapter(this.database.connection)) { adapter.selectcommand = sqlcommand; adapter.fill(resultset); } referencecursor.dispose(); sqlcommand.dispose(); return resultset; } /// <summary> /// creates data adapter. /// </summary> /// <param name="connection">the connection.</param> /// <returns>dbdataadapter.</returns> dbdataadapter createdataadapter(dbconnection connection) { return dbproviderfactories.getfactory(connection).createdataadapter(); } }cutting down query in stored proc 'fixed' values - make sure nothing in sproc causing issue. did find interesting problems described below.
so, i'm @ loss. turned on odp tracing - , did notice seems having issues when dealing pool. first time, got :
2016-07-26 11:43:55.268765 tid:6 (pri) (svc) (ent) oracleconnectionimpl.evaluatedbmajorminorpatchsetversion() 2016-07-26 11:43:55.268765 tid:6 (pri) (svc) (ext) oracleconnectionimpl.evaluatedbmajorminorpatchsetversion() 2016-07-26 11:43:55.268765 tid:6 (pri) (svc) (ext) oracleconnectionimpl.connect() (oper=open) (aff=n/a) (inst=wmsqa) (affmatch=n/a) (sessid=2540:42190) (f;f;f;wmsqa;n) (pmid=29382239) 2016-07-26 11:43:55.268765 tid:6 (pri) (ent) (cp) poolmanager`3.putnewpr() (aff=n/a) (inst=wmsqa) (affmatch=n/a) (sessid=2540:42190) (f;f;f;wmsqa;n) (pmid=29382239) 2016-07-26 11:43:55.268765 tid:6 (pri) (ent) (cp) oraclepool.putnewpr() 2016-07-26 11:43:55.268765 tid:6 (pri) (ent) (cp) pool`3.putnewpr() 2016-07-26 11:43:55.268765 tid:6 (pri) (cp) pool`3.putnewpr() (oper=cp:put:reg1) (aff=n/a) (inst=wmsqa) (affmatch=n/a) (sessid=2540:42190) (f;f;t;wmsqa;n) (pmid=29382239) 2016-07-26 11:43:55.268765 tid:6 (pri) (ext) (cp) pool`3.putnewpr() 2016-07-26 11:43:55.268765 tid:6 (pri) (ext) (cp) oraclepool.putnewpr() 2016-07-26 11:43:55.268765 tid:6 (pri) (ext) (cp) poolmanager`3.putnewpr() (aff=n/a) (inst=wmsqa) (affmatch=n/a) (sessid=2540:42190) (f;f;t;wmsqa;n) (pmid=29382239) 2016-07-26 11:43:55.269765 tid:6 (pri) (ext) (cp) poolmanager`3.createnewpr() (aff=n/a) (inst=wmsqa) (affmatch=n/a) (sessid=2540:42190) (f;f;t;wmsqa;n) (pmid=29382239) 2016-07-26 11:43:55.269765 tid:6 (pri) (cp) poolmanager`3.populatepool() populatepoolthreadfunc(created=2; max=25; total=3) 2016-07-26 11:43:55.269765 tid:6 (pri) (ext) (cp) poolmanager`3.populatepool() 2016-07-26 11:43:55.880826 tid:15 (pri) (ent) ttcexecutesql.receiveexecuteresponse() 2016-07-26 11:43:55.881826 tid:15 (pri) (ent) oracleexception.ctor() 2016-07-26 11:43:55.885826 tid:15 (pri) (ent) oracleerror.ctor() 2016-07-26 11:43:55.886826 tid:15 (pri) (ext) oracleerror.ctor() 2016-07-26 11:43:55.886826 tid:15 (pri) (ext) oracleexception.ctor() 2016-07-26 11:43:55.890827 tid:15 (pri) (ttc) (err) ttcexecutesql.receiveexecuteresponse() (txnid=n/a) oracleinternal.network.networkexception (0x000030f9): ora-12537: network session: end of file @ oracleinternal.network.readerstream.read(orabuf ob) @ oracleinternal.ttc.orabufreader.getdatafromnetwork() @ oracleinternal.ttc.orabufreader.read(boolean bignoredata) @ oracleinternal.ttc.marshallingengine.unmarshalub1(boolean bignoredata) @ oracleinternal.ttc.ttcexecutesql.receiveexecuteresponse(accessor[]& defineaccessors, accessor[] bindaccessors, boolean bhasreturningparams, sqlmetadata& sqlmetadata, sqlstatementtype statementtype, int64 noofrowsfetchedlasttime, int32 noofrowstofetch, int32& noofrowsfetched, int64& queryid, int32 longfetchsize, int64 initiallobfetchsize, int64[] scnfromexecution, boolean ballinputbinds, int32 arraybindcount, dataunmarshaller& dataunmarshaller, marshalbindparametervaluehelper& marshalbindparamshelper, int64[]& rowsaffectedbyarraybind, boolean bdefinedone, boolean& bmorethanonerowaffectedbydmlwithretclause, list`1& implicitrslist, boolean blobarrayfetchrequired) ramping tracing 127, got :
2016-07-26 14:22:11.459348 tid:1 (pri) (svc) (ent) oracledatareaderimpl.fetchmorerows() 2016-07-26 14:22:11.459348 tid:1 (pri) (svc) (ent) oracleconnectionimpl.addallpiggybackrequests() 2016-07-26 14:22:11.459348 tid:1 (pri) (ttc) (ent) ttcclose.write() 2016-07-26 14:22:11.460348 tid:1 (pri) (ttc) (ent) ttcfunction.writefunctionheader() 2016-07-26 14:22:11.460348 tid:1 (pri) (ttc) (ent) ttcmessage.writettccode() 2016-07-26 14:22:11.460348 tid:1 (pri) (ttc) (ext) ttcmessage.writettccode() 2016-07-26 14:22:11.460348 tid:1 (pri) (ttc) (ext) ttcfunction.writefunctionheader() 2016-07-26 14:22:11.460348 tid:1 (pri) (ttc) (ext) ttcclose.write() 2016-07-26 14:22:11.460348 tid:1 (pri) (svc) (ext) oracleconnectionimpl.addallpiggybackrequests() 2016-07-26 14:22:11.460348 tid:1 (pri) (ttc) (ent) ttcexecutesql.sendexecuterequest() 2016-07-26 14:22:11.460348 tid:1 (pri) (ttc) (ent) ttcexecutesql.getexecuteoptions() 2016-07-26 14:22:11.460348 tid:1 (pri) (ttc) (ext) ttcexecutesql.getexecuteoptions() 2016-07-26 14:22:11.460348 tid:1 (pri) (ttc) (ent) ttcexecutesql.writeoall8message() 2016-07-26 14:22:11.460348 tid:1 (pri) (ttc) (ent) ttcfunction.writefunctionheader() 2016-07-26 14:22:11.460348 tid:1 (pri) (ttc) (ent) ttcmessage.writettccode() 2016-07-26 14:22:11.460348 tid:1 (pri) (ttc) (ext) ttcmessage.writettccode() 2016-07-26 14:22:11.460348 tid:1 (pri) (ttc) (ext) ttcfunction.writefunctionheader() 2016-07-26 14:22:11.460348 tid:1 (pri) (ttc) (ent) ttcexecutesql.writepisdef() 2016-07-26 14:22:11.460348 tid:1 (pri) (ttc) (ext) ttcexecutesql.writepisdef() 2016-07-26 14:22:11.460348 tid:1 (pri) (ttc) (ent) ttcexecutesql.writepisdefdata() 2016-07-26 14:22:11.461348 tid:1 (pri) (ttc) (ext) ttcexecutesql.writepisdefdata() 2016-07-26 14:22:11.461348 tid:1 (net) (snd) 00 4c 00 00 06 00 00 00 |.l......| 2016-07-26 14:22:11.461348 tid:1 (net) (snd) 00 00 |.. | 2016-07-26 14:22:11.461348 tid:1 (net) (snd) 11 69 00 01 01 01 01 02 |.i......| 2016-07-26 14:22:11.461348 tid:1 (net) (snd) 03 5e 00 02 80 40 01 03 |.^...@..| 2016-07-26 14:22:11.461348 tid:1 (net) (snd) 00 00 01 01 0d 00 00 00 |........| 2016-07-26 14:22:11.461348 tid:1 (net) (snd) 00 01 01 00 00 00 00 00 |........| 2016-07-26 14:22:11.461348 tid:1 (net) (snd) 00 00 00 00 00 00 01 00 |........| 2016-07-26 14:22:11.461348 tid:1 (net) (snd) 00 00 00 00 00 00 00 00 |........| 2016-07-26 14:22:11.461348 tid:1 (net) (snd) 02 01 36 00 00 00 03 a7 |..6.....| 2016-07-26 14:22:11.461348 tid:1 (net) (snd) eb c6 00 01 01 00 00 00 |........| 2016-07-26 14:22:11.461348 tid:1 (net) (snd) 00 00 |.. | 2016-07-26 14:22:11.461348 tid:1 (pri) (ttc) (ext) ttcexecutesql.writeoall8message() 2016-07-26 14:22:11.461348 tid:1 (pri) (ttc) (ext) ttcexecutesql.sendexecuterequest() 2016-07-26 14:22:11.461348 tid:1 (pri) (ttc) (ent) ttcexecutesql.receiveexecuteresponse() 2016-07-26 14:22:11.461348 tid:1 (pri) (buf) (cobp.get) (poolid:2) (key:8192) (bufid:8) (count:5) (orabufreader.getdatafromnetwork) 2016-07-26 14:22:12.161348 tid:1 (pri) (ent) ttcexecutesql.receiveexecuteresponse() 2016-07-26 14:22:12.162348 tid:1 (pri) (ent) oracleexception.ctor() 2016-07-26 14:22:12.162348 tid:1 (pri) (ent) oracleerror.ctor() 2016-07-26 14:22:12.162348 tid:1 (pri) (ext) oracleerror.ctor() 2016-07-26 14:22:12.162348 tid:1 (pri) (ext) oracleexception.ctor() 2016-07-26 14:22:12.163348 tid:1 (pri) (ttc) (err) ttcexecutesql.receiveexecuteresponse() (txnid=n/a) oracleinternal.network.networkexception (0x000030f9): ora-12537: network session: end of file @ oracleinternal.network.readerstream.read(orabuf ob) @ oracleinternal.ttc.orabufreader.getdatafromnetwork() @ oracleinternal.ttc.orabufreader.read(boolean bignoredata) @ oracleinternal.ttc.marshallingengine.unmarshalub1(boolean bignoredata) @ oracleinternal.ttc.ttcexecutesql.receiveexecuteresponse(accessor[]& defineaccessors, accessor[] bindaccessors, boolean bhasreturningparams, sqlmetadata& sqlmetadata, sqlstatementtype statementtype, int64 noofrowsfetchedlasttime, int32 noofrowstofetch, int32& noofrowsfetched, int64& queryid, int32 longfetchsize, int64 initiallobfetchsize, int64[] scnfromexecution, boolean ballinputbinds, int32 arraybindcount, dataunmarshaller& dataunmarshaller, marshalbindparametervaluehelper& marshalbindparamshelper, int64[]& rowsaffectedbyarraybind, boolean bdefinedone, boolean& bmorethanonerowaffectedbydmlwithretclause, list`1& implicitrslist, boolean blobarrayfetchrequired) 2016-07-26 14:22:12.163348 tid:1 (pri) (ext) ttcexecutesql.receiveexecuteresponse() 2016-07-26 14:22:12.163348 tid:1 (pri) (ttc) (ext) ttcexecutesql.receiveexecuteresponse() 2016-07-26 14:22:12.163348 tid:1 (pri) (ent) oracledatareaderimpl.fetchmorerows() 2016-07-26 14:22:12.163348 tid:1 (pri) (svc) (err) oracledatareaderimpl.fetchmorerows() (txnid=n/a) oracle.manageddataaccess.client.oracleexception (0x80004005): ora-12537: network session: end of file ---> oracleinternal.network.networkexception (0x000030f9): ora-12537: network session: end of file i tweaked query in sproc commenting out select statements , clauses, , gradually introduced them in see if it's query that's problem (running in oracle developer works fine, though). i've narrowed down statement - if reintroduce it, end of file exceptions again :
where -- handle records contain numeric value in ref field. isnumeric(ptt.ref_field_2) = 1 this isnumeric function looks in package ;
function isnumeric (p_string in varchar2) return int v_new_num number; begin if p_string null return 0; end if; v_new_num := to_number(p_string); return 1; exception when value_error return 0; end isnumeric; at first thought might number of exceptions caught preventing further execution due number of exceptions. simplified function return '1' , still didn't work every time.
the results of query passed via ref cursor e.g.
procedure devsp_api_getxxxx( p_startdate in date , p_enddate in date , p_xxx in p_xxx_type , <snip> , p_dataout out sys_refcursor ) begin open p_dataout select 'asdsa' "x", 'sadasdsad' "z", 0 "qty", <snip> finally, removed function calls select statements , clauses - , instantly stops error occurring - replaced them same logic function run - inline within select , statements.
so what's going on? there limitation in odp.net when running sprocs within package contain calls functions? seems bit odd.
the workaround not use functions, don't want select statements have conditional expressions in them repeat same logic in union-based queries.
any or pointers appreciated!
ps. transparency, posted on oracle forum last week have had no responses.
update : 03-aug-2016
finally managed access server try bit of tracing. here's came in trace log when process ends prematurely :
*** 2016-08-03 10:14:39.111 *** session id:(2526.53330) 2016-08-03 10:14:39.111 *** client id:() 2016-08-03 10:14:39.111 *** service name:(xxxxx) 2016-08-03 10:14:39.111 *** module name:(iisexpress.exe) 2016-08-03 10:14:39.111 *** client driver:(odpm.net) 2016-08-03 10:14:39.111 *** action name:() 2016-08-03 10:14:39.111 exception [type: sigsegv, address not mapped object] [addr:0x0] [pc:0x3cebe1d, pmucfst()+13] [flags: 0x0, count: 1] incident 109041 created, dump file /u01/app/oracle/diag/rdbms/xxxx/xxxx/incident/incdir_109041/xxxxx_ora_13149_i109041.trc ora-07445: exception encountered: core dump [pmucfst()+13] [sigsegv] [addr:0x0] [pc:0x3cebe1d] [address not mapped object] [] ssexhd: crashing process... shadow_core_dump = partial i can see core dump in same folder. i've tried grepping bucket trace files in there see if there's obvious - honest it's difficult search when don't know you're looking for!
i can see shedload of core dumps previous attempts run sprocs whenever used function - it's @ least reasonably consistent.
i ran same issue. oracle support responded with:
it looks hitting bug 18191823 - hang / ora-600 [19708] etc.. referencing stale refcursor bind ( doc id 18191823.8 )
workaround
ensure duration of bound pl/sql variable @ least long of ref cursor bound.
the fix first included in july 2016 patches.
Comments
Post a Comment