Table TABLE_1 has the structure,
CREATE TABLE table_1
(
ID NUMBER (10),
LINE_NO NUMBER (10),
CODES VARCHAR2 (100)
)
It has the values,
INSERT INTO TABLE_1 VALUES (1,1,'ERR101:ERR102');
INSERT INTO TABLE_1 VALUES (1,2,'ERR103:ERR104:ERR106');
INSERT INTO TABLE_1 VALUES (1,1,'ERR105');
INSERT INTO TABLE_1 VALUES (1,3,'ERR107:ERR108');
INSERT INTO TABLE_1 VALUES (2,1,'');
INSERT INTO TABLE_1 VALUES (2,1,'ERR109:ERR110');
COMMIT;
It has the data,
-----------------------------------------------------------------------------------
ID | LINE_NO | CODES
-----------------------------------------------------------------------------------
1 1 ERR101:ERR102
1 2 ERR103:ERR104:ERR106
1 1 ERR105
1 3 ERR107:ERR108
2 1
2 1 ERR109:ERR110
-----------------------------------------------------------------------------------
I need to create a view which should run on the table TABLE_1 and to have the CODES column separated using the delimiter ':' into rows like,
-----------------------------------------------------------------------------------
ID | LINE_NO | CODES
-----------------------------------------------------------------------------------
1 1 ERR101
1 1 ERR102
1 1 ERR105
1 2 ERR103
1 2 ERR104
1 2 ERR106
1 3 ERR107
1 3 ERR108
2 1
2 1 ERR109
2 1 ERR110
-----------------------------------------------------------------------------------
Kindly help with the changes.
Thanks in Advance