Oracle Database Interaction Using ODP.NET and ASP.NET: Accessing PL/SQL Stored Procedures - Working with a simple PL/SQL procedure and executing it using ODP.NET
(Page 2 of 4 )
Let us start creating a PL/SQL procedure. You can do this in several ways. It includes using SQL*Plus, iSQL*Plus or even “Oracle Developer tools for Visual Studio.NET”. In my case I used “Oracle Developer tools for Visual Studio.NET” which is an excellent add-in provided by Oracle to integrate with Visual Studio.NET. I need not leave the Visual Studio.NET environment to work with an Oracle database, if I install it. And most of all, it is completely FREE. You can download it from http://www.oracle.com/technology/tech/dotnet/tools/index.html.
The simple PL/SQL procedure I created for demonstrating this example is as follows:
PROCEDURE "SCOTT"."P_INCREASE_SALARY" AUTHID CURRENT_USER IS
BEGIN
update emp set sal = sal + 500;
commit;
END "P_INCREASE_SALARY";
Because I created the above from “Oracle Developer Tools for Visual Studio.NET”, the syntax would be a bit different. But, overall, we can understand that the name of the stored procedure is “P_INCREATE_SALARY” and it increases all the salaries of employees (in EMP table) by 500. It is always a good practice to issue a “commit” statement after a DML statement (to remove unnecessary locks after successful database manipulations).
Now let us see how to execute the stored procedure from ASP.NET using ODP.NET.
Dim cn As New OracleConnection("User
ID=scott;password=tiger;Data Source=ORCL")
Try
Dim cmd As New OracleCommand("P_INCREASE_SALARY", cn)
cmd.CommandType = CommandType.StoredProcedure
cn.Open()
cmd.ExecuteNonQuery()
cmd.Dispose()
Me.lblError.Text = "Succesfully executed.."
Catch ex As Exception
Me.lblError.Text = ex.Message
Finally
If cn.State = ConnectionState.Open Then
cn.Close()
End If
End Try
Within the above code fragment, I created an “OracleConnection” object and “OracleCommand” object. “OracleCommand” is assigned with our previous stored procedure named “P_INCREASE_SALARY”. The most important statement to concentrate on here is the following:
cmd.CommandType = CommandType.StoredProcedure
The above statement confirms that the “OracleCommand” needs to execute a stored procedure (and not a query or DML statement). That’s all. The rest will be automatically taken care by ODP.NET, ASP.NET and the Oracle database!
Next: A simple PL/SQL procedure with a single parameter >>
More ASP.NET Articles
More By Jagadish Chaterjee
