ORA-01722 after upgrade to 8.0.0

[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

[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

[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

[aehimself]

Indeed, by specifying REQUEST_RERUN to be of string-type the error goes away.

Glad to hear!

I 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.

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?

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.