How to call a stored procedure from Oracle 11g Express Database using Entity Framework

Question

I know this question is asked too many times, I searched a lot, didn't find a single solution.

I am using Visual Studio 2012, Entity Framework 6.1.3, .NET 4.5, ASP.NET MVC 4 - application. The database and VS2012 connectivity is ok and I am able to use ADO.NET Entity Data Model .edmx to interact with database perfectly fine.

I have this simple table CATEGORIES. This table has two columns CATEGORY_ID and CATEGORY_NAME. The rows are:

CATEGORY_ID      CATEGORY_NAME
----------------------------------
    1            SELECT A CATEGORY
    2            Grocery
    3            Cosmetics
    4            Clothing
    5            Stationary
    6            Electronics
    7            Sweets

Please give me:

1. Oracle the database code example to create the procedure or function for this CATEGORY table.
2. Entity Framework code example to get rows from the procedure or function

Answer 1

Ok after 4 hours of trying continuously without any rest I found the solution. I have a Table in Oracle Database like this:

1. CATEGORY_ID CATEGORY_NAME
2. 1 SELECT A CATEGORIE
3. 2 Grocery
4. 3 Cosmetics
5. 4 Clothing
6. 5 Stationary
7. 6 Electronics

For this I have a procedure:

create or replace PROCEDURE GET_CAT_NAME(cat_id IN number, cat_name OUT 
varchar2)
IS
BEGIN
SELECT CATEGORY_NAME INTO cat_name 
FROM CATEGORY WHERE CATEGORY_ID = cat_id;
END;​

For this I have written code in C# like this:

using (OracleConnection objConn = new OracleConnection("DATA SOURCE=localhost:1521/XE; PASSWORD=irfan118406; USER ID=IRFAN_OMIDB22"))
        {
            OracleCommand objCmd = new OracleCommand("GET_CAT_NAME", objConn);
            objCmd.CommandType = CommandType.StoredProcedure;

            OracleParameter pout_descr = new OracleParameter("id", OracleDbType.Varchar2, 2000);

            //objCmd.Parameters.Add(new OracleParameter("id",OracleDbType.Varchar2,ParameterDirection.Input)).Value = 2;
            objCmd.Parameters.Add("cat_id",OracleDbType.Int32).Value = 3;
            objCmd.Parameters.Add("cat_name", OracleDbType.Varchar2).Direction = ParameterDirection.Output;

            objCmd.Parameters["cat_name"].Size = 20;

            try
            {
                objConn.Open();
                objCmd.ExecuteNonQuery();
                Response.Write(objCmd.Parameters["cat_name"].Value.ToString());

            }
            catch (Exception ex)
            {
                Response.Write(ex.ToString());
            }

            objConn.Close();
        }

Code works perfectly without any error.

UPDATE:

objCmd.Parameters["cat_name"].Size = 20;

This line of code is super important, I was getting an error for continuous 3 hours: "ORA-06502: PL/SQL: numeric or value error calling a stored procedure", After putting the line of code, error disappeared and runs fine.