Code First error - ORA-06502: PL/SQL: numeric or value error: character to number conversion error

[aldare666]

Hello,

I am using EF code first. I am not using Entity Developer and DBContext template, just pure Entity Framework with Devart as a provider.

My entity :

Code: Select all

public class Table1
	{
		#region Constructors and Destructors

		public Table1()
		{
			this.Table2 = new WcfObservableCollection<Table2>();
			this.Table3 = new WcfObservableCollection<Table3>();
		}

		#endregion

		#region Properties

		public bool PropertyBool { get; set; }

		[NotNullValidator]
		public string PropertyText { get; set; }

		public string PropertyTextNullable { get; set; }
		public WcfObservableCollection<Table2> Table2 { get; set; }
		public WcfObservableCollection<Table3> Table3 { get; set; }

		#endregion
	}

My mapping is something like...

Code: Select all

public class TABLE1Map : EntityTypeConfiguration<Table1>
    {
        public TABLE1Map()
        {

            // Primary Key
            this.HasKey(t => t.Id);

            // Properties
            this.Property(t => t.Id)
                .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

            this.Property(t => t.PropertyText)
                .IsRequired()
                .HasMaxLength(50);

            this.Property(t => t.PropertyTextNullable)
                .HasMaxLength(50);

            // Table & Column Mappings
            this.ToTable("TABLE1", "DEV_KOTVA");
            this.MapToStoredProcedures(s=>s.Insert(i=>i.HasName("TABLE1_INSERT", "KOTVA_ADAPTER")).Update(d=>d.HasName("TABLE1_UPDATE", "KOTVA_ADAPTER")).Delete(d=>d.HasName("TABLE1_DELETE", "KOTVA_ADAPTER")));
            this.Property(t => t.Id).HasColumnName("TABLE1_ID");
            this.Property(t => t.PropertyText).IsUnicode(false).HasColumnName("PROPERTYTEXT");
            this.Property(t => t.PropertyBool).HasColumnName("PROPERTYBOOL");
			this.Property(t => t.PropertyTextNullable).IsUnicode(false).HasColumnName("PROPERTYTEXTNULLABLE");

            // Relationships
            this.HasMany(t => t.Table3)
                .WithMany(t => t.Table1)
                .Map(m => 
					{
						m.ToTable("TABLE12TABLE3");
						m.MapLeftKey("TABLE1_ID");
						m.MapRightKey("TABLE3_ID"); 
					})
                .MapToStoredProcedures(s=>s.Insert(i=>i.HasName("TABLE12TABLE3_INSERT", "KOTVA_ADAPTER")).Delete(d=>d.HasName("TABLE12TABLE3_DELETE", "KOTVA_ADAPTER")));


        }
    }

Storede procedures are :

Code: Select all

PROCEDURE Table1_Update
  (
    TABLE1_ID            IN NUMBER,
    PROPERTYTEXT         IN VARCHAR2,
    PROPERTYBOOL         IN NUMBER,
    PROPERTYTEXTNULLABLE IN VARCHAR2
  ) IS
  BEGIN
  
    UPDATE Table1 t
    SET    t.PROPERTYTEXT         = Table1_Update.PROPERTYTEXT,
           t.PROPERTYBOOL         = Table1_Update.PROPERTYBOOL,
           t.PROPERTYTEXTNULLABLE = Table1_Update.PROPERTYTEXTNULLABLE
    WHERE  t.TABLE1_ID = Table1_Update.TABLE1_ID;
  
  END Table1_Update;

PROCEDURE Table1_Insert
  (
    PROPERTYTEXT         IN VARCHAR2,
    PROPERTYBOOL         IN NUMBER,
    PROPERTYTEXTNULLABLE IN VARCHAR2,
    currParam            OUT SYS_REFCURSOR
  ) IS
    TABLE1_ID NUMBER := SQ_TABLE1.nextval;
  BEGIN
  
    INSERT INTO TABLE1 t
      (t.TABLE1_ID,
       t.PROPERTYTEXT,
       t.PROPERTYBOOL,
       t.PROPERTYTEXTNULLABLE)
    VALUES
      (Table1_Insert.TABLE1_ID,
       Table1_Insert.PROPERTYTEXT,
       Table1_Insert.PROPERTYBOOL,
       Table1_Insert.PROPERTYTEXTNULLABLE);
  
    OPEN currParam FOR
      SELECT Table1_Insert.TABLE1_ID AS TABLE1_ID
      FROM   dual;
  
  END Table1_Insert;

When I try to insert new entity it works, It is created and the ID is filled to newly created entity.
So far, so good. But when I try to update the same entity, I get an error :

ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 2

EF trace of successfull insert is :

Code: Select all

KOTVA_ADAPTER.TABLE1_INSERT
-- PROPERTYBOOL: 'False' (Type = Boolean, IsNullable = false)
-- PROPERTYTEXT: 'tble1' (Type = String, IsNullable = false, Size = 5)
-- PROPERTYTEXTNULLABLE: 'null' (Type = String, IsNullable = false)
-- Executing at 4.11.2014 10:51:26 +01:00
-- Completed in 99 ms with result: aj

EF trace of unsuccessfull update is :

Code: Select all

KOTVA_ADAPTER.TABLE1_UPDATE
-- TABLE1_ID: '40' (Type = Decimal, IsNullable = false)
-- PROPERTYBOOL: 'False' (Type = Boolean, IsNullable = false)
-- PROPERTYTEXT: 'tble1_changed' (Type = String, IsNullable = false, Size = 13)
-- PROPERTYTEXTNULLABLE: 'null' (Type = String, IsNullable = false)
-- Executing at 4.11.2014 10:51:29 +01:00
-- Failed in 140 ms with error: ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 2

When I do the same for entity without boolean, everything works. It seems the boolean=>integer conversion is done only for inserts and not for updates.

Is it possible there might be a bug? Or is there any solution for this problem?

Thank you in advance for any answer!

[aldare666]

OK, additional information : I used DBMonitor and found out the boolean value is not the reason of the problem. The problem is incorrect ordering of parameters when calling the stored procedure.

The DBMonitor log :

Code: Select all

BEGIN KOTVA_ADAPTER.TABLE1_UPDATE(:TABLE1_ID,:PROPERTYBOOL,:PROPERTYTEXT,:PROPERTYTEXTNULLABLE);END;

Parameters PROPERTYBOOL and PROPERTYTEXT are swapped. This seems to be a bug.
Could I ask anyone from Devart team to look at it and help me to overcome this problem?

Thanks a lot!

[Shalex]

There is a Describe Stored Procedure connection string parameter. By default, it is set to True which means that additional queries for stored routine metadata must be performed. This adjusts a parameters set of stored procedure in the scenarios like yours. So please remove the Describe Stored Procedure option from your connection string. Does this help?

[aldare666]

Thank you for answer.

When I set this parameter to False, I don't even get through the unit test for insertion of new entity, the error when inserting is :

Devart.Data.Oracle.OracleException: ORA-06550: line 2, column 3:
PLS-00306: wrong number or types of arguments in call to 'TABLE1_INSERT'
ORA-06550: line 2, column 3:
PL/SQL: Statement ignored

With DescribeStoredProcedure = True insertion works.

So even if subsequent update of entity was successfull, this wouldn't be a solution to my problem

[Shalex]

We cannot reproduce the problem. Please send us a small complete test project with the corresponding DDL/DML script for reproducing.

[aldare666]

Test project has been sent.

[MariiaI]

Thank you for the test project. We have reproduced the issue, we will investigate this behaviour and inform you about the results as soon as possible.
To avoid the error, please change the Table1 class:
this code

Code: Select all

public long? Id { get; set; }	
public bool PropertyBool { get; set; }
public string PropertyText { get; set; }
public string PropertyTextNullable { get; set; }

replace with this

Code: Select all

public long? Id { get; set; }	
public string PropertyText { get; set; }
public bool PropertyBool { get; set; }
public string PropertyTextNullable { get; set; }

Please notify us about the results.

[aldare666]

Thank you for your answer.

This fix works.
I already tried swapping stored procedure parameters, which works as well.
Both these fixes can be used as a temporary solution, but are not acceptable for real use.

I am looking forward to receive some good news on this issue

Thanks in advance

[MariiaI]

We have investigated the issue. It is related to the peculiarity of the fluent mapping approach with the non-conservation of the parameters order.
The most suitable solution is adding "PassParametersByName=True;" to the connection string being used.

[aldare666]

Mariial, this seems to work. Many thanks!

[MariiaI]

Glad to see that the issue was resolved. If you have any further questions, feel free to contact us.