Reputation: 73
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:
CATEGORY
table.Upvotes: 1
Views: 769
Reputation: 73
Ok after 4 hours of trying continuously without any rest I found the solution. I have a Table in Oracle Database like this:
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.
Upvotes: 1