Calling oracle store procedure asp.net odp.net

In this article, we have show how to call the oracle store procedure in (ASP.NET).In this example I have used ODP.NET(oracle data provider for net) because odp.net is faster as compared to Microsoft oracle client library. Microsoft stated that “System.Data.OracleClient” are deprecated and will be removed in a future version of the .NET Framework. So I strongly recommended using the odp.net.

Suppose we have table  tkt_user and has following columns as given below

CREATE TABLE TKT_USER

(

  PK_USER_ID                  NUMBER,

  USER_NAME                   VARCHAR2(500 BYTE),

  PASSWORD                    VARCHAR2(30 BYTE),

  EMAIL_ID                    VARCHAR2(500 BYTE),

  NAME                        VARCHAR2(100 BYTE),

  ACTIVE_STATUS               NUMBER,

  

)

Then I have create store procdure to insert records as given below

CREATE OR REPLACE PROCEDURE SP_TKT_USER_INSERT(

P_USER_NAME IN VARCHAR2,

P_PASSWORD IN VARCHAR2,

P_EMAIL_ID IN VARCHAR2,

P_NAME IN VARCHAR2,

P_ACTIVE_STATUS IN NUMBER,

)

AS

BEGIN

INSERT INTO TKT_USER

(PK_USER_ID,

USER_NAME,

PASSWORD,

EMAIL,

NAME,

IS_ACTIVE

,

NAME) VALUES

(seq_tkt_user.NEXTVAL,

P_USER_NAME,

P_PASSWORD,

P_EMAIL_ID,

P_NAME,

P_ACTIVE_STATUS,

);

COMMIT;

END SP_TKT_USER_INSERT;

Codebehind cs page we have called this way is given below

    var param = new OracleParameter[5];

            param[0] = new OracleParameter("P_USER_NAME", OracleDbType.Varchar2);

            param[0].Value = “TEST”;

            param[0].Direction = ParameterDirection.Input;

            param[1] = new OracleParameter("P_PASSWORD", OracleDbType.Varchar2);

            param[1].Value = “abc”;

            param[1].Direction = ParameterDirection.Input;

            param[2] = new OracleParameter("P_EMAIL_ID", OracleDbType.Varchar2);

            param[2].Value = “anilhsarma1983.blog@gmail.com”;

            param[2].Direction = ParameterDirection.Input;

            param[3] = new OracleParameter("P_NAME", OracleDbType.Varchar2);

            param[3].Value = “Anil Sharma”;

           param[3].Direction = ParameterDirection.Input;

            param[4] = new OracleParameter("P_ACTIVE_STATUS", OracleDbType.Int32);

            param[4].Value = 1;

            param[4].Direction = ParameterDirection.Input;

OracleDataAccess.ExecuteNonQuery(QTL.Utility.Conn, CommandType.Text, “SP_TKT_USER_INSERT”, param)

Note:

QTL.Utility.Conn =connection string  path stored in web config file

SP_TKT_USER_INSERT =store procedure Name

Param =parameter in array

OracleDataAccess = I have created class to execute oracle queries similar sqlhelperclass. If you need this class you can contact me this email id “anilhsarma1983.blog@gmail.com”. I will u send you attachment of this library. Otherwise you can used “parameter.addwithvalue” similar aso.net