Code:
test@ORA11G>
test@ORA11G> --
test@ORA11G> drop table mpr_text;
Table dropped.
test@ORA11G> CREATE TABLE MPR_TEXT
2 (
3 REF_CODE VARCHAR2(30 CHAR) NOT NULL,
4 TYPE VARCHAR2(30 CHAR) NOT NULL,
5 KEY1 VARCHAR2(30 CHAR) NOT NULL,
6 KEY2 VARCHAR2(30 CHAR) NOT NULL,
7 LINE_NO NUMBER(10) NOT NULL,
8 TEXT_LINE VARCHAR2(2000 CHAR),
9 CRE_USR VARCHAR2(30 CHAR) NOT NULL,
10 CRE_DAT DATE NOT NULL,
11 MOD_USR VARCHAR2(30 CHAR) NOT NULL,
12 MOD_DAT DATE NOT NULL
13 )
14 ;
Table created.
test@ORA11G>
test@ORA11G> --
test@ORA11G> Insert into MPR_TEXT
2 (REF_CODE, TYPE, KEY1, KEY2, LINE_NO, TEXT_LINE, CRE_USR, CRE_DAT, MOD_USR, MOD_DAT)
3 Values
4 ('O', 'OP', '123456', 'DFLT', 1, 'MY TESTREMARK ON HEADER LINE 1
5 ANOTHER TESTREMARK ON HEADER LINE 2', 'ME', to_date('1/1/2010','mm/dd/yyyy'), 'ME', to_date('2/1/2010','mm/dd/yyyy'));
1 row created.
test@ORA11G> Insert into MPR_TEXT
2 (REF_CODE, TYPE, KEY1, KEY2, LINE_NO, TEXT_LINE, CRE_USR, CRE_DAT, MOD_USR, MOD_DAT)
3 Values
4 ('O', 'OP', '123456', 'DFLT', 2, 'AND ANOTHER TESTREMARK ON HEADER BUT A NEW LINE', 'ME', to_date('1/15/2010','mm/dd/yyyy'), 'ME', to_date('2/15/2010','mm/dd/yyyy'));
1 row created.
test@ORA11G> Insert into MPR_TEXT
2 (REF_CODE, TYPE, KEY1, KEY2, LINE_NO, TEXT_LINE, CRE_USR, CRE_DAT, MOD_USR, MOD_DAT)
3 Values
4 ('O', 'OP', '123456', '1', 1, 'LINE 1 TESTREMARK 1
5 ANOTHER LINE 1 TESTREMARK 2', 'ME', to_date('2/1/2010','mm/dd/yyyy'), 'ME', to_date('3/1/2010','mm/dd/yyyy'));
1 row created.
test@ORA11G> Insert into MPR_TEXT
2 (REF_CODE, TYPE, KEY1, KEY2, LINE_NO, TEXT_LINE, CRE_USR, CRE_DAT, MOD_USR, MOD_DAT)
3 Values
4 ('O', 'OP', '123456', '2', 1, 'LINE 2 TESTREMARK 1
5 ANOTHER LINE 2 TESTREMARK 2', 'ME', to_date('2/15/2010','mm/dd/yyyy'), 'ME', to_date('3/15/2010','mm/dd/yyyy'));
1 row created.
test@ORA11G> Insert into MPR_TEXT
2 (REF_CODE, TYPE, KEY1, KEY2, LINE_NO, TEXT_LINE, CRE_USR, CRE_DAT, MOD_USR, MOD_DAT)
3 Values
4 ('O', 'OP', '123456', '1', 2, 'LINE 1 TESTREMARK 3', 'ME', to_date('3/1/2010','mm/dd/yyyy'), 'ME', to_date('4/1/2010','mm/dd/yyyy'));
1 row created.
test@ORA11G> commit;
Commit complete.
test@ORA11G>
test@ORA11G> -- fetch data
test@ORA11G> select * from mpr_text;
REF_CODE TYPE KEY1 KEY2 LINE_NO TEXT_LINE CRE_USR CRE_DAT MOD_USR MOD_DAT
---------- ----- -------- -------- ---------- ----------------------------------------------- -------- --------- -------- ---------
O OP 123456 DFLT 1 MY TESTREMARK ON HEADER LINE 1 ME 01-JAN-10 ME 01-FEB-10
ANOTHER TESTREMARK ON HEADER LINE 2
O OP 123456 DFLT 2 AND ANOTHER TESTREMARK ON HEADER BUT A NEW LINE ME 15-JAN-10 ME 15-FEB-10
O OP 123456 1 1 LINE 1 TESTREMARK 1 ME 01-FEB-10 ME 01-MAR-10
ANOTHER LINE 1 TESTREMARK 2
O OP 123456 2 1 LINE 2 TESTREMARK 1 ME 15-FEB-10 ME 15-MAR-10
ANOTHER LINE 2 TESTREMARK 2
O OP 123456 1 2 LINE 1 TESTREMARK 3 ME 01-MAR-10 ME 01-APR-10
5 rows selected.
test@ORA11G>
test@ORA11G> -- run query
test@ORA11G> select ref_code,
2 type,
3 key1,
4 key2,
5 line_no,
6 substr(text_line,
7 case x.iter when 1 then 1 else instr(text_line,chr(10),1,x.iter-1)+1 end,
8 case
9 when instr(text_line,chr(10),1,x.iter) > 0
10 then instr(text_line,chr(10),1,x.iter) - case x.iter when 1 then 1 else instr(text_line,chr(10),1,x.iter-1) + 1 end
11 else length(text_line)
12 end
13 ) text_line,
14 x.iter num,
15 cre_dat
16 from mpr_text t,
17 (select level iter
18 from dual
19 connect by level <= (select max(length(text_line)-length(replace(text_line,chr(10))))+1 from mpr_text)
20 ) x
21 WHERE (x.iter <= LENGTH(text_line)-LENGTH(REPLACE(text_line,chr(10)))+1)
22 order by key1, cre_dat, line_no, num;
REF_CODE TYPE KEY1 KEY2 LINE_NO TEXT_LINE NUM CRE_DAT
---------- ----- -------- -------- ---------- ----------------------------------------------- ---------- ---------
O OP 123456 DFLT 1 MY TESTREMARK ON HEADER LINE 1 1 01-JAN-10
O OP 123456 DFLT 1 ANOTHER TESTREMARK ON HEADER LINE 2 2 01-JAN-10
O OP 123456 DFLT 2 AND ANOTHER TESTREMARK ON HEADER BUT A NEW LINE 1 15-JAN-10
O OP 123456 1 1 LINE 1 TESTREMARK 1 1 01-FEB-10
O OP 123456 1 1 ANOTHER LINE 1 TESTREMARK 2 2 01-FEB-10
O OP 123456 2 1 LINE 2 TESTREMARK 1 1 15-FEB-10
O OP 123456 2 1 ANOTHER LINE 2 TESTREMARK 2 2 15-FEB-10
O OP 123456 1 2 LINE 1 TESTREMARK 3 1 01-MAR-10
8 rows selected.
test@ORA11G>
test@ORA11G>