Oracle Stored Procedure Example (NOT a Ref Cursor) : I need to call an Oracle stored...

Tue, 07 Jul 2009 07:34:13 -700

Author: skingaby
Subject: 1363
Posted: 07-Jul-2009 at 7:34am

I need to call an Oracle stored procedure that takes one INPUT parameter and returns one OUTPUT parameter. There are no Entities involved so the Entity Framework (and consequently the DevForce model, will not help).
I tried to follow the largely undocumented RdbKey functionality, but was unsuccessful because no matter what I tried, then I called AdoHelper(RdbKey) I got an AdoHelper configured for Sql Server. Grrr.
I finally worked around it as shown below.
(Note: I am using the Devart dotConnect for Oracle drivers.)

Here is the proc I need to call. It is contained inside a Package on Oracle.

PACKAGE PKG_FUNCTIONS AS



    PROCEDURE GetNextSequenceValue(

        sequence_name IN VARCHAR,

        next_val OUT NUMBER);



END PKG_FUNCTIONS;



PACKAGE BODY PKG_FUNCTIONS AS



    PROCEDURE GetNextSequenceValue(

        sequence_name IN VARCHAR,

        next_val OUT NUMBER)

    IS

        select_stmt VARCHAR(200);

    BEGIN

        EXECUTE IMMEDIATE CONCAT('select ' , CONCAT(sequence_name , '.NEXTVAL from dual'))

            INTO next_val;

    END;



END PKG_FUNCTIONS;

After refactoring my working example, I ended up with a new DbHelper class that takes care of configuring the connection and returning the ADO objects I need to execute the procedure.

class DbHelper

{



    private static Devart.Data.Oracle.OracleProviderFactory _factory = new Devart.Data.Oracle.OracleProviderFactory();

    private static RdbKey _configRdbKey;



    static DbHelper()

    {

            var config = IdeaBlade.Core.IdeaBladeConfig.Instance;

            var keyElement = config.EdmKeys[0];

            var key = new EdmKey(keyElement);

            _configRdbKey = (RdbKey)key.RdbKey;

    }   



    public static Devart.Data.Oracle.OracleProviderFactory DbFactory

    {

        get

        {

            return _factory;

        }

    }



    public static DbConnection CreateConnection()

    {

        var cn = DbFactory.CreateConnection();

        cn.ConnectionString = _configRdbKey.ConnectionString;

        return cn;

    }



    public static DbCommand CreateCommand(DbConnection cn)

    {

        var cmd = DbFactory.CreateCommand();

        cmd.Connection = cn;

        return cmd;

    }



    public static DbCommand CreateCommand(DbConnection cn, string commandText)

    {

        var cmd = CreateCommand(cn);

        cmd.CommandText = commandText;

        return cmd;

    }



    public static DbCommand CreateCommand(DbConnection cn, string commandText,

        System.Data.CommandType commandType)

    {

        var cmd = CreateCommand(cn, commandText);

        cmd.CommandType = commandType;

        return cmd;

    }



    public static DbParameter CreateParameter()

    {

        var parm = DbFactory.CreateParameter();

        return parm;

    }



    public static DbParameter CreateParameter(string name)

    {

        var parm = CreateParameter();

        parm.ParameterName = name;

        return parm;

    }

    

    public static DbParameter CreateParameter(string name,

        System.Data.DbType type)

    {

        var parm = CreateParameter(name);

        parm.DbType = type;

        return parm;

    }



    public static DbParameter CreateParameter(string name,

        System.Data.DbType type, System.Data.ParameterDirection direction)

    {

        var parm = CreateParameter(name, type);

        parm.Direction = direction;

        return parm;

    }



    public static DbParameter CreateParameter(string name,

        System.Data.DbType type, System.Data.ParameterDirection direction, object value)

    {

        var parm = CreateParameter(name, type, direction);

        parm.Value = value;

        return parm;

    }



}

The method to actually execute the stored procedure, then looks like this:

public static long GetNextSequenceValue(string sequenceName)

{

    var cn = DbHelper.CreateConnection();

    var cmd = DbHelper.CreateCommand(cn, "PKG_GCSAG_FUNCTIONS.GETNEXTSEQUENCEVALUE", CommandType.StoredProcedure);

    var parm = DbHelper.CreateParameter("SEQUENCE_NAME", DbType.String, ParameterDirection.Input, sequenceName );

    cmd.Parameters.Add(parm);

    parm = DbHelper.CreateParameter("NEXT_VAL", DbType.Int64, ParameterDirection.Output);

    cmd.Parameters.Add(parm);

    cn.Open();

    cmd.ExecuteNonQuery();

    var retval = Decimal.ToInt64((Decimal)cmd.Parameters[1].Value);

    cn.Close();

    return retval;

}

DevForce Community Forum : Oracle Stored Procedure Example (NOT a Ref Cursor)

Oracle Stored Procedure Example (NOT a Ref Cursor) : I need to call an Oracle stored...