Prev: Patch set 10.2.0.5 re-release
Next: some questions about 11gR2
From: Ken Quirici on 9 Aug 2010 09:02
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
From: John Hurley on 9 Aug 2010 09:18
On Aug 9, 9:02 am, Ken Quirici <kquir...(a)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.
From: Ken Quirici on 9 Aug 2010 13:03
Hi John,

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...(a)yahoo.com> wrote:
> On Aug 9, 9:02 am, Ken Quirici <kquir...(a)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.

 | 
Pages: 1
Prev: Patch set 10.2.0.5 re-release
Next: some questions about 11gR2