plsql cannot use variables in sql select

Question

I am getting very fed up of APEX in recent days, i am trying to run a interactive report within application express and cannot seem to use variables i declare within my sql queries. Below is the error, i also included the code that works. the get_longlat() function is tested and works it returns a SDO_GEOMETRY data type.

ORA-06550: line 7, column 12: PLS-00306: wrong number or types of arguments in call to '||' ORA-06550: line 7, column 1: PL/SQL: Statement ignored

DECLARE
l_query VARCHAR2(4096);
l_location SDO_GEOMETRY := get_longlat(v('P2_POSTCODE'));
BEGIN

l_query := '
                SELECT
                "VENUE_ID",
                "VENUE_NAME",
                "CITY",
                "VENUE_NO",
                "POSTCODE",
                dbms_lob.getlength("THUMBNAIL") "THUMBNAIL",
                "DESCRIPTION",
                SDO_GEOM.SDO_DISTANCE(G_LOCATION, '  || l_location || ',0.005,''unit=mile'') "G_LOCATION"                
                FROM   "GAMEVENUE"
                WHERE (SDO_WITHIN_DISTANCE(G_LOCATION,' || l_location || ',''distance=250 unit=mile'') = ''TRUE'');';

RETURN l_query;

This works

l_query := '
                SELECT
                "VENUE_ID",
                "VENUE_NAME",
                "CITY",
                "VENUE_NO",
                "POSTCODE",
                dbms_lob.getlength("THUMBNAIL") "THUMBNAIL",
                "DESCRIPTION",
                SDO_GEOM.SDO_DISTANCE(G_LOCATION, MDSYS.SDO_GEOMETRY(2001,4326,SDO_POINT_TYPE(-4.1530439,50.371089,NULL),NULL,NULL),0.005,''unit=mile'') "G_LOCATION"                
                FROM   "GAMEVENUE"
                WHERE (SDO_WITHIN_DISTANCE(G_LOCATION,SDO_GEOMETRY(2001,4326,SDO_POINT_TYPE(-4.1530439,50.371089,null),null,null),''distance=250 unit=mile'') = ''TRUE'')';

|| only works with character types, or types that can be implicitly converted to character types. The || operator concatenates character strings and CLOB data. — TurtlesAllTheWayDown
– TurtlesAllTheWayDown, Commented Dec 4, 2017 at 14:45

Barbaros Özhan · Accepted Answer · 2017-12-04 14:45:09Z

Because SDO_GEOMETRY is not of type char or number( implicitly considerable as char ), you can't use it in direct concetenation. Try this instead :

l_query := '
                SELECT
                "VENUE_ID",
                "VENUE_NAME",
                "CITY",
                "VENUE_NO",
                "POSTCODE",
                dbms_lob.getlength("THUMBNAIL") "THUMBNAIL",
                "DESCRIPTION",
                SDO_GEOM.SDO_DISTANCE(G_LOCATION, MDSYS.SDO_GEOMETRY('||l_sdo_gtype||','||l_sdo_srid||',MDSYS.SDO_POINT_TYPE('||l_longitude||','||l_latitude||',NULL),NULL,NULL),0.005,''unit=mile'') "G_LOCATION"                
                FROM   "GAMEVENUE"
                WHERE (SDO_WITHIN_DISTANCE(G_LOCATION,SDO_GEOMETRY('||l_sdo_gtype||','||l_sdo_srid||',SDO_POINT_TYPE('||l_longitude||','||l_latitude||',null),null,null),''distance=250 unit=mile'') = ''TRUE'')';

where l_sdo_gtype,l_sdo_srid, l_longitude, l_latitude are of type number;

Parsing returned query results in "ORA-06550: line 10, column 78: ORA-00936: missing expression". If you believe your query is syntactically correct, check the "Use Generic Column Names" checkbox below to proceed without parsing.

Collectives™ on Stack Overflow

plsql cannot use variables in sql select

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related