apex select list item should return default values

0
votes

I have an select list item which is having LOV as select name, name from dual, but I need to display some default value for that item, the default PL/SQL code is 

DECLARE
   l_workspace   VARCHAR2 (4000);
   l_email       VARCHAR2 (4000);
BEGIN
   SELECT WORKSPACES
     INTO l_workspace
     FROM ALLUSER_WORKSPACES_FACT
    WHERE LOWER (email) = LOWER ( :app_user);

   FOR i
      IN (SELECT COLUMN_VALUE col1
            FROM TABLE (f_str2tbl (LOWER (l_Workspace), ',')))
   LOOP
      l_email := i.col1;
      RETURN l_email;
   END LOOP;
END;

o/p is like:

a
b
c

The select list value is displaying only the 1st value as remaining data is not being displayed.

What's wrong with the PL/SQL code? I need to get all the output in the select list as 

a
b
c

Thanks

2
oracle-apexoracle-apex-5oracle-apex-5.1
It seems that you're confusing a LoV with the default value. Default value is "one value" (for example, "a") so - if that's what you got, that's OK. List of values displays several options you can choose, so your (simplified?) select name, name from dual should return all those "a, b, c" values. - Littlefoot
i want to get multiple value as the result of default values from the above plsql code - Abinnaya
You can't get it, as far as I can tell. Default value is one value, not multiple values. - Littlefoot

2 Answers

0
votes

If your select list is defined with Allow Multi Selection = Yes then the selected values are held as a colon-delimited list e.g. 'A:B:C'. So you need to make your default the same. The apex_string.join function can help - in fact their example is:

apex_string.join(apex_t_varchar2('a','b','c'),':')
-> a:b:c
0
votes

Statement

return l_email;

always ends function, so it breaks the loop.

For this LOV you should use PL/SQL Function Body returning SQL Query with code like 

DECLARE
   l_workspace   VARCHAR2 (4000);
   l_email       VARCHAR2 (4000);
BEGIN
  SELECT WORKSPACES
     INTO l_workspace
     FROM ALLUSER_WORKSPACES_FACT
    WHERE LOWER (email) = LOWER ( :app_user);

   return 'SELECT COLUMN_VALUE col1
            FROM TABLE (f_str2tbl (LOWER (l_Workspace), '',''))';
END;