Oracle String concatenation

Createorreplaceprocedureproc_query_prior_departmentisCursordepartment_list_nosubisselectdep.id, dep. name, dep. paren

Create or replace procedure proc_query_prior_departmentisCursor department_list_nosub isselect dep. id, dep. name, dep. paren

Create or replace procedure proc_query_prior_department
Is

Cursor department_list_nosub is
Select dep. id, dep. name, dep. parent from department dep where org_id = 7;
Union_mc varchar2 (200 );

Begin

Delete from ma_department_role;
Commit;
For varDep in department_list_nosub loop

For cur in (select t. parent, t. name from department t start with t. id = varDep. id connect by prior t. parent = t. id order by t. id asc) loop
If cur. parent is not null then

If (union_mc is null) then
Union_mc: = cur. name;
Else
Union_mc: = union_mc | '/' | cur. name;
End if;
End if;
End loop;
Insert into ma_department_role (id, name) values (varDep. id, union_mc );
Union_mc: = null;
End loop;
Commit;

End;

,