desc emp

Name         Type
----         ----------
eid          number(5)
ename        varchar2(25)
dept_id      number(5)

desc dept

Name         Type
----         ----------
dept_id      number(5)
dept_name    varchar2(25)

SQL> create table t1 (
  2  eid          number(5),
  3  ename        varchar2(25),
  4  dept_id      number(5)
  5  )
  6  /

Table created.

SQL> create table t2 (
  2  dept_id      number(5),
  3  dept_name    varchar2(25)
  4  )
  5  /

Table created.

SQL> create table ext (
  2  eid          number(5),
  3  fill1        varchar2(25),
  4  ename        varchar2(25),
  5  dept_id      number(5),
  6  dept_name    varchar2(25),
  7  fill2        varchar2(10)
  8  )
  9  organization external (
 10    type oracle_loader
 11    default directory WORK_DIR
 12    access parameters (
 13      records delimited by X'0A'
 14      nobadfile
 15      nologfile
 16      nodiscardfile
 17      fields terminated by ','
 18      (eid, fill1, ename, dept_id, dept_name, fill2)
 19    )
 20    location ('t.txt')
 21  )
 22  reject limit unlimited
 23  /

Table created.

SQL> insert all 
  2    into t1 (eid, ename, dept_id) values (eid, trim(ename), dept_id)
  3    into t2 (dept_id, dept_name) values (dept_id, trim(dept_name))
  4  select * from ext
  5  /

4 rows created.

SQL> select * from t1;
       EID ENAME                        DEPT_ID
---------- ------------------------- ----------
       123 scott                              4
        45 john                              34

2 rows selected.

SQL> select * from t2;
   DEPT_ID DEPT_NAME
---------- -------------------------
         4 sales
        34 marketing

2 rows selected.

desc students

Name           Type
----           ----------
student_id     number(5)
student_name   varchar2(25)
subject_id     varchar2(5)
marks          number(3)

STUDENT_ID    STUDENT_NAME   SUBJECT_ID  MARKS
----------    ------------   ----------  ------
123           king           T1           89
123           king           T2           97
123           king           T3           67
4568          steven         T1           97
4568          steven         T4           83
4568          steven         T9           54

123, king, T1, 89, T2, 97, T3, 67
4568, steven, T1, 97, T4, 83, T9, 54

load data
infile flat_file.csv
into table staging
fields
(student_id         terminated by ',',
 student_name       terminated by ',',
 subjects_and_marks terminated by x'0a')

SCOTT@orcl_11g> CREATE TABLE staging
  2    (student_id	    number(5),
  3  	student_name	    varchar2(25),
  4  	subjects_and_marks  CLOB)
  5  /

Table created.

SCOTT@orcl_11g> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test.log

SCOTT@orcl_11g> COLUMN subjects_and_marks FORMAT A25 WORD_WRAPPED
SCOTT@orcl_11g> SELECT * FROM staging
  2  /

STUDENT_ID STUDENT_NAME              SUBJECTS_AND_MARKS
---------- ------------------------- -------------------------
       123  king                     T1, 89, T2, 97, T3, 67
      4568  steven                   T1, 97, T4, 83, T9, 54

SCOTT@orcl_11g> CREATE TABLE students
  2    (student_id     number(5),
  3  	student_name   varchar2(25),
  4  	subject_id     varchar2(5),
  5  	marks	       number(3))
  6  /

Table created.

SCOTT@orcl_11g> CREATE OR REPLACE FUNCTION list_element
  2  	(p_string    VARCHAR2,
  3  	 p_element   INTEGER,
  4  	 p_separator VARCHAR2 DEFAULT ',')
  5  	RETURN	     VARCHAR2
  6  AS
  7    v_string      VARCHAR2 (32767);
  8  BEGIN
  9    v_string := p_separator || p_string || p_separator;
 10    v_string := SUBSTR (v_string,
 11  			   INSTR (v_string, p_separator, 1, p_element)
 12  			   + LENGTH (p_separator));
 13    RETURN TRIM (SUBSTR (v_string, 1, INSTR (v_string, p_separator) - 1));
 14  END list_element;
 15  /

Function created.

SCOTT@orcl_11g> INSERT INTO students
  2  SELECT student_id, student_name,
  3  	    list_element (subjects_and_marks, rn1),
  4  	    list_element (subjects_and_marks, rn2)
  5  FROM   staging,
  6  	    (SELECT ((ROWNUM - 1) * 2) + 1 AS rn1,
  7  		    ((ROWNUM - 1) * 2) + 2 AS rn2
  8  	     FROM   DUAL
  9  	     CONNECT BY LEVEL <=
 10  		    (SELECT MAX (LENGTH (subjects_and_marks)
 11  				 - LENGTH (REPLACE (subjects_and_marks, ',', ''))) + 1
 12  		     FROM   staging))
 13  WHERE  list_element (subjects_and_marks, rn1) IS NOT NULL
 14  /

6 rows created.

SCOTT@orcl_11g> SELECT * FROM students ORDER BY student_id, subject_id
  2  /

STUDENT_ID STUDENT_NAME              SUBJE      MARKS
---------- ------------------------- ----- ----------
       123  king                     T1            89
       123  king                     T2            97
       123  king                     T3            67
      4568  steven                   T1            97
      4568  steven                   T4            83
      4568  steven                   T9            54

6 rows selected.

SCOTT@orcl_11g>