Coding for Stored Procedures is a lot of work! - TopLink/JPA

Will simple Stored Procedure calls be made any easier in TopLink? Below is the JDBC code for calling a stored procedure. Following it is the lengthy TopLink code for calling the same proc.
JDBC:
CallableStatement stmt = conn.prepareCall("{CALL JEROME_TEST.EMP_SELECT(?,?,?)}");
stmt.setInt(1, 7369);
stmt.registerOutParameter(2, Types.VARCHAR);
stmt.setString(2, "Get");
stmt.registerOutParameter(3, Types.VARCHAR);
       
stmt.execute();
System.out.println("job = " + stmt.getString(2));
System.out.println("name = " + stmt.getString(3));TopLink
StoredProcedureCall call = new StoredProcedureCall();
call.setProcedureName("JEROME_TEST.EMP_SELECT");
call.addUnamedArgument("empno");
call.addUnamedInOutputArgument("job", "job", String.class);
call.addUnamedOutputArgument("name");
       
DataModifyQuery query = new DataModifyQuery();
query.setCall(call);
query.addArgument("empno");
query.addArgument("job");
query.addArgument("name");
m_session.addQuery("JEROME_TEST.EMP_SELECT", query);
m_session.getEventManager().addListener(
        new SessionEventAdapter() {
            public void outputParametersDetected(SessionEvent event) {
                if (((StoredProcedureCall)event.getProperty("call")).
                    getProcedureName().equals("JEROME_TEST.EMP_SELECT")){
                           
                    DatabaseRow row = (DatabaseRow)event.getResult();
                    Object returnJob = row.get("job");
                    Object returnName = row.get("name");
                    System.out.println("job = " + returnJob);
                    System.out.println("name = " + returnName);
                }
            }
        });
Vector parameters = new Vector();
parameters.addElement(new java.lang.Integer(7369));
parameters.addElement("Get");
parameters.addElement("");
Object data = m_session.executeQuery(query, parameters); 

Much of the code you have defined is for being able to reuse the call. If you just want to execute it you can use:
StoredProcedureCall call = new StoredProcedureCall();
call.setProcedureName("JEROME_TEST.EMP_SELECT");
call.addUnamedArgumentValue(new Integer(7369));
call.addUnamedInOutputArgumentValue("job", "Get", String.class);
call.addUnamedOutputArgument("name");
Map row = (Map) session.executeSelectingCall(call).first();
System.out.println("job = " + row.get("job"));
System.out.println("name = " + row.get("name"));

Related

.NET Performance Problem

Hi,
I am running into an issue where a procedure is being called and a simple insert is happening on a test table with no constraints. The developer is wrapping it in one call but is doing 12,000 inserts. He is getting about 160 inserts per second but seems to expect 6-700 based on what SQL Server does. The problem is he is using ODP.NET but seems to be using a lot of the built in .NET dll's. Each insert is for one row. I know about Forall, but this makes it difficult since he is just doing one row via the stored proc. Does anyone have any suggestions? Would using the Oracle supplied dll's work better?
As you can see it is a standard proc. I am just trying to help him find a better way to tune the .NET to Oracle interface for better throughput, if you think I am missing something, please let me know! Thanks : )
Here is the code.
PROCEDURE "FI_ACCOUNT_INSERT_FILEUPLOAD"
(p_ACCOUNT_NUMBER IN FILEIMPORT_ACCOUNT.ACCOUNT_NUMBER%TYPE,
p_CORP IN FILEIMPORT_ACCOUNT.CORP%TYPE, p_MAINBANK IN FILEIMPORT_ACCOUNT.MAINBANK%TYPE,
p_ASSOCIATION IN FILEIMPORT_ACCOUNT.ASSOCIATION%TYPE, p_CONTRACT_NUMBER IN FILEIMPORT_ACCOUNT.CONTRACT_NUMBER%TYPE,
p_HOST_SYSTEM_ID IN FILEIMPORT_ACCOUNT.HOST_SYSTEM_ID%TYPE, p_INSTITUTION_ID IN FILEIMPORT_ACCOUNT.INSTITUTION_ID%TYPE,
p_SEED_PARTICIPATION IN FILEIMPORT_ACCOUNT.SEED_PARTICIPATION%TYPE,
p_CAMS_PARTICIPATION IN FILEIMPORT_ACCOUNT.CAMS_PARTICIPATION%TYPE,
p_ACTIVITY_ID IN ACTIVITY_ACCOUNT.ACTIVITY_ID%TYPE) IS
 
  BEGIN
 
            INSERT INTO     FILEIMPORT_ACCOUNT(
                         ACCOUNT_NUMBER,
                         CORP,
                         MAINBANK,
                         ASSOCIATION,
                         CONTRACT_NUMBER,
                         HOST_SYSTEM_ID,
                         INSTITUTION_ID,
                         SEED_PARTICIPATION,
                         CAMS_PARTICIPATION,
                         ACTIVITY_ID)
          VALUES          (p_ACCOUNT_NUMBER,
                         p_CORP,
                         p_MAINBANK,
                         p_ASSOCIATION,
                         p_CONTRACT_NUMBER,
                         p_HOST_SYSTEM_ID,
                         p_INSTITUTION_ID,
                         p_SEED_PARTICIPATION,
                         p_CAMS_PARTICIPATION,
                         p_ACTIVITY_ID);
 
  END "FI_ACCOUNT_INSERT_FILEUPLOAD"; 
Hi Tom,
What do you mean by "The developer is wrapping it in one call... "?
Did you already try array binding as suggested in your other post?
Here's an example..
Re: Oracle insert speed (92k records)
I tested it with the following procedure instead of a direct insert, and I insert 60,000 records in about 5 seconds.
create or replace procedure ins_proc (
c0 number,c1 varchar2, c2 varchar2, c3 varchar2,
c4 varchar2,c5 varchar2,c6 varchar2,c7 varchar2,
c8 varchar2,c9 varchar2) as
begin
insert into bulkttab values(c0,c1,c2,c3,c4,c5,c6,c7,c8,c9);
end;
On the other hand, executing 60000 ExecuteNonQuery's completes in about 22 seconds on my system.
Hope it helps,
Greg 
I looked at your examples but they do not detail what to do with a stored proc or maybe I am not seeing it. Can you do array binding and have it pass the information to a stored proc? Can you show me an example.
This is what I see:
OracleCommand command = new OracleCommand("insert into bulkttab values(:0,:1,:2,:3,:4,:5,:6,:7,:8,:9)", connection); 
gdarling, could you shoot me an email at troach#gmail.com?
I have some code that I don't want to be made available on a public forum, but I think I found my problem. If not, then I can always mask some stuff.
Once again, thanks!
Just as an FYI, they said they are using the Oracle dll's but this is what I see and it doesn't look like ODP.net
(Again, I am not the developer)
This is what he has for his test case and SQL Server does run in about half the time. I think Oracle can beat it or match it with the Oracle supplied dll. When I run it, it takes 17 second.
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using Microsoft.Practices.EnterpriseLibrary.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Oracle;
using System.Diagnostics;
using System.Data.OracleClient;
using System.Data.Common;
namespace OracleSQLSpeed
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        int activityID = 415;
        int loopCount = 5000;
        string acctNumber = "1234567891123456";
        string corp = "000000";
        string mainbank = "000000000";
        string instID = "000000";
        int seeds = 1;
        int cams = 1;
        string systemID = "S";
       
        private void Form1_Load(object sender, EventArgs e)
        {
            NumLoops.Text = loopCount.ToString();
        }
        public void SetLoopCount()
        {
            if (int.TryParse(NumLoops.Text,out loopCount))
                loopCount = int.Parse(NumLoops.Text);
            else
               loopCount = 5000;
           if (loopCount <= 1)
               loopCount = 1;
           if (loopCount > 50000)
               loopCount = 50000;
            NumLoops.Text = loopCount.ToString();
           
           
        }
       
        private void ButtonSQLDelete_Click(object sender, EventArgs e)
        {
            Database db;
            db = DatabaseFactory.CreateDatabase("BBX");
            DbCommand acctCommand = db.GetStoredProcCommand("spFILEIMPORT_ACCOUNT_DELETE");
            db.AddInParameter(acctCommand, "ACTIVITY_ID", DbType.Int64, activityID);
           
            db.ExecuteNonQuery(acctCommand);
           
            this.LabelTime.Text = string.Empty;
        }
        private void ButtonOracleDelete_Click(object sender, EventArgs e)
        {
            Database db;
            db = DatabaseFactory.CreateDatabase("CAMS");
            DbCommand acctCommand = db.GetStoredProcCommand("FILEIMPORT_ACCOUNT_PKG.FI_ACCOUNT_DELETE_ACTIVITYID");
            db.AddInParameter(acctCommand, "p_ACTIVITY_ID", DbType.Int64, activityID);
           
            db.ExecuteNonQuery(acctCommand);
            this.LabelTime.Text = string.Empty;
        }
private void ButtonOracleLoad_Click(object sender, EventArgs e)
{
Database db;
db = DatabaseFactory.CreateDatabase("CAMS");
DateTime endTime;
SetLoopCount();
DateTime startTime = DateTime.Now;
for(int i=1; i <= loopCount; i++)
{
DbCommand acctCommand = db.GetStoredProcCommand("FILEIMPORT_ACCOUNT_PKG.FI_ACCOUNT_INSERT_FILEUPLOAD");
db.AddInParameter(acctCommand, "P_ACCOUNT_NUMBER", DbType.String, acctNumber);
db.AddInParameter(acctCommand, "P_CORP", DbType.String, i.ToString().PadLeft(6,char.Parse("0")));
db.AddInParameter(acctCommand, "P_MAINBANK", DbType.String, mainbank);
db.AddInParameter(acctCommand, "P_ASSOCIATION", DbType.String, System.DBNull.Value);
db.AddInParameter(acctCommand, "P_CONTRACT_NUMBER", DbType.Int64, System.DBNull.Value);
db.AddInParameter(acctCommand, "P_HOST_SYSTEM_ID", DbType.String, systemID);
db.AddInParameter(acctCommand, "p_INSTITUTION_ID", DbType.String, i.ToString().PadRight(6,char.Parse("0")));
db.AddInParameter(acctCommand, "p_SEED_PARTICIPATION", DbType.Int32, seeds);
db.AddInParameter(acctCommand, "p_CAMS_PARTICIPATION", DbType.Int32, cams);
db.AddInParameter(acctCommand, "p_ACTIVITY_ID", DbType.Int64, activityID);
db.ExecuteNonQuery(acctCommand);
}
endTime = DateTime.Now;
TimeSpan processingTime = endTime.Subtract(startTime);
LabelTime.Text = loopCount.ToString() + " Records Processed In: " + processingTime.Minutes.ToString() + " Minutes " + processingTime.Seconds.ToString() + " Seconds";
}
        private void ButtonSQLLoad_Click(object sender, EventArgs e)
        {
            Database db;
            db = DatabaseFactory.CreateDatabase("BBX");
            DateTime endTime;
            SetLoopCount();
            DateTime startTime = DateTime.Now;
           
            for (int i = 1; i <= loopCount; i++)
            {
                DbCommand acctCommand = db.GetStoredProcCommand("spFILEIMPORT_ACCOUNT_INSERT");
                db.AddInParameter(acctCommand, "ACCOUNT_NUMBER", DbType.String, acctNumber);
                db.AddInParameter(acctCommand, "CORP", DbType.String, i.ToString().PadLeft(6, char.Parse("0")));
                db.AddInParameter(acctCommand, "MAINBANK", DbType.String, mainbank);
                db.AddInParameter(acctCommand, "ASSOCIATION", DbType.String, System.DBNull.Value);
                db.AddInParameter(acctCommand, "CONTRACT_NUMBER", DbType.Int64, System.DBNull.Value);
                db.AddInParameter(acctCommand, "HOST_SYSTEM_ID", DbType.String, systemID);
                db.AddInParameter(acctCommand, "INSTITUTION_ID", DbType.String, i.ToString().PadRight(6, char.Parse("0")));
                db.AddInParameter(acctCommand, "SEED_PARTICIPATION", DbType.Int32, seeds);
                db.AddInParameter(acctCommand, "CAMS_PARTICIPATION", DbType.Int32, cams);
                db.AddInParameter(acctCommand, "ACTIVITY_ID", DbType.Int64, activityID);
                db.ExecuteNonQuery(acctCommand);
            }
            endTime = DateTime.Now;
            TimeSpan processingTime = endTime.Subtract(startTime);
            LabelTime.Text = loopCount.ToString() + " Records Processed In: " + processingTime.Minutes.ToString() + " Minutes " + processingTime.Seconds.ToString() + " Seconds";
        }
    }
} 
Sorry for jumping in... long sample and possible rant to follow.
I see something in this code that I think deserves some attention: transaction management.
While the sample uses the Microsoft provider for Oracle (System.DataAccess.OracleClient), the behavior is the same as the Oracle provider (ODP.NET) in this case. The OracleCommand.ExecuteNonQuery method is "followed by" an implicit commit. The provider operates, by default, in auto-commit mode. In a situation like this, there are two negative consequences:
1) Time to process is increased
2) Redo generation is increased
Neither of these are desirable.
Here is a fully worked sample using the "scott" sample user to illustrate this:
Database objects:
CREATE TABLE PERF_TEST
(
  ACCOUNT_NUMBER     NUMBER(8),
  CORP               VARCHAR2(32),
  MAINBANK           VARCHAR2(32),
  ASSOCIATION        VARCHAR2(32),
  CONTRACT_NUMBER    NUMBER(8),
  HOST_SYSTEM_ID     VARCHAR2(32),
  INSTITUTION_ID     VARCHAR2(32),
  SEED_PARTICIPATION NUMBER(8),
  CAMS_PARTICIPATION NUMBER(8),
  ACTIVITY_ID        NUMBER(8)
);
CREATE OR REPLACE PACKAGE PERF_TEST_PKG AS
  PROCEDURE DO_INSERT_TEST(P_ACCOUNT_NUMBER IN NUMBER,
                           P_CORP IN VARCHAR2,
                           P_MAINBANK IN VARCHAR2,
                           P_ASSOCIATION IN VARCHAR2,
                           P_CONTRACT_NUMBER IN NUMBER,
                           P_HOST_SYSTEM_ID IN VARCHAR2,
                           P_INSTITUTION_ID IN VARCHAR2,
                           P_SEED_PARTICIPATION IN NUMBER,
                           P_CAMS_PARTICIPATION IN NUMBER,
                           P_ACTIVITY_ID IN NUMBER);
END;
/
CREATE OR REPLACE PACKAGE BODY PERF_TEST_PKG AS
  PROCEDURE DO_INSERT_TEST(P_ACCOUNT_NUMBER IN NUMBER,
                           P_CORP IN VARCHAR2,
                           P_MAINBANK IN VARCHAR2,
                           P_ASSOCIATION IN VARCHAR2,
                           P_CONTRACT_NUMBER IN NUMBER,
                           P_HOST_SYSTEM_ID IN VARCHAR2,
                           P_INSTITUTION_ID IN VARCHAR2,
                           P_SEED_PARTICIPATION IN NUMBER,
                           P_CAMS_PARTICIPATION IN NUMBER,
                           P_ACTIVITY_ID IN NUMBER) IS
  BEGIN
    INSERT INTO PERF_TEST (ACCOUNT_NUMBER,
                           CORP,
                           MAINBANK,
                           ASSOCIATION,
                           CONTRACT_NUMBER,
                           HOST_SYSTEM_ID,
                           INSTITUTION_ID,
                           SEED_PARTICIPATION,
                           CAMS_PARTICIPATION,
                           ACTIVITY_ID)
    VALUES(P_ACCOUNT_NUMBER,
           P_CORP,
           P_MAINBANK,
           P_ASSOCIATION,
           P_CONTRACT_NUMBER,
           P_HOST_SYSTEM_ID,
           P_INSTITUTION_ID,
           P_SEED_PARTICIPATION,
           P_CAMS_PARTICIPATION,
           P_ACTIVITY_ID);
  END;
END;
/C#:
using System;
using System.Data;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
namespace PerformanceTest
{
  class Program
  {
    private const string constr = "User Id=scott; Password=tiger; Data Source=otndemo; Pooling=false; Enlist=false";
   
    static void Main(string[] args)
    {
      // call the multiple commit sample (default auto-commit mode)
      MultiCommit();
      // call the single commit sample (explicit transaction)
      SingleCommit();
      // prevent command window from closing automatically when running from visual studio
      Console.WriteLine();
      Console.WriteLine("ENTER to continue...");
      Console.ReadLine();
    }
    public static void MultiCommit()
    {
      // used to track execution duration
      DateTime timeStart;
      DateTime timeEnd;
      double totalSeconds;
      // create and open connection
      OracleConnection con = new OracleConnection(constr);
      con.Open();
      // create command object
      OracleCommand cmd = con.CreateCommand();
      cmd.CommandText = "PERF_TEST_PKG.DO_INSERT_TEST";
      cmd.CommandType = CommandType.StoredProcedure;
      // capture test start time
      timeStart = DateTime.Now;
      // do the loop test thing
      for (int i = 1; i <= 5000; i++)
      {
        // add each parameter
        cmd.Parameters.Add("P_ACCOUNT_NUMBER", OracleDbType.Decimal, i, ParameterDirection.Input);
        cmd.Parameters.Add("P_CORP", OracleDbType.Varchar2, i.ToString().PadLeft(6, char.Parse("0")), ParameterDirection.Input);
        cmd.Parameters.Add("P_MAINBANK", OracleDbType.Varchar2, "Main Bank: " + i.ToString(), ParameterDirection.Input);
        cmd.Parameters.Add("P_ASSOCIATION", OracleDbType.Varchar2, DBNull.Value, ParameterDirection.Input);
        cmd.Parameters.Add("P_CONTRACT_NUMBER", OracleDbType.Decimal, DBNull.Value, ParameterDirection.Input);
        cmd.Parameters.Add("P_HOST_SYSTEM_ID", OracleDbType.Varchar2, "Host System ID: " + i.ToString(), ParameterDirection.Input);
        cmd.Parameters.Add("P_INSTITUTION_ID", OracleDbType.Varchar2, i.ToString().PadRight(6, char.Parse("0")), ParameterDirection.Input);
        cmd.Parameters.Add("P_SEED_PARTICIPATION", OracleDbType.Decimal, i, ParameterDirection.Input);
        cmd.Parameters.Add("P_CAMS_PARTICIPATION", OracleDbType.Decimal, i, ParameterDirection.Input);
        cmd.Parameters.Add("P_ACTIVITY_ID", OracleDbType.Decimal, i, ParameterDirection.Input);
        // call the stored procedure
        cmd.ExecuteNonQuery();
        // remove parameters for next iteration
        cmd.Parameters.Clear();
      }
      // capture test end time
      timeEnd = DateTime.Now;
      // calculate total seconds for this test
      totalSeconds = timeEnd.Subtract(timeStart).TotalSeconds;
      // display time used for test
      Console.WriteLine(" Multi commit: {0,7:##.000} total seconds.\n", totalSeconds);
      // clean up
      con.Dispose();
      cmd.Dispose();
    }
    public static void SingleCommit()
    {
      // used to track execution duration
      DateTime timeStart;
      DateTime timeEnd;
      double totalSeconds;
      // create and open connection
      OracleConnection con = new OracleConnection(constr);
      con.Open();
      // create command object
      OracleCommand cmd = con.CreateCommand();
      cmd.CommandText = "PERF_TEST_PKG.DO_INSERT_TEST";
      cmd.CommandType = CommandType.StoredProcedure;
      // capture test start time
      timeStart = DateTime.Now;
      // create an explicit transaction
      // this transaction is the only difference
      // between this and the MultiCommit
      OracleTransaction txn = con.BeginTransaction();
      // do the loop test thing
      for (int i = 1; i <= 5000; i++)
      {
        // add each parameter
        cmd.Parameters.Add("P_ACCOUNT_NUMBER", OracleDbType.Decimal, i, ParameterDirection.Input);
        cmd.Parameters.Add("P_CORP", OracleDbType.Varchar2, i.ToString().PadLeft(6, char.Parse("0")), ParameterDirection.Input);
        cmd.Parameters.Add("P_MAINBANK", OracleDbType.Varchar2, "Main Bank: " + i.ToString(), ParameterDirection.Input);
        cmd.Parameters.Add("P_ASSOCIATION", OracleDbType.Varchar2, DBNull.Value, ParameterDirection.Input);
        cmd.Parameters.Add("P_CONTRACT_NUMBER", OracleDbType.Decimal, DBNull.Value, ParameterDirection.Input);
        cmd.Parameters.Add("P_HOST_SYSTEM_ID", OracleDbType.Varchar2, "Host System ID: " + i.ToString(), ParameterDirection.Input);
        cmd.Parameters.Add("P_INSTITUTION_ID", OracleDbType.Varchar2, i.ToString().PadRight(6, char.Parse("0")), ParameterDirection.Input);
        cmd.Parameters.Add("P_SEED_PARTICIPATION", OracleDbType.Decimal, i, ParameterDirection.Input);
        cmd.Parameters.Add("P_CAMS_PARTICIPATION", OracleDbType.Decimal, i, ParameterDirection.Input);
        cmd.Parameters.Add("P_ACTIVITY_ID", OracleDbType.Decimal, i, ParameterDirection.Input);
        // call the stored procedure
        cmd.ExecuteNonQuery();
        // remove parameters for next iteration
        cmd.Parameters.Clear();
      }
      // only commit at the end of the transaction
      txn.Commit();
      // capture test end time
      timeEnd = DateTime.Now;
      // calculate total seconds for this test
      totalSeconds = timeEnd.Subtract(timeStart).TotalSeconds;
      // display time used for test
      Console.WriteLine("Single commit: {0,7:##.000} total seconds.\n", totalSeconds);
      // clean up
      con.Dispose();
      cmd.Dispose();
    }
  }
}The only difference between the methods above is that the MultiCommit issues a commit after each call to "cmd.ExecuteNonQuery();" (i.e. 5,000 commits in this sample) whereas the SingleCommit method uses an explicit transaction and a single commit after calling "cmd.ExecuteNonQuery();".
What sort of difference can this have in regard to performance? A test run on my system:
Multi commit:  11.578 total seconds.
Single commit:   2.094 total seconds.
ENTER to continue...I would say it would be worth investigating over-commiting as a possible issue in the application.
- Mark 
Thank you Mark! This will be a HUGE help.
The redo and 5000 commits makes perfect sense.
Also, if gdarling still has an example on the array binding and if that will work also. 
Darn it Mark! you beat me to the punch! I should have posted this code yesterday instead of waiting until today to 'clean it up' before posting! :)
The other thing to note, Tom, is that it seems your code code continually creates a new command and parameters each time, instead of just re-using them. While it doesnt seem to make a large difference in execution, it does make a difference.
Anyway, here's the complete code I was working, and the results, including array binding. (Note that array binding is specific to Oracle, so you couldnt use Sytem.Data.OracleClieint with it, or generic factory code (without casting it to ODP classes).
Cheers,
Greg
RESULTS
=========
inserting 10000 records
new command and params, without txn
elapsed: 00:00:16.0156250
new command and params, with txn
elapsed: 00:00:05.1875000
reuse command and params, without txn
elapsed: 00:00:13.6250000
reuse command and params, with txn
elapsed: 00:00:03.8593750
arraybinding
elapsed: 00:00:00.7187500
SQL
===========
create table bulkttab(col0 number,col1 varchar2(4000), col2 varchar2(4000), col3 varchar2(4000),
col4 varchar2(4000),col5 varchar2(4000),col6 varchar2(4000),col7 varchar2(4000),
col8 varchar2(4000),col9 varchar2(4000));
create or replace procedure ins_proc (
c0 number,c1 varchar2, c2 varchar2, c3 varchar2,
c4 varchar2,c5 varchar2,c6 varchar2,c7 varchar2,
c8 varchar2,c9 varchar2) as
begin
insert into bulkttab values(c0,c1,c2,c3,c4,c5,c6,c7,c8,c9);
end;
CODE
==========
using System;
using System.Data;
using Oracle.DataAccess.Client;
using System.Data.Common;
class ArrayBind
{
static int loopCount = 10000;
static string somestringval = "abcdefghijklmnopqrstuvwxyz";
static void Main(string[] args)
{
Console.WriteLine("inserting {0} records", loopCount);
Console.WriteLine("\nnew command and params, without txn");
newparams(false);
Console.WriteLine("\nnew command and params, with txn");
newparams(true);
Console.WriteLine("\nreuse command and params, without txn");
reuse(false);
Console.WriteLine("\nreuse command and params, with txn");
reuse(true);
Console.WriteLine("\narraybinding");
arraybind();
}
private static void newparams(bool useTxn)
{
DbProviderFactory myfactory =
DbProviderFactories.GetFactory("Oracle.DataAccess.Client");
using (DbConnection conn = myfactory.CreateConnection())
{
conn.ConnectionString = "user id=scott;password=tiger;data source=orcl;";
conn.Open();
DateTime endTime;
DateTime startTime = DateTime.Now;
DbTransaction txn = null;
if (useTxn)
txn = conn.BeginTransaction();
for (int i = 1; i <= loopCount; i++)
{
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = "ins_proc";
cmd.CommandType = CommandType.StoredProcedure;
//cmd.CommandText = "insert into bulkttab values(:0,:1,:2,:3,:4,:5,:6,:7,:8,:9)";
cmd.Connection = conn;
DbParameter p1 = cmd.CreateParameter();
p1.DbType = DbType.Int32;
p1.Value = 1;
cmd.Parameters.Add(p1);
for (int j = 1; j < 10; j++)
{
DbParameter p = cmd.CreateParameter();
p.DbType = DbType.String;
p.Value = somestringval;
cmd.Parameters.Add(p);
}
cmd.ExecuteNonQuery();
}
if (useTxn)
txn.Commit();
endTime = DateTime.Now;
Console.WriteLine("elapsed: {0}", endTime - startTime);
}
}
private static void reuse(bool useTxn)
{
DbProviderFactory myfactory =
DbProviderFactories.GetFactory("Oracle.DataAccess.Client");
using (DbConnection conn = myfactory.CreateConnection())
{
conn.ConnectionString = "user id=scott;password=tiger;data source=orcl;";
conn.Open();
DateTime endTime;
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = "ins_proc";
cmd.CommandType = CommandType.StoredProcedure;
//cmd.CommandText="insert into bulkttab values(:0,:1,:2,:3,:4,:5,:6,:7,:8,:9)";
cmd.Connection = conn;
DbParameter p1 = cmd.CreateParameter();
p1.DbType = DbType.Int32;
p1.Value = 1;
cmd.Parameters.Add(p1);
for (int j = 1; j < 10; j++)
{
DbParameter p = cmd.CreateParameter();
p.DbType = DbType.String;
p.Value = somestringval;
cmd.Parameters.Add(p);
}
DateTime startTime = DateTime.Now;
DbTransaction txn = null;
if (useTxn)
txn = conn.BeginTransaction();
for (int i = 1; i <= loopCount; i++)
{
// reuse the parameters and command
cmd.Parameters[0].Value = i;
cmd.Parameters[1].Value = somestringval;
// ... etc..
}
if (useTxn)
txn.Commit();
endTime = DateTime.Now;
Console.WriteLine("elapsed: {0}", endTime - startTime);
}
}
private static void arraybind()
{
string connectStr = "User Id=scott;Password=tiger;Data Source=orcl";
int[] myArrayofNums = new int[loopCount];
string[] myArrayofV2s = new string[loopCount];
for (int i = 0; i < loopCount; i++)
{
myArrayofNums[i] = i;
myArrayofV2s[i] = somestringval;
}
OracleConnection connection = new OracleConnection(connectStr);
//OracleCommand command = new OracleCommand("insert into bulkttab values(:0,:1,:2,:3,:4,:5,:6,:7,:8,:9)", connection);
OracleCommand command = new OracleCommand("ins_proc", connection);
command.CommandType = CommandType.StoredProcedure;
command.ArrayBindCount = loopCount;
OracleParameter numParam = new OracleParameter("param2", OracleDbType.Int32);
numParam.Direction = ParameterDirection.Input;
numParam.Value = myArrayofNums;
command.Parameters.Add(numParam);
for (int i = 1; i < 10; i++)
{
OracleParameter v2param = new OracleParameter("", OracleDbType.Varchar2);
v2param.Direction = ParameterDirection.Input;
v2param.Value = myArrayofV2s;
command.Parameters.Add(v2param);
}
connection.Open();
DateTime start = DateTime.Now;
OracleTransaction tx = connection.BeginTransaction();
command.ExecuteNonQuery();
tx.Commit();
DateTime stop = DateTime.Now;
Console.WriteLine("elapsed: {0}", (stop - start));
connection.Close();
command.Dispose();
connection.Dispose();
}
} 
This is a message that I found that pertains to this, but it was back on November 12, 2003.
Sendings typed Arrays (ArrayBindCount) with DBNull's in the middle 
... 
Mark, gdarling,
We are having trouble with array binding. Passing in an integer array with null values. Here is where we are stuck. If you have any ideas please let me know. We could do the object but if we do, we have the boxing and unboxing problem and that slows down performance. The other alternative is just to pass in a string and do the conversion on the Oracle side (turning a char into a number or date).
private void ButtonOracleDriverBinding_Click(object sender, EventArgs e)
        {
            Oracle.DataAccess.Client.OracleConnectionStringBuilder builder = new Oracle.DataAccess.Client.OracleConnectionStringBuilder();
            builder.DataSource = "TESTDB";
            builder.UserID = "user";
            builder.Password = "password";
            Oracle.DataAccess.Client.OracleConnection orcon = new Oracle.DataAccess.Client.OracleConnection(builder.ConnectionString);
            orcon.Open();
            DateTime endTime;
            SetLoopCount();
            DateTime startTime = DateTime.Now;
            Oracle.DataAccess.Client.OracleTransaction tran;
            tran = orcon.BeginTransaction();
           
            string[] acctNum = new string[loopCount];
            string[] corp = new string[loopCount];
            string[] mainBank = new string[loopCount];
            string[] association = new string[loopCount];
            Int64[] contractNumber = new Int64[loopCount];
            string[] hostSystemID = new string[loopCount];
            string[] instID = new string[loopCount];
            Int32[] seedParticipation = new Int32[loopCount];
            Int32[] camsParticipation = new Int32[loopCount];
            Int64[] actID = new Int64[loopCount];
            for (int i = 1; i <= loopCount; i++)
            {
                acctNum[i - 1] = acctNumber + i.ToString().PadLeft(3, char.Parse("0"));
                corp[i - 1] = i.ToString().PadLeft(6, char.Parse("0"));
                mainBank[i - 1] = mainbank;
                //association[i - 1] = " ";
                //contractNumber[i - 1] = 0;
                hostSystemID[i - 1] = systemID;
                instID[i - 1] = i.ToString().PadRight(6, char.Parse("0"));
                seedParticipation[i - 1] = seeds;
                camsParticipation[i - 1] = cams;
                actID[i - 1] = activityID;
            }
            //wrap the inserts in a transaction         
            Oracle.DataAccess.Client.OracleCommand acctCommand = new Oracle.DataAccess.Client.OracleCommand("FILEIMPORT_ACCOUNT_PKG.FI_ACCOUNT_INSERT_FILEUPLOAD", orcon);
            //acctCommand.Transaction = tran;
            acctCommand.CommandType = CommandType.StoredProcedure;
            acctCommand.ArrayBindCount = loopCount;
            acctCommand.Parameters.Add("P_ACCOUNT_NUMBER",OracleDbType.Varchar2,ParameterDirection.Input).Value =  acctNum;
            acctCommand.Parameters.Add("P_CORP", OracleDbType.Varchar2, ParameterDirection.Input).Value = corp;
            acctCommand.Parameters.Add("P_MAINBANK", OracleDbType.Varchar2, ParameterDirection.Input).Value = mainBank;
            acctCommand.Parameters.Add("P_ASSOCIATION", OracleDbType.Varchar2, ParameterDirection.Input).Value = association;
            acctCommand.Parameters.Add("P_CONTRACT_NUMBER", OracleDbType.Int32, ParameterDirection.Input).Value = contractNumber;
            acctCommand.Parameters.Add("P_HOST_SYSTEM_ID", OracleDbType.Varchar2, ParameterDirection.Input).Value = hostSystemID;
            acctCommand.Parameters.Add("p_INSTITUTION_ID", OracleDbType.Varchar2, ParameterDirection.Input).Value = instID;
            acctCommand.Parameters.Add("p_SEED_PARTICIPATION", OracleDbType.Int32, ParameterDirection.Input).Value = seedParticipation;
            acctCommand.Parameters.Add("p_CAMS_PARTICIPATION", OracleDbType.Int32, ParameterDirection.Input).Value = camsParticipation;
            acctCommand.Parameters.Add("p_ACTIVITY_ID", OracleDbType.Int32, ParameterDirection.Input).Value = actID;
            acctCommand.Execute

executing sql query in oaf

hi all,
i have to fetch a value using a sql query in the PFR of controller.below is the query
sqlText = "SELECT application_id ";
sqlText += "FROM ego_attr_groups_v ";
sqlText += "WHERE attr_group_name ='TREASURY'";
i would like to know how to get the jdbc connection , execute query and assign to a variable.
please help.
thanks
ramya 
Hi,
make use of Prepared Statement in AM method
String applicationid = null;
ResultSet resultset = null;
try
{
Connection conn = getOADBTransaction().getJdbcConnection();
String query = "SELECT application_id from FROM ego_attr_groups_v WHERE attr_group_name ='TREASURY'";
PreparedStatement stmt = conn.prepareStatement(query);
resultset = stmt.executeQuery();
}
while (resultset.next())
{
applicationid = (String)resultset.getInt("APPLICATION_ID").toString();
}
}
catch(Exception e)
{ 
}
// then return the application id from AM's method
return applicationid;
Thanks,
Gaurav 
Hi ,
import oracle.jdbc.OraclePreparedStatement;
import oracle.jdbc.OracleResultSet;
import oracle.apps.fnd.framework.server.OADBTransaction;
refer the following code
OAMessageChoiceBean lineTypeBean = (OAMessageChoiceBean)webBean.findChildRecursive("LineType");
OraclePreparedStatement oraclePreparedStatement = null;
OracleResultSet oracleResultSet = null;
String lineTypeIdValue = null;
StringBuffer stringBuffer = new StringBuffer();
stringBuffer.append("SELECT line_type_id FROM apps.po_line_types_val_v ");
stringBuffer.append(" WHERE UPPER ( line_type ) = UPPER ( '");
stringBuffer.append(lineTypeStr);
stringBuffer.append("' ) ");
OADBTransaction oadbTransaction = am.getOADBTransaction();
try{
oraclePreparedStatement = (OraclePreparedStatement)oadbTransaction.createPreparedStatement(stringBuffer.toString(), 1);
oracleResultSet = (OracleResultSet)oraclePreparedStatement.executeQuery();
if(oracleResultSet.next()){
lineTypeIdValue = oracleResultSet.getString(1);
}
else
oracleResultSet.close();
}
catch(Exception sqlexception){
pageContext.putDialogMessage(new OAException("OraclePreparedStatement OR OracleResultSet Exception"));
sqlexception.printStackTrace();
}

Array Binding Error : ORA-01485: compile bind length different from execute

Hi All -
I am running into an issue where I keep receiving the following error when trying to use Array Binding. I am dynamically enumerating through a DataSet and populating tables in Oracle of the same name with all of the DataTable data. I've tried debugging just with one single datatable of 10 records with datatype double, however still receive the exception. For reference, the connection is already opened before calling this method.
public void BulkLoadWithArrayBinding(System.Data.DataTable dt)
{
StringBuilder sb = new StringBuilder();
List<OracleParameter> parameters = new List<OracleParameter>(dt.Columns.Count);
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
sb.Append("INSERT INTO " + dt.TableName + " (");
foreach (DataColumn dc in dt.Columns)
{
sb.Append(dc.ColumnName.ToUpper());
if (dc.Ordinal < dt.Columns.Count - 1)
sb.AppendLine(",");
}
sb.Append(") VALUES(");
foreach (DataColumn dc in dt.Columns)
{
string parameterName = dc.ColumnName.ToUpper();
sb.Append(":" + parameterName);
if(dc.Ordinal < dt.Columns.Count - 1)
sb.AppendLine(",");
String[] sArray = new String[dt.Rows.Count];
DateTime[] dArray = new DateTime[dt.Rows.Count];
Decimal[] dbArray = new Decimal[dt.Rows.Count];
OracleParameter p = null;
if (dc.DataType.Name == "String")
{
for (int i = 0; i < dt.Rows.Count; i++)
{
sArray[i] = dt.Rows[i][dc.Ordinal].ToString();
}
p = new OracleParameter(parameterName,
OracleDbType.Varchar2, dt.Rows.Count, ParameterDirection.Input);
p.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
p.Size = sArray.Length;
p.Value = sArray;
}
else if (dc.DataType.Name == "DateTime")
{
for (int i = 0; i < dt.Rows.Count; i++)
{
dArray[i] = Convert.ToDateTime(dt.Rows[i][dc.Ordinal]);
}
p = new OracleParameter(parameterName,
OracleDbType.Date, dt.Rows.Count, ParameterDirection.Input);
p.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
p.Size = dArray.Length;
p.Value = dArray;
}
else
if (dc.DataType.Name == "Double")
{
for (int i = 0; i < dt.Rows.Count; i++)
{
dbArray[i] = Convert.ToDecimal(dt.Rows[i][dc.Ordinal]);
}
/*p = new OracleParameter(parameterName,OracleDbType.Decimal, dbArray.Length, ParameterDirection.Input);*/
p = new OracleParameter();
p.ParameterName = parameterName;
p.OracleDbType = OracleDbType.Decimal;
p.Direction = ParameterDirection.Input;
p.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
p.Size = dbArray.Length;
p.Value = dbArray;
}
cmd.Parameters.Add(p);
//parameters.Add(p);
}
sb.AppendLine(")");
cmd.CommandText = sb.ToString();
cmd.CommandType = CommandType.Text;
cmd.ArrayBindCount = dt.Rows.Count;
cmd.BindByName = true;
cmd.ExecuteNonQuery();
} 
Hi,
I didn't actually run your code, but you shouldn't be setting p.CollectionType = OracleCollectionType.PLSQLAssociativeArray; Associative Arrays are differnet from Array binding.
An associative array is the equivalent of a scalar array type which you'd pass into a stored procedure for a single execution.
Array binding is used for "execute this statement X times, here's the data for all X executions all up front."
Here's a small complete example, hopefully it helps,
Greg
//create table onedouble(col1 number);
using System;
using System.Data;
using Oracle.DataAccess.Client;
namespace ConsoleApplication2
{
    class Program
    {
        static void Main(string[] args)
        {
            double[] doubs = new double[10];
            for (int i=0;i<10;i++)
                doubs[i] = Double.Parse(i.ToString()+"."+i.ToString());
            using (OracleConnection con = new OracleConnection("data source=orcl;user id=scott;password=tiger"))
            {
                con.Open();
                using (OracleCommand cmd = new OracleCommand("", con))
                {
                    cmd.CommandText = "insert into onedouble values(:1)";
                    cmd.ArrayBindCount = 10;
                    cmd.Parameters.Add(new OracleParameter("",OracleDbType.Double));
                    cmd.Parameters[0].Value = doubs;
                    // this causes ora-1485
                    //cmd.Parameters[0].CollectionType = OracleCollectionType.PLSQLAssociativeArray;
                    cmd.ExecuteNonQuery();
                }
            }
        }
    }
}results
=======
SQL> select * from onedouble;
COL1
----------
0
1.1
2.2
3.3
4.4
5.5
6.6
7.7
8.8
9.9 
You are awesome. Thanks so much. I removed:
p.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
and it works.

Difference between associative array, array etc?

Hi,
i'm wondering what the differences to these are? They seem kind of similar, but an array need a custom type class in C# and the associative array does not. When should you use which? Say I want to pass a collection of numbers to a stored procedure maybe? 
Hi
For an array of scalar types, I'd use PLSQL Associative Array (aka Index-by-table), as the support is built in to ODP.net directly and has been for a while (9.2.x).
If you need to pass an array of object types, that's what you'd need UDT support for (added in 11.1.0.6.20).
Hope it helps,
Greg 
Thanks for the reply!
OK, but there's no performance difference between the two? 
You'd want to test something appropriate for what your usage to gauge that.
I just did a quick test and Associative Array is a fair amount faster than UDT for my test as far as the actual passing of the object and invocation of the proc (I passed 1000 strings of size 2000) , but I also used an empty Stored Procedure body and what the proc actually DOES could affect things.
Here's my code if you want to test. I used ODT to generate the V2TYP class, and then changed string[] m_V2TYP; to public inside that class just for ease of use.
Hope it helps,
Greg
/*
create type v2typ is table of varchar2(4000);
/
create or replace package testpack as
TYPE v2array is table of varchar2(4000) index by BINARY_INTEGER;
PROCEDURE test_assoc_array(v2arr1 IN v2array);
procedure test_udt(v2udt in v2typ);
END;
/
CREATE or replace PACKAGE BODY testpack AS
PROCEDURE test_assoc_array(v2arr1 IN v2array) IS begin null; END;
procedure test_udt(v2udt in v2typ) is begin null; end;
END;
/
*/
using System;
using System.Data;
using Oracle.DataAccess.Client;
namespace compare_assocarray_and_udt
{
    class Program
    {
        static string constr = "data source=orcl;user id=scott;password=tiger;";
        static void Main(string[] args)
        {
            int numelements = 1000;
            string[] vals = new string[numelements];
            for (int i = 0; i < numelements; i++)
                vals[i] = new string('a', 2000);
            V2TYP myv2typ = new V2TYP();
            myv2typ.m_V2TYP = vals;
            
            test_assoc_array(vals);
            test_udt(myv2typ);
        }
        static void test_assoc_array(string[] vals)
        {
            using (OracleConnection con = new OracleConnection(constr))
            {
                con.Open();
                using (OracleCommand cmd = new OracleCommand("testpack.test_assoc_array", con))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    OracleParameter Param1 = cmd.Parameters.Add("param1", OracleDbType.Varchar2);
                    Param1.Direction = ParameterDirection.Input;
                    Param1.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
                    Param1.Size = vals.Length;
                    Param1.Value = vals;
                    DateTime start = System.DateTime.Now;
                    cmd.ExecuteNonQuery();
                    Console.WriteLine("assoc array elapsed: {0}ms", (System.DateTime.Now - start).TotalMilliseconds.ToString());
                }
            }
        }
        static void test_udt(V2TYP val)
        {
            using (OracleConnection con = new OracleConnection(constr))
            {
                con.Open();
                using (OracleCommand cmd = new OracleCommand("testpack.test_udt", con))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    OracleParameter Param1 = cmd.Parameters.Add("param1", OracleDbType.Object);
                    Param1.Direction = ParameterDirection.Input;
                    Param1.UdtTypeName = "SCOTT.V2TYP";
                    Param1.Value = val;
                    DateTime start = System.DateTime.Now;
                    cmd.ExecuteNonQuery();
                    Console.WriteLine("udt elapsed:         {0}ms", (System.DateTime.Now - start).TotalMilliseconds.ToString());
                }
            }
        }
    }
} 
Thanks, I will try this later.
I actually want to use table functions on the collection to use in a SQL query, and having read a bit about it, it seems like that's not possible on associative arrays. So I have to go with a UDT either way!
Thanks!

ODP.NET associative arrays

Hi,
I need to get an array of integers from a PL/SQL stored procedure. I did it the same way as the documentation, but there occurs an "expression of wrong type" exception.
What is not correct with the following code?
int[] answer = new int[MAX_INDICES];
oracle_command.CommandType = CommandType.Text;
oracle_command.CommandText =
" DECLARE " +
" BEGIN " +
"     :1 := " + packageName + ".indexTab(" + cacheType + "(:2));" +
" END; ";
OracleParameter rparam = new OracleParameter(":1", OracleDbType.Int32);
rparam.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
rparam.Value = null;
rparam.Size = MAX_INDICES;
rparam.Direction = ParameterDirection.Output;
OracleParameter param1 = new OracleParameter(":2", OracleDbType.Int32);
param1.Value = _index;
param1.Direction = ParameterDirection.Input;
               
oracle_command.Parameters.Add(rparam);
oracle_command.Parameters.Add(param1);
try
{
     oracle_command.ExecuteNonQuery();
     answer = (int[])rparam.Value;
}
catch(OracleException e)
{
throw new ParallelException(ParallelException.Type.PLSQL, e.Number, e.Message);
}
finally
{
oracle_command.Parameters.Clear();
}
return answer;
Thx for your help,
Martin

Categories

Resources