| Update failed ORA-01722:invalid number [message #507794] |
Wed, 18 May 2011 00:46  |
 |
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  |
 |
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 #508094 is a reply to message #507972] |
Thu, 19 May 2011 07:23 |
|
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 #508174 is a reply to message #508094] |
Thu, 19 May 2011 13:10 |
|
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.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
