Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> OCI: How to determine faulty record when using OCI_ATTR_PREFETCH_ROWS > 1

OCI: How to determine faulty record when using OCI_ATTR_PREFETCH_ROWS > 1

From: André Hartmann <andrehartmann_at_hotmail.com>
Date: Thu, 19 Feb 2004 13:21:21 +0100
Message-ID: <4034aa41$1@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:21:21 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US