How Can Make Store Procedure In Oracle

For This You Will have to make A Package Beacause in Oracle Store Procedures are composed in Packages.

and Acces By Also With It’s Name

I give you basic simple example of It and How can U Can Use to it with Asp.net

In Vb.

Step1 : For this first of all install oracle and Toad Software for this purpose of If Oracle have 10g or 11i then too good

Then you have no need of Toad

This time i am telling it with use of toad

Run to Toad and Connect it with your Schema(Database)

then click on red rounded option

Then Select your Package as given below in Picture

Create a New Package With This option

Every Package Have two parts

One is Body

Second is header or Specification

Body of Your Package

CREATE OR REPLACE PACKAGE BODY SYSTEM.PKG_MCFMS AS
–start of Authenticate procedure variables
ACTUALPASSWORD VARCHAR2(30);
ACTUALUSERNAME VARCHAR2(30);
BIT VARCHAR2(2);
–end of Authenticate procedure variables
PROCEDURE LoginCheck(uname in varchar2,upass in varchar2,ret out number) IS
BEGIN
SELECT  PASSWORD INTO PWD FROM MONT_USER_MASTER WHERE upper(USER_NAME)=upper(UNAME);
IF  upper(PWD) = upper(UPASS) THEN
RET := 1;
ELSE
RET := 2;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RET := 3;
END ;
PROCEDURE Proc_Authentication(UserId IN VARCHAR2,UserPassword IN VARCHAR2,RET OUT NUMBER,USERROLE OUT VARCHAR2,UserName OUT VARCHAR2) IS
BEGIN
SELECT  PASSWORD,USER_NAME,USER_ACTIVE,USER_LEVEL,NAME INTO ACTUALPASSWORD,ACTUALUSERNAME,BIT,USERROLE,UserName FROM MONT_USER_MASTER WHERE upper(USER_NAME)=UPPER(UserId);
BEGIN
IF UPPER(ACTUALPASSWORD) =UPPER(UserPassword) AND UPPER(ACTUALPASSWORD) IS NOT NULL THEN
BEGIN
IF UPPER(BIT)=’Y’ THEN
RET := 2;  –this value is for success
ELSE
RET := 3; –this value indicate to not active level
END IF;
END;
ELSE
BEGIN
RET := 1; –this value indicate that password wrong
END;
END IF;
END;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RET := -99; –this value is for exception handling
END ;
END PKG_MCFMS;

Specificatio of Package

CREATE OR REPLACE PACKAGE SYSTEM.PKG_MCFMS ASPROCEDURE Proc_Authentication(UserId IN VARCHAR2,UserPassword IN VARCHAR2,RET OUT NUMBER,USERROLE OUT VARCHAR2,UserName OUT VARCHAR2);END PKG_MCFMS;

/

Now Start to your visual studio and Make new WebSite and Make Connection With Oracle define this code in web config file into connectionString Section

<connectionStrings>

<!–section of connection with oracle database–>

<add name=”cn” connectionString=”user id=system;password=manageowm;data source=M2″/>

<!–section of connection end with oracle database–>

</connectionStrings>

For connectivity In oracle you will be make a service it’s not connect as MSSqlServer

M2 is a Service Name

Now Make a Class As Given Below in Your web Site Into App_code Folder

Imports Microsoft.VisualBasic

Imports System.Data.OracleClient

Imports System.Data

Imports System.Configuration

Namespace Deam_BLL

”’ <summary>

”’ User Properties Interface

”’ </summary>

”’ <remarks></remarks>

Interface ifaceUser

Property strUserName() As String

Property strUserId() As String

Property strUserPassword() As String

Property strLoginId() As String

Property strRole() As String

End Interface

”’ <summary>

”’ User Property Class

”’ </summary>

”’ <remarks></remarks>

Public Class clsUserprp : Implements ifaceUser

Dim _strLoginId, _strUserName, _strUserId, _strUserPassword, _strRole As String

Public Property strLoginId() As String Implements ifaceUser.strLoginId

Get

Return _strLoginId

End Get

Set(ByVal value As String)

_strLoginId = value

End Set

End Property

Public Property strUserId() As String Implements ifaceUser.strUserId

Get

Return _strUserId

End Get

Set(ByVal value As String)

_strUserId = value

End Set

End Property

Public Property strUserName() As String Implements ifaceUser.strUserName

Get

Return _strUserName

End Get

Set(ByVal value As String)

_strUserName = value

End Set

End Property

Public Property strUserPassword() As String Implements ifaceUser.strUserPassword

Get

Return _strUserPassword

End Get

Set(ByVal value As String)

_strUserPassword = value

End Set

End Property

Public Property strRole() As String Implements ifaceUser.strRole

Get

Return _strRole

End Get

Set(ByVal value As String)

_strRole = value

End Set

End Property

End Class

”’ <summary>

”’ Login Class

”’ </summary>

”’ <remarks></remarks>

Public Class clsLogin : Inherits clsOracleConn

Public Function Login(ByVal _UserId As String, ByVal _UserPassword As String) As Integer

Dim cmd As New OracleCommand

Try

If (conn.State = ConnectionState.Closed) Then

conn.Open()

End If

cmd.CommandText = “PKG_MCFMS.Proc_Authentication”

cmd.CommandType = CommandType.StoredProcedure

cmd.Connection = conn

cmd.Parameters.AddWithValue(“UserId”, _UserId)

cmd.Parameters.AddWithValue(“UserPassword”, _UserPassword)

cmd.Parameters.Add(“RET”, OracleType.Number).Direction = ParameterDirection.Output

cmd.Parameters.Add(“USERROLE”, OracleType.VarChar, 5).Direction = ParameterDirection.Output

cmd.Parameters.Add(“UserName”, OracleType.VarChar, 30).Direction = ParameterDirection.Output

cmd.ExecuteNonQuery()

Dim K As Int32

Dim OutStatus As String

Dim OutUserName As String

OutStatus = cmd.Parameters(“USERROLE”).Value.ToString()

OutUserName = cmd.Parameters(“UserName”).Value.ToString()

K = cmd.Parameters(“RET”).Value

If (K = 2) Then

HttpContext.Current.Session(“LoginId”) = _UserId.ToUpper().Trim()

HttpContext.Current.Session(“UserId”) = _UserId.ToUpper().Trim()

HttpContext.Current.Session(“Password”) = _UserPassword.ToUpper().Trim()

HttpContext.Current.Session(“UserName”) = OutUserName.ToUpper().Trim()

HttpContext.Current.Session(“UserRole”) = OutStatus.ToUpper().Trim()

End If

Return K

Catch ex As Exception

Return ex.Message

End Try

End Function

End Class

For Connectivity Make also a Inheritable Class

”’ <summary>

”’ Main Connection Class

”’ </summary>

”’ <remarks></remarks>

Public MustInherit Class clsOracleConn

Public conn As OracleConnection

Public Sub New()

conn = New OracleConnection(ConfigurationManager.ConnectionStrings(“cn”).ConnectionString)

End Sub

End Class

Public MustInherit Class clsSapConn

Public Shared connSap As OracleConnection

Public Sub New()

connSap = New OracleConnection(ConfigurationManager.ConnectionStrings(“cn”).ConnectionString)

End Sub

End Class

End Namespace

Now Access this Class on your Login Page on Button Click As Given Below

Imports Deam_BLL

Partial Class Masters_Default2

Inherits System.Web.UI.Page

Dim objUserLogin As New clsLogin

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim OutPutFunction As Integer

Dim OutUserLogin As Integer

txtUserName.TextMode = TextBoxMode.SingleLine

txtPassword.TextMode = TextBoxMode.Password

Try

OutPutFunction = objUserLogin.Login(txtUserName.Text.Trim().ToUpper(), txtPassword.Text.Trim().ToUpper())

Dim ticket As New FormsAuthenticationTicket(1, Session(“LoginId”), Date.Now, Date.Now.AddHours(12), True, Session(“UserRole”), FormsAuthentication.FormsCookiePath)

Dim hash As String = FormsAuthentication.Encrypt(ticket)

Dim tempCookie As New HttpCookie(FormsAuthentication.FormsCookieName, hash)

Response.Cookies.Add(tempCookie)

If OutPutFunction = 2 Then

‘Call objUserLogin.User_LoggedIn_History(Session(“LoginId”), OutUserLogin)

If Session(“UserRole”) = “ADMIN” Or Session(“UserRole”) = “SEMI” Then

Response.Redirect(“Authenticate_Selection.aspx”)

Else

Response.Redirect(“User/Home.aspx”)

End If

End If

Catch ex As Exception

Dim message As String

message = ex.Message.Trim()

DivException.Attributes.Add(“display”, “block”)

DivException.InnerText = message

End Try

End Sub

End Class

Now Go into source of Your Page and Write down given tags

<div style=”width: 100%; color: White; text-align: left; vertical-align: top” runat=”server”>

<div id=”FirstDiv” style=”width: 100%; color: White;” runat=”server”>

<fieldset style=”background-color: #0065a2;”>

<legend style=”color: White; padding-top: 15px; font-size: medium; font-weight: bold;”>

Member Login</legend>

<table align=”left” cellpadding=”0″ cellspacing=”0″ class=”Table”>

<tr>

<td width=”100%” style=”font-size: small; font-weight: bold; padding-left: 15px;”

colspan=”2″>

<asp:Literal ID=”ltrlMsg” runat=”server”></asp:Literal>

</td>

</tr>

<tr>

<td width=”50%” style=”font-size: small; font-weight: bold; padding-left: 15px;”>

Portal Id

</td>

<td style=”padding-left: 5px;”>

<asp:TextBox ID=”txtUserName” runat=”server” class=”validate[required] text-input”></asp:TextBox>

</td>

</tr>

<tr>

<td width=”50%” style=”font-size: small; font-weight: bold; padding-left: 15px;”>

Password

</td>

<td style=”padding-left: 5px;”>

<asp:TextBox ID=”txtPassword” runat=”server” class=”validate[required] text-input”></asp:TextBox>

</td>

</tr>

<tr>

<td style=”vertical-align: bottom; text-align: center; padding-top: 15px; font-size: small;”>

<asp:LinkButton ID=”lnkForgetPass” SkinID=”lnkForgetPass” runat=”server” ToolTip=”click here to retrieve your password”>Forget Password</asp:LinkButton>

</td>

<td style=”text-align: center; font-size: small; padding-top: 15px;”>

<%–<img id=”btnLogin” title=”Sign In” alt=”Click Me” src=”Images/signin.jpg” runat=”server” onclick=”return btnLogin_onclick()” />–%>

<asp:Button ID=”Button1″ runat=”server” Text=”Login” ToolTip=”click here for login”

SkinID=”btnLogin” />

</td>

</tr>

</table>

</fieldset>

</div>

</div>

<div id=”DivException” style=”background-color: Yellow; color: Red; border-color:Gray; border-style:hidden;”

runat=”server”>

<asp:Label Text=”” ID=”lblException” runat=”server” ></asp:Label>

</div>

then Double click on your page and Go into page load event

Protected Sub Page_PreLoad(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.PreLoad

If (Page.IsPostBack = False) Then

FirstDiv.Style.Add(“display”, “block”)

DivException.Attributes.Add(“display”, “none”)

End If

End Sub

Run to your site page and test

Be Happy and Enjoy in Programming World!!!

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s