9 Replies Latest reply: Nov 21, 2012 8:50 PM by 975108 RSS

    Using variables in PL/SQL function body returning SQL query

    975108
      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
          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
            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
              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
                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
                  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
                    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
                       
                      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
                        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
                          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!! =)