Exception thrown using reader.GetOracleLob

[flhd2000]

When I execute the following code:

Code: Select all

     OracleCommand cmd = new OracleCommand("SELECT DATA_ID " +
                                                   ",CONTROL_ID " +
                                                   ",ASSOCIATE_ID " +
                                                   ",STATUS_CD " +
                                                   ",TYPE_CD " +
                                                   ",ENTITY_ID " +
                                                   ",CHECKOUT_DT " +
                                                   ",CHECKIN_DT " +
                                                   ",RECON_DT " +
                                                   ",XMLType.getClobVal(ENTITY_OUT_DATA) AS ENTITY_OUT_DATA " +
                                                   ",XMLType.getClobVal(ENTITY_IN_DATA) AS ENTITY_IN_DATA" +
                                                   ",XMLType.getClobVal(ENTITY_BASE_DATA) AS ENTITY_BASE_DATA" +
                                                   ",LAST_CHGD_ID " +
                                                   ",LAST_CHGD_DT " +
                                                   "FROM STAGING_DATA WHERE STAGING_DATA_ID = :STAGING_DATA_ID", conn);
OracleDataReader reader = cmd.ExecuteReader();
try
{
        while (reader.Read())
        {
                dcMobileStaging rec = new dcMobileStaging();
                //rec.rec.mENTITY_OUT_DATA IS a type of OracleLob(CLOB);
                rec.mENTITY_OUT_DATA = reader.GetOracleLob("ENTITY_OUT_DATA");
                stagingList.Add(rec);
        }
}
finally
{
        reader.close();
}

The query seems to execute without exceptions. However, when I try to use the reader object to retrieve the data and assign the data I recieve the following errors:

Code: Select all

-		reader.GetOracleLob("ENTITY_OUT_DATA")	{Devart.Data.Oracle.OracleLob}	Devart.Data.Oracle.OracleLob
+		base	{Devart.Data.Oracle.OracleLob}	System.IO.Stream {Devart.Data.Oracle.OracleLob}
		Cached	false	bool
		CanRead	true	bool
		CanSeek	true	bool
		CanWrite	true	bool
+		ChunkSize	'reader.GetOracleLob("ENTITY_OUT_DATA").ChunkSize' threw an exception of type 'Devart.Data.Oracle.OracleException'	int {Devart.Data.Oracle.OracleException}
+		Connection	{Devart.Data.Oracle.OracleConnection}	Devart.Data.Oracle.OracleConnection
		IsBatched	false	bool
+		IsEmpty	'reader.GetOracleLob("ENTITY_OUT_DATA").IsEmpty' threw an exception of type 'Devart.Data.Oracle.OracleException'	bool {Devart.Data.Oracle.OracleException}
		IsNull	false	bool
		IsTemporary	true	bool
+		Length	'reader.GetOracleLob("ENTITY_OUT_DATA").Length' threw an exception of type 'Devart.Data.Oracle.OracleException'	long {Devart.Data.Oracle.OracleException}
		LobType	Clob	Devart.Data.Oracle.OracleDbType
		Position	0	long
+		Value	'reader.GetOracleLob("ENTITY_OUT_DATA").Value' threw an exception of type 'Devart.Data.Oracle.OracleException'	object {Devart.Data.Oracle.OracleException}
+		Static members
+		Non-Public members

I am able to successfully access the rest of the non-lob attributes returned by the query.
I should also mention this error only happens with very large CLOB below is the length of the data I am working with:

DBMS_LOB.GetLength(XMLTYPE.getClobVal(ENTITY_OUT_DATA))

156447

I am using dotConnect for Oracle Version: 7.9.333
OS: Windows 7
.NET Framework 4.5


Thanks in advance for any help.

[Pinturiccio]

We could not reproduce the issue. Please tell us the following:
1. Oracle server version;
2. the connection mode you use: Direct or OCI;
3. If you use OCI connection mode, specify Oracle Client version;
4. DDL/DML scripts.

Please also send us a small test project for reproducing the issue and the xml with which the issue is reproduced.

[flhd2000]

Oracle Server Version: 11gR2
Oracle Client Version:11g
Connecting Using: OCI

What is the standard way of uploading demo projects?

[flhd2000]

We have figured out the issue. It appears there is an Oracle issue in 11gR2 with the function XMLType.getClobVal(). When using this particular function the entire listener was killed. The resolution was to use OracleXML in my code and do not use the conversion.

[Pinturiccio]

We are glad to hear that you have found the reason of the issue and a workaround. If you have any questions, feel free to contact us.