Home » SQL & PL/SQL » SQL & PL/SQL » Multiple record insert using PLSQL (11.0.2.10)
Multiple record insert using PLSQL [message #665861] Wed, 27 September 2017 02:50 Go to next message
OraFerro
Messages: 315
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 Go to previous message
cookiemonster
Messages: 12957
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.
Previous Topic: sub totals in sql
Next Topic: Cuimulative totals
Goto Forum:
  


Current Time: Wed Dec 13 13:06:49 CST 2017

Total time taken to generate the page: 0.01876 seconds