locked
Executing Oracle Stored procedure using Enterprise Library RRS feed

  • Question

  • User-824468864 posted

    Hi,

    I am using Enterprise Library for data access. The backend is Oracle and the connection string I am using is:

    <add name="Oracle" connectionString=" Data Source=XE;User id=SYSTEM;Password=Temp1234;" providerName="System.Data.OracleClient"/>

     I have 3 questions:

    1] How to specify parameters for Oracle Stored Procedure using "AddInParameter" method of class Database.

    2] Using .net code and Enterprise Library  for executing "Select" stored procedure which returns SYS_REFCURSOR as output parameter, which datatype to be used? DbType enum does not have corresponding datatype.

    3] When I am executing stored procedure by specifying parameters explicitly as comma separated string as follows, it works fine.

    DbCommand insertCommand = db.GetStoredProcCommand(spName, "LastTest", "FirstTest", DateTime.Now, DateTime.Now,0);

    But if "AddInParameter" method of class Database is used to provide parameter collection, I get following error.

    "ORA-06550: line 1, column 7:
    PLS-00306: wrong number or types of arguments in call to 'USP_INSERT_PERSON'
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored"

     Can you please give me sample code to execute Oracle stored procedure using Enterprise Library in which parameters are passed using "AddInParameter" method of Database class?

    Thanks and Regards,

    Vinod

    Monday, January 23, 2012 12:15 AM

Answers

  • User-824468864 posted

    I got the solution. It is as follows:

    "lstCol" contains entities and each entity provides SP parameter name (string) and it's value(object).

    public static bool ExecuteOracleSP(string spName, List<ClsColumns> lstCol)

    {

    int returnValue;

    Database database = DatabaseFactory.CreateDatabase("Oracle");

    DbCommand cmd = database.GetStoredProcCommand(spName);

    database.DiscoverParameters(cmd);

    foreach (ClsColumns obj in lstCol)

    {

    database.SetParameterValue(cmd, obj.ColumnName, obj.ColumnValue);

    }

    returnValue = database.ExecuteNonQuery(cmd);

    return returnValue > 0 ? true : false;

    }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 23, 2012 5:28 AM

All replies

  • User1210476459 posted

    Hi,

    You need to add parameter something like this...

    .AddParameter("@LasTest", OracleDbType.DateTime,DateTime.NOW, oCmd);

    .AddParameter("@FirstTest", OracleDbType.DateTime,DateTime.NOW, oCmd);

    Regards,

    Pradeep

    Monday, January 23, 2012 12:47 AM
  • User-824468864 posted

    I got the solution. It is as follows:

    "lstCol" contains entities and each entity provides SP parameter name (string) and it's value(object).

    public static bool ExecuteOracleSP(string spName, List<ClsColumns> lstCol)

    {

    int returnValue;

    Database database = DatabaseFactory.CreateDatabase("Oracle");

    DbCommand cmd = database.GetStoredProcCommand(spName);

    database.DiscoverParameters(cmd);

    foreach (ClsColumns obj in lstCol)

    {

    database.SetParameterValue(cmd, obj.ColumnName, obj.ColumnValue);

    }

    returnValue = database.ExecuteNonQuery(cmd);

    return returnValue > 0 ? true : false;

    }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 23, 2012 5:28 AM