ORA-01722 after upgrade to 8.0.0

The offical for ZeosLib 7.3 Report problems, ask for help, post proposals for the new version of Zeoslib 7.3/v8
Quick Info:
-We made two new drivers: odbc(raw and unicode version) and oledb
-GUID domain/field-defined support for FB
-extended error infos of Firebird
-performance ups are still in queue
In future some more feature will arrive, so stay tuned and don't hassitate to help
Post Reply
Marian Aldenhövel
Fresh Boarder
Fresh Boarder
Posts: 5
Joined: 29.06.2024, 13:50

ORA-01722 after upgrade to 8.0.0

Post by Marian Aldenhövel »

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:

Code: Select all

SELECT
  *
FROM TT_ZEOS
WHERE
  (
    (apiID=:apiID)
    OR (:apiID IS NULL)
  )
  AND (
    (REQUEST_RERUN=:REQUEST_RERUN)
    OR (:REQUEST_RERUN IS NULL)
  )
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:

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;
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:

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';
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
You do not have the required permissions to view the files attached to this post.
User avatar
aehimself
Zeos Dev Team
Zeos Dev Team
Posts: 777
Joined: 18.11.2018, 17:37
Location: Hungary

Re: ORA-01722 after upgrade to 8.0.0

Post by aehimself »

From Oracle:
The attempted conversion of a character string for column or expression to a number failed because the character string is not a valid numeric literal
In other words, you are attempting to convert a non-number to a number somewhere.

Did you check when the error is being raised, is the data being downloaded already? I'd be surprised if that was the case. We use mainly Oracle databases at work with several hundred tables, tens-hundreds of fields in each and a couple of million records and discovered a handful of Oracle errors - 1722 being none of them.

That leaves us with the preparation stage and what strikes me first is that you only give value to the apiID parameter of the query leaving REQUEST_RERUN undefined, including it's type. Now if REQUEST_RERUN is a number field your issue might simply be solved by either
- Giving the unused parameter a type by Qry.ParamByName('REQUEST_RERUN').SQLType := stInteger
- Disabling the use of ZParameters with the DISABLE_ZPARAM conditional directive
Delphi 12.1, Zeos 8 from latest GIT snapshot
Using:
- MySQL server 8.0.18; libmariadb.dll 3.3.8
- Oracle server 11.2.0, 12.1.0, 19.0.0; oci.dll 21.13
- MSSQL 2012, 2019; sybdb.dll FreeTDS_2435
- SQLite 3.45.2
Marian Aldenhövel
Fresh Boarder
Fresh Boarder
Posts: 5
Joined: 29.06.2024, 13:50

Re: ORA-01722 after upgrade to 8.0.0

Post by Marian Aldenhövel »

Hi,

Thank you for your analysis.

Indeed, by specifying REQUEST_RERUN to be of string-type the error goes away. I simply assigned a default value using AsString to achieve that.

REQUEST_RERUN is declared as VARCHAR with a check-constraint limiting it to 'Y' and 'N' to emulate a boolean value.

I am happy with the solution, but I do not understand how a NUMBER comes into play here :-). Where exactly is the conversion to a number attempted?

Regards, Marian
User avatar
aehimself
Zeos Dev Team
Zeos Dev Team
Posts: 777
Joined: 18.11.2018, 17:37
Location: Hungary

Re: ORA-01722 after upgrade to 8.0.0

Post by aehimself »

Marian Aldenhövel wrote: 10.07.2024, 10:31Indeed, by specifying REQUEST_RERUN to be of string-type the error goes away.
Glad to hear!
Marian Aldenhövel wrote: 10.07.2024, 10:31I simply assigned a default value using AsString to achieve that.
Yes, if you assign a value with the pre-defined methods the internal setters are adjusting the parameter type internally.
Marian Aldenhövel wrote: 10.07.2024, 10:31I am happy with the solution, but I do not understand how a NUMBER comes into play here :-). Where exactly is the conversion to a number attempted?
I suppose this will have something to do with the RDBMS system itself. My guess is some kind of default value for unset types but take this with a grain of salt as I can be completely off.
Delphi 12.1, Zeos 8 from latest GIT snapshot
Using:
- MySQL server 8.0.18; libmariadb.dll 3.3.8
- Oracle server 11.2.0, 12.1.0, 19.0.0; oci.dll 21.13
- MSSQL 2012, 2019; sybdb.dll FreeTDS_2435
- SQLite 3.45.2
Post Reply