CREATE TABLE MPR_TEXT
(
 REF_CODE       VARCHAR2(30 CHAR)              NOT NULL,
 TYPE           VARCHAR2(30 CHAR)              NOT NULL,
 KEY1           VARCHAR2(30 CHAR)              NOT NULL,
 KEY2           VARCHAR2(30 CHAR)              NOT NULL,
 LINE_NO        NUMBER(10)                     NOT NULL,
 TEXT_LINE      VARCHAR2(2000 CHAR),
 CRE_USR        VARCHAR2(30 CHAR)              NOT NULL,
 CRE_DAT        DATE                           NOT NULL,
 MOD_USR        VARCHAR2(30 CHAR)              NOT NULL,
 MOD_DAT        DATE                           NOT NULL
)
TABLESPACE DATA
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
           INITIAL          16000K
           MINEXTENTS       1
           MAXEXTENTS       UNLIMITED
           PCTINCREASE      0
           BUFFER_POOL      DEFAULT
          )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

Insert into MPR_TEXT
  (REF_CODE, TYPE, KEY1, KEY2, LINE_NO, TEXT_LINE, CRE_USR, CRE_DAT, MOD_USR, MOD_DAT)
Values
  ('O', 'OP', '123456', 'DFLT', 1, 'MY TESTREMARK ON HEADER LINE 1
ANOTHER TESTREMARK ON HEADER LINE 2', 'ME', sysdate, 'ME', sysdate);
Insert into MPR_TEXT
  (REF_CODE, TYPE, KEY1, KEY2, LINE_NO, TEXT_LINE, CRE_USR, CRE_DAT, MOD_USR, MOD_DAT)
Values
  ('O', 'OP', '123456', 'DFLT', 2, 'AND ANOTHER TESTREMARK ON HEADER BUT A NEW LINE', 'ME', sysdate, 'ME', sysdate);
Insert into MPR_TEXT
  (REF_CODE, TYPE, KEY1, KEY2, LINE_NO, TEXT_LINE, CRE_USR, CRE_DAT, MOD_USR, MOD_DAT)
Values
  ('O', 'OP', '123456', '1', 1, 'LINE 1 TESTREMARK 1
ANOTHER LINE 1 TESTREMARK 2', 'ME', sysdate, 'ME', sysdate);
Insert into MPR_TEXT
  (REF_CODE, TYPE, KEY1, KEY2, LINE_NO, TEXT_LINE, CRE_USR, CRE_DAT, MOD_USR, MOD_DAT)
Values
  ('O', 'OP', '123456', '2', 1, 'LINE 2 TESTREMARK 1
ANOTHER LINE 2 TESTREMARK 2', 'ME', sysdate, 'ME', sysdate);
Insert into MPR_TEXT
  (REF_CODE, TYPE, KEY1, KEY2, LINE_NO, TEXT_LINE, CRE_USR, CRE_DAT, MOD_USR, MOD_DAT)
Values
  ('O', 'OP', '123456', '1', 2, 'LINE 1 TESTREMARK 3', 'ME', sysdate, 'ME', sysdate);

select ref_code,
          type,
           key1,
           key2,
           line_no,
           substr(text_line,
              case x.iter when 1 then 1 else instr(text_line,chr(10),1,x.iter-1)+1 end,
              case
                when instr(text_line,chr(10),1,x.iter) > 0
                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
                else length(text_line)
             end
            ) text_line,
          x.iter num,
          cre_dat
    from mpr_text t,
          (select level iter
             from dual
           connect by level <= (select max(length(text_line)-length(replace(text_line,chr(10))))+1 from mpr_text)
          ) x
   WHERE (x.iter <= LENGTH(text_line)-LENGTH(REPLACE(text_line,'+'))+1)
   order by key1, cre_dat, x.iter;

REF_CODE      TYPE  KEY1    KEY2   LINE_NO  TEXT_LINE                                                           NUM CRE_DAT
O             OP    123456  DFLT   1        MY TESTREMARK ON HEADER LINE 1                                      1     12.05.2010 16:10:17
O             OP    123456  DFLT   2        AND ANOTHER TESTREMARK ON HEADER BUT A NEW LINE                     1     12.05.2010 16:10:17
O             OP    123456  1      2        LINE 1 TESTREMARK 3                                                 1     12.05.2010 16:10:17
O             OP    123456  2      1        LINE 2 TESTREMARK 1                                                 1     12.05.2010 16:10:17
O             OP    123456  1      1        LINE 1 TESTREMARK 1                                                 1     12.05.2010 16:10:17