PL/SQL: numeric or value errors

Question

I'm getting an error and I don't know why:

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.STANDARD", line 394
ORA-06512: at "DOMINOS.DISTANCE", line 10
ORA-06512: at "DOMINOS.ZOEKWINKELVOORADRES", line 19
ORA-06512: at line 5

The cursor should contain 145 rows. When I execute the procedure I get the error message above after 54 rows.

create or replace procedure zoekWinkelVoorAdres
    (v_postcode in postcode.postcode%type,
      v_huisnr in WINKEL.HUISNR%type,
      v_id out WINKEL.ID%type,
      v_afstand out number)
is
    type lat_array is varray(100000) of POSTCODE.LAT%type;
    type lon_array is varray(100000) of POSTCODE.LON%type;
    type id_array is varray(100000) of winkel.id%type;
    a_lat lat_array;
    a_lon lon_array;
    a_id id_array;
    latwin postcode.lat%type;
    lonwin postcode.lon%type;
    latklant postcode.lat%type;
    lonklant postcode.lon%type;
    vafstand number(38);
    cursor winkelafstand is
        select w.ID, p.lat, p.lon 
        from winkel w 
        join postcode p 
        on w.POSTCODE_ID_FK = p.POSTCODE_ID;
begin
    select lat, lon into latklant,lonklant 
    from postcode 
    where postcode = v_postcode;
    open winkelafstand;
    fetch winkelafstand bulk collect into a_id, a_lat, a_lon;
    close winkelafstand;
    for i in a_lat.first..a_lat.last loop
        vafstand := distance(a_lat(i),a_lon(i),latklant,lonklant);
        dbms_output.put_line(vafstand || ' ' || a_id(i));
        insert into winkel_afstand
             (Winkel_ID, afstand) values(a_id(i),vafstand);
    end loop;
end;
/

Answer 1

From a bit of searching it looks like you get this error if you give the same same position for both sets of coordinates.

Assuming your distance function is defined similarly to that linked example:

CREATE OR REPLACE FUNCTION DISTANCE 
( 
Lat1 IN NUMBER, 
Lon1 IN NUMBER, 
Lat2 IN NUMBER, 
Lon2 IN NUMBER 
) RETURN NUMBER IS 
DegToRad NUMBER := 57.29577951; 
BEGIN
RETURN(6387.7 * ACOS((sin(NVL(Lat1,0) / DegToRad) * SIN(NVL(Lat2,0) / DegToRad)) + 
(COS(NVL(Lat1,0) / DegToRad) * COS(NVL(Lat2,0) / DegToRad) * 
COS(NVL(Lon2,0) / DegToRad - NVL(Lon1,0)/ DegToRad)))); 
END; 
/

... then if you pass the same pair of values twice the calculation evaluates to something invalid due to rounding errors, e.g. with

select distance(53.8662, 10.68117, 53.8662, 10.68117) from dual

Adding debugs for the components (in the function, between BEGIN and RETURN) shows:

dbms_output.put_line(lat1 ||','|| lon1);
dbms_output.put_line(sin(NVL(Lat1,0) / DegToRad));
dbms_output.put_line(SIN(NVL(Lat2,0) / DegToRad));
dbms_output.put_line(COS(NVL(Lat1,0) / DegToRad));
dbms_output.put_line(COS(NVL(Lat2,0) / DegToRad)); 
dbms_output.put_line(COS(NVL(Lon2,0) / DegToRad));
dbms_output.put_line(NVL(Lon1,0)/ DegToRad);

.8076421638813717679360124563997362950201
.8076421638813717679360124563997362950201
.5896729051949185735939828069514084977347
.5896729051949185735939828069514084977347
.9826737619730074300608748352929523713616
.1864215844752715888130518254292967904505

and when those are multiplied and added together the result is:

1.00000000000000000000000000000000000001

So the whole thing evaluates to RETURN(6387.7 * ACOS(1.00000000000000000000000000000000000001)), and ACOS(1.00000000000000000000000000000000000001) throws the same error, at least in PL/SQL:

declare
  result number;
begin
  result := acos(1.00000000000000000000000000000000000001);
end;
/

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.STANDARD", line 394
ORA-06512: at line 4

The SQL function gets a different error:

select acos(1.00000000000000000000000000000000000001) from dual;

SQL Error: ORA-01428: argument '1.00000000000000000000000000000000000001' is out of range

... but it's the same problem, it isn't meaningful to pass a value greater than 1 to ACOS.

As a workaround you could change the function to ROUND() the value before calling ACOS(), with a high enough argument to not significantly affect other calculations, though as with any rounding it won't be perfect (but clearly isn't anyway!):

  RETURN(6387.7 * ACOS(ROUND((
    (SIN(NVL(Lat1,0) / DegToRad) * SIN(NVL(Lat2,0) / DegToRad))
      + (COS(NVL(Lat1,0) / DegToRad)
        * COS(NVL(Lat2,0) / DegToRad)
        * COS(NVL(Lon2,0) / DegToRad - NVL(Lon1,0)/ DegToRad)
        )
    ), 9))
  ); 

With that change:

select distance(53.8662, 10.68117, 53.8662, 10.68117) from dual;

DISTANCE(53.8662,10.68117,53.8662,10.68
---------------------------------------
                                      0

If you can't change the function then you will have to compare the values to decide whether it is safe to call it.

Answer 2

Oracle has its own Spatial library that contains functions which handle distance between latitude/longitude points.

Oracle Setup:

CREATE TABLE Postcode (
  postcode_id NUMBER(8,0),
  postcode    VARCHAR2(9),
  location SDO_GEOMETRY
);

INSERT INTO USER_SDO_GEOM_METADATA (
  TABLE_NAME, COLUMN_NAME, DIMINFO, SRID
) VALUES (
  'POSTCODE',
  'LOCATION', 
  SDO_DIM_ARRAY(
    SDO_DIM_ELEMENT('LAT', -90.0, 90.0, 0.5),
    SDO_DIM_ELEMENT('LONG', -180.0, 180.0, 0.5)
  ), 
  8307
);

CREATE INDEX Postcode_SIDX ON Postcode( location )
  INDEXTYPE IS MDSYS.SPATIAL_INDEX;

CREATE TABLE winkel ( id INT, postcode_id INT );

CREATE TABLE winkel_afstand ( id INT, distance NUMBER(10,5) );

Test Data:

INSERT INTO winkel
SELECT 1, 1 FROM DUAL UNION ALL
SELECT 2, 2 FROM DUAL UNION ALL
SELECT 3, 3 FROM DUAL UNION ALL
SELECT 4, 4 FROM DUAL;

INSERT INTO Postcode
-- Buckingham Palace, London, England
SELECT 1, 'SW1A 1AA', SDO_GEOMETRY( 2001, 8307, SDO_POINT_TYPE(51.5014, -0.1419,NULL), NULL, NULL) FROM DUAL UNION ALL
-- Big Ben, London, England
SELECT 2, 'SW1A 0AA', SDO_GEOMETRY( 2001, 8307, SDO_POINT_TYPE(51.5007, -0.1246,NULL), NULL, NULL) FROM DUAL UNION ALL
-- Edinburgh CAstle, Edinburgh, Scotland
SELECT 3, 'EH1 2NG',  SDO_GEOMETRY( 2001, 8307, SDO_POINT_TYPE(55.9486, -3.1999,NULL), NULL, NULL) FROM DUAL UNION ALL
-- Snowdon, Llanberis, Wales
SELECT 4, 'LL55 4TY', SDO_GEOMETRY( 2001, 8307, SDO_POINT_TYPE(53.0685, -4.0763,NULL), NULL, NULL) FROM DUAL;

Query:

You can rewrite your procedure as a single INSERT statement (no cursors, varrays or loops necessary):

INSERT INTO winkel_afstand
SELECT w.id,
       sdo_geom.sdo_distance( p.location, q.loc, 0.005, 'unit=mile' )
FROM   winkel w
       INNER JOIN
       postcode p
       ON w.postcode_id = p.postcode_id
       CROSS JOIN
       ( SELECT location AS loc
         FROM   postcode
         WHERE  postcode = 'SW1A 0AA' ) q;

Output:

SELECT * FROM winkel_afstand;

        ID   DISTANCE
---------- ----------
         1    1.18963 
         2          0 
         3  373.09907 
         4  292.33809

However, even without using Oracle's spatial data you can still simplify your procedure a lot:

CREATE PROCEDURE zoekWinkelVoorAdres (
  v_postcode in  postcode.postcode%type,
  v_huisnr   in  WINKEL.HUISNR%type,
  v_id       out WINKEL.ID%type,
  v_afstand  out number
)
IS
BEGIN
  INSERT INTO winkel_afstand
  SELECT w.id,
         distance(
           lat,
           lon,
           lt,
           ln
         )
  FROM   winkel w
         INNER JOIN
         ( SELECT p.*,
                  FIRST_VALUE( CASE WHEN postcode = v_postcode THEN lat END )
                    IGNORE NULLS OVER () AS lt,
                  FIRST_VALUE( CASE WHEN postcode = v_postcode THEN lon END )
                    IGNORE NULLS OVER () AS ln
           FROM   postcode p ) p
         ON w.postcode_id = p.postcode_id;
END;
/

Answer 3

Parsing the error stack:

ORA-06502: PL/SQL: numeric or value error

Caused by an attempt to cast a non-numeric string to a number datatype, or some other data conversion error.

ORA-06512: at "SYS.STANDARD", line 394

The bottom of the call stack, the program which throws the exception. As it's the Oracle STANDARD package, this means it's one of the built-in functions, such as TO_NUMBER().

ORA-06512: at "DOMINOS.DISTANCE", line 10

The procedure which called the previous function which threw the error.

ORA-06512: at "DOMINOS.ZOEKWINKELVOORADRES", line 19

The procedure which called the previous function.

ORA-06512: at line 5 

The top of the call stack, the code which starts it all. Perhaps an anonymous block?

So, the code you posted, ZOEKWINKELVOORADRES() isn't very helpful, as the error is generated by line 10 of DISTANCE(). What we can tell is that there is value error in row 54 of the cursor. So you must debug your data set.

Basically you need to log the inputs to DISTANCE(). For development you could get away with a dbms_output.put_line() before the call to DISTANCE() which shoes the values of a_lat(i) , a_lon(i) , latklant and lonklant. For robust diagnosis in production you should log errors and contextual info such as input parameters in a persistent store (log table or file).