I'll get you started with the concept
a) external table to access the report as SQL
SQL> CREATE TABLE rep
2 (
3 LINE varchar2(1000),
4 student varchar2(180),
5 result varchar2(20)
6 )
7 ORGANIZATION external
8 (
9 TYPE oracle_loader
10 DEFAULT DIRECTORY temp
11 ACCESS PARAMETERS
12 (
13 RECORDS DELIMITED BY NEWLINE
14 READSIZE 1048576
15 FIELDS LDRTRIM
16 MISSING FIELD VALUES ARE NULL
17 REJECT ROWS WITH ALL NULL FIELDS
18 (
19 line (1:1000) char(1000),
20 student (9:200) char(180),
21 result (9:200) char(20)
22 )
23 )
24 location
25 (
26 'report.txt'
27 )
28 )REJECT LIMIT UNLIMITED
29 /
Table created.
SQL> select line from rep;
LINE
---------------------------------------------------------------
10th standard results
declared on 05 aug 2017
Student-Ajay Kumar Roll No-123456 College-XYZ School
Seq Sub Marks
1 Sub1 90
2 Sub2 87
3 Sub3 NA
4 Sub4 NA
Result: retake exam
Student-Vijay Kumar Roll No-123457 College-ABC School
Seq Sub Marks
1 Sub1 90 out of 100
2 Sub2 87 out of 100
3 Sub3 70
4 Sub5 89
Result: Passed
Student-Manoj Kumar Roll No-123458 College-PQR School
Seq Sub Marks
1 SubX 40
2 SubY 50
3 SubZ 10
Result: Failed
Notice that fields can have overlap - thats fine with fixed width definition
b) use file row and last_value to group the student rows
SQL> with t as (
2 select rownum r, r.* from rep r
3 )
4 select
5 last_value(case when line like 'Student%' then r end ignore nulls) over ( order by r ) student_row,
6 substr(student,1,instr(student,'Roll No')-1) student_name,
7 case when line like 'Result%' then result end result
8 from t
9 /
STUDENT_ROW STUDENT_NAME RESULT
----------- ------------------------------------------------------------ --------------------
3 Ajay Kumar
3
3
3
3
3
3 retake exam
10 Vijay Kumar
10
10
10
10
10
10 Passed
17 Manoj Kumar
17
17
17
17
17 Failed
22 rows selected.
<code>
c) Grouping to bring the rows together
<code>
SQL> with t as (
2 select rownum r, r.* from rep r
3 ),
4 extracted_fields as (
5 select
6 last_value(case when line like 'Student%' then r end ignore nulls) over ( order by r ) student_row,
7 substr(student,1,instr(student,'Roll No')-1) student_name,
8 case when line like 'Result%' then result end result
9 from t
10 )
11 select student_row, max(student_name) student_name, max(result) result
12 from extracted_fields
13 group by student_row
14 order by 1;
STUDENT_ROW STUDENT_NAME RESULT
----------- ------------------------------------------------------------ --------------------
3 Ajay Kumar retake exam
10 Vijay Kumar Passed
17 Manoj Kumar Failed
So you can expand this to pick out whatever fields you like