problems w/ external files

I tried this over in the 'misc' oracle group w/o result - I'm hoping somebody here might see something going on.

several problems with external tables (on a PC w/ Oracle 10g downloaded - I'm running Vista Home Premium - yes I know this isn' t exactly the right op sys but everything seems fine up til now - honest!):

when I create an external table and write a pl/sql anonymous block which has a

for i in (select * from <external_table> order by <col>) loop

and run it from sqlplus in a command window I get an immediate 29913 error which goes away when I take away the order by clause.
Can't I do order by's in external table queries?

Secondly, even running the resulting 'corrected' pl/sql I get another 29913 after it reads 377577 rows ok. Note that since it read 377k+ rows ok the definition of the external table is ok. (it's organized to use LOADER not DATA PUMP).

I have an error trap inside my cursor loop which identifies errors inside the cursor loop with the prefix
'in loop: ' to which I append sqlcode, ' ', and sqlerrm, and write (and commit) the result to
an errors table I've defined. The error I get is:

ORA-29913: error in executing callout

which does NOT have the 'in loop: ' prefix; it is apparently issued by my final error trap
outside the loop. My errors table DOES specify that it occurred at the 377578'th row read
since I keep a row counter.

You will note that even tho I allocate 2000 bytes to the error message
column it appears\somewhat truncated.

So howcum the error message blows out of the loop - I know it terminates the loop before the external table is done because
when I use the external table as fodder for sql*loader into a normal db table it loads a little over 377900 rows. Also I can tell because the
error message dumps the contents of the row that errorred out and it's clearly a row INSIDE the
table, not at the end. And is there some limit to the number of rows in an external table that
can be selected? Some initialization parameter? Some sqlloader parameter? I have 2 GB of
memory. And why is the darn error message being truncated?

I hope this provides enuf info for somebody to recognize what's going on.

Any help appreciated!

And why the heck did Google Groups do away with the preview which prevented these jagged lines for which I apologize!

Regards,

Ken Quirici Received on Mon Aug 09 2010 - 08:02:58 CDT