CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloperoracle-apex
user5730489
user5730489

Reputation:

parsing returned query in sql

I am trying to execute this code in oracle application express. I keep receiving a parsing returned query results.

Parsing returned query results in "ORA-06550: line 11, column 19: ORA-00933: SQL command not properly ended". If you believe your query is syntactically correct, check the "Use Generic Column Names" checkbox below to proceed without parsing.

I cant figure out what it means and how to correct this error. Can anyone help me or give me some advice? please.

IF v('P2_POSTCODE_SEARCH') IS NOT NULL THEN
   GEOCODE_GM_XML (l_postcode, l_lat, l_lng);
l_query := l_query||' '||' 
TO_CHAR(
       SDO_GEOM.SDO_DISTANCE
       (SDO_GEOMETRY(2001, -- SDO_GTYPE
                                 8307, -- SDO_SRID
                                 SDO_POINT_TYPE('||l_lng||', --X longitude
                                                '||l_lat||', --Y latitude
                                                null),     --Z 3D only
                                 null, -- SDO_ELEM_INFO_ARRAY
                                 null), -- SDO_ORDINATE_ARRAY 
        location, 
        0.005, 
        ''unit=mile''),''9g999'') distance';

else

l_query := l_query||' '||' NULL DISTANCE';
END IF;

Upvotes: 1

Views: 747

Answers (1)

kevinskio
kevinskio

Reputation: 4551

Here is a stripped down version of your script. Part of troubleshooting is removing all the other things we should do like commenting the code and checking for nulls. The code below is just a test case and compiles and runs on Oracle 11g. Run this as a script and supply some values for l_lng an l_lat. Check the output, test and repeat.

DECLARE
  L_QUERY VARCHAR2(2000);
  l_lng  NUMBER(20);
  l_lat NUMBER(20);
BEGIN

  L_QUERY := L_QUERY || ' ' || ' 
    TO_CHAR(
           SDO_GEOM.SDO_DISTANCE
           (SDO_GEOMETRY(2001, 
                                     8307, 
                                     SDO_POINT_TYPE(' || L_LNG || ', 
                                                    ' || L_LAT || ', 
                                                    null),     
                                     null, 
                                     null), 
            location, 
            0.005, 
            ''unit=mile''),''9g999'') distance';

  L_QUERY := L_QUERY || ' ' || 'FROM BARS';

  DBMS_OUTPUT.PUT_LINE(L_QUERY);
END;

Upvotes: 1

Related Questions