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
function_that_returns_an_sql_query. Can you include the full error message, including the line number, and some information about that function?