SCOTT@10gXE> CREATE OR REPLACE DIRECTORY my_dir2 AS 'c:\oracle2'
  2  /

Directory created.

SCOTT@10gXE> CREATE TABLE ext_tab
  2    (a VARCHAR2 (10),
  3  	b VARCHAR2 (10),
  4  	c VARCHAR2 (10),
  5  	d VARCHAR2 (10))
  6  ORGANIZATION external
  7    (TYPE oracle_loader
  8  	DEFAULT DIRECTORY my_dir2
  9  	ACCESS PARAMETERS
 10  	  (RECORDS DELIMITED BY '~'
 11  	   LOGFILE 'test.log'
 12  	   FIELDS LDRTRIM
 13  	   MISSING FIELD VALUES ARE NULL
 14  	   REJECT ROWS WITH ALL NULL FIELDS
 15  	     (filler1 CHAR(255) TERMINATED BY WHITESPACE,
 16  	      a       CHAR(255) TERMINATED BY "B ",
 17  	      b       CHAR(255) TERMINATED BY "C ",
 18  	      c       CHAR(255) TERMINATED BY "D ",
 19  	      d       CHAR(255) TERMINATED BY "~ "))
 20    location ('test.dat'))
 21  REJECT LIMIT UNLIMITED
 22  /

Table created.

SCOTT@10gXE> SELECT * FROM ext_tab
  2  /

A          B          C          D
---------- ---------- ---------- ----------
A1 A2      B1 B2      C1 C2      D1 D2
A3         B3         C3         D3
A4 A5 A6   B4 B5 B6   C4 C5 C6   D4 D5 D6

3 rows selected.

SCOTT@10gXE> CREATE TABLE test_tab
  2    (a VARCHAR2 (2),
  3  	b VARCHAR2 (2),
  4  	c VARCHAR2 (2),
  5  	d VARCHAR2 (2))
  6  /

Table created.

SCOTT@10gXE> 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_string || p_separator;
 10    FOR i IN 1 .. p_element - 1 LOOP
 11  	 v_string := SUBSTR (v_string,
 12  			     INSTR (v_string, p_separator)
 13  			       + LENGTH (p_separator));
 14    END LOOP;
 15    RETURN SUBSTR (v_string, 1, INSTR (v_string, p_separator) - 1);
 16  END list_element;
 17  /

Function created.

SCOTT@10gXE> INSERT INTO test_tab (a, b, c, d)
  2  SELECT list_element (a, rn),
  3  	    list_element (b, rn),
  4  	    list_element (c, rn),
  5  	    list_element (d, rn)
  6  FROM   ext_tab,
  7  	    (SELECT ROWNUM AS rn
  8  	     FROM   DUAL
  9  	     CONNECT BY LEVEL <=
 10  		    (SELECT  MAX (LENGTH (a)
 11  				    - LENGTH (REPLACE (a, ' ', '')))
 12  		     FROM    ext_tab))
 13  WHERE  list_element (a, rn) IS NOT NULL
 14  /

6 rows created.

SCOTT@10gXE> SELECT * FROM test_tab ORDER BY a
  2  /

A  B  C  D
-- -- -- --
A1 B1 C1 D1
A2 B2 C2 D2
A3 B3 C3 D3
A4 B4 C4 D4
A5 B5 C5 D5
A6 B6 C6 D6

6 rows selected.

SCOTT@10gXE>