Insert XMLType data hangs waiting for client on 64 bit server

Insert XMLType data hangs waiting for client on 64 bit server

Postby qz5kxt1 » Fri 16 Dec 2016 23:40

I wrote a new C#.Net application which reads XML files and inserts data into a Oracle table with one of the columns defined as XMLType. On my developer machine (32-bit Windows 7) it reads all of the files, inserts the data successfully. When I deploy the app on to a 64 bit Windows 12 server it reads most of the files and inserts data but when the input XML file is a little larger (13k) the app will hang on the connection.executenonquery() statement. In the Task Manager you can see it with 0 percent CPU. Using TOAD if you look at the session browser under the Waits tab I see a SQL Net waiting for client. It never completes. I have tried defining the xmltype column as binary and clob. Same result. I have been unable to find any google info that has been helpful. I have installed the latest version 9.2 of dotConnect. Here is my table ddl and some of my code below that: CREATE TABLE NUCDBA.XML_DOWNLOAD
(
DOWNLOAD_TYPE VARCHAR2(20 BYTE) NOT NULL,
XML_FILE_NAME VARCHAR2(100) NOT NULL,
XML_FILE_DATE VARCHAR2(25) NOT NULL,
DOWNLOAD_XML SYS.XMLTYPE NOT NULL,
CREATE_USER VARCHAR2(8 BYTE) NOT NULL,
CREATE_DATE DATE NOT NULL
)
XMLTYPE DOWNLOAD_XML STORE AS BINARY XML (
TABLESPACE NUCDBA_DATA
ENABLE STORAGE IN ROW
CHUNK 8192
PCTVERSION 10
NOCACHE
LOGGING
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
))

private void InsertXML()
{
try
{
if (bLogFiles)
logger.Trace("Before Insert");

sSQL = "INSERT INTO XML_DOWNLOAD VALUES(:filetype,:filename,:filedate,:downloadxml,:createuser,:createdate)";

oCommNUC = new Devart.Data.Oracle.OracleCommand(sSQL, connNUC);

oCommNUC.Parameters.Add("filetype", sXMLFileType);
oCommNUC.Parameters.Add("filename", sXMLFileName);
oCommNUC.Parameters.Add("filedate", sXMLTimeDate);
oCommNUC.Parameters.Add("downloadxml", OracleDbType.Xml);

//if (bLogFiles)
// logger.Trace("Before create new oraclexml from payload");

xml = new OracleXml(sXMLPayload, connNUC);
//if (bLogFiles)
// logger.Trace("Before assign xml to parm" + xml);
oCommNUC.Parameters["downloadxml"].Value = xml;

//if (bLogFiles)
// logger.Trace("After assign downloadxml parm value");

oCommNUC.Parameters.Add("createuser", "SYSTEM");
odtNow = new OracleDate(System.DateTime.Now);
oCommNUC.Parameters.Add("createdate", odtNow);
oCommNUC.ParameterCheck = true;
oCommNUC.Prepare();

if (bLogFiles)
logger.Trace("After Prepare ");

oCommNUC.ExecuteNonQuery();

if (bLogFiles)
logger.Trace("After Execute Insert ");

}
catch (OracleException oe)
{
logger.Trace("Oracle error msg = " + oe.ErrorCode.ToString() + oe.Message);
logger.Error("InsertXML " + oe.ErrorCode.ToString() + oe.Message);
}
catch (System.Exception e)
{
Console.WriteLine("==FATAL ERROR==");
Console.WriteLine(e.ToString());
logger.Error("System Error: " + e.Message + e.StackTrace);
}
}
Thanks for any help.
qz5kxt1
 
Posts: 3
Joined: Tue 12 Apr 2016 23:27

Re: Insert XMLType data hangs waiting for client on 64 bit server

Postby Shalex » Wed 21 Dec 2016 07:50

We have received an email that the issue is solved.
Shalex
Devart Team
 
Posts: 7782
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle