.net oracle append issue

montoyam

I have a .net framework project needing to write to an Oracle database. I am able to update, but on insert I am getting an error: "ORA-01722: invalid number"

However, all the fields in Oracle are defined as strings, and again, the Update command works no problem. The only thing I can think of is the "Returning" statement. I don't see where dtvalue is defined in the bindings, but I am guessing it is using the field that I defined in the DataTables controller, which is a string, not numeric.

var response = new Editor(db, "fnd_stanislaus_users as SU", "USERID")
                    .Model<FMSUsers>("SU")
                    .Field(new Field("SU.USERID as UserID"))
                    .Field(new Field("SU.APPLICATION as Application"))
                    .Field(new Field("SU.USER_NAME as UserName"))
                    .Field(new Field("SU.EMPLID as EmplID"))
                    .Field(new Field("SU.DEPTID as DeptID"))
                    .Field(new Field("SU.DEPARTMENT_DESCRIPTION as Department"))
                    .Field(new Field("FND_USER.START_DATE as StartDate")
                        .SetFormatter(Format.NullEmpty())
                        .GetFormatter(Format.DateSqlToFormat("MM/dd/yyyy"))
                        .Set(false)
                    )
                    .Field(new Field("FND_USER.END_DATE as EndDate")
                        .SetFormatter(Format.NullEmpty())
                        .GetFormatter(Format.DateSqlToFormat("MM/dd/yyyy"))
                        .Set(false)
                    )
                    .Field(new Field("FND_USER.DESCRIPTION as FullName").Set(false))
                    .Field(new Field("FND_USER.USER_NAME as FMSUserName").Set(false))
                    .Field(new Field("FND_USER.LAST_LOGON_DATE as LastLogin").Set(false))
                    .LeftJoin("FND_USER", "FND_USER.USER_NAME", "=", "SU.USERID")
                    .Debug(true)
                    .Process(request)
                    .Data();
                return Json(response);

Again, the update works just fine, just not insert.

allan

Hi,

I suspect it will be this. Are you able to put a debug trace on it to see if it calls into the else statement there (line 135 is the one that has my attention specifically). But that should only happen if there isn't a primary key found from the query above.

Allan

montoyam

it appears oracle didn't like having a string primary key? USERID is a string, but it is unique. When I changed it to a numeric ID field, it worked just fine. The issue is that ID field was not being populated (null) so I had to populate it with unique values and will need to get max+1 each time I append a new record as ID is not set as auto-increment.

If USERID truly is a unique field, why can't I use that as my primary? why must it be an integer field?

allan

When you say "it appears oracle didn't like having a string primary key?" do you mean our integration with Oracle, or Oracle itself? If the latter, I have no idea! You've need to contact Oracle support (although I would be surprised if that was the case).

Our code should allow for the primary key to be a string (line 123 in the link I gave above) but it might be that there is an error somewhere that I am not seeing.

Our integration will use the field which is designated as the primary in the database, not just what Editor is being told is the primary key. It isn't clear to me if that is perhaps the issue here?

Allan