Re: problems w/ external files
From: John Hurley <hurleyjohnb_at_yahoo.com>
Date: Mon, 9 Aug 2010 06:18:16 -0700 (PDT)
Message-ID: <3e9fb403-27cd-45e2-a561-b576ab872749_at_l14g2000yql.googlegroups.com>
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
Date: Mon, 9 Aug 2010 06:18:16 -0700 (PDT)
Message-ID: <3e9fb403-27cd-45e2-a561-b576ab872749_at_l14g2000yql.googlegroups.com>
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 - 08:18:16 CDT