This discussion is archived
9 Replies Latest reply: Nov 21, 2012 6:50 PM by 975108 RSS

Using variables in PL/SQL function body returning SQL query

975108 Newbie
Currently Being Moderated
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;
  • 1. Re: Using variables in PL/SQL function body returning SQL query
    matthew_morris Expert
    Currently Being Moderated
    I've re-spaced your SQL to have opening and closing parenthesis aligned vertically (my favorite means for ensuring they are matched). Looks to me like you have unmatched parenthesis. Fill in l_lng and l_lat with valid values and try to run the following from SQL*Plus.
    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
                                        ),  --  <-------  unmatched 
                                 0.005, 
                                 'UNIT=MILE'
                                ) DISTANCE
    from   CARS
  • 2. Re: Using variables in PL/SQL function body returning SQL query
    975108 Newbie
    Currently Being Moderated
    Hey I think that bracket is actually matched with SDO_GEOMETRY(2001,
    select CAR_ID, CAR_NAME, CAR_POSTCODE,
           SDO_GEOM.SDO_DISTANCE(car_location, SDO_GEOMETRY(2001, -- <---- matched here?!
                                 8307,
                                 SDO_POINT_TYPE(to_char(l_lng), 
                                                to_char(l_lat),
                                                NULL
                                               ),
                                 NULL,
                                 NULL
                                                           ), -- <------ matched with SDO_GEOMETRY(
                                 0.005, 
                                 'UNIT=MILE'
                                ) DISTANCE
    from   CARS
    Thanks for your reply though! =)
  • 3. Re: Using variables in PL/SQL function body returning SQL query
    Prabodh Guru
    Currently Being Moderated
    Hi,

    A. Have you changed the Region type to PL/SQL Function body returning SQL query ?
    B. Have you tried running your query in SQLWorkshop, SQLPlus, SQL Developer or TOAD as has been already suggested? You will usually get a line and column where the error occurs.

    Cheers,
  • 4. Re: Using variables in PL/SQL function body returning SQL query
    975108 Newbie
    Currently Being Moderated
    Hey,

    A) Yes I have selected the Region as a PL/SQL Function body returning SQL query

    B) argh okay great, so i ran it threw SQL Workshop thing and it came up with this error:
    PLS-00372: In a procedure, RETURN statement cannot contain an expression
    I don't understand why though, I thought that by doing to_char and adding it within the l_query variable would make it just return a string

    Thanks
  • 5. Re: Using variables in PL/SQL function body returning SQL query
    Prabodh Guru
    Currently Being Moderated
    Try removing the to_char, let implicit conversion take its course.

    Cheers,
  • 6. Re: Using variables in PL/SQL function body returning SQL query
    975108 Newbie
    Currently Being Moderated
    Hi,

    Yer I tried that too and still the same error message!! :/

    EDIT: Okay so i just tried to run this through SQL Workshop and the same error message was there so looks like SQL Workshop can't help us!
     
    BEGIN
    
    return 'select * from cars';
    
    END;
    Thanks

    Edited by: 972105 on 19-Nov-2012 21:34

    Edited by: 972105 on 19-Nov-2012 21:37
  • 7. Re: Using variables in PL/SQL function body returning SQL query
    Prabodh Guru
    Currently Being Moderated
     
    BEGIN

    return 'select * from cars';

    END;
    {quote}
    Wait a minute. What is return doing in this snippet? Should it not be DBMS_OUTPUT.PUT_LINE?
    All that SQLWorkshop , or any of the other tools suggested, is going to do is show you the SELECT statement. You have to run the returned SELECT to see what is going wrong.
    
    Cheers,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
  • 8. Re: Using variables in PL/SQL function body returning SQL query
    matthew_morris Expert
    Currently Being Moderated
    This is the way to test the function from SQL*Workshop:

    P.S. Please note properly aligned parenthesis on take II. :)
    CREATE FUNCTION test_sqlfunc(l_lng   VARCHAR2,
                                 l_lat   VARCHAR2)
    RETURN VARCHAR2
    AS
     
    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 test_sqlfunc;
    
    SELECT test_sqlfunc('[lng_val]', '[lat_val]') FROM dual;
  • 9. Re: Using variables in PL/SQL function body returning SQL query
    975108 Newbie
    Currently Being Moderated
    Okay so did a little playing around and eventually got it to work, the SQL Command didn't help with the function FYI (maybe something to do with it testing SQL statements and not PL/SQL?).

    My 1st problem was that one of my columns relied on an apex text box which isn't always filled in so the SQL Compiler didn't like that. To solve this I did an IF NOT NULL, ELSE statement.

    My 2nd problem was after I did that i didn't have a value for Distance in the ELSE statement and so APEX didn't know weather or not to add a Distance column. I solved this by making the distance column NULL.

    Here is my final working code:
    DECLARE
    
    l_lat NUMBER;
    l_lng NUMBER;
    
    l_SDO_GEOMETRY SDO_GEOMETRY;
    l_query VARCHAR2(30000);
    
    BEGIN
    IF :P1_POSTCODE IS NOT NULL THEN
    
    POSTCODE_TO_LAT_LNG_GM_API (:P1_POSTCODE, l_lat, l_lng);
    
    l_query := 'select 
    CAR_ID, CAR_NAME, CAR_DESC, CAR_MAKE, CAR_MODEL, CAR_MILEAGE, CAR_PRICE, CAR_YEAR, CAR_POSTCODE,
    SDO_GEOM.SDO_DISTANCE(car_location, SDO_GEOMETRY(2001,
                                        8307,
                                        SDO_POINT_TYPE(' || l_lng || ',' 
                                                       || l_lat || ',
                                                       NULL),
                                        NULL,
                                        NULL), 
                                        0.005, ''UNIT=MILE'') DISTANCE,
    dbms_lob.getlength(CAR_IMAGE_SMALL) CAR_IMAGE_SMALL,
    CAR_FUEL_TYPE, CAR_TRANSMISSION, CAR_ENGINE_SIZE, CAR_NUM_DOORS, CAR_BODY_TYPE, CAR_COLOUR
     from   CARS';
    
    ELSE
    
    l_query := 'select 
    CAR_ID, CAR_NAME, CAR_DESC, 
    NULL Distance,
    dbms_lob.getlength(CAR_IMAGE_SMALL) CAR_IMAGE_SMALL,
    CAR_FUEL_TYPE, CAR_TRANSMISSION, CAR_ENGINE_SIZE, CAR_NUM_DOORS, CAR_BODY_TYPE, CAR_COLOUR
     from   CARS';
    
    END IF;
    
    RETURN l_query;
    END;
    So yer problem solved now, thanks to you guys for trying to help out!! =)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points