How to Solve AuditLogs - No Log Inserted When Size > 2000 #2913 | Support

0

piseth created 2 years ago

When The size of exception > 2000 , i see the error "AuditLogs"."Exceptions" (actual: 2738, maximum: 2000) Then I don't see the exception being inserted in the AuditLogs table in the database. How to solve this in order to have logs being inserted in the AuditLogs table?

0

EngincanV created 2 years ago

Support Team .NET Developer

Hi, please see this answer.

0

piseth created 2 years ago

this answer

Where to create MyAuditLogContributor? In Web, or Application, or EntityFrameworkCore ?

Please note that i am using Oracle.

builder.Entity<AuditLogAction>(b => { b.Property(x => x.Parameters).HasMaxLength(int.MaxValue); });

When i run dotnet ef database update, i get the following error: Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-22858: invalid alteration of datatype

0

EngincanV created 2 years ago

Support Team .NET Developer

Where to create MyAuditLogContributor? In Web, or Application, or EntityFrameworkCore ?

You can create it in your web or application project.

Please note that i am using Oracle. builder.Entity<AuditLogAction>(b => { b.Property(x => x.Parameters).HasMaxLength(int.MaxValue); });

Then can you set the .HasColumnType("varchar2(max)") and remove the .HasMaxLength(int.MaxValue) statement as below:

builder.Entity(b =>
{
   b.Property(x => x.Parameters).HasColumnType("varchar2(max)");
});

0

piseth created 2 years ago

Where to create MyAuditLogContributor? In Web, or Application, or EntityFrameworkCore ?

You can create it in your web or application project.

Please note that i am using Oracle. builder.Entity<AuditLogAction>(b => { b.Property(x => x.Parameters).HasMaxLength(int.MaxValue); });

Then can you set the .HasColumnType("varchar2(max)") and remove the .HasMaxLength(int.MaxValue) statement as below:
builder.Entity(b => 
{ 
   b.Property(x => x.Parameters).HasColumnType("varchar2(max)"); 
}); 

In oracle, the maximum of varchar2 is 2000. i can not increase more.

0

EngincanV created 2 years ago

Support Team .NET Developer

In oracle, the maximum of varchar2 is 2000. i can not increase more.

Are you sure? As I know, varchar2 can store 4000 bytes/characters max. (https://docs.oracle.com/cd/E18283_01/server.112/e17110/limits001.htm)

0

piseth created 2 years ago

In oracle, the maximum of varchar2 is 2000. i can not increase more.

Are you sure? As I know, varchar2 can store 4000 bytes/characters max. (https://docs.oracle.com/cd/E18283_01/server.112/e17110/limits001.htm)

Yes you're right. But I get error when I increase the size:

    builder.Entity&lt;AuditLog&gt;(b =>
    {
        b.Property(x => x.Exceptions).HasColumnType("nvarchar2(4000)");
    });

    builder.Entity&lt;AuditLogAction&gt;(b =>
    {
        b.Property(x => x.Parameters).HasColumnType("nvarchar2(4000)");
    });

2022-04-16 16:17:17.791266 ThreadID:1 (ERROR) OracleRelationalCommand.ExecuteNonQuery() : Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-00910: specified length too long for its datatype ORA-06512: at line 12 at OracleInternal.ServiceObjects.OracleConnectionImpl.VerifyExecution(Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, SqlStatementType sqlStatementType, Int32 arrayBindCount, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone) at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteNonQuery(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, OracleException& exceptionForArrayBindDML, OracleConnection connection, Boolean isFromEF) at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteNonQuery() at Oracle.EntityFrameworkCore.Storage.Internal.OracleRelationalCommandBuilderFactory.OracleRelationalCommandBuilder.OracleRelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject) Failed executing DbCommand (378ms) [Parameters=[], CommandType='Text', CommandTimeout='0'] declare l_nullable user_tab_columns.nullable % type; begin select nullable into l_nullable from user_tab_columns where table_name = 'QoEAuditLogs' and column_name = 'Exceptions' ; if l_nullable = 'N' then EXECUTE IMMEDIATE 'ALTER TABLE "QoEAuditLogs" MODIFY "Exceptions" nvarchar2(4000) NULL'; else EXECUTE IMMEDIATE 'ALTER TABLE "QoEAuditLogs" MODIFY "Exceptions" nvarchar2(4000)'; end if; end;

0

piseth created 2 years ago

any update plz?

0

EngincanV created 2 years ago

Support Team .NET Developer

What was your Oracle version?

0

piseth created 2 years ago

What was your Oracle version?

Sorry for late reply. My oracle is 19c Enterprise Edition Release 19.0.0.0.0

0

EngincanV created 2 years ago

Support Team .NET Developer

NCHAR and NVARCHAR2 use character length semantics. The number of characters for columns with one of these data types depend on the character set, NLS_NCHAR_CHARACTERSET. ODP.NET Entity Framework Core defaults to a 2-byte character set, which allows a maximum of 2000 characters for NCHAR and NVARCHAR2 columns. If a [Maxlength(4000)] data annotation or fluent API equivalent is used for a string entity property, ODP.NET will map the property to an NCLOB type because the specified length is greater than 2000 characters. (https://docs.oracle.com/en/database/oracle/oracle-database/21/odpnt/EFCoreDataTypeMapping.html#GUID-484E9D3A-8E42-417F-9591-F2E7305E3F6A)

According to the description, you either need to add [MaxLength(4000)] data annotation to the property or change the data type to NCLOB.

builder.Entity<AuditLog>(b =>
    {
        b.Property(x => x.Exceptions).HasColumnType("CLOB").HasMaxLength(4000);
    });
    
builder.Entity<AuditLogAction>(b =>
    {
        b.Property(x => x.Parameters).HasColumnType("CLOB").HasMaxLength(4000);
    });

0

piseth created 2 years ago

.HasMaxLength(4000)

Thank you. I follow you by adding the following:

builder.Entity<AuditLog>(b => { b.Property(x => x.Exceptions).HasColumnType("CLOB").HasMaxLength(4000); });

builder.Entity<AuditLogAction>(b => { b.Property(x => x.Parameters).HasColumnType("CLOB").HasMaxLength(4000); });

BUT i get the following error after dotnet ef database update.

declare l_nullable user_tab_columns.nullable % type; begin select nullable into l_nullable from user_tab_columns where table_name = 'QoEAuditLogs' and column_name = 'Exceptions' ; if l_nullable = 'N' then EXECUTE IMMEDIATE 'ALTER TABLE "AuditLogs" MODIFY "Exceptions" CLOB NULL'; else EXECUTE IMMEDIATE 'ALTER TABLE "AuditLogs" MODIFY "Exceptions" CLOB'; end if; end; Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-22858: invalid alteration of datatype

I google about it. It seems that it is not allowed to change from nvarchar2 to CLOB. From the following link, it is required to change nvarchar2 to other data type first. secondly change to CLOB. https://stackoverflow.com/questions/13402510/oracle-changing-varchar2-column-to-clob if so, how can i do that in MyProjectDbContext?

1

maliming created 2 years ago

Support Team Fullstack Developer

hi

You can try to add twice migrations. change nvarchar2 to other data type first. secondly change to CLOB.

How to Solve AuditLogs - No Log Inserted When Size > 2000 #2913

13 Answer(s)