Hi

We are currently using LLBLGen v2.0.0.0 Final (Feb 14th, 2007) with .Net v2.0.50727. The SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll is v2.0.07.0219 and we are using Adapter scenario. We are using Oracle ODP.Net v2.102.2.20 (10.2.0.2.20) and Oracle database version 10.2.0.2.

When inserting data into a table containing a column of XmlType we get an Oracle error when the size of the data is greater than 4K. See this link for a description (and suggestion for a solution) of the error: http://www.makiwa.com/index.php/2006/07/16/ora-01461-when-inserting-into-an-oracle-xmltype-column/

Stack trace:

Method Enter: DataAccessAdapterBase.PersistQueue
Persistence action info: Action: Insert. Queue length: 1
Current persisted entity info: 
            Entity: DIPS.StructuredEpr.Generated.EntityClasses.FragmentEntity. ObjectID: 66b36ce4-ef14-47b8-bc84-4ef85e29b5f9
            PrimaryKey field: FragmentGid. Type: System.String. Value: utveee+0000000055921
Method Enter: CreateInsertDQ
Method Enter: CreateSingleTargetInsertDQ
Generated Sql query: 
            Query: INSERT INTO "DIPSSTRUCTEPRDB"."SEFRAGMENT" ("FRAGMENTGID", "VERSIONINDEPENDENTGID", "VERSION", "APPROVALSIGNATUREGID", "DISPLAYTITLE", "OWNERDOCUMENTGID", "FRAGMENTTYPEGID", "TIDSSTEMPEL", "VALIDITY", "FRAGMENTDATA") VALUES (:FragmentGid1, :VersionIndependentGid2, :Version3, :ApprovalSignatureGid4, :DisplayTitle5, :OwnerDocumentGid6, :FragmentTypeGid7, :TidsStempel8, :Validity9, :FragmentData10)
            Parameter: :FragmentGid1 : StringFixedLength. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: "utveee+0000000055921".
            Parameter: :VersionIndependentGid2 : StringFixedLength. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: "utveee+0000000055921".
            Parameter: :Version3 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.
            Parameter: :ApprovalSignatureGid4 : StringFixedLength. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: <undefined value>.
            Parameter: :DisplayTitle5 : String. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: "1".
            Parameter: :OwnerDocumentGid6 : StringFixedLength. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: <undefined value>.
            Parameter: :FragmentTypeGid7 : StringFixedLength. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: "auusev+0000000000006".
            Parameter: :TidsStempel8 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 0.
            Parameter: :Validity9 : Int16. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.
            Parameter: :FragmentData10 : String. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: "<?xml version="1.0"?>
<FragmentData xsi:type="d1p1:MockFragmentData" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:d1p1="http://DIPS.no/StructuredEpr/Test" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://DIPS.no/StructuredEpr">
  <ReferencedFragments />
  <d1p1:ReturnConstDisplayTitle>false</d1p1:ReturnConstDisplayTitle>
  <d1p1:ValidityReevaluated>false</d1p1:ValidityReevaluated>
<d1p1:Name>*** put 4000 character here !!!!   *****</d1p1:Name>
</FragmentData>".

Method Exit: CreateSingleTargetInsertDQ
Method Exit: CreateInsertDQ
Method Enter: DataAccessAdapterBase.ExecuteActionQuery
Method Enter: DataAccessAdapterBase.OpenConnection
Method Exit: DataAccessAdapterBase.OpenConnection
Method Exit: DataAccessAdapterBase.ExecuteActionQuery
Method Exit: DataAccessAdapterBase.SaveEntityCollection(3)
SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException: An exception was caught during the execution of an action query: ORA-01461: can bind a LONG value only for insert into a LONG column. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception. ---> Oracle.DataAccess.Client.OracleException ORA-01461: can bind a LONG value only for insert into a LONG column   at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure)
   at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src)
   at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery()
   at SD.LLBLGen.Pro.ORMSupportClasses.ActionQuery.Execute()
   --- End of inner exception stack trace ---
   at SD.LLBLGen.Pro.ORMSupportClasses.ActionQuery.Execute()
   at SD.LLBLGen.Pro.ORMSupportClasses.BatchActionQuery.Execute()
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteActionQuery(IActionQuery queryToExecute)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.PersistQueue(List`1 queueToPersist, Boolean insertActions)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.SaveEntityCollection(IEntityCollection2 collectionToSave, Boolean refetchSavedEntitiesAfterSave, Boolean recurse)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.SaveEntityCollection(IEntityCollection2 collectionToSave)

Note that the content of ”Parameter: :FragmentData10” is over 4K of data (the section "put 4000 character here !!!!" is shortened and should be replaced by 4000 asterisks).

The definition of the "FragmentData" column is (LLBLGen generated code):

base.AddElementFieldMapping( "FragmentEntity", "FragmentData", "FRAGMENTDATA", true, (int)OracleDbType.XmlType, 2147483647, 0, 0, false, "", null, typeof(System.String), 17 );

The problem seems to be that the definition parameter type "String" is used for the "FragmentData" column, and this makes the code break when trying to insert > 4K of data into the XmlType column. As you can see from the stacktrace, the parameter "FragmentData" is of type "String":

Parameter: :FragmentData10 : String

Again, see the example in the link provided at the top of this post.

Is there any way to get around this problem? Is this something that could be fixed in the LLBLGen libraries?

I guess this might be a problem when selecting XmlType data as well?

Thanks, Kjell-Arne

I couldn't find anything on this error except that old (2 pages) thread: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=1213

XmlType should be able to store up tp 4 gigabytes of data so my 4K should be within reach

It seems to me that the parameter type needs to be changed to OracleType.Clob (instead of String), and how I can accomplish that is what I don't understand.

Kjell-Arne

Well I may be way off, but should a string be able to store 4GB of data?

My point is that by using an XmlType I should not be limited to the 4K boundary. When LLBLGen treats the OracleParameter for the XmlType as a DbType.String that is excatly what happends when Oracle executes the query. As shown in the example (ref. the external link) the insert statement need to be modified:

cmd.CommandText = "INSERT INTO XMLTYPE_TEST (MyData) VALUES (XMLTYPE(:xmlparam))";

and the OracleParameter needs to use OracleType.Clob:

OracleParameter param = new OracleParameter(
    "xmlparam",
    OracleType.Clob,
    xml.Length, 
    ParameterDirection.Input, 
    "MyData",
    DataRowVersion.Default,
    true,
    xml);

As far as I can see this can only be done by modifying the LLBLGenPro runtime libraries.

Is there anybody that has some constructive suggestions? I believe you easily can reproduce this problem on a 10g database.

Kjell-Arne

I think it's a bug. It's either in the in the OracleSpecificCreator.cs, line 116 (Oracle DQE) or in the Oracle 10g driver's OracleSchemaRetriever.cs.

In the OracleDQE routine, there types are picked for the parameter. We had some problems with ODP.NET where it did pick the wrong dbtype based on the passed in Oracle type so we do that ourselves. Yes, this is silly, but trust me, it payed off.

Now, for the XmlType, this has to be done also I think. It currently selects the 'string' type, and doesn't set a size/length. This thus defaults to 4KB. However ODP.NET internally also selects the String type for Xml so that's not going to make a proper difference.

I think the size of the Xml type isn't properly obtained or set by the 10g driver. I'll see if I can determine exactly what the driver sees and should report on a 10g Xml type and will use that as a fix for this issue.

Opting to use 'clob' doesn't work, as oracle needs to know if it's a string (dbtype) or xml (oracledbtype). Specifying oracledbtype.clob makes things worse, it's not a clob, it's xml.

Thanks for the answer.

If you are able to make a fix I can compile a new DLL and test if it works if you send/post me the soruce code.

We had nothing but trouble with the Oracle drivers since we started using the XmlType two years ago, I'm almost starting to regret using it at all

Kjell-Arne

Reproduced. It's not the driver, that one correctly detects the size (2gb)

It works when I pass in the type correctly as Xmltype. I'll use that as the fix.

Fixed in build 2.0.07.0319

That's great!

I guess I should wait for a new version to be released then.

Kjell-Arne

KAF wrote:

That's great!

I guess I should wait for a new version to be released then.

Kjell-Arne

That will be later today