Prev: Triggers
Next: needed privileges for mat views
From: Ken Quirici on 7 Aug 2010 21:49
Hi,

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
 | 
Pages: 1
Prev: Triggers
Next: needed privileges for mat views