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

Discussion of open issues, suggestions and bugs regarding Entity Framework support in ADO.NET Data providers
Post Reply
aldare666
Posts: 10
Joined: Wed 08 Oct 2014 11:31

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

Post by aldare666 » Tue 04 Nov 2014 13:23

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
Posts: 10
Joined: Wed 08 Oct 2014 11:31

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

Post by aldare666 » Tue 04 Nov 2014 15:27

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
Site Admin
Posts: 8245
Joined: Thu 14 Aug 2008 12:44

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

Post by Shalex » Thu 06 Nov 2014 09:57

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
Posts: 10
Joined: Wed 08 Oct 2014 11:31

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

Post by aldare666 » Thu 06 Nov 2014 13:28

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
Site Admin
Posts: 8245
Joined: Thu 14 Aug 2008 12:44

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

Post by Shalex » Thu 06 Nov 2014 17:57

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

aldare666
Posts: 10
Joined: Wed 08 Oct 2014 11:31

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

Post by aldare666 » Mon 10 Nov 2014 10:36

Test project has been sent.

MariiaI
Devart Team
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

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

Post by MariiaI » Tue 11 Nov 2014 14:08

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
Posts: 10
Joined: Wed 08 Oct 2014 11:31

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

Post by aldare666 » Tue 11 Nov 2014 14:39

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
Devart Team
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

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

Post by MariiaI » Wed 12 Nov 2014 07:31

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
Posts: 10
Joined: Wed 08 Oct 2014 11:31

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

Post by aldare666 » Thu 13 Nov 2014 10:05

Mariial, this seems to work. Many thanks!

MariiaI
Devart Team
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

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

Post by MariiaI » Fri 14 Nov 2014 13:54

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

Post Reply