tell me, how is my answer:

Using an external procedure (the ability to write a
stored procedure in C) or by using a java stored procedure, we can run external
commands like this.

materially different from their answer:

You can use an external procedure (Oracle Version 8.0.5 and higher) to call sqlloader from a stored procedure in plsql

????

OR

skip the triggers and UTL_FILE stuff and go straight into the real tables? If I had to pre-process the data, I would either:

o use sqlldr to go straight into the REAL tables from the get go if possible

o write some C code to process and array insert the data into the REAL tables in the first place

o direct path load the data into a scratch table and using a stored procedure that employeed BULK COLLECTS and FORALL inserts -- process the data

how many records are we talking about at a time here and how often?

and what does the file format look like?

what about something like this:

create or replace directory test as '/home/tkyte'
/

drop table t;

create table t ( a number, b number, c varchar2(30), d varchar2(30), e varchar2(30), f clob);

declare
l_bfile bfile;
l_size number;
l_last number := 1;
l_current number;
l_clob clob;
l_rec t%rowtype;
l_comma raw(1) := utl_raw.cast_to_raw( ',' );
begin
l_bfile := bfilename( 'TEST', 'x.dat' );
dbms_lob.fileopen( l_bfile );
l_size := dbms_lob.getlength( l_bfile );


loop
dbms_application_info.set_client_info( l_last || ' of ' || l_size );

l_current := dbms_lob.instr( l_bfile, l_comma, l_last, 1 );
exit when (nvl(l_current,0) = 0);
l_rec.a := utl_raw.cast_to_varchar2(dbms_lob.substr( l_bfile, l_current-l_last, l_last ));
l_last := l_current+1;

l_current := dbms_lob.instr( l_bfile, l_comma, l_last, 1 );
exit when (nvl(l_current,0) = 0);
l_rec.b := utl_raw.cast_to_varchar2(dbms_lob.substr( l_bfile, l_current-l_last, l_last ));
l_last := l_current+1;

l_current := dbms_lob.instr( l_bfile, l_comma, l_last, 1 );
exit when (nvl(l_current,0) = 0);
l_rec.c := utl_raw.cast_to_varchar2(dbms_lob.substr( l_bfile, l_current-l_last, l_last ));
l_last := l_current+1;

l_current := dbms_lob.instr( l_bfile, l_comma, l_last, 1 );
exit when (nvl(l_current,0) = 0);
l_rec.d := utl_raw.cast_to_varchar2(dbms_lob.substr( l_bfile, l_current-l_last, l_last ));
l_last := l_current+1;

l_current := dbms_lob.instr( l_bfile, l_comma, l_last, 1 );
exit when (nvl(l_current,0) = 0);
l_rec.e := utl_raw.cast_to_varchar2(dbms_lob.substr( l_bfile, l_current-l_last, l_last ));
l_last := l_current+1;

insert into t (a,b,c,d,e,f) values (l_rec.a, l_rec.b, l_rec.c, l_rec.d, l_rec.e, empty_clob() )
returning f into l_rec.f;

l_current := dbms_lob.instr( l_bfile, '0A', l_last, 1 );
exit when (nvl(l_current,0) = 0);
dbms_lob.loadFromFile( l_rec.f, l_bfile, l_current-l_last+1, 1, l_last );
l_last := l_current+1;
end loop;
end;
/


to avoid the issue alltogether?

... The only thing that I changed
from your script....

you changed it.

so, give us, just like I give you, the COMPLETE example. show us YOUR variation and the 12 lines that don't work.


(better yet - if you are like me and are paid to write code for money.... what about debugging it? I cannot be the only one left that can do that can I?)

not sure what you are asking here

but I would strongly encourage you to use an external table, then you do not need sqlldr at all.