Error Oracle -1722 Invalid number after migrating DataServer to OE 11.7

In OpenEdge 11.7, the ABL queries are compiled with a strict dependence on the table field's position (FOREIGN-POS) in the foreign database. If an ABL query runs against an Oracle table whose fields differ in position from the Oracle table the ABL query was compiled against, unexpected errors such as "Error Oracle -1722 Invalid number" can occur. This error triggers because the numeric field is wrongly assigned the value of another character field. Both fields share the same position (FOREIGN-POS) in the compile and runtime Oracle schema. The problem may go unnoticed and cause data corruption if values from fields of compatible datatypes are permuted.

Nov 16, 2021
Knowledge

Title

URL Name

error-oracle-1722-invalid-number-after-migrating-dataserver-to-oe-11-7

Article Number

000119524

Environment

Product: OpenEdge DataServer for Oracle Version: 11.7 to 11.7.5, 12.1 OS: All supported platforms Other: Oracle 11g

Question/Problem Description

In OpenEdge 11.7, the ABL queries are compiled with a strict dependence on the table field's position (FOREIGN-POS) in the foreign database.

If an ABL query runs against an Oracle table whose fields differ in position from the Oracle table the ABL query was compiled against, unexpected errors such as "Error Oracle -1722 Invalid number" can occur.

Oracle -1722 invalid number error triggers because the numeric field is incorrectly assigned the value of another character field.

Both fields share the same position (FOREIGN-POS) in the compile and runtime Oracle schema.

This problem may go unnoticed and cause data corruption if values from fields of compatible datatypes are permuted.

Steps to Reproduce

/* Simulate the Development environment */ 1. Load FOO_Compile.sql into Oracle database. 2. Create an empty db "sh_compile.db" (logical name: sh) 3. Create Oracle DataServer schema and pull "foo" table 4. Run/Compile ABL statement (query.p) CREATE FOO. ASSIGN FOO.MYCHARFIELD = "ABCDE" FOO.MYNUMFIELD = 12345. RELEASE FOO. DataServer log with -Dsrv logentrytypes,SQL:4 reads: ORA SQL the SQL statement SELECT /*+ ROWID(T0) */ * FROM OE11.FOO T0 ORA SQL the SQL statement INSERT INTO OE11.FOO (MYCHARFIELD,MYNUMFIELD) VALUES (:X1, :X2) RETURNING ROWID INTO :rid ORA SQL Bind Mode call OCIBindByName: Column name=X1, value=ABCDE ORA SQL Bind Mode call OCIBindByName: Column name=X2, value=12345 ORA SQL Bind Mode call OCIBindByName: Column name=:rid, value= /* Simulate runtime environment */ 5. Load FOO_Runtime.sql into Oracle database 6. Create an empty db "sh_runtime.db" (logical name: sh) 7. Create Oracle DataServer schema and pull "foo" table 8. Run the COMPILED version of the procedure generated in step 4 () DataServer log with -Dsrv logentrytypes,SQL:4 will now show the MYNUMFIELD is assigned a *character* value.

Clarifying Information

Migrated from OpenEdge 10.2x to OpenEdge 11.7.x or 12.1

Error Message

Error Oracle -1722 Invalid number

Defect Number

OCTA-16996

Enhancement Number

Cause

Executing compiled code (.r) after altering the schema, the field number returned is wrong. As a result, the data inserts in the wrong columns.

Resolution

Fixed version(s): 11.7.6.0, 11.7.5.016, OpenEdge 12.2

Workaround

Notes

Progress Article(s):

ABL query via DataServer for Oracle fails with error ORA-01722: invalid number

Keyword Phrase

Last Modified Date

10/23/2023 12:08 PM

Error Oracle -1722 Invalid number after migrating DataServer to OE 11.7

Files(0)

Was this article helpful?

Was this article helpful?

Error Oracle -1722 Invalid number after migrating DataServer to OE 11.7

Information

Related Articles