Make trace files with large sql statements available to developers

Hi All,  

I am working on a sql script to make trace files (10046 traces) available to developers through sqlplus (mixed sun/win environment, oracle 9.2.0.5). I have found several options of which the one from Jared Still
(http://www.dbazine.com/oracle/or-articles/still1) was the most elegant
(what's in a name) in my opinion.

However (there always is one) I have trace files that contain sql statements that are more than 4000 characters in length (statements are generated by Oracle Warehouse Builder). And external tables cannot be defined using LOB's or LONG's.
One workaround is the approach from asktom http://asktom.oracle.com/pls/ask/f?p=4950:8:6858521250456814988::NO::F4950_P 8_DISPLAYID,F4950_P8_CRITERIA:1112673450743 where the file is loaded into a table and then selecting from that table. To solve the problem of statements of more than 4000 characters I would have to cut up the long line into parts before inserting and, after selecting, glue them together again. But I do not find that very elegant :-). Does anybody have better ideas than I have at the moment?  

I stripped the external table approach of Jared for some testing. I have tried to attach a relatively small trace file to this message, also for testing. No luck uploading it.  

create or replace directory udump
as '/opt/oracle/admin/NLD2/udump';

create table nld1_ora_13989 ( text varchar2(4000) ) organization external (
type oracle_loader
default directory UDUMP
access parameters (
records delimited by newline
nobadfile
nodiscardfile
nologfile
 )
location('nld2_ora_13989.trc')
) reject limit unlimited
;

select *
from nld1_ora_13989;

drop directory udump;
drop table nld1_ora_13989;

Kind regards,
Eric Buddelmeijer
DBA
Elegant Application Services
P.J. Oudweg 15
1314 CH Almere
tel. +31 (36) 547 7105
gsm +31 (6) 5356 0396
e-mail Eric.Buddelmeijer_at_elegant.nl
web www.elegant.nl

This e-mail and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this e-mail in error, please delete the message and notify us. This e-mail message has been swept by MIMEsweeper for the presence of computer viruses         

.

--
http://www.freelists.org/webpage/oracle-l