Limit to No of External Tables

[talgentratom]

I'm made a bit of development that uses a collection of external tables to import data. There are a series of normal tables that have three external tables to go with it, new, change and delete. Each day files are read in and these external tables are created, and the data in them is either added, modified or deleted from the master table depending on the table. This was all working fine till I tried running in with lots of files at once. I found that if I have 100 or less files it works fine. Otherwise when it is processing all the data is dies on the 101st and gives this error:

ORA-01410: invalid ROWID

Is there a limit to the number of external tables you can have in a database? This doesn't seem to be a space problem, and unix shows there being a fair bit of space left. The tablespaces are set to autoextend.

Any help would be greatly apprieciated.

[slimdave]

I would turn on SQL tracing and see the exact statement that is failing .. maybe it's a recursive statement.

[talgentratom]

Found the solution to this now. It turns out the cursor was built upon a table that truncated on commit. During this loop the location of external tables were altered issuing an automatic commit, which cleared the table thus killing the cursor. I guess the cursor caches 100 records at a time, or something along those lines.