A programmer colleague of mine asked if he could submit a batch of
insert statements to Oracle (9i) using C#. And if there's any way to
identify which insert statement, if any failed during the batch
execution.

The idea is to improve performance by preventing client/server back
and forths, e.g. better to submit 1,000 insert statements as a batch/
block, have the server execute the statements in one go, then send
back a single response than to have 1,000 round-trips for each insert
(rolling back if there's an error).

Can this be done in C# without any PL/SQL? If so, how? The programmer
is using this mechanism to communicate with Oracle:

http://msdn.microsoft.com/en-us/libr...cleclient.aspx

Although you can get a transaction defined using the above, then loop
through your inserts, I don't think it's submitting everything to the
server as a block. One final note: we can't use any solution requiring
stored procedures on the server side; the admins won't permit it and
this is a rule we can't change.

Thanks.

Dana

On Jan 28, 10:19*am, dana_at_w... (AT) yahoo (DOT) com wrote:
The best way to do this is probably to determine how to set up a SQL
statement with bind variables in C#. In such a setup, there would be
one parse call, and then repeated execution calls with new bind
variable values. I do not have a C# example which sets up bind
variables, but here is a VB.Net example which performs a SELECT using
bind variables, which should be similar:
Dim comData As New OracleClient.OracleCommand
Dim snpData As OracleClient.OracleDataReader
Dim strSQL As String = ""
Dim dteStartDate As Date = CDate(cboStartDate.Text)
Dim dteEndDate As Date = CDate(cboEndDate.Text)

strSQL = "SELECT /*+ ORDERED */" & vbCrLf
strSQL = strSQL & " SR.LOGICAL_GROUP," & vbCrLf
strSQL = strSQL & " SR.ID RESOURCE_ID," & vbCrLf
strSQL = strSQL & " SUM(LT.HOURS) HOURS," & vbCrLf
strSQL = strSQL & " MIN(LT.DAY24) FIRST_DAY," & vbCrLf
strSQL = strSQL & " MAX(LT.DAY24) LAST_DAY" & vbCrLf
strSQL = strSQL & "FROM" & vbCrLf
strSQL = strSQL & "(SELECT" & vbCrLf
strSQL = strSQL & " LT.RESOURCE_ID," & vbCrLf
strSQL = strSQL & " LT.DAY24," & vbCrLf
strSQL = strSQL & " ROUND(COUNT(DISTINCT C2.COUNTER)/10)
HOURS" & vbCrLf
strSQL = strSQL & "FROM" & vbCrLf
strSQL = strSQL & " (SELECT /*+ INDEX(LT,IND_LT_SHIFT_DATE)
*/" & vbCrLf
strSQL = strSQL & " LT.RESOURCE_ID," & vbCrLf
strSQL = strSQL & " (TO_DATE( :StartDate1,'DD-MON-YYYY')
+C1.COUNTER) DAY24," & vbCrLf
strSQL = strSQL & " DECODE(SIGN(LT.CLOCK_IN-(TO_DATE
( :StartDate2,'DD-MON-YYYY')+C1.COUNTER)),-1,0,TRUNC((LT.CLOCK_IN-TRUNC
(LT.CLOCK_IN))*240)) CI_PERIOD," & vbCrLf
strSQL = strSQL & " DECODE(SIGN(LT.CLOCK_OUT-(TO_DATE
( :StartDate3,'DD-MON-YYYY')+C1.COUNTER)),1,239,0,239,TRUNC
((LT.CLOCK_OUT-TRUNC(LT.CLOCK_OUT))*240)) CO_PERIOD" & vbCrLf
strSQL = strSQL & " FROM" & vbCrLf
strSQL = strSQL & " (SELECT" & vbCrLf
strSQL = strSQL & " ROWNUM-1 COUNTER" & vbCrLf
strSQL = strSQL & " FROM" & vbCrLf
strSQL = strSQL & " PART" & vbCrLf
strSQL = strSQL & " WHERE" & vbCrLf
strSQL = strSQL & " ROWNUM<= :NumberDays) C1," & vbCrLf
strSQL = strSQL & " LABOR_TICKET LT," & vbCrLf
strSQL = strSQL & " SHOP_RESOURCE SR" & vbCrLf
strSQL = strSQL & " WHERE" & vbCrLf
strSQL = strSQL & " LT.SHIFT_DATE BETWEEN (TO_DATE
( :StartDate4,'DD-MON-YYYY')+C1.COUNTER)-1 AND (TO_DATE
( :StartDate5,'DD-MON-YYYY')+C1.COUNTER)" & vbCrLf
strSQL = strSQL & " AND SUBSTR(LT.EMPLOYEE_ID,4,1)<>'0'" &
vbCrLf
strSQL = strSQL & " AND LT.CLOCK_OUT>(TO_DATE
( :StartDate6,'DD-MON-YYYY')+C1.COUNTER)" & vbCrLf
strSQL = strSQL & " AND LT.RESOURCE_ID IS NOT NULL" &
vbCrLf
strSQL = strSQL & " AND NVL(SR.ACTIVE_RESOURCE,'Y')='Y'" &
vbCrLf
strSQL = strSQL & " AND SR.LOGICAL_GROUP IS NOT NULL" &
vbCrLf
strSQL = strSQL & " AND LT.RESOURCE_ID=SR.ID) LT," & vbCrLf
strSQL = strSQL & " (SELECT" & vbCrLf
strSQL = strSQL & " ROWNUM-1 COUNTER" & vbCrLf
strSQL = strSQL & " FROM" & vbCrLf
strSQL = strSQL & " PART" & vbCrLf
strSQL = strSQL & " WHERE" & vbCrLf
strSQL = strSQL & " ROWNUM<=240) C2" & vbCrLf
strSQL = strSQL & "WHERE" & vbCrLf
strSQL = strSQL & " C2.COUNTER BETWEEN CI_PERIOD AND
CO_PERIOD" & vbCrLf
strSQL = strSQL & "GROUP BY" & vbCrLf
strSQL = strSQL & " LT.RESOURCE_ID," & vbCrLf
strSQL = strSQL & " LT.DAY24) LT," & vbCrLf
strSQL = strSQL & " SHOP_RESOURCE SR" & vbCrLf
strSQL = strSQL & "WHERE" & vbCrLf
strSQL = strSQL & " SR.LOGICAL_GROUP IS NOT NULL" & vbCrLf
strSQL = strSQL & " AND NVL(SR.ACTIVE_RESOURCE,'Y')='Y'" &
vbCrLf
strSQL = strSQL & " AND SUBSTR(SR.LOGICAL_GROUP,3,1)=' '" &
vbCrLf
strSQL = strSQL & " AND SR.ID=LT.RESOURCE_ID(+)" & vbCrLf
strSQL = strSQL & "GROUP BY" & vbCrLf
strSQL = strSQL & " SR.LOGICAL_GROUP," & vbCrLf
strSQL = strSQL & " SR.ID" & vbCrLf
strSQL = strSQL & "ORDER BY" & vbCrLf
strSQL = strSQL & " SR.LOGICAL_GROUP," & vbCrLf
strSQL = strSQL & " SR.ID"

With comData
'.Connection = dbVMFG
.CommandText = strSQL
.CommandType = CommandType.Text

.Parameters.AddWithValue("StartDate1", Format(CDate
(cboStartDate.Text), "dd-MMM-yyyy"))
.Parameters.AddWithValue("StartDate2", Format(CDate
(cboStartDate.Text), "dd-MMM-yyyy"))
.Parameters.AddWithValue("StartDate3", Format(CDate
(cboStartDate.Text), "dd-MMM-yyyy"))
.Parameters.AddWithValue("NumberDays", CInt
(dteEndDate.Subtract(dteStartDate).Days) + 1)
.Parameters.AddWithValue("StartDate4", Format(CDate
(cboStartDate.Text), "dd-MMM-yyyy"))
.Parameters.AddWithValue("StartDate5", Format(CDate
(cboStartDate.Text), "dd-MMM-yyyy"))
.Parameters.AddWithValue("StartDate6", Format(CDate
(cboStartDate.Text), "dd-MMM-yyyy"))
End With

snpData = comData.ExecuteReader()

DataGridView1.Rows.Clear()
Do While snpData.Read()
DataGridView1.Rows.Add(snpData("logical_group"), snpData
("resource_id"), snpData("hours"))
Loop

snpData.Close()

snpData = Nothing
comData.Dispose()

---

I wonder if an anonymous PL/SQL block using a FORALL could be
submitted to the database to send all of the data to the server in one
pass?
http://www.oracle.com/technology/sam...ll/readme.html
http://download.oracle.com/docs/cd/B..._statement.htm

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

dana_at_work (AT) yahoo (DOT) com wrote:
: A programmer colleague of mine asked if he could submit a batch of
: insert statements to Oracle (9i) using C#. And if there's any way to
: identify which insert statement, if any failed during the batch
: execution.

: The idea is to improve performance by preventing client/server back
: and forths, e.g. better to submit 1,000 insert statements as a batch/
: block, have the server execute the statements in one go, then send
: back a single response than to have 1,000 round-trips for each insert
: (rolling back if there's an error).

I would expect you could send an anonymous block. That would be sent as a
single statement and send back a single response.

sqlString =
"declare
linenumber number:=1;
begin
insert into t values ('a','b');
linenumber :=1+linenumber;
insert into t values ('A','B');
-- etc.
exception
raise_application_error(-20000,'line='||linenumber||sqlerrm);
end ;
"
( the final ";" may not be wanted)

That would be allowed in MS Access (as a pass-thru query), so I assume c#
could allow that.

"Charles Hooper" <hooperc2000 (AT) yahoo (DOT) com> wrote

On Jan 28, 10:19 am, dana_at_w... (AT) yahoo (DOT) com wrote:
The best way to do this is probably to determine how to set up a SQL
statement with bind variables in C#. In such a setup, there would be
one parse call, and then repeated execution calls with new bind
variable values. I do not have a C# example which sets up bind
variables, but here is a VB.Net example which performs a SELECT using
bind variables, which should be similar:
Dim comData As New OracleClient.OracleCommand
Dim snpData As OracleClient.OracleDataReader
Dim strSQL As String = ""
Dim dteStartDate As Date = CDate(cboStartDate.Text)
Dim dteEndDate As Date = CDate(cboEndDate.Text)

strSQL = "SELECT /*+ ORDERED */" & vbCrLf
strSQL = strSQL & " SR.LOGICAL_GROUP," & vbCrLf
strSQL = strSQL & " SR.ID RESOURCE_ID," & vbCrLf
strSQL = strSQL & " SUM(LT.HOURS) HOURS," & vbCrLf
strSQL = strSQL & " MIN(LT.DAY24) FIRST_DAY," & vbCrLf
strSQL = strSQL & " MAX(LT.DAY24) LAST_DAY" & vbCrLf
strSQL = strSQL & "FROM" & vbCrLf
strSQL = strSQL & "(SELECT" & vbCrLf
strSQL = strSQL & " LT.RESOURCE_ID," & vbCrLf
strSQL = strSQL & " LT.DAY24," & vbCrLf
strSQL = strSQL & " ROUND(COUNT(DISTINCT C2.COUNTER)/10)
HOURS" & vbCrLf
strSQL = strSQL & "FROM" & vbCrLf
strSQL = strSQL & " (SELECT /*+ INDEX(LT,IND_LT_SHIFT_DATE)
*/" & vbCrLf
strSQL = strSQL & " LT.RESOURCE_ID," & vbCrLf
strSQL = strSQL & " (TO_DATE( :StartDate1,'DD-MON-YYYY')
+C1.COUNTER) DAY24," & vbCrLf
strSQL = strSQL & " DECODE(SIGN(LT.CLOCK_IN-(TO_DATE
( :StartDate2,'DD-MON-YYYY')+C1.COUNTER)),-1,0,TRUNC((LT.CLOCK_IN-TRUNC
(LT.CLOCK_IN))*240)) CI_PERIOD," & vbCrLf
strSQL = strSQL & " DECODE(SIGN(LT.CLOCK_OUT-(TO_DATE
( :StartDate3,'DD-MON-YYYY')+C1.COUNTER)),1,239,0,239,TRUNC
((LT.CLOCK_OUT-TRUNC(LT.CLOCK_OUT))*240)) CO_PERIOD" & vbCrLf
strSQL = strSQL & " FROM" & vbCrLf
strSQL = strSQL & " (SELECT" & vbCrLf
strSQL = strSQL & " ROWNUM-1 COUNTER" & vbCrLf
strSQL = strSQL & " FROM" & vbCrLf
strSQL = strSQL & " PART" & vbCrLf
strSQL = strSQL & " WHERE" & vbCrLf
strSQL = strSQL & " ROWNUM<= :NumberDays) C1," & vbCrLf
strSQL = strSQL & " LABOR_TICKET LT," & vbCrLf
strSQL = strSQL & " SHOP_RESOURCE SR" & vbCrLf
strSQL = strSQL & " WHERE" & vbCrLf
strSQL = strSQL & " LT.SHIFT_DATE BETWEEN (TO_DATE
( :StartDate4,'DD-MON-YYYY')+C1.COUNTER)-1 AND (TO_DATE
( :StartDate5,'DD-MON-YYYY')+C1.COUNTER)" & vbCrLf
strSQL = strSQL & " AND SUBSTR(LT.EMPLOYEE_ID,4,1)<>'0'" &
vbCrLf
strSQL = strSQL & " AND LT.CLOCK_OUT>(TO_DATE
( :StartDate6,'DD-MON-YYYY')+C1.COUNTER)" & vbCrLf
strSQL = strSQL & " AND LT.RESOURCE_ID IS NOT NULL" &
vbCrLf
strSQL = strSQL & " AND NVL(SR.ACTIVE_RESOURCE,'Y')='Y'" &
vbCrLf
strSQL = strSQL & " AND SR.LOGICAL_GROUP IS NOT NULL" &
vbCrLf
strSQL = strSQL & " AND LT.RESOURCE_ID=SR.ID) LT," & vbCrLf
strSQL = strSQL & " (SELECT" & vbCrLf
strSQL = strSQL & " ROWNUM-1 COUNTER" & vbCrLf
strSQL = strSQL & " FROM" & vbCrLf
strSQL = strSQL & " PART" & vbCrLf
strSQL = strSQL & " WHERE" & vbCrLf
strSQL = strSQL & " ROWNUM<=240) C2" & vbCrLf
strSQL = strSQL & "WHERE" & vbCrLf
strSQL = strSQL & " C2.COUNTER BETWEEN CI_PERIOD AND
CO_PERIOD" & vbCrLf
strSQL = strSQL & "GROUP BY" & vbCrLf
strSQL = strSQL & " LT.RESOURCE_ID," & vbCrLf
strSQL = strSQL & " LT.DAY24) LT," & vbCrLf
strSQL = strSQL & " SHOP_RESOURCE SR" & vbCrLf
strSQL = strSQL & "WHERE" & vbCrLf
strSQL = strSQL & " SR.LOGICAL_GROUP IS NOT NULL" & vbCrLf
strSQL = strSQL & " AND NVL(SR.ACTIVE_RESOURCE,'Y')='Y'" &
vbCrLf
strSQL = strSQL & " AND SUBSTR(SR.LOGICAL_GROUP,3,1)=' '" &
vbCrLf
strSQL = strSQL & " AND SR.ID=LT.RESOURCE_ID(+)" & vbCrLf
strSQL = strSQL & "GROUP BY" & vbCrLf
strSQL = strSQL & " SR.LOGICAL_GROUP," & vbCrLf
strSQL = strSQL & " SR.ID" & vbCrLf
strSQL = strSQL & "ORDER BY" & vbCrLf
strSQL = strSQL & " SR.LOGICAL_GROUP," & vbCrLf
strSQL = strSQL & " SR.ID"

With comData
'.Connection = dbVMFG
.CommandText = strSQL
.CommandType = CommandType.Text

.Parameters.AddWithValue("StartDate1", Format(CDate
(cboStartDate.Text), "dd-MMM-yyyy"))
.Parameters.AddWithValue("StartDate2", Format(CDate
(cboStartDate.Text), "dd-MMM-yyyy"))
.Parameters.AddWithValue("StartDate3", Format(CDate
(cboStartDate.Text), "dd-MMM-yyyy"))
.Parameters.AddWithValue("NumberDays", CInt
(dteEndDate.Subtract(dteStartDate).Days) + 1)
.Parameters.AddWithValue("StartDate4", Format(CDate
(cboStartDate.Text), "dd-MMM-yyyy"))
.Parameters.AddWithValue("StartDate5", Format(CDate
(cboStartDate.Text), "dd-MMM-yyyy"))
.Parameters.AddWithValue("StartDate6", Format(CDate
(cboStartDate.Text), "dd-MMM-yyyy"))
End With

snpData = comData.ExecuteReader()

DataGridView1.Rows.Clear()
Do While snpData.Read()
DataGridView1.Rows.Add(snpData("logical_group"), snpData
("resource_id"), snpData("hours"))
Loop

snpData.Close()

snpData = Nothing
comData.Dispose()

---

I wonder if an anonymous PL/SQL block using a FORALL could be
submitted to the database to send all of the data to the server in one
pass?
http://www.oracle.com/technology/sam...ll/readme.html
http://download.oracle.com/docs/cd/B..._statement.htm

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

Your best bet is to do as Charles says with bind variables. This will be
most performant. (by a wide margin) If C# supports binding arrays then you
can do that and send 1 statement with array of 100 as bind values. (Oracle
has had an array interface for a long time). This is not using pl/sql. The
Oracle OCI, ole objects, and their c++ interface have this (the array
interface) so I am assuming c# must also. I would do a search on
otn.oracle.com and see what you find. But bind variables are the way to go.
(and yes with array processing you would know what row didn't get processed
if any)
Jim