to_number(bb)

서브쿼리 문제라기 보다는

BB 컬럼에 숫자외에 다른 문자가 있다는 뜻입니다.

값에 대한 검증을 하는게 좋을듯 합니다.

WITH a_table AS
(
SELECT 1 cc FROM dual
UNION ALL SELECT 67 FROM dual
)
, b_table AS
(
SELECT 289 roleid, '1,2,67,45' bb FROM dual
UNION ALL SELECT 290, '3,4,5,6,7' FROM dual
)
SELECT *
  FROM a_table
 WHERE cc IN (SELECT REGEXP_SUBSTR(bb, '[^,]+', 1, LEVEL) cc
                FROM (SELECT bb FROM b_table WHERE roleid = 289)
               CONNECT BY LEVEL <= LENGTH(bb) - LENGTH(REPLACE(bb,',')) + 1
              )
;