Re: problems w/ external files

Thanks for your reply!

When I go into sqlplus at the command prompt it says

10g Enterprise edition - release 10.2.0.3.8.

Not sure what that means relative to your question.

Regards,

Ken Quirici

On Aug 9, 9:18 am, John Hurley <hurleyjo..._at_yahoo.com> wrote:
> On Aug 9, 9:02 am, Ken Quirici <kquir..._at_yahoo.com> wrote:
>
>
>
> > Hi,
>
> > 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
>
> What version of Oracle exactly are you running?
>
> If it is the base 10g then it is missing a whole bunch of 10g
> maintenance.  Unless you have a support contract and patch it up you
> may be running into problems that are already fixed.
Received on Mon Aug 09 2010 - 12:03:53 CDT