oracle - SQLDeveloper : Not enough arguments -

i'm writing apex application utilises spatial proximity searching via googlemaps api, i've built function , compiles fine, whenever try run in sqldeveloper error not enough arguments.

the function created set new lat/long point takes 1 input of postcode stores table, uses google map api return long lat co-ords postcode, on return builds sdo_geometry object, returned , set in location column in stores table.

function code:

create or replace function set_spatial_point (      -- accept postcodes store table     p_postcode stores.postcode%type )     return mdsys.sdo_geometry     -- build local variables     l_lng      varchar2(100);     l_lat      varchar2(100);     n_spatial_object mdsys.sdo_geometry; begin     -- populate long , lat parameters     postcode_to_lat_lng_gm_api(p_postcode, l_lat, l_lng);      -- populate new spatial object     n_spatial_object := mdsys.sdo_geometry     (         -- use 01 wish add point map         2001,          -- srid wgs84 longitutde/latitude format         8307,          -- set information of point ( don't need z co-ord )         sdo_point_type         (             l_lng,             l_lat,             null          ),         null,   -- have no sdo_elem_info_array         null    -- have no sdo_ordinate_array     );      -- return new spatial object     dbms_output.put_line('sdo_gtype='||n_spatial_object.sdo_gtype);     dbms_output.put_line('first element sdo_ordinates='||n_spatial_object.sdo_ordinates(1));     return n_spatial_object; end set_spatial_point; 

why getting not enough arguments error, when call valid stores.postcode%type, ( have tried changing varchar2 doesn't make difference.)

edit: after creating function, run call method:

declare    my_object    mdsys.sdo_geometry;   begin   my_object := set_spatial_value('mk80pb'); end; 

i following error:

error starting @ line : 1 in command - declare   my_object    mdsys.sdo_geometry;   begin my_object := set_spatial_value('mk80pb'); end; error report - ora-06550: line 4, column 14: pls-00201: identifier 'set_spatial_value' must declared ora-06550: line 4, column 1: pl/sql: statement ignored 06550. 00000 -  "line %s, column %s:\n%s" *cause:    pl/sql compilation error. *action: 

the error obvious: create function set_spatial_point() call set_spatial_value() , fails since not exist.

now, let's try out. had replace of code since don't have declarations of stores table or google call, hard-coded results call:

create or replace function set_spatial_point (      -- accept postcodes store table     p_postcode varchar2 )     return sdo_geometry     -- build local variables     l_lng      varchar2(100);     l_lat      varchar2(100);     n_spatial_object sdo_geometry; begin     -- populate long , lat parameters     -- postcode_to_lat_lng_gm_api(p_postcode, l_lat, l_lng);     l_lat:=45.3;     l_lng:= 3.7;      -- populate new spatial object     n_spatial_object := sdo_geometry     (         -- use 01 wish add point map         2001,          -- srid wgs84 longitutde/latitude format         8307,          -- set information of point ( don't need z co-ord )         sdo_point_type         (             l_lng,             l_lat,             null          ),         null,   -- have no sdo_elem_info_array         null    -- have no sdo_ordinate_array     );      -- return new spatial object     dbms_output.put_line('sdo_gtype='||n_spatial_object.sdo_gtype);     dbms_output.put_line('first element sdo_ordinates='||n_spatial_object.sdo_ordinates(1));     return n_spatial_object; end set_spatial_point; / show errors 

that works fine.

let's try out:

sql> select set_spatial_point('xxxx') dual; error @ line 1: ora-06531: reference uninitialized collection ora-06512: @ "scott.set_spatial_point", line 38 

again, obvious. error on line 38:

        dbms_output.put_line('first element sdo_ordinates='||n_spatial_object.sdo_ordinates(1)); 

that fails (obviously again) since n_spatial_object.sdo_ordinates() null (like wrote on line 32.

let's remove debugging lines , redefine function. let's try out. works expected:

sql> select set_spatial_point('xxxx') dual;  set_spatial_point('xxxx')(sdo_gtype, sdo_srid, sdo_point(x, y, z), sdo_elem_inf ------------------------------------------------------------------------------- sdo_geometry(2001, 8307, sdo_point_type(3.7, 45.3, null), null, null)  1 row selected.