Multiple record insert using PLSQL [message #665861] |
Wed, 27 September 2017 02:50 |
|
OraFerro
Messages: 433 Registered: July 2011
|
Senior Member |
|
|
Dear All,
In the following example, is the SP "JOBS_ADD_ROWS" called once or as many times as the size of the passed array ""?
Note: the purpose of this post is to compare the below technique of multiple row insert to the one discussed previously in post:
http://www.orafaq.com/forum/m/662710/#msg_662710
PROCEDURE JOBS_ADD_ROWS
(
I_JOB_ID IN varchar2,
I_JOB_TITLE IN varchar2,
I_MIN_SALARY IN NUMBER,
I_MAX_SALARY IN Number
)
AS
new_sal number;
BEGIN
if I_MAX_SALARY = 16000 then
new_sal := I_MAX_SALARY + 500;
else
new_sal := I_MAX_SALARY + 1;
end if;
INSERT INTO JOBS VALUES
(
I_JOB_ID ,
I_JOB_TITLE ,
I_MIN_SALARY,
new_sal
);
END;
--> .net code
Protected Sub btnInsert_Click(sender As Object, e As EventArgs) Handles btnInsert.Click
cn = New OracleConnection(System.Configuration.ConfigurationManager.ConnectionStrings("OraConnectionString").ConnectionString)
cn.Open()
cmd = New OracleCommand
cmd.Connection = cn
cmd.CommandText = "JOBS_ADD_ROWS"
cmd.CommandType = CommandType.StoredProcedure
paramOracle = New Oracle.DataAccess.Client.OracleParameter
paramOracle.ParameterName = "JOB_ID"
paramOracle.DbType = DbType.String
Dim Arr1() As String = {"IT_DBA", "IT_MAN", "IT_VP"}
paramOracle.Value = Arr1
paramOracle.Direction = ParameterDirection.Input
cmd.Parameters.Add(paramOracle)
paramOracle = New Oracle.DataAccess.Client.OracleParameter
paramOracle.ParameterName = "JOB_TITLE"
paramOracle.DbType = DbType.String
Dim Arr2() As String = {"Database Administrator", "IT Manager", "IT Vice President"}
paramOracle.Value = Arr2
paramOracle.Direction = ParameterDirection.Input
cmd.Parameters.Add(paramOracle)
paramOracle = New Oracle.DataAccess.Client.OracleParameter
paramOracle.ParameterName = "MIN_SALARY"
paramOracle.DbType = DbType.Int32
Dim Arr3() As Integer = {8000, 12000, 18000}
paramOracle.Value = Arr3
paramOracle.Direction = ParameterDirection.Input
cmd.Parameters.Add(paramOracle)
paramOracle = New Oracle.DataAccess.Client.OracleParameter
paramOracle.ParameterName = "MAX_SALARY"
paramOracle.DbType = DbType.Int32
Dim Arr4() As Integer = {16000, 24000, 35000}
paramOracle.Value = Arr4
paramOracle.Direction = ParameterDirection.Input
cmd.Parameters.Add(paramOracle)
cmd.ArrayBindCount = Arr1.Length
cmd.ExecuteNonQuery()
End Sub
Regards,
Ferro
[Updated on: Wed, 27 September 2017 02:52] Report message to a moderator
|
|
|
Re: Multiple record insert using PLSQL [message #665863 is a reply to message #665861] |
Wed, 27 September 2017 03:27 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
While I don't understand .net code it should be obvious that since the stored proc doesn't take array parameters it'd have to be called as many times as the size of the .net array if you wanted it to insert all the records.
|
|
|