h4. okay so I have this procedure
POSTCODE_TO_LAT_LNG_GM_API(postcode IN VARCHAR2, lat OUT NUMBER, p_long OUT NUMBER)
to convert a postcode into lat/long values. I then need to add them to the returned SQL statement so I used the string concat operator || with to_char but it comes up with this error when I try to apply the changes:
Query cannot be parsed within the Builder. If you believe your query is syntactically correct, check the ''generic column'' checkbox below the region source to proceed without parsing.
ORA-00936: missing expression
h4. Does anyone know what I am doing wrong here I have tried so many different ways round and none seem to work!! :/
h4. btw I'm using Oracle 11g release 11.2.0.3.0 and Apex version 4.1.1.00.23
DECLARE
l_lat NUMBER;
l_lng NUMBER;
l_SDO_GEOMETRY SDO_GEOMETRY;
l_query VARCHAR2(30000);
BEGIN
POSTCODE_TO_LAT_LNG_GM_API (:P1_POSTCODE, l_lat, l_lng);
l_query := 'select
CAR_ID, CAR_NAME, CAR_POSTCODE,
SDO_GEOM.SDO_DISTANCE(car_location, SDO_GEOMETRY(2001,
8307,
SDO_POINT_TYPE(' || to_char(l_lng) || ','
|| to_char(l_lat) || ',
NULL),
NULL,
NULL),
0.005, ''UNIT=MILE'') DISTANCE
from CARS';
RETURN l_query;
END;