I was looking to reproduce the APEX 4.0 quick pick functionality for APEX 3.2 and remembered a blog entry from Anthony Rayner on the exact subject back in 2008. Armed with the step by step approach and code I decided to tweak it so that I could reuse the LOV definition for my current item rather than passing the SQL query as a parameter. The reason for this is that I might want to use a static or a dynamic LOV, or a named version of them at the application level (basically I wanted to create a crude manual plugin).

Here’s the APEX IDE item settings for my “Quick Pick” item

And here’s an example screenshot from development

Lastly… here’s the behind the scenes code which reuses most of Anthony’s original quick_pick procedure with an additional supporting function adapted from Simon Hunt’s blog on turning a string into a queryable table.

CREATE OR REPLACE TYPE apexLovType
AS OBJECT
(
  DISPLAY_COL  VARCHAR2(4000),
  VALUE_COL    VARCHAR2(4000)
)
/
create or replace
PACKAGE UTILS AS

  TYPE ret_val_t IS TABLE OF apexLovType;

  FUNCTION get_static_lov_results
  ( p_static_lov VARCHAR2
  ) RETURN ret_val_t PIPELINED;

  PROCEDURE quick_pick
  ( p_item        VARCHAR2
  , p_target_item VARCHAR2
  );

END UTILS;
/
create or replace
PACKAGE BODY UTILS AS
  FUNCTION get_static_lov_results
  ( p_static_lov VARCHAR2
  ) RETURN ret_val_t PIPELINED
  IS
    l_static_lov VARCHAR2(4000);
    l_vc_arr2 apex_application_global.vc_arr2;
  BEGIN
    l_static_lov := p_static_lov;
    l_vc_arr2 := apex_util.string_to_table(l_static_lov, ',');
    FOR z IN 1 .. l_vc_arr2.COUNT LOOP
      PIPE ROW(apexLOvType(apex_util.string_to_table(l_vc_arr2(z),';')(1), apex_util.string_to_table(l_vc_arr2(z),';')(2)));
    END LOOP;
  END;

  PROCEDURE quick_pick
  ( p_item        VARCHAR2
  , p_target_item VARCHAR2
  ) AS
    TYPE cur_type IS REF CURSOR;
    cur             cur_type;
    v_display       VARCHAR2(4000);
    v_return        VARCHAR2(4000);
    l_names         DBMS_SQL.VARCHAR2_TABLE;
    l_app_id        apex_application_page_items.application_id%TYPE   := v('APP_ID');
    l_page_id       apex_application_page_items.page_id%TYPE          := v('APP_PAGE_ID');
    l_query         apex_application_page_items.lov_definition%TYPE;
  BEGIN
    FOR c IN
    ( SELECT CASE
               WHEN lov_definition LIKE '.%.' THEN
                 (SELECT list_of_values_query
                  FROM   apex_application_lovs
                  WHERE  lov_type = 'Dynamic'
                  AND    lov_id   = replace(p.lov_definition,'.','')
                  UNION
                  SELECT 'SELECT display_value d, return_value r FROM apex_application_lov_entries WHERE lov_id = '||replace(p.lov_definition,'.','')
                  FROM   apex_application_lovs
                  WHERE  lov_type = 'Static'
                  AND    lov_id   = replace(p.lov_definition,'.',''))
               WHEN lov_definition LIKE 'STATIC2:%' THEN
                 'SELECT * FROM TABLE(utils.get_static_lov_results('''||replace(lov_definition, 'STATIC2:','')||'''))'
               ELSE
                 lov_definition
              END lov_definition
      FROM   apex_application_page_items p
      WHERE  application_id = l_app_id
      AND    page_id        = l_page_id
      AND    item_name      = p_item
    ) LOOP
      l_query := c.lov_definition;
      --
      -- Lets just make sure we have a query defined
      --
      IF l_query IS NULL THEN
        RAISE_APPLICATION_ERROR(-20001, 'Quick Picks Item '||p_item||' does not have a LOV query defined!');
      END IF;

      --get any binds and replace with the value from session
      l_names := WWV_FLOW_UTILITIES.GET_BINDS(l_query);
      FOR i IN 1..l_names.COUNT LOOP
      l_query := REPLACE( LOWER(l_query),
                          LOWER(l_names(i)),
                          '(select v('''||
                            LOWER( LTRIM(l_names(i), ':')) ||
                            ''') from dual)');
      END LOOP;
      HTP.P('<div>');
      OPEN cur FOR l_query;
      LOOP
        FETCH cur INTO v_display, v_return;
        EXIT WHEN cur%NOTFOUND;
        HTP.ANCHOR('javascript:setValue(''' ||
                    p_target_item || ''',''' ||
                    v_return || ''');',
        '[' || v_display || ']',
        null,
        'class=''itemlink''');
      END LOOP;
      CLOSE cur;
      HTP.P('</div>');
      HTP.P('<br/>');
    END LOOP;
  END quick_pick;
END UTILS;
/

P.S. watch out for any literals in your LOV query as the above code from Anthony puts the entire query source in lowercase.