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; /

In general the problem is caused by the data in the dataset processed by your code does not fit into the datatypes used. Either strings could be too long or you treat non-numeric data as numeric, i.e. oracle is unable to convert the data to a number. — user2672165
– user2672165, Commented Jun 12, 2016 at 8:49

Alex Poole · Accepted Answer · 2017-07-04 16:01:56Z

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.

Why not use LEAST to restrict ACOS to a maximum of 1 rather than rounding and potentially losing accuracy?
@MTO - you're losing some accuracy anyway from the existing rounding errors, though not as much. No idea how much difference either makes to the overall distance calculation. But yes, anything to keep it valid. Or the function could check if they're the same and just return zero without calculating anything,
@Mr_and_Mrs_D - they go in the function, between BEGIN and RETURN. (That's the only place they can go, but added that to the answer). Your client needs to show the output too of course, e.g. set serveroutput on in SQL*Plus or SQL Developer before calling the function.

APC · Accepted Answer · 2016-06-12 11:35:36Z

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).

MT0 · Accepted Answer · 2016-06-12 22:59:54Z

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; /

Spatial is an extra-cost option, isn't it? Makes sense to use it if it's available, of course...
Yes, according to this licensing information Spatial and Graph is an enterprise edition cost option.

Collectives™ on Stack Overflow

PL/SQL: numeric or value errors

3 Answers 3

4 Comments

Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

2 Comments

Related