SQL> ed
Wrote file afiedt.buf

  1  WITH nstr AS(
  2  SELECT 940 AS STR_ID, rpad('1',1)       AS STR_NAME, CAST(NULL AS NUMBER) AS PARENT_STR_ID FROM DUAL UNION
  3  SELECT 950 AS STR_ID, rpad('2',22)      AS STR_NAME, 940                  AS PARENT_STR_ID FROM DUAL UNION
  4  SELECT 972 AS STR_ID, rpad('3',11)      AS STR_NAME, 950                  AS PARENT_STR_ID FROM DUAL UNION
  5  SELECT 973 AS STR_ID, rpad('4',11)      AS STR_NAME, 972                  AS PARENT_STR_ID FROM DUAL UNION
  6  SELECT 980 AS STR_ID, rpad('5',3)       AS STR_NAME, CAST(NULL AS NUMBER) AS PARENT_STR_ID FROM DUAL UNION
  7  SELECT 981 AS STR_ID, rpad('6',20)      AS STR_NAME, 980                  AS PARENT_STR_ID FROM DUAL UNION
  8  SELECT 981 AS STR_ID, '1234 123 333 1234567Z'    AS STR_NAME, 980                  AS PARENT_STR_ID FROM DUAL
  9  --SELECT 980 AS STR_ID, rpad('7',21)             AS STR_NAME, CAST(NULL AS NUMBER) AS PARENT_STR_ID FROM DUAL
 10   )
 11              SELECT SYS_CONNECT_BY_PATH(nstr.str_name, 'Zf ') PATH,
 12                     nstr.str_id,
 13                     LEVEL
 14                FROM nstr
 15               START WITH nstr.parent_str_id IS NULL
 16*           CONNECT BY PRIOR nstr.str_id   = nstr.parent_str_id
SQL> /
ERROR:
ORA-30004: when using SYS_CONNECT_BY_PATH function, cannot have separator as
part of column value



no rows selected

QL> WITH nstr AS(
  2  SELECT 940 AS STR_ID, rpad('1',5)       AS STR_NAME, CAST(NULL AS NUMBER) AS PARENT_STR_ID FROM DUAL UNION
  3  SELECT 950 AS STR_ID, rpad('2',20)      AS STR_NAME, 940                  AS PARENT_STR_ID FROM DUAL UNION
  4  SELECT 980 AS STR_ID, rpad('5',15)       AS STR_NAME, CAST(NULL AS NUMBER) AS PARENT_STR_ID FROM DUAL UNION
  5  SELECT 981 AS STR_ID, rpad('7',10)||'Z'             AS STR_NAME, 980  AS PARENT_STR_ID FROM DUAL
  6   )
  7              SELECT SYS_CONNECT_BY_PATH(nstr.str_name, 'Z') PATH,
  8                     nstr.str_id,
  9                     LEVEL
 10  ,length(STR_NAME) l
 11                FROM nstr
 12               START WITH nstr.parent_str_id IS NULL
 13            CONNECT BY PRIOR nstr.str_id   = nstr.parent_str_id
 14  /
ERROR:
ORA-30004: when using SYS_CONNECT_BY_PATH function, cannot have separator as part of column value



no rows selected