Below given is the Way how to execute a function in a C#,ASP.net Pages
In Case Of Function 1 Its Working Properly
But in Case of Function 2 Uncomment the code which is Commented in .cs PAge...
At that time i am getting a Error specifying that the
Error:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'GETMYUSER'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
------------------------------------------------------------------------
.DDL
CREATE TABLE user_master(user_name VARCHAR2(25),u_id VARCHAR2(4) NOT NULL,password VARCHAR2(15),user_status VARCHAR2(4),
auth_id VARCHAR2(4),auth_date VARCHAR2(11),login_date VARCHAR2(11),last_password_date VARCHAR2(11)
INSERT INTO user_master
VALUES('ABCD','102','ABCD','Y','101','23-OCT-2011','18-Nov-2011','23-OCT-2011');
INSERT INTO user_master
VALUES('ABCD','101','DEFG','Y','102','25-MAR-2011','18-Nov-2011','25-MAR-2011');
----------------------------------------------------------------------------
PLSQL FUNCTIONS
1
Create or Replace Function GetMyUser --(Id VarChar)
return VARCHAR2 as Detail VARCHAR2(50);
Total VARCHAR2(50):='';
Cursor MyCur is Select User_Name as DETAIL From USer_Master;-- where U_ID=ID;
Begin
Open Mycur;
Loop
Fetch MyCur into Detail;
Exit when MyCur%notFound;
Total:=Detail;
End Loop;
close MyCur;
Return Total;
End;
----------------------------------------------------------------------------
2
Create or Replace Function GetMyUser(Id VarChar)
return VARCHAR2 as Detail VARCHAR2(50);
Total VARCHAR2(50):='';
Cursor MyCur is Select User_Name as DETAIL From USer_Master where U_ID=ID;
Begin
Open Mycur;
Loop
Fetch MyCur into Detail;
Exit when MyCur%notFound;
Total:=Detail;
End Loop;
close MyCur;
Return Total;
End;
----------------------------------------------------------------------------
.aspx PAge
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Simple Functions.aspx.cs" Inherits="Learn_Functions_Simple_Functions" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Simple Functios</title>
</head>
<body>
<form id="form1" runat="server">
<center>
<div>
</div>
<br />
<br />
</center>
<div>
<center>
<asp:Label ID="Label" runat="server" Text="STATUS FLAG IS:"></asp:Label>
<asp:TextBox ID="txtFlg" runat="server"></asp:TextBox>
<br />
<asp:Button ID="Button1" runat="server" Text="Button" onclick="Button1_Click" />
</center>
</div>
</form>
</body>
</html>
----------------------------------------------------------------------------
.Cs Page
using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.OleDb;
using System.Web;
using System.Web.UI;
using System.Web.Configuration;
using System.Web.UI.WebControls;
public partial class Learn_Functions_Simple_Functions : System.Web.UI.Page
{
OleDbConnection Con;
OleDbCommand Cmd;
OleDbDataReader _dr;
string Str, Constr;
protected void Page_Load(object sender, EventArgs e)
{
Constr = WebConfigurationManager.ConnectionStrings["Constr"].ConnectionString;
Con = new OleDbConnection(Constr);
if (Con.State == ConnectionState.Closed) Con.Open();
}
protected void Button1_Click(object sender, EventArgs e)
{
Cmd = new OleDbCommand("GetMyUser", Con);
Cmd.CommandType = CommandType.StoredProcedure;
//Cmd.Parameters.Add("Id", OleDbType.VarChar).Value = "101";
Cmd.Parameters.Add("P_RV", OleDbType.VarChar, 30).Direction = ParameterDirection.ReturnValue;
Cmd.ExecuteNonQuery();
Str = Convert.ToString(Cmd.Parameters["P_RV"].Value);
txtFlg.Text = Str;
}
}
-------------------------------------------------------------------------------------
Kindly Help me with Resolving the Error as Per Function 2
Thanks in Advance
Sushil Dharmar