DECLARE
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE jolie_2';
FOR I IN (select JOLIE.CODE_OLD, JOLIE.CODE_NEW, JOLIE.CODE_DESC
from jolie_1 jolie) LOOP
INSERT INTO jolie_2
(CODE_TYPE,
TYPE_DESC,
CODE_OLD,
CODE_OTHER,
CODE_NEW,
CODE_DESC,
SYS_DESC)
SELECT 'ACCIDENTREASON' CODE_TYPE,
'' TYPE_DESC,
REGEXP_SUBSTR(I.CODE_OLD, '[^/]+', 1, LEVEL, 'i') AS CODE_OLD,
'' CODE_OTHER,
I.CODE_NEW,
I.CODE_DESC,
'ETA' SYS_DESC
FROM DUAL
CONNECT BY LEVEL <=
LENGTH(I.CODE_OLD) - LENGTH(REPLACE(I.CODE_OLD, '/', '')) + 1;
COMMIT;
END LOOP;
END;