0

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.

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:

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;

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.

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;    

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.

Any ideas would be appreciated

4
  • 1
    I assume the problem is with the query returned by function_that_returns_an_sql_query. Can you include the full error message, including the line number, and some information about that function? Commented Jul 26, 2019 at 23:31
  • 1
    Also the value passed to the function ( apex_application.g_f02(i) ), Also it might help you to review the help sections How to Ask and minimal reproducible example. Commented Jul 28, 2019 at 19:25
  • @JonHeller prntscr.com/olcuj7 This is the error, line 94 is the one starting with EXECUTE IMMEDIATE. The function returns VARCHAR2, with the form being as described above, SELECT firstcol FROM (LOV QUERY) WHERE firstcol = :parameter; Commented Jul 29, 2019 at 7:39
  • @Belayer The value passed is the value of the item, it works for everything else in the validation procedure. I can try assigning this value to a different variable and using that variable(tried it, didnt work), also tried a static assignment of the variable, still the same error. As far as minimal reproductible example, this would require a post several pages long to give you sets of item descriptions, a plsql dynamic region,... Commented Jul 29, 2019 at 7:44

1 Answer 1

1

Just wanted to post an update, actually got a solution going. And it was the stupidest thing ever.

The bit that is executed dynamically, I ended it with a semicolon, shouldnt have done that. That was all there was to it.

That one semicolon was what was messing me up, now it works. So if anyone stumbles upon this question, check your semicolons.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.