Oracle Database Interaction Using ODP.NET and ASP.NET: Accessing Stored Procedures, Functions - A word about stored functions and about accessing them using ODP.NET
(Page 5 of 5 )
Stored functions (or even simply functions) are also very similar to the stored procedures, but they do return values (directly) to the calling programs or applications. Even though you can still use IN, OUT and IN OUT types of parameters with functions, we rarely use IN OUT.
Now let us start with a simple function definition as follows:
FUNCTION "SCOTT"."GET_DNAME" (
"ENO" IN NUMBER) RETURN VARCHAR2 IS
deptname dept.DNAME%type;
BEGIN -- executable part starts here
select dname into deptname from dept
where deptno = (select deptno from emp
where empno = "ENO");
return deptname;
END "GET_DNAME";
The above function simply returns a department name based on the employee number we pass to it. I used a sub-query to get the department name in the above scenario. Now let us look into the ODP.NET part of our ASP.NET application. It is programmed something like this:
Dim cn As New OracleConnection("User ID=scott;password=tiger;Data
Source=ORCL")
Try
Dim cmd As New OracleCommand("get_dname", cn)
cmd.CommandType = CommandType.StoredProcedure
Dim p_dname As New OracleParameter("deptname",
OracleDbType.Varchar2, 25)
p_dname.Direction = ParameterDirection.ReturnValue
cmd.Parameters.Add(p_dname)
cmd.Parameters.Add("ENO", OracleDbType.Int32).Value =
7369
cn.Open()
cmd.ExecuteNonQuery()
Me.lblMsg.Text = "Dept. name of the employee: " &
cmd.Parameters("deptname").Value.ToString
cmd.Dispose()
Catch ex As Exception
Me.lblMsg.Text = ex.Message
Finally
If cn.State = ConnectionState.Open Then
cn.Close()
End If
End Try
I hope you can easily understand the above program except for the following set of statements.
Dim p_dname As New OracleParameter("deptname",
OracleDbType.Varchar2, 25)
p_dname.Direction = ParameterDirection.ReturnValue
cmd.Parameters.Add(p_dname)
cmd.Parameters.Add("ENO", OracleDbType.Int32).Value = 7369
Within the above set of statements, we declared a parameter named “deptname” which is of type “varchar2” and has a maximum size of 25. The direction of that parameter has been specified as “ParameterDirection.ReturnValue”, which means that our application can expect a value directly from that function itself. I hope you can understand “ENO” very easily as it is simply an IN parameter to the function. We retrieve back the return value using the following statement:
cmd.Parameters("deptname").Value.ToString
In my upcoming article, I would focus on Packages and other stuff. Any comments, suggestions, bugs, errors, feedback etc. are highly appreciated at jag_chat@yahoo.com.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
