had to turn a row value (which was a comma separated list) to a column of values

solution was this query, although it would be much better to create a stored procedure or a function instead using this query directly:

SELECT SUBSTR ( :P_STR,
                LOC + 1,
                NVL (LEAD (LOC) OVER (ORDER BY LOC) - LOC - 1,
                LENGTH ( :P_STR) - LOC)) TO_ROW
  FROM (    SELECT  DISTINCT (INSTR ( :P_STR,
                                      :DELIMITER,
                                      1,
                                      LEVEL)) LOC
              FROM  DUAL
           CONNECT  BY LEVEL < LENGTH (:P_STR));

example:

DECLARE
    l_STR VARCHAR2(2000);
    c_DELIMITER CHAR;
BEGIN
    l_STR := 'car park,on hold,fenne lily';
    c_DELIMITER := ',';

    FOR x IN (  SELECT SUBSTR ( l_STR,
                                LOC + 1,
                                NVL (LEAD (LOC) OVER (ORDER BY LOC) - LOC - 1,
                                LENGTH ( l_STR) - LOC)) TO_ROW
                  FROM (    SELECT  DISTINCT (INSTR ( l_STR,
                                                      c_DELIMITER,
                                                      1,
                                                      LEVEL)) LOC
                              FROM  DUAL
                           CONNECT  BY LEVEL < LENGTH (l_STR))

    )
    LOOP
        DBMS_OUTPUT.PUT_LINE(x.TO_ROW);
    END LOOP;
END;

/*
car park
on hold
fenne lily
*/