title: Dynamically checking if value is in sql query
body: <p>I have a dynamically generated plsql region, in it, I have a bunch of dynamically generated items, some are popup_from_query and those have a query stored in an underlying table. But they also enable you to just type the return value in. So now I need to somehow check if the value you entered is in the LOV.</p>
  
  <p>So I added some code to my validation(some also have extra conditions so I check those). I made a function that returns the relevant LOV, but reworked to something like this:</p>
  
  <pre><code>SELECT id  --this is always the name of the column containing return value
      FROM ( select something id, --this is the return value
         something_else name --display value
  from table
  WHERE conditions) 
     WHERE id= :param;
  </code></pre>
  
  <p>This selects the return column from the lov, where the value is equal to the value we have entered, so this returns the entered value if it is in the LOV, and nothing if its not a valid value.</p>
  
  <pre><code>IF checking_lov THEN
        a_lov := function_that_returns_an_sql_query;
        DECLARE
          x VARCHAR2(100);
        BEGIN
          EXECUTE IMMEDIATE
            a_lov
          INTO x
           USING apex_application.g_f02(i); --value of parameter
        EXCEPTION
          WHEN no_data_found THEN
            x := NULL;
        END;
        IF x IS NULL THEN
          display_error;  --I do stuff here to display error, not important to you
        END IF;
    END IF;    
  </code></pre>
  
  <p>But I can't seem to get this to work. APEX doesn't find anything wrong with the code, it compiles without errors. But when I try it out, it throws an 'Invalid character' error, and the validation passes. I have tried perhaps making the entire a_lov query, adding an 'INTO x' and changing ':param' into 'apex_application.g_f02(i). But it just doesn't work.</p>
  
  <p>Any ideas would be appreciated</p>