CODEWITHSUNDEEP
sqloracle-databaseplsqloracle-apex
Matt Farrell
Matt Farrell

Reputation: 199

plsql cannot use variables in sql select

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'')';

Upvotes: 0

Views: 317

Answers (1)

Barbaros Özhan
Barbaros Özhan

Reputation: 65105

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;

Upvotes: 1

Related Questions