Author Topic: SQL Script errors with ORA-01722: invalid number in EA v 13.5  (Read 1915 times)

ArchBeast

  • EA Novice
  • *
  • Posts: 8
  • Karma: +0/-0
    • View Profile
SQL Script errors with ORA-01722: invalid number in EA v 13.5
« on: December 13, 2017, 12:09:31 am »
This SQL query ran ok in EA v13 but errors with 'OraOLEDB [-2147467259] ORA-01722: invalid number'
in EA v 13.5:

Select

--Look for an association class and list the source and target class of the association

-- The 'AS' clause needs to be followed by a simple string or eaDocX searches don't work

ifO.ea_guid AS CLASSGUID,
ifO.Object_Type As CLASSTYPE,


ifO.Alias As IF_ID,

-- Replace any commas in the interface name result so we don't confuse CSV!

Replace(ifO.Name, ',',';') AS IF_NAME,
ifO.Status As IF_STATUS,
ifO.PDATA5 As CAPABILITY,
srcO.Name AS SRC_SYS_NAME,
tgtO.Name AS TGT_SYS_NAME

FROM t_object tgtO , t_object srcO, t_connector, t_object ifO, t_package pkg

WHERE
-- These macros dont work in eaDocX searches - no results will be output
pkg.Package_ID in (#Branch#) AND ifO.Package_ID = pkg.Package_ID

AND tgtO.Object_ID = t_connector.End_Object_ID
AND srcO.Object_ID = t_connector.Start_Object_ID
AND t_connector.PDATA1 IS NOT NULL
AND ifO.Object_ID = To_Number(t_connector.PDATA1)
AND ifO.Stereotype like '%interface%'

ORDER by 1

Whoops it doesn't work in EA v 13 either, but it used too! Other scripts also work so not an OLDEB problem.
« Last Edit: December 13, 2017, 12:24:47 am by ArchBeast »

Geert Bellekens

  • EA Guru
  • *****
  • Posts: 8480
  • Karma: +207/-26
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: SQL Script errors with ORA-01722: invalid number in EA v 13.5
« Reply #1 on: December 13, 2017, 12:34:27 am »
I guess this is an SQL search and not a script.
First thing to do is check the dbError.txt in the %appdata% folder for EA.

Then remove the comments. IIRC EA will strip (or it used to) everything after the first comments, so your search will be nothing more then "SELECT"

Next thing is the To_Number(t_connector.PDATA1) there's a good chance you have somewhere a text in a PDATA1
I guess you better cast both fields to string to compare them instead of trying to cast it to a number.

Geert

Simon M

  • EA Administrator
  • EA Guru
  • *****
  • Posts: 6453
  • Karma: +55/-6
    • View Profile
Re: SQL Script errors with ORA-01722: invalid number in EA v 13.5
« Reply #2 on: December 13, 2017, 08:43:20 am »
Next thing is the To_Number(t_connector.PDATA1) there's a good chance you have somewhere a text in a PDATA1
I guess you better cast both fields to string to compare them instead of trying to cast it to a number.
I'll agree that this is very likely the problem, it would also explain why it used to work but now doesn't.

The other option is to filter the t_connector table to the Connector_Type you're interested in. I'm guessing 'Association'.
Simon

support@sparxsystems.com

Paolo F Cantoni

  • EA Guru
  • *****
  • Posts: 6264
  • Karma: +104/-89
  • Inconsistently correct systems DON'T EXIST!
    • View Profile
Re: SQL Script errors with ORA-01722: invalid number in EA v 13.5
« Reply #3 on: December 13, 2017, 10:52:45 am »
I guess this is an SQL search and not a script.
First thing to do is check the dbError.txt in the %appdata% folder for EA.

Then remove the comments. IIRC EA will strip (or it used to) everything after the first comments, so your search will be nothing more then "SELECT"

Next thing is the To_Number(t_connector.PDATA1) there's a good chance you have somewhere a text in a PDATA1
I guess you better cast both fields to string to compare them instead of trying to cast it to a number.

Geert
To reinforce what Geert has said, don't forget that a NULL value will also trigger type issues.  We find that a lot with function calls in queries.

Again, for the record, I HATE NULLS!

Paolo
Inconsistently correct systems DON'T EXIST!
... Therefore, aim for consistency; in the expectation of achieving correctness....
-Semantica-
Helsinki Principle Rules!

Simon M

  • EA Administrator
  • EA Guru
  • *****
  • Posts: 6453
  • Karma: +55/-6
    • View Profile
Re: SQL Script errors with ORA-01722: invalid number in EA v 13.5
« Reply #4 on: December 14, 2017, 08:06:50 am »
don't forget that a NULL value will also trigger type issues.
The query already has that check.
Simon

support@sparxsystems.com

Paolo F Cantoni

  • EA Guru
  • *****
  • Posts: 6264
  • Karma: +104/-89
  • Inconsistently correct systems DON'T EXIST!
    • View Profile
Re: SQL Script errors with ORA-01722: invalid number in EA v 13.5
« Reply #5 on: December 14, 2017, 10:46:10 am »
don't forget that a NULL value will also trigger type issues.
The query already has that check.
But only on a specific column.  We get caught bu NULL values in the other columns!  We then have to exclude (or at least handle them).

Paolo
Inconsistently correct systems DON'T EXIST!
... Therefore, aim for consistency; in the expectation of achieving correctness....
-Semantica-
Helsinki Principle Rules!

MaXyM

  • EA User
  • **
  • Posts: 64
  • Karma: +5/-0
    • View Profile
Re: SQL Script errors with ORA-01722: invalid number in EA v 13.5
« Reply #6 on: December 24, 2017, 09:19:39 am »
I guess you better cast both fields to string to compare them instead of trying to cast it to a number.

Casting to empty string is in general wrong idea (even if this time it might work)
It's better to use proper syntax for comparing values incl. NULLs. For example
Code: [Select]
val1 IS DISTINCT FROM val2
or
val1 IS NOT DISTINCT FROM val2
It's worth to remember that in most cases/SQL dialects any operation with NULL (including boolean evaluation) gives NULL. And then NULL is never casted to boolean. So even NULL = NULL condition is not TRUE (but it's not FALSE too)

In fact this is great subject to rise during recruitment
I love that ;)

« Last Edit: December 24, 2017, 09:22:09 am by MaXyM »

Geert Bellekens

  • EA Guru
  • *****
  • Posts: 8480
  • Karma: +207/-26
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: SQL Script errors with ORA-01722: invalid number in EA v 13.5
« Reply #7 on: December 24, 2017, 06:12:25 pm »
I guess you better cast both fields to string to compare them instead of trying to cast it to a number.
Casting to empty string is in general wrong idea (even if this time it might work)

I did not say cast to empty string. The null values where already checked, so this was a case of the field having a non numerical value.
When comparing a numerical field with a string field it is a better idea to cast the numerical field to a string, then to cast the string field to a numerical type.

Geert

Geert Bellekens

  • EA Guru
  • *****
  • Posts: 8480
  • Karma: +207/-26
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: SQL Script errors with ORA-01722: invalid number in EA v 13.5
« Reply #8 on: December 24, 2017, 06:26:56 pm »
It's better to use proper syntax for comparing values incl. NULLs. For example
Code: [Select]
val1 IS DISTINCT FROM val2
or
val1 IS NOT DISTINCT FROM val2

Since I've never used IS DISTINCT FROM  I did a little research.
It is a standard SQL feature, but it it does not seem to be supported by Oracle, SQL server. see https://www.sql-workbench.net/dbms_comparison.html
I haven't tried ms-access, (.eap files) and the website doesn't mention access sql syntax.

Geert