DECLARE
	V_PRODUCTS APEX_APPLICATION_GLOBAL.VC_ARR2;
BEGIN
	SELECT ID_PRODUCTS RETURN_VALUE
		BULK COLLECT
		INTO V_PRODUCTS
		FROM CATEGORIES_VIRT
		WHERE NVL(:P2_ID,'-1')='-1' OR ID_CATEGORIES = :P2_ID;
	
	RETURN(APEX_UTIL.TABLE_TO_STRING(V_PRODUCTS));
END;

SELECT C.NAME||' / '||P.NAME DISPLAY_VALUE, P.ID RETURN_VALUE 
FROM PRODUCTS P LEFT JOIN CATEGORIES C
 ON P.CATEGORY_ID = C.ID
WHERE P.ACTIVE = 1
ORDER BY P.CATEGORY_ID, P.NAME

DECLARE
  ITEM_ARRAY_ HTMLDB_APPLICATION_GLOBAL.VC_ARR2;
  DELIM_ VARCHAR2(1) := ':';
BEGIN
  ITEM_ARRAY_ := HTMLDB_UTIL.STRING_TO_TABLE(TRIM(BOTH DELIM_ FROM :P2_ADD_PRODUCT),DELIM_); 

  DELETE FROM CATEGORIES_VIRT 
   WHERE ID_CATEGORIES = :P2_ID
     AND INSTR(':'||:P2_ADD_PRODUCT||':', ':'||ID_PRODUCTS||':') = 0;

  FOR I IN 1..ITEM_ARRAY_.COUNT
  LOOP
    BEGIN
      INSERT INTO CATEGORIES_VIRT (ID_PRODUCTS, ID_CATEGORIES) 
      VALUES (ITEM_ARRAY_(I), :P2_ID);
    EXCEPTION
      WHEN DUP_VAL_ON_INDEX THEN NULL;
    END; 
  END LOOP;
  COMMIT;
END;