CODEWITHSUNDEEP
sqloracle-databasedebuggingoracle-sqldeveloper
RonnyKnoxville
RonnyKnoxville

Reputation: 6394

Why do SQL errors not show you the error source?

Is it possible to find the line or column where an error is occurring when executing SQL code in Oracle SQL developer?

For example, imagine you are running a very simple line of code

SELECT * FROM employeesTbl WHERE active = 1

But for some reason, active is VARCHAR and someone has entered the ";!/asd02" into this field.

You will only get an ORA- error, but it does not tell you which row caused it.

Does anyone know why this is?

Upvotes: 4

Views: 2693

Answers (3)

Ben
Ben

Reputation: 52893

You get an error because the field is a character and you're assuming it's a number. Which, you shouldn't be doing. If you want the field to be numeric then you have to have a numeric field! This is a general rule, all non-character columns should be the correct data-type to avoid this type of problem.

I'm not certain why Oracle doesn't tell you what row caused the error, it may be physically possible using the rowid in a simple select as you have here. If you're joining tables or using conversion functions such as to_number it would become a lot more difficult, if possible at all.

I would imagine that Oracle did not want to implement something only partially, especially when this is not an Oracle error but a coding error.

To sort out the problem create the following function:

create or replace function is_number( Pvalue varchar2 
      ) return number is
    /* Test whether a value is a number. Return a number
       rather than a plain boolean so it can be used in
       SQL statements as well as PL/SQL.
       */

   l_number number;

begin

   -- Explicitly convert.
   l_number := to_number(Pvalue);

   return 1;

exception when others then
   return 0;

end;
/

Run the following to find your problem rows:

SELECT * FROM employeesTbl WHERE is_number(active) = 0

Or this to ignore them:

SELECT *
  FROM ( SELECT *
           FROM employeesTbl
          WHERE is_number(active) = 1 )
WHERE active = 1

Upvotes: 1

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239764

Multiple rows may contain errors. For the system to be consistent (as a "set-based" language), it ought to return you all rows which contain errors - and not all row errors may be caused by the same error.

However, it could be computationally expensive to compute this entire error set - and the system "knows" that any further computation on this query is going to result in failure anyway - so it represents wasted resources when other queries could be running successfully.

I agree that it would be nice to turn on this type of reporting as an option (especially in non-production environments), but no database vendor seems to have done so.

Upvotes: 2

Jens Schauder
Jens Schauder

Reputation: 81970

The reason behind this is that in general developer support in sql, pl/sql and the like is really abysmal. One result is a really broken exception concept in pl/sql, almost useless exceptions in (oracle) sql and little hope that it is better in any rdbms.

I think the reason behind all that is that databases are persistent beasts (pun intended). Many companies and developers change from time to time there preferred main development language (C, C++, VB, Java, C#, Groovy, Scala ..). But they rarely change the database, possibly because you will still have the old databases around with no chance to migrate them.

This in turn means most DB-devs know only a single database system reasonable well, so they don't see what is possible in other systems. Therefore there is little to no pressure to make database systems any more usable for developers.

Upvotes: 4

Related Questions