SQL Developer

Hi all,

Please help me to solve this issue.

Our aim to populate the data to the table IMAP_MANAGEMENT_PARENT_CHILD from the view(IMAP_MANAGEMENT_HIER).I am unable to execute the below DML query. I got the below error... Please help me to solve this issue.

Error report:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 41
*06502. 00000 - "PL/SQL: numeric or value error%s"*
**Cause:*
**Action:*

Query
------------------
declare
V_MAX_DEPTH INTEGER;
v_stmt varchar2(32767);
i integer;
begin
select max(level) into v_max_depth
from IMAP_MANAGEMENT_HIER
connect by nocycle prior MANAGEMENT_ID=PARENT_ID
start with PARENT_ID is null;
v_stmt := 'insert into IMAP_MANAGEMENT_PARENT_CHILD (MEMBER_KEY, ANCESTOR_KEY, DISTANCE, IS_LEAF)' || chr(10)
|| 'select MANAGEMENT_ID as member_key, null, null, 0 from IMAP_MANAGEMENT_HIER where PARENT_ID is null' || chr(10)
|| 'union all' || chr(10)
|| 'select' || chr(10)
|| ' member_key,' || chr(10)
|| ' replace(replace(ancestor_key, ''\p'', ''|''), ''\'', ''\'') as ancestor_key,' || chr(10)
|| ' case when depth is null then 0' || chr(10)
|| ' else max(depth) over (partition by member_key) - depth + 1' || chr(10)
|| ' end as distance,' || chr(10)
|| ' is_leaf' || chr(10)
|| 'from' || chr(10)
|| '(' || chr(10)
|| ' select' || chr(10)
|| ' member_key,' || chr(10)
|| ' depth,' || chr(10)
|| ' case' || chr(10)
|| ' when depth is null then '''' || member_key' || chr(10)
|| ' when instr(hier_path, ''|'', 1, depth + 1) = 0 then null' || chr(10)
|| ' else substr(hier_path, instr(hier_path, ''|'', 1, depth) + 1, instr(hier_path, ''|'', 1, depth + 1) - instr(hier_path, ''|'', 1, depth) - 1)' || chr(10)
|| ' end ancestor_key,' || chr(10)
|| ' is_leaf' || chr(10)
|| ' from' || chr(10)
|| ' (' || chr(10)
|| ' select MANAGEMENT_ID as member_key, PARENT_ID as ancestor_key, sys_connect_by_path(replace(replace(MANAGEMENT_ID, ''\'', ''\''), ''|'', ''\p''), ''|'') as hier_path,' || chr(10)
|| ' case when MANAGEMENT_ID in (select PARENT_ID from IMAP_MANAGEMENT_HIER ) then 0 else 1 end as IS_LEAF' || chr(10)
|| ' from IMAP_MANAGEMENT_HIER ' || CHR(10)
|| ' connect by nocycle prior MANAGEMENT_ID = PARENT_ID ' || chr(10)
|| ' start with PARENT_ID is null' || chr(10)
|| ' ),' || chr(10)
|| ' (' || chr(10)
|| ' select null as depth from dual' || chr(10);
for i in 1..v_max_depth - 1 loop
v_stmt := v_stmt || ' union all select ' || i || ' from dual' || chr(10);
end loop;
v_stmt := v_stmt || ' )' || chr(10)
|| ')' || chr(10)
|| 'where ancestor_key is not null' || chr(10);
execute immediate v_stmt;
end;
/

MANY THANKS IN ADVANCE.

Kind Regards,
Mohan