Enterprise Library 2006 -- Calling Oracle Procedure with Parameters

Discussion in 'ASP .Net' started by TattedProgrammer, Aug 31, 2006.

  1. Hello All,

    I am completely stuck, I have tried for an entire day to get a simple
    procedure call to my Oracle Db via the enterprise Library 06. I am not
    an oracle fan to start, but have to deal with it for now. I am only
    trying to retrieve data into a dataset from the oracle procedure,
    nothing fancy! Here is my 100th version of code I have.


    [ORACLE PROCEDURE I AM CALLING]

    PROCEDURE "METLAB"."GET_LOOKUPS_PRC" (
    "P_LOOKUP_TYPE" IN VARCHAR2, P_RETURNSET OUT SYS_REFCURSOR) IS

    BEGIN

    OPEN P_RETURNSET FOR

    SELECT LOOKUP_ID,
    LOOKUP_VALUE,
    LOOKUP_TYPE
    FROM MRLOOKUPS
    WHERE LOOKUP_TYPE = P_LOOKUP_TYPE
    ORDER BY LOOKUP_VALUE ASC;

    NULL;

    END "GET_LOOKUPS_PRC";


    Code (Text):
     [#01]
      OracleDatabase db = new OracleDatabase(dbConn);
      DbCommand objComm = db.GetStoredProcCommand("GET_LOOKUPS_PRC");

      db.AddInParameter(objComm, "P_LOOKUP_TYPE",DbType.String,"NACE");
      db.AddOutParameter(objComm, "P_RETURNSET",DbType.Object,8000);

      objDS = db.ExecuteDataSet(objComm);


    I would really like to continue writing the code similar to what I have
    been doing with the Application Block previous. [HERE IS WHAT I AM USE
    TO]
      //Create Sql Parameters for SP
        SqlParameter[] arrParms = new SqlParameter[3];
        arrParms[0] = new SqlParameter("@p_UserName", arr[0]);
        arrParms[1] = new SqlParameter("@p_DbId", arr[1]);
        arrParms[2] = new SqlParameter("@p_AddRemove",
    GetAddRemove(((CheckBox)sender).Checked));

      //Update the records//
        SqlHelper.ExecuteNonQuery(strConnection,
    CommandType.StoredProcedure, "up_AddOrRemovePerms", arrParms);

    [MY CLOSEST ATTEMPT FOR THE ORACLE SIDE OF IT .. STILL DOESN'T WORK]
    [#02]
      Dataset objDS = new Dataset;
      OracleParameter[] arrParams = new OracleParameter[2];

      arrParams[0] = new
    OracleParameter("P_LOOKUP_TYPE",OracleType.VarChar,50);
      arrParams[1] = new OracleParameter("P_RETURNSET", OracleType.Cursor);


      objDS = db.ExecuteDataSet("GET_LOOKUPS_PRC", arrParams);

    THE ERROR FOR [#02]
    "Object must implement IConvertible"

    ANY DOCUMENTATION LINKS OR HELPFUL SOURCE CODE FOR ORACLE USING THE
    ENTERPRISE LIBRARY WOULD BE MUCH APPRECIATED.

    Thank in advanced!!
    Spence
     
    TattedProgrammer, Aug 31, 2006
    #1
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.