<TT>
----------------------------------------------------------------
-- function to extract specified columns from a line of text
----------------------------------------------------------------
create or replace function getcol(line         in varchar2,
                                  colnum       in number,
                                  delimiter    in char default ';',
                                  enclosed_by  in char default null,
                                  trim         in boolean default true,
                                  trimset      in varchar2 default chr(9) || chr(32) )
return varchar2
is
   retval   varchar2(2000);
   
   v_line   varchar2(2000);
   v_col    varchar2(2000);
   
   v_pos1   number(4);
   v_pos2   number(4);
begin
   -- make sure that the first and last character 
   -- of the line is a delimiter
   v_line := rtrim( line, delimiter ) || delimiter;
   v_line := delimiter || ltrim( v_line, delimiter );
    
   v_pos1 := instr( v_line, delimiter, 1, colnum );
   v_pos2 := instr( v_line, delimiter, 1, colnum + 1 );
   
   -- extract the column
   v_col  := substr( v_line, v_pos1 + 1, v_pos2 - (v_pos1 + 1) );
   
   -- delete the whitespace
   if trim = true then
      v_col := rtrim( v_col, trimset );
      v_col := ltrim( v_col, trimset );
   end if;
   
   -- delete the enclosing character
   if enclosed_by is not null then
      if substr(v_col,1,1) = enclosed_by 
            and substr(v_col,length(v_col),1) = enclosed_by
               and length(v_col) > 1         then
         v_col := substr( v_col, 1 + 1, length( v_col ) - (1 + 1) );
      end if;   
   end if;
   
   -- delete again the whitespace
   if trim = true then
      v_col := rtrim( v_col, trimset );
      v_col := ltrim( v_col, trimset );
   end if;

   return ( v_col );
end;
/
show errors
 
 
--------------------------------------------
-- plsql block for testing function getcol
--------------------------------------------
set serveroutput on
declare
   v_line varchar2(2000);
   v_col varchar2(2000);
begin
   v_line := 
   '7369,  "SMITH", "CLERK", 7902, "17-DEC-80", 800, , 20  ';
   
   -- empno
   v_col := getcol( v_line, 1, ',');
   dbms_output.put_line('col   : ' || v_col );
   dbms_output.put_line('length: ' || nvl(length(v_col),0) );
   
   -- ename
   v_col := getcol( v_line, 2, ',', '"' );
   dbms_output.put_line('col   : ' || v_col );
   dbms_output.put_line('length: ' || nvl(length(v_col),0) );
   
   -- job
   v_col := getcol( v_line, 3, ',', '"' );
   dbms_output.put_line('col   : ' || v_col );
   dbms_output.put_line('length: ' || nvl(length(v_col),0) );
   
   -- mgr
   v_col := getcol( v_line, 4, ',' );
   dbms_output.put_line('col   : ' || v_col );
   dbms_output.put_line('length: ' || nvl(length(v_col),0) );
   
   -- hiredate
   v_col := getcol( v_line, 5, ',', '"' );
   dbms_output.put_line('col   : ' || v_col );
   dbms_output.put_line('length: ' || nvl(length(v_col),0) );
   
   -- sal
   v_col := getcol( v_line, 6, ',');
   dbms_output.put_line('col   : ' || v_col );
   dbms_output.put_line('length: ' || nvl(length(v_col),0) );
   
   -- comm
   v_col := getcol( v_line, 7, ',');
   dbms_output.put_line('col   : ' || v_col );
   dbms_output.put_line('length: ' || nvl(length(v_col),0) );
   
   -- deptno
   v_col := getcol( v_line, 8, ',');
   dbms_output.put_line('col   : ' || v_col );
   dbms_output.put_line('length: ' || nvl(length(v_col),0) );
end;
/
   
  
----------------------------------
-- create test table for loading 
----------------------------------
drop table loadtest;
create table loadtest (
   empno    number(4),
   ename    varchar2(20),
   hiredate date,
   sal      number(18,2),
   comm      number(18,2)
);
  
  
------------------------------------------------------
-- contents from file "scott.txt"
------------------------------------------------------
7369,"SMITH","CLERK",7902,"17-DEC-80",800,,20
7499,"ALLEN","SALESMAN",7698,"20-FEB-81",1600,300,30
7521,"WARD","SALESMAN",7698,"22-FEB-81",1250,500,30
7566,"JONES","MANAGER",7839,"02-APR-81",2975,,20
7654,"MARTIN","SALESMAN",7698,"28-SEP-81",1250,1400,30
7698,"BLAKE","MANAGER",7839,"01-MAY-81",2850,,30
7782,"CLARK","MANAGER",7839,"09-JUN-81",2450,,10
7788,"SCOTT","ANALYST",7566,"19-APR-87",3000,,20
7839,"KING","PRESIDENT",,"17-NOV-81",5000,,10
7844,"TURNER","SALESMAN",7698,"08-SEP-81",1500,0,30
7876,"ADAMS","CLERK",7788,"23-MAY-87",1100,,20
7900,"JAMES","CLERK",7698,"03-DEC-81",950,,30
7902,"FORD","ANALYST",7566,"03-DEC-81",3000,,20
7934,"MILLER","CLERK",7782,"23-JAN-82",1300,,10
  
    
-----------------------------------------
-- contents from file scott.ctl
-----------------------------------------
load data
 
infile      'd:\temp\loader\scott.txt'
badfile     'd:\temp\loader\scott.bad'
discardfile 'd:\temp\loader\scott.dsc'
 
replace
 
into table loadtest
(
   empno      CHAR(2000)   "getcol( :empno, 1, \',\' )",
   ename      CHAR(2000)   "getcol( :empno, 2, \',\', \'\"\' )",
   hiredate   CHAR(2000)   "to_date( getcol( :empno, 5, \',\', \'\"\' ), \'DD-MON-YY\' )",
   sal        CHAR(2000)   "to_number( getcol( :empno, 6, \',\' ) )",
   comm       CHAR(2000)   "to_number( getcol( :empno, 7, \',\' ) )"
)
</TT>