ORA-01722 after upgrade to 8.0.0
Posted: 01.07.2024, 16:22
Hi,
I am using ZeosLib in Delphi 10.3 to work with an Oracle database. Thank you for your great work!
For various reasons I have upgraded from ZeosLib 7.2.14 to 8.0.0 and now my project triggers ORA-01722 Invalid Number on a seemingly innocent query:
I have attached a simple testprogram, but unfortunately I cannot recreate the error entirely from scratch. There seems to be something
in my data triggering it.
The program simply creates and sets up the access and calls Open() on the query:
With Zeos 7.2.14 in the searchpath this works fine. When I compile with 8.0.0 it fails with ORA-01722.
In my actual application the query is run against a table with 1.3 Million records. I have tried to simplify it like this:
This also works in 7.1.14 and fails in the same way with 8.0.0.
Next I tried recreating the problem from scratch. I had SQLDeveloper create DDL for the table, removed all the unneeded fields from
the statement and issued that, followed by a INSERT.
Cloudflare will not let me include the statement here. It is just a simple CREATE TABLE with two plain VARCHAR fields. Followed by an INSERT with two literal strings. The GUID used throughout for apiID and 'N' for REQUEST_UPDATE.
Now the error is not triggered any more. I have also tried copying the record from the old table to the new instead of
typing it in.
Again Cloudflare filters my SQL. In this case I did an INSERT (..) SELECT .. FROM TT_ZEOS WHERE ..
I do not quite understand what properties of the table are retained when creating it from a SELECT and that go away when creating separately.
If you read this, thank you for your time.
Regards, Marian
I am using ZeosLib in Delphi 10.3 to work with an Oracle database. Thank you for your great work!
For various reasons I have upgraded from ZeosLib 7.2.14 to 8.0.0 and now my project triggers ORA-01722 Invalid Number on a seemingly innocent query:
Code: Select all
SELECT
*
FROM TT_ZEOS
WHERE
(
(apiID=:apiID)
OR (:apiID IS NULL)
)
AND (
(REQUEST_RERUN=:REQUEST_RERUN)
OR (:REQUEST_RERUN IS NULL)
)
in my data triggering it.
The program simply creates and sets up the access and calls Open() on the query:
Code: Select all
// Set up DB connection:
Conn := TZConnection.Create(Self);
Conn.Protocol := 'oracle';
Conn.Database := '*******';
Conn.User := '*******';
Conn.Password := '*******';
Conn.Connect;
// Open Query.
Qry := TZQuery.Create(Self);
Qry.Connection := Conn;
Qry.SQL.Text :=
'SELECT '+
' * '+
'FROM TT_ZEOS '+
'WHERE '+
' ( '+
' (apiID=:apiID) '+
' OR (:apiID IS NULL) '+
' ) '+
' AND ( '+
' (REQUEST_RERUN=:REQUEST_RERUN) '+
' OR (:REQUEST_RERUN IS NULL) '+
' ) '+
Qry.ParamByName('apiID').AsString := 'd565e7c9-3877-4fd0-8e7a-caa0f7edc53b';
Qry.Open;
In my actual application the query is run against a table with 1.3 Million records. I have tried to simplify it like this:
Code: Select all
CREATE TABLE TT_ZEOS AS SELECT APIID, REQUEST_RERUN FROM TT_E2E_MM;
DELETE FROM TT_ZEOS WHERE APIID <> 'd565e7c9-3877-4fd0-8e7a-caa0f7edc53b';
Next I tried recreating the problem from scratch. I had SQLDeveloper create DDL for the table, removed all the unneeded fields from
the statement and issued that, followed by a INSERT.
Cloudflare will not let me include the statement here. It is just a simple CREATE TABLE with two plain VARCHAR fields. Followed by an INSERT with two literal strings. The GUID used throughout for apiID and 'N' for REQUEST_UPDATE.
Now the error is not triggered any more. I have also tried copying the record from the old table to the new instead of
typing it in.
Again Cloudflare filters my SQL. In this case I did an INSERT (..) SELECT .. FROM TT_ZEOS WHERE ..
I do not quite understand what properties of the table are retained when creating it from a SELECT and that go away when creating separately.
If you read this, thank you for your time.
Regards, Marian