ORA-01722: invalid numberORA-02063: preceding line from DOTMATICS

Toad for Oracle
1

Dear Support, I get this error message (ORA-01722: invalid numberORA-02063: preceding line from DOTMATICS) when trying to use the “Data” in TOAD 12.0.6.53 Prof Edition. This view is a dblink from another application. Please note that I can see the data and tables via SQLPLUS. Please advise. Thanks, Mel

2

Just so you know, this is not an official support channel. It is just a forum, indented for users to be able to help each other. As it happens, the members of the Toad team are on this forum too.

Anyway, about the problem…I would suggest trying to identify which row/column is producing the error, and see if there is something about the definition of your view which does not match up with the data. I am not 100% certain, but I believe that SQL*Plus retrieves all data as strings, which may explain why you are not getting the error there.

3

On 30/04/15 18:08, hmanalo wrote:

*ORA-01722: invalid numberORA-02063: preceding line from DOTMATICS
*
Thread created by hmanalo
Dear Support, I get this error message (ORA-01722: invalid
numberORA-02063: preceding line from DOTMATICS) when trying to use the
"Data" in TOAD 12.0.6.53 Prof Edition. This view is a dblink from
another application. Please note that I can see the data and tables via
SQLPLUS. Please advise. Thanks, Mel
To reply, please reply-all to this email.

Following up on what John said, SQL*Plus does get data as strings because that's all it knows about how to display. ** As far as I am aware **
Anyway, your problem is being caused by something in your query that is fetching back what is expected to be a number, but the data are not in a format that is recognisable as a number.
So, DOTMATICS will be the source of the trouble - probably the database link name if I remember correctly. And in order for us to help you resolve your difficulty, we would need the following information please:

  • What columns, defined as NUMBER are you attempting to SELECT from the database link?
  • What are the definitions of the table(s) at the remote end, for those columns?
    You could, select each number column in turn until you get an error message to identify the problem column(s). Then, armed with the names of the column(s) that error, you can then do what SQL*Plus effectively does and:
    SELECT TO_CHAR(number_column) from table@DOTMATICS WHERE ...
    That way, you will not get, hopefully, any errors and you might be able to better identify the problem data.
    HTH
    Cheers,
    Norm. [TeamT]