Home » Other » Client Tools » Update failed ORA-01722:invalid number (Embarcadero RAD Studio XE, Oracle 11g)
Update failed ORA-01722:invalid number [message #507794] Wed, 18 May 2011 00:46 Go to next message
Al_Petro
Messages: 14
Registered: January 2011
Junior Member
Hi All!
Enviroment is Embarcadero RAD Studio XE. Work with DataSnap WebBroker Application. Its Server methods ancestor is TDSServerModule.
I've got SQLConnection (dbx), SQLDataSet, DataSetProvider on server side and SQLConnection, DSProviderConnection, ClientDataSet plus DataSource, DBGrid, DBNavigator on client side.
SQLConnection on server side uses Oracle driver - dbxora.dll. DB: Oracle 11g.
SQLConnection on client side uses Datasnap driver.
SQLDataSet has DbxCommandType set to Dbx.SQL, CommandText: "Select * from Table1".
All fields except indexed one have their pfInWhere set to false.
updateMode of DataSetProvaider is set to upWhereKeyOnly.
All is well up to point where ApplayUpdates is fired. It does nothing. HandleReconcileError shows ORA-01722: invalid number.
Oracle explains:
ORA-01722:invalid number
Cause: The attempted conversion of a character string to a number failed because the character string was not a valid numeric literal. Only numeric fields or character fields containing numeric data may be used in arithmetic functions or expressions. Only numeric fields may be added to or subtracted from dates.
Action: Check the character strings in the function or expression. Check that they contain only numbers, a sign, a decimal point, and the character "E" or "e" and retry the operation.
But all updated fields are of character strings type. There is no need for conversion.
I suppose the automatically created SQL has some extra checking. But I cannot see those SQLs.
I guess must be a way of controlling those SQLs thru params, but don't know which.
May be comparison old value - new value of index column is depending on this conversion?
Please help!
Re: Update failed ORA-01722:invalid number [message #507970 is a reply to message #507794] Wed, 18 May 2011 11:27 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
Senior Member
Al_Petro wrote on Wed, 18 May 2011 01:46
... Etc ...
ORA-01722:invalid number
Cause: The attempted conversion of a character string to a number failed because the character string was not a valid numeric literal.
... Etc ...
Please help!

You need to check the actual column type(s) defined in the database table.
One or more of your fields that are defined as character has a non -numeric value and may be mapping to a numeric column in the database and therefore the attempt to do a character to number conversion occurs.

[Updated on: Wed, 18 May 2011 11:36] by Moderator

Report message to a moderator

Re: Update failed ORA-01722:invalid number [message #507972 is a reply to message #507794] Wed, 18 May 2011 11:29 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Please help!
Help is NOT possible.
We don't know SQL that causes the error.
We don't know table DML operates against.
We don't know raw data causing the error.
You have a mystery & we have no clues.
Sad
Re: Update failed ORA-01722:invalid number [message #508094 is a reply to message #507972] Thu, 19 May 2011 07:23 Go to previous messageGo to next message
Al_Petro
Messages: 14
Registered: January 2011
Junior Member
Thank you for your replies!
They give me hope.
Table Script is:
ALTER TABLE DBO_HISTSQL.DIRTTBL
 DROP PRIMARY KEY CASCADE;

DROP TABLE DBO_HISTSQL.DIRTTBL CASCADE CONSTRAINTS;

CREATE TABLE DBO_HISTSQL.DIRTTBL
(
  UNICDIRT    NUMBER(10)                        NOT NULL,
  CLEANCODE   CHAR(36 CHAR),
  NATIVECODE  CHAR(36 CHAR)                     NOT NULL,
  CHNGEDCODE  CHAR(36 CHAR),
  DESCRIPTIN  VARCHAR2(256 CHAR)
)
TABLESPACE USERS
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE UNIQUE INDEX DBO_HISTSQL.PK95 ON DBO_HISTSQL.DIRTTBL
(UNICDIRT)
LOGGING
TABLESPACE USERS
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE OR REPLACE TRIGGER DBO_HISTSQL.DirtTbl_UnicDirt_TRG BEFORE INSERT OR UPDATE ON DBO_HISTSQL.DIRTTBL FOR EACH ROW
DECLARE 
v_newVal NUMBER(12) := 0;
v_incval NUMBER(12) := 0;
BEGIN
  IF INSERTING AND :new.UnicDirt IS NULL THEN
    SELECT  DirtTbl_UnicDirt_SEQ.NEXTVAL INTO v_newVal FROM DUAL;
    -- If this is the first time this table have been inserted into (sequence == 1)
    IF v_newVal = 1 THEN 
      --get the max indentity value from the table
      SELECT NVL(max(UnicDirt),0) INTO v_newVal FROM DirtTbl;
      v_newVal := v_newVal + 1;
      --set the sequence to that value
      LOOP
           EXIT WHEN v_incval>=v_newVal;
           SELECT DirtTbl_UnicDirt_SEQ.nextval INTO v_incval FROM dual;
      END LOOP;
    END IF;
    -- save this to emulate @@identity
   sqlserver_utilities.identity := v_newVal; 
   -- assign the value from the sequence to emulate the identity column
   :new.UnicDirt := v_newVal;
  END IF;
END;
/


ALTER TABLE DBO_HISTSQL.DIRTTBL ADD (
  CONSTRAINT CK__DIRTTBL__CHNGEDC__30C33EC3
  CHECK (
  ( NOT REGEXP_LIKE(ChngedCode, '%[^\ 0123456789]%') )
),
  CONSTRAINT CK__DIRTTBL__CLEANCO__32AB8735
  CHECK (
  ( NOT REGEXP_LIKE(CleanCode, '%[^\ 0123456789]%') )
),
  CONSTRAINT CK__DIRTTBL__NATIVEC__3493CFA7
  CHECK (
  ( NOT REGEXP_LIKE(NativeCode, '%[^\ 0123456789]%') )
),
  CONSTRAINT PK95
  PRIMARY KEY
  (UNICDIRT)
  USING INDEX DBO_HISTSQL.PK95);

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE ON DBO_HISTSQL.DIRTTBL TO PUBLIC;


Components on the server side look like this:
object SQLConnection1: TSQLConnection
    ConnectionName = 'ORA_FEB2'
    DriverName = 'ORACLE'
    GetDriverFunc = 'getSQLDriverORACLE'
    LibraryName = 'dbxora.dll'
    LoginPrompt = False
    Params.Strings = (
      'drivername=ORACLE'
      'database=FEB2'
      'user_name=DBO_HISTSQL'
      'Password=LokoT22'
      'blobsize=-1'
      'localecode=0000'
      'isolationlevel=ReadCommitted'
      'rowsetsize=20'
      'os authentication=False'
      'multiple transaction=False'
      'trim char=False'
      'decimal separator=.')
    VendorLib = 'oci.dll'
    Connected = True
    Left = 48
    Top = 16
  end
  object SQLDataSet1: TSQLDataSet
    SchemaName = 'DBO_HISTSQL'
    Active = True
    CommandText = 'SELECT * FROM DIRTTBL'
    DbxCommandType = 'Dbx.SQL'
    MaxBlobSize = -1
    Params = <>
    SQLConnection = SQLConnection1
    Left = 128
    Top = 16
    object SQLDataSet1UNICDIRT: TFMTBCDField
      FieldName = 'UNICDIRT'
      ProviderFlags = [pfInWhere]
      Precision = 10
      Size = 0
    end
    object SQLDataSet1CLEANCODE: TWideStringField
      FieldName = 'CLEANCODE'
      ProviderFlags = [pfInUpdate]
      FixedChar = True
      Size = 36
    end
    object SQLDataSet1NATIVECODE: TWideStringField
      FieldName = 'NATIVECODE'
      ProviderFlags = [pfInUpdate]
      Required = True
      FixedChar = True
      Size = 36
    end
    object SQLDataSet1CHNGEDCODE: TWideStringField
      FieldName = 'CHNGEDCODE'
      ProviderFlags = [pfInUpdate]
      FixedChar = True
      Size = 36
    end
    object SQLDataSet1DESCRIPTIN: TWideStringField
      FieldName = 'DESCRIPTIN'
      ProviderFlags = [pfInUpdate]
      Size = 256
    end
  end
  object dspEmloyees: TDataSetProvider
    DataSet = SQLDataSet1
    Options = [poRetainServerOrder, poUseQuoteChar]
    UpdateMode = upWhereKeyOnly
    Left = 216
    Top = 16
  end


On the client grid shows the "dirttable" and allows to change data. But ApplayUpdates doesn't work. Even if changed only one field "discriptin" which has no any constraints.
With best regards. Alex.


[mod-edit: code tags added by bb; next time please add them yourself]

[Updated on: Sat, 21 May 2011 04:42] by Moderator

Report message to a moderator

Re: Update failed ORA-01722:invalid number [message #508110 is a reply to message #508094] Thu, 19 May 2011 08:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel
Re: Update failed ORA-01722:invalid number [message #508174 is a reply to message #508094] Thu, 19 May 2011 13:10 Go to previous messageGo to next message
Al_Petro
Messages: 14
Registered: January 2011
Junior Member
Thank you for your replies!
They give me hope.
Oracle version is 11.2.0.1
Table Script is:
ALTER TABLE DBO_HISTSQL.DIRTTBL
 DROP PRIMARY KEY CASCADE;

DROP TABLE DBO_HISTSQL.DIRTTBL CASCADE CONSTRAINTS;

CREATE TABLE DBO_HISTSQL.DIRTTBL
(
  UNICDIRT    NUMBER(10)   NOT NULL,
  CLEANCODE   CHAR(36 CHAR),
  NATIVECODE  CHAR(36 CHAR)  NOT NULL,
  CHNGEDCODE  CHAR(36 CHAR),
  DESCRIPTIN  VARCHAR2(256 CHAR)
)
TABLESPACE USERS
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE UNIQUE INDEX DBO_HISTSQL.PK95 ON DBO_HISTSQL.DIRTTBL
(UNICDIRT)
LOGGING
TABLESPACE USERS
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;

CREATE OR REPLACE TRIGGER DBO_HISTSQL.DirtTbl_UnicDirt_TRG
 BEFORE INSERT OR UPDATE ON DBO_HISTSQL.DIRTTBL FOR EACH ROW
DECLARE 
v_newVal NUMBER(12) := 0;
v_incval NUMBER(12) := 0;
BEGIN
  IF INSERTING AND :new.UnicDirt IS NULL THEN
    SELECT  DirtTbl_UnicDirt_SEQ.NEXTVAL INTO v_newVal FROM DUAL;
 into (sequence == 1)
    IF v_newVal = 1 THEN 
      SELECT NVL(max(UnicDirt),0) INTO v_newVal FROM DirtTbl;
      v_newVal := v_newVal + 1;
      LOOP
           EXIT WHEN v_incval>=v_newVal;
           SELECT DirtTbl_UnicDirt_SEQ.nextval INTO v_incval FROM dual;
      END LOOP;
    END IF;
   sqlserver_utilities.identity := v_newVal; 
 column
   :new.UnicDirt := v_newVal;
  END IF;
END;
/

ALTER TABLE DBO_HISTSQL.DIRTTBL ADD (
  CONSTRAINT CK__DIRTTBL__CHNGEDC__30C33EC3
  CHECK (
  ( NOT REGEXP_LIKE(ChngedCode, '%[^\ 0123456789]%') )
),
  CONSTRAINT CK__DIRTTBL__CLEANCO__32AB8735
  CHECK (
  ( NOT REGEXP_LIKE(CleanCode, '%[^\ 0123456789]%') )
),
  CONSTRAINT CK__DIRTTBL__NATIVEC__3493CFA7
  CHECK (
  ( NOT REGEXP_LIKE(NativeCode, '%[^\ 0123456789]%') )
),
  CONSTRAINT PK95
  PRIMARY KEY
  (UNICDIRT)
  USING INDEX DBO_HISTSQL.PK95);

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE 
ON DBO_HISTSQL.DIRTTBL TO PUBLIC;

Components on the server side look like this:
 object SQLConnection1: TSQLConnection
    ConnectionName = 'ORA_FEB2'
    DriverName = 'ORACLE'
    GetDriverFunc = 'getSQLDriverORACLE'
    LibraryName = 'dbxora.dll'
    LoginPrompt = False
    Params.Strings = (
      'drivername=ORACLE'
      'database=FEB2'
      'user_name=DBO_HISTSQL'
      'Password=LokoT22'
      'blobsize=-1'
      'localecode=0000'
      'isolationlevel=ReadCommitted'
      'rowsetsize=20'
      'os authentication=False'
      'multiple transaction=False'
      'trim char=False'
      'decimal separator=.')
    VendorLib = 'oci.dll'
    Connected = True
    Left = 48
    Top = 16
  end
  object SQLDataSet1: TSQLDataSet
    SchemaName = 'DBO_HISTSQL'
    Active = True
    CommandText = 'SELECT * FROM DIRTTBL'
    DbxCommandType = 'Dbx.SQL'
    MaxBlobSize = -1
    Params = <>
    SQLConnection = SQLConnection1
    Left = 128
    Top = 16
    object SQLDataSet1UNICDIRT: TFMTBCDField
      FieldName = 'UNICDIRT'
      ProviderFlags = [pfInWhere]
      Precision = 10
      Size = 0
    end
    object SQLDataSet1CLEANCODE: TWideStringField
      FieldName = 'CLEANCODE'
      ProviderFlags = [pfInUpdate]
      FixedChar = True
      Size = 36
    end
    object SQLDataSet1NATIVECODE: TWideStringField
      FieldName = 'NATIVECODE'
      ProviderFlags = [pfInUpdate]
      Required = True
      FixedChar = True
      Size = 36
    end
    object SQLDataSet1CHNGEDCODE: TWideStringField
      FieldName = 'CHNGEDCODE'
      ProviderFlags = [pfInUpdate]
      FixedChar = True
      Size = 36
    end
    object SQLDataSet1DESCRIPTIN: TWideStringField
      FieldName = 'DESCRIPTIN'
      ProviderFlags = [pfInUpdate]
      Size = 256
    end
  end
  object dspEmloyees: TDataSetProvider
    DataSet = SQLDataSet1
    Options = [poRetainServerOrder, poUseQuoteChar]
    UpdateMode = upWhereKeyOnly
    Left = 216
    Top = 16
  end


On the client grid shows the "dirttable" and allows to change data. But ApplayUpdates doesn't work. Even if changed only one field "discriptin" which has no any constraints.
With best regards. Alex.
Re: Update failed ORA-01722:invalid number [message #508616 is a reply to message #508174] Mon, 23 May 2011 07:55 Go to previous messageGo to next message
Al_Petro
Messages: 14
Registered: January 2011
Junior Member
Made simple (no constraints, foreign keys or whatever) table with two Varchar2 columns - no error on ApplayUpdates. But presence column of numeric type: Int, Integer, Number, Numeric causes "ORA-01722: invalid number" at ApplayUpdates. Sad Even when numeric data is not changed. I guess it happens in process of comparison of new and old values of numeric columns, which include by unknown to me reason character to number conversion. Confused
Any suggestions are welcomed.
With best regards. Alex.
Re: Update failed ORA-01722:invalid number [message #508640 is a reply to message #508616] Mon, 23 May 2011 09:40 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
the data is not numeric
Re: Update failed ORA-01722:invalid number [message #508656 is a reply to message #508640] Mon, 23 May 2011 11:55 Go to previous messageGo to next message
Al_Petro
Messages: 14
Registered: January 2011
Junior Member
can be
Re: Update failed ORA-01722:invalid number [message #508670 is a reply to message #508656] Mon, 23 May 2011 16:52 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
When data is numeric, no error is thrown.
When data is non-numeric error is thrown.
Re: Update failed ORA-01722:invalid number [message #508858 is a reply to message #508670] Tue, 24 May 2011 11:18 Go to previous messageGo to next message
Al_Petro
Messages: 14
Registered: January 2011
Junior Member
That is true! But question is: How non-numeric data could emerge out of the numeric column?
Re: Update failed ORA-01722:invalid number [message #508886 is a reply to message #508858] Tue, 24 May 2011 17:35 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Oracle is too dumb to mis-report reality.
I believe Oracle better reports reality than you.
If you INSIST you are correct, then your only recourse is to file a Bug Report with MOS.
Re: Update failed ORA-01722:invalid number [message #509040 is a reply to message #508886] Wed, 25 May 2011 13:10 Go to previous messageGo to next message
Al_Petro
Messages: 14
Registered: January 2011
Junior Member
Nevertheless I continue investigation.
Added Int Field "DATA2" to table, fill it with data and on the client side do next:
ClientDataSet1->Edit();
ClientDataSet1->FieldByName("DATA2")->AsInteger = 5;
ClientDataSet1->Post();

Then
SQLConnection->ApplayUpdates(-1);
Got error "ORA-01722: invalid number"
As I said in my first post situation is complicated byQuote:
SQLConnection on server side uses Oracle driver - dbxora.dll.
SQLConnection on client side uses Datasnap driver.]

How can I see what comes to server?
Any suggestions are welcomed.
With best regards. Alex.
Re: Update failed ORA-01722:invalid number [message #509046 is a reply to message #509040] Wed, 25 May 2011 13:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Activate Net trace.
http://download.oracle.com/docs/cd/B19306_01/network.102/b14213/sqlnet.htm#sthref589

Regards
Michel
Re: Update failed ORA-01722:invalid number [message #512332 is a reply to message #509046] Sun, 19 June 2011 05:39 Go to previous message
Al_Petro
Messages: 14
Registered: January 2011
Junior Member
Hi All!
Problem is resolved! After connection for session set NLS_NUMERIC_CHARACTERS to (.,).
Thanks All for participation!
Best regards. Alex.
Previous Topic: Column Header
Next Topic: can't understand warnings in simple Pl/SQL function
Goto Forum:
  


Current Time: Thu Mar 28 10:16:29 CDT 2024