I am trying to insert values into a GEOM column in a table which already has X and Y co-ordinate columns. The eastings/northings values are in OSGB36 (British National Grid) and I want the dataset to be a point dataset. I am trying to update the GEOM column with the following code, which I found in the discussion thread
1090918
SQL> declare
2 stmt varchar2(1000) :=
3 'insert into address_sample values ('||
4 'mdsys.sdo_geometry(2002,27700,null,'||
5 'mdsys.sdo_elem_info_array(1,1,1),'||
6 'mdsys.sdo_ordinate_array(';
7 begin
8 for rec in (select xcoord, ycoord from address_sample) loop
9 stmt := stmt||rec.xcoord||','||rec.ycoord||',';
10 end loop;
11 stmt := rtrim(stmt,',')||')))';
12 execute immediate stmt;
13* end;
However, I keep getting the error message
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 9
Why is this? Both XCOORD and YCOORD are of datatype number(8). I need to geocode this table fairly urgently (in the next two days) so any help would be greatly appreciated.
Thanks
Sharon