Re: How to determine faulty record when using array bind with length > 1

oops

  sorry... forget the prefetch thing... its got nothing to do with that. i am using array bind... not prefetch! sorry about that!

"André Hartmann" <andrehartmann_at_hotmail.com> schrieb im Newsbeitrag news:4034aa41$1_at_olaf.komtel.net...
> Hi there,
>
> I am using OCI to access my Oracle database. My job is to import the
> contents of a (large) txt file line by line into an Oracle database.
> Currently I am using OCI_ATTR_PREFETCH_ROWS=1, i.e. I prepare an insert
> statement, then open my file and for each line that I read i bind my
> variables and execute the statement. after the end of the file I close my
> statement.
>
> Now I want to boost my performance by using array-bind, i.e. i bind say
> 100 values for each variable in the statement (i.e. 100 lines from the
file)
> at the same time and execute it. Tests have shown in my environment that
> when i go from 1 --> 100 (array length) i can insert 40times more records
in
> a given time eriod than before, when i go to array length = 10,000 its
even
> 90times more! so its worth it...
>
> ... however here is my problem: until now my import program reports the
> lines of the file that cause problems and cannot be imported. it says
like:
>
> line 17: ORA-XXXXX
> line 229: ORA-YYYYY
> line 11588: ORA-ZZZZZ
>
> and so on. That allows the user to identify questionable lines and mend
> them.
>
> Now when I bind 100 lines at the same time and execute my statement and
it
> fails... how can i determine which line caused the problem ? I still want
to
> produce the above protocol, is there any means of telling which of the
array
> entries was processed successfully last and what was the first faulty one
> that caused the ORA-AAAAA problem ?
>
> Thanks,
>
> André
> :)
>
>
Received on Thu Feb 19 2004 - 06:25:52 CST