Check Multiple Values in WHERE Clause SQL Query REGEXP_LIKE

-- Created on 9/29/2017 by C_CKANDA

DECLARE

  -- Local variables here

  LN                  INTEGER;

  LV_TRANS_TYPES      VARCHAR2(4000);

  LV_TRANS_TYPE_IDS   VARCHAR2(4000);

  LV_TRANS_TYPES_IDS  VARCHAR2(4000);

  LV_TRANS_TYPES_IDS1 VARCHAR2(4000);

BEGIN

  -- Test statements here

  SELECT LISTAGG(TRANSACTION_TYPE_ID, '|') WITHIN GROUP(ORDER BY 1)

    INTO LV_TRANS_TYPES_IDS

    FROM MTL_TRANSACTION_TYPES

   WHERE TRANSACTION_TYPE_NAME IN

         (SELECT MEANING

            FROM FND_LOOKUP_VALUES_VL

           WHERE LOOKUP_TYPE = 'LOOKUPNAME');

-- it will return multiple values like 2|38|45|60 

  LV_TRANS_TYPES_IDS := '^(' || LV_TRANS_TYPES_IDS || ')$';

 -- assignment  ^(2|38|45|60)$ passing this values to the below query

  SELECT COUNT(*)

    INTO LN

    FROM MTL_TRANSACTION_TYPES

   WHERE 1 = 1

     AND REGEXP_LIKE(TRANSACTION_TYPE_ID, LV_TRANS_TYPES_IDS);

  DBMS_OUTPUT.PUT_LINE(LN);

END;