Identify which line triggers the ORA-01722 error

[pyro]

Personal, I have a very large +/- 300 lines sql and in some line triggers the error (running from within the PL / SQL Developer):

Select all

 
ORA-01722: número inválido 

only that does not specify in which line is the error. How can we know that? It's complicated to find the error without the line.

[edson.amorim]

Dear colleague,

Go back to the code and see where the cursor is !!

Alé is the error,

Sincerely,

[pyro]

I already did this, in other errors the course points to the error line, but in this specific error does not.

In fact I found it very strange.

[Trevisolli]

Brother,

a tip (I think the solution has to be found "in the hand" even) ...
Remove all the lines of your "SELECT" and go back one to a ...

Whatever, we are there.

[pyro]

I have a strange problem in a Select with Union All. Select is like this below:

Select all

 
select campo1 
         campo2 
         campo3 
from( 
 select 0 campo1 
          campo2 
          0 campo3 
 from tabela2 
union all 
 select campo1 
          0  campo2 
          0 campo3 
 from tabela1 
union all 
 elect 0  campo1 
         0  campo2 
         campo3 
 from tabela3 
) 

The fact is that the field1 and field3 fields return the data normally and field2 always returns zero, and if I run SELECT separately It returns value. Am I doing something wrong at Union All?

[pyro]

I posted the previous answer in the wrong way!
Sorry.

[tiagovon]

It's the following brother, you're simply trying to turn a string into number ...

Enter this link, it's a site that contains all Oracle's mistakes ...
[0]]
is very interesting even ...

[margaridi]

Create a debug variable and update it at various points of the program (for example, in lines 50, 100, 150, 200, 250 and 300).
Print your result in the treatment of exception.
If the result indicates the number 150 is because the error is happening between line 150 and 200.
You can go upgrading the variable in more points within this range until you find the specific line.

Select all

 
DECLARE 
 
vDebug     NUMBER(03); 
 
BEGIN 
 
   COMANDO; 
 
vDebug:=1; 
 
   COMANDO; 
   COMANDO; 
   COMANDO; 
   COMANDO; 
 
vDebug:=2; 
 
   COMANDO; 
   COMANDO; 
   COMANDO; 
   COMANDO; 
   COMANDO; 
   COMANDO; 
   COMANDO; 
   COMANDO; 
 
vDebug:=3; 
 
   COMANDO; 
   COMANDO; 
   COMANDO; 
   COMANDO; 
 
EXCEPTION 
 
   When others then 
 
       dbms_output.put_line('Putz ... deu pau ! ... ' || '[' || to_char(vDebug) || ']', substr(sqlerrm,1,150)); 
      raise_application_error(-20999,'Putz ... deu pau ! ... ' || '[' || to_char(vDebug) || ']'||substr(sqlerrm,1,150)); 
 
 
END; 

[NightSpy]

Have you tried to see the error running query in SQL Plus?