SQLLDR is the most probable tool for doing this. If you look in $ORACLE_HOME/rbms/demo, you'll find a couple of examples. One of them might look like:
LOAD DATA
INFILE *
INTO TABLE DEPT
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(DEPTNO, DNAME, LOC)
BEGINDATA
12,RESEARCH,"SARATOGA"
10,"ACCOUNTING",CLEVELAND
11,"ART",SALEM
13,FINANCE,"BOSTON"
21,"SALES",PHILA.
22,"SALES",ROCHESTER
42,"INT'L","SAN FRAN"
If you alter the terminated by ',' into terminated by ':', it'll load a : delimited file. Please see the server utilities manual:
</code>
http://docs.oracle.com/cd/A58617_01/server.804/a58244/toc.htm for complete details on sqlldr.
If you wish to do this in PLSQL, it is easy enough. Here is a sample routine that will load a delimited text file into a table. You must configure UTL_FILE for this to work correctly (see
http://docs.oracle.com/cd/A58617_01/server.804/a58241/ch8.htm#1939 <code>for information on setting that up).
drop table badlog;
create table badlog( errm varchar2(4000), data varchar2(4000) );
create or replace
function load_data( p_table in varchar2,
p_cnames in varchar2,
p_dir in varchar2,
p_filename in varchar2,
p_delimiter in varchar2 default '|' )
return number
is
l_input utl_file.file_type;
l_theCursor integer default dbms_sql.open_cursor;
l_buffer varchar2(4000);
l_lastLine varchar2(4000);
l_status integer;
l_colCnt number default 0;
l_cnt number default 0;
l_sep char(1) default NULL;
l_errmsg varchar2(4000);
begin
l_input := utl_file.fopen( p_dir, p_filename, 'r' );
l_buffer := 'insert into ' || p_table || ' values ( ';
l_colCnt := length(p_cnames)-
length(replace(p_cnames,',',''))+1;
for i in 1 .. l_colCnt
loop
l_buffer := l_buffer || l_sep || ':b'||i;
l_sep := ',';
end loop;
l_buffer := l_buffer || ')';
dbms_sql.parse( l_theCursor, l_buffer, dbms_sql.native );
loop
begin
utl_file.get_line( l_input, l_lastLine );
exception
when NO_DATA_FOUND then
exit;
end;
l_buffer := l_lastLine || p_delimiter;
for i in 1 .. l_colCnt
loop
dbms_sql.bind_variable( l_theCursor, ':b'||i,
substr( l_buffer, 1,
instr(l_buffer,p_delimiter)-1 ) ) ;
l_buffer := substr( l_buffer,
instr(l_buffer,p_delimiter)+1 );
end loop;
begin
l_status := dbms_sql.execute(l_theCursor);
l_cnt := l_cnt + 1;
exception
when others then
l_errmsg := sqlerrm;
insert into badlog ( errm, data )
values ( l_errmsg, l_lastLine );
end;
end loop;
dbms_sql.close_cursor(l_theCursor);
utl_file.fclose( l_input );
commit;
return l_cnt;
end load_data;
/
And here is an example using the above:
ops$tkyte@8i> create table t1 ( x int, y int, z int );
Table created.
ops$tkyte@8i> host echo 1,2,3 > /tmp/t1.dat
ops$tkyte@8i> host echo 4,5,6 >> /tmp/t1.dat
ops$tkyte@8i> host echo 7,8,9 >> /tmp/t1.dat
ops$tkyte@8i> host echo 7,NotANumber,9 >> /tmp/t1.dat
ops$tkyte@8i> begin
2 dbms_output.put_line(
3 load_data( 'T1',
4 'x,y,z',
5 '/tmp',
6 't1.dat',
7 ',' ) || ' rows loaded' );
8 end;
9 /
3 rows loaded
PL/SQL procedure successfully completed.
ops$tkyte@8i> select * from badlog;
ERRM DATA
------------------- -------------------
ORA-01722: invalid number 7,NotANumber,9
ops$tkyte@8i> select * from t1;
X Y Z
---------- ---------- ----------
1 2 3
4 5 6
7 8 9