Varchar2_to_blob, applying a time-out issue when updating LOB fields to the database

Converts a string to BLOB type data and writes to the server.

1, first use the To_clob function to turn the VARCHAR2 field into a CLOB field.

2 Use the C2B above function to turn clob into blobs.

That is: C2B (To_clob (varchar2 field))

CREATE OR REPLACE FUNCTIONC2B (binchClobdefaultEmpty_clob ())RETURNBLOB--typecasts BLOB to CLOB (binary conversion) isRes BLOB; B_len Number:=Dbms_lob.getlength (b); Dest_offset1 Number:= 1; Src_offset1 Number:= 1; Amount_cINTEGER:=dbms_lob.lobmaxsize; Blob_csid Number:=Dbms_lob.default_csid; Lang_ctxINTEGER:=Dbms_lob.default_lang_ctx; WarningINTEGER;BEGIN   ifB_len> 0   Thendbms_lob.createtemporary (res, TRUE); Dbms_lob.OPEN(res, dbms_lob.lob_readwrite); Dbms_lob.converttoblob (res, B, Amount_c, DE St_offset1, Src_offset1, Blob_csid, Lang_ctx , warning); Else   SelectEmpty_blob () intoRes fromdual; End if ; RETURNRes--Res is OPEN hereENDC2B;

Example:

Declare BB blob; begin BB: = c2b (to_clob ('abc')); Dbms_output.put_line (Dbms_lob.getlength (BB));   Insert  into  Values(2commits; End;

Ok!

Issue background:

A metadata update save function, which is often stuck in a state of deadlock, sometimes occurs when SQL execution that further blocks other functions (can be confirmed when querying blocking information from the database side).

Crawling the dump on the application server can actually see that the app has been writing a LOB field to the database, but in common sense, the network transmission should be short-lived.

Verification Method:

1. Use Sqlplus directly on the application server to update a LOB field (transfer content from the application server), or to have a simple console program update a lob to the database to verify the response time.

2, let the application server bypass the protection wall, through the core switch directly connected to the database server, make difference contrast.

Combining the dump of the application server with the session state of the database side, the wait event, the application and the database are indeed transmitting the contents of a LOB field. Of course, the network transmission time under normal circumstances should be very short.

Please contact the customer administrator to verify that the application server to the database has an enterprise-grade firewall and other hardware devices, check the protection wall configuration and logs are abnormal.

Sql:

94

17186

ACTIVE

Sql*net more data from client

LC0039999

workgroup\win-div4vrinj9h

W3wp.exe

Dump Thread:

OS ThreadId: 0x3e38( the) Child SP IPPagersite000000000d8acc78 0000000076f9fefa [Ndirectmethodframestandalone:000000000d8acc78] System.Data.Common.UnsafeNativeMethods.OCILobWrite( System.Data.OracleClient.OciHandle, System.Data.OracleClient.OciHandle, System.Data.OracleClient.OciHandle, UInt32 ByRef, UInt32, IntPtr, UInt32, Byte, IntPtr, IntPtr, UInt16, charsetform) 000000000d8acbf0 000007fef2b0395b Domainn Eutralilstubclass.il_stub_pinvoke (System.Data.OracleClient.OciHandle, System.Data.OracleClient.OciHandle, System.Data.OracleClient.OciHandle, UInt32 ByRef, UInt32, IntPtr, UInt32, Byte, IntPtr, IntPtr, UInt16, charsetform) * * * /c0>WARNING:Unable to verify checksum for System.Data.OracleClient.ni.dll000000000d8acd30 000007FEF2AFEDFB System.Data.OracleClient.TracedNativeMethods.OCILobWrite (System.Data.OracleClient.OciHandle, System.Data.OracleClient.OciHandle, System.Data.OracleClient.OciHandle,Int32ByRef, UInt32, IntPtr, UInt32, Byte, UInt16, charsetform) 000000000d8ace30 000007fef2b2ca45  System.Data.OracleClient.OracleLob.Write (byte[],  Int32, Int32  )000000000d8acf10 000007fef2b1c021 System.Data.OracleClient.OracleParameterBinding.CreateTemporaryLobForValue ( System.Data.OracleClient.OracleConnection, System.Data.OracleClient.OracleType, System.Object) 000000000d8acf60 000007fef2b1cb67 System.Data.OracleClient.OracleParameterBinding.PrepareForBind ( System.Data.OracleClient.OracleConnection,Int32ByRef) 000000000d8acfd0 000007fef2b0e1b0 System.Data.OracleClient.OracleCommand.Execute ( System.Data.OracleClient.OciStatementHandle, System.Data.CommandBehavior, Boolean, System.Data.OracleClient.OciRowidDescriptor ByRef, System.Collections.ArrayList ByRef) 000000000d8ad0c0 000007fef2b0e7e5 System.Data.OracleClient.OracleCommand.ExecuteNonQueryInternal (Boolean, System.Data.OracleClient.OciRowidDescriptor ByRef) 000000000d8ad150 000007FEF2B0E6FD System.Data.OracleClient.OracleCommand.ExecuteNonQuery () 000000000d8ad200 000007ff001eee35 Genersoft.Platform.Core.DataAccess.Database.ExecuteWithNoQuery (System.String, system.data.idbdataparameter[], Boolean) * * *ERROR:Module Load Completed butSymbols could not  beLoaded for Genersoft.Platform.Core.DataAccess.dll000000000d8ad2b0 000007ff00c7dbb9 Genersoft.Platform.Core.DataAccess.Database.RunProcWithNoQuery (System.String, system.data.idbdataparameter[]) 000000000d8ad300 000007ff00c7d787 Genersoft.Platform.Resource.ResourceManager.DataModelManager.SaveModel ( Genersoft.Platform.Resource.Metadata.DataModel.GspDataModel) * * *ERROR:Module Load Completed butSymbols could not  beLoaded for Genersoft.Platform.Resource.DataModelManager.dll000000000d8ad3f0 000007ff00c7d168 Genersoft.Platform.Resource.ResourceManager.DataModelManager.SaveModelAllInfor ( Genersoft.Platform.Resource.Metadata.DataModel.GspDataModel) 000000000d8ad430 000007ff00c7c120 Genersoft.Platform.Resource.ResourceManager.MetadataService.SaveAndCheckInMetadata (System.String, System.String , System.String, System.String, System.String, System.String, System.String, System.String, System.String, System.DateTime, Genersoft.Platform.Resource.Metadata.IMetadataContent, System.String) * * *ERROR:Module Load Completed butSymbols could not  beLoaded for Genersoft.Platform.Resource.MetadataManager.dll000000000d8ad600 000007ff00c73dd0 Genersoft.Platform.RuntimeADP.Core.Manager.GSPBizEntityManager.SaveBizEntity ( Genersoft.Platform.RuntimeADP.SPI.IBusinessEntity, Boolean, Genersoft.Platform.RuntimeADP.SPI.BizUnitAssignment, Boolean, Boolean ByRef, System.Collections.Generic.Dictionary '2<System.String,Genersoft.Platform.Resource.Metadata.DataObject.GspDataTable>) * * *WARNING:Unable to verify checksum for genersoft.platform.runtimeadp.core.dll***ERROR:Module Load Completed butSymbols could not  beLoaded for Genersoft.Platform.RuntimeADP.Core.dll000000000d8ad7b0 000007ff00b8f92a Genersoft.Platform.RuntimeADP.Core.Manager.GSPBizEntityManager.AddBizEntity ( Genersoft.Platform.RuntimeADP.SPI.IBusinessEntity, Genersoft.Platform.RuntimeADP.SPI.BizUnitAssignment, Boolean BYREF) 000000000d8ad8b0 000007ff00b8f4d8 Genersoft.Platform.RuntimeADP.Core.Manager.GSPBizEntityManager.AddBizEntity ( Genersoft.Platform.RuntimeADP.SPI.IBusinessEntity, Genersoft.Platform.RuntimeADP.SPI.BizUnitAssignment) 000000000d8ad8f0 000007ff00b85894 Genersoft.HR.Sys.RunTimeCore.Manager.HRSalaryRuntimeManager.SubSetSynchronization (System.String, System.String, System.String, System.Collections.Generic.List '1<genersoft.hr.sys.spi.hrwritebackfieldmapping>, System.String) * * *WARNING:Unable to verify checksum for genersoft.hr.sys.runtimecore.dll***ERROR:Module Load Completed butSymbols could not  beLoaded for Genersoft.HR.Sys.RunTimeCore.dll000000000d8adb30 000007ff00b84a4f Genersoft.HR.Sys.RunTimeCore.Manager.HRSalaryRuntimeManager.SubSetSynchronization (System.String) 000000000d8ae328 000007FEF95A10B4 [Debuggeru2mcatchhandlerframe:000000000d8ae328] 000000000d8ae410 000007fef95a10b4 [Customgcframe:000000000d8ae410] 000000000d8ae3d8 000007fef95a10b4 [Gcframe:000000000d8ae3d8] 000000000d8ae368 000007fef95a10b4 [Gcframe:000000000d8ae368] 000000000d8ae738 000007fef95a10b4 [Helpermethodframe_protectobj:000000000d8ae738] System.runtimemethodhandle._invokemethodfast (System.iruntimemethodinfo, System.Object, System.object[], system.signaturestruct ByRef, System.Reflection.MethodAttributes, System.runtimetype) 000000000d8ae880 000007fef865587f System.Reflection.RuntimeMethodInfo.Invoke (System.Object, System.Reflection.BindingFlags, System.Reflection.Binder, system.object[], System.Globalization.CultureInfo, Boolean) 000000000d8ae9c0 000007fef86a0426 System.Reflection.RuntimeMethodInfo.Invoke (System.Object, System.Reflection.BindingFlags, System.Reflection.Binder, system.object[], System.Globalization.CultureInfo) 000000000D8AEA10 000007ff005635a5 Genersoft.Platform.AppFramework.Service.GSPRestfulContext.Invoke (System.String , System.String, System.String, Boolean, system.string[],Int32[] ByRef, system.string[] ByRef) * * *ERROR:Module Load Completed butSymbols could not  beLoaded for Genersoft.Platform.AppFramework.RestfulService.dll000000000d8aead0 000007ff00563174 Genersoft.Platform.AppFramework.RESTFulWebService.GSPHttpWebHandler.Invoke (System.IO.BinaryReader, SYSTEM.WEB.HTTPCONTEXT) * * *ERROR:Module Load Completed butSymbols could not  beLoaded for Genersoft.Platform.AppFramework.RESTFulWebService.dll000000000d8aebd0 000007ff001efc76 Genersoft.Platform.AppFramework.RESTFulWebService.GSPHttpWebHandler.ProcessRequest (System.Web.HttpContext) 000000000d8aecd0 000007fef3625c25 system.web.httpapplication+ CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute () * * *WARNING:Unable to verify checksum for System.Web.ni.dll000000000d8aed90 000007fef35f337a System.Web.HttpApplication.ExecuteStep (IExecutionStep, Boolean ByRef) 000000000d8aee40 000007fef3607030 System.web.httpapplication+applicationstepmanager.resumesteps (System.Exception) 000000000d8aef00 000007fef35f2879 System.Web.HttpApplication.System.Web.IHttpAsyncHandler.BeginProcessRequest ( System.Web.HttpContext, System.AsyncCallback, System.Object) 000000000d8aef60 000007fef35f777c System.Web.HttpRuntime.ProcessRequestInternal (System.Web.HttpWorkerRequest) 000000000d8aefe0 000007fef35f6477 System.Web.Hosting.ISAPIRuntime.ProcessRequest (INTPTR,Int32) 000000000d8af110 000007fef3d5d6dd domainneutralilstubclass.il_stub_comtoclr (Int64,Int32,Int32ByRef) 000000000d8af448 000007fef960e93e [Gcframe:000000000d8af448] 000000000d8af488 000007fef960e93e [Contexttransitionframe:000000000d8af488] 000000000d8af4c8 000007fef960e93e [Gcframe:000000000d8af4c8] 000000000d8af6b0 000007fef960e93e [Commethodframe:000000000D8AF6B0]

Varchar2_to_blob, applying a time-out issue when updating LOB fields to the database