Greetings,

I have a table that stores multiple locations (lat and lon) for a project. I want to convert this list of lat and lons into a sdo_geometry so that I can plot a polygon on the map. I created this procedure to insert the sdo_geomety column in a temporary table. But I am getting PL/SQL: numeric or value error: character to number conversion error. Not sure what I am doing wrong here. Any help will be greatly appreciated.
Thanks
Vatsa.
Here is the procedure:
create or replace procedure "GEN_POLYGON_FOR_PROJ" (
p_project_number in number )
as
l_poly sdo_geometry;
l_loc_list varchar2(2000) := NULL;

Cursor L (p_proj number) is
Select to_char(lon)||','||to_char(lat) as loc
FROM PM_PROJECT_LOCATION
where proj_id = p_proj ;
begin
FOR I in L(p_project_number) Loop

l_loc_list := l_loc_list ||','|| I.loc ;
dbms_output.put_line(l_loc_list) ;

END LOOP;
dbms_output.put_line(substr(l_loc_list,2)) ;

l_poly := mdsys.sdo_geometry(2003, 1041002, null, sdo_elem_info_array(1, 1003, 1), MDSYS.sdo_ordinate_array(substr(l_loc_list,2))) ;

delete from VR_PROJECT_POLYGONS where project_number = p_project_number;
insert into VR_PROJECT_POLYGONS(PROJECT_NUMBER,PROJ_POLYGON) values (p_project_number,l_poly);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM) ;
end "GEN_POLYGON_FOR_PROJ";

select
sdo_geometry(
  2001,
  4326,
  sdo_point_type(p.lon, p.lat, null),
  null,
  null))
from PM_PROJECT_LOCATION p;