Oracle Database Interaction Using ODP.NET and ASP.NET: Accessing Stored Procedures, Functions - What about IN OUT parameter in a PL/SQL procedure?
(Page 3 of 5 )
If we summarize our experience with IN as well as OUT of PL/SQL procedures, we can understand that IN is used for passing information to a PL/SQL procedure and OUT is used to get back the information from a PL/SQL procedure.
But what about IN OUT? It is just the combination of both IN and OUT together. In brief, the same parameter can be used to pass and return the values. In our example below, we are trying to create a stored procedure with two parameters. One will be of type IN and the other will be of type IN OUT. Let us see the stored procedure first.
PROCEDURE "SCOTT"."P_INCREASESALARY" (
"ENO" IN NUMBER,
"INCSAL" IN OUT NUMBER) IS
emp_sal emp.SAL%type;
BEGIN
update emp set sal = sal + "INCSAL"
where empno = "ENO";
select sal into "INCSAL" from emp
where empno = "ENO";
END "P_INCREASESALARY";
Within the above stored procedure, we need to understand several issues. The name of the stored procedure will be “P_INCREASESALARY”. It contains two parameters, “ENO” and “INCSAL”. “ENO” is an IN parameter (which means we can only pass values through it, but not return any values). “INCSAL” is an IN OUT parameter (which means we can pass and retrieve values).
Within the body of the stored procedure, I am trying to update the particular employee (specified by “ENO”) with an incremental salary (specified by “INCSAL”). After successful updating, I would like to return the latest salary of the same employee back to the application. For that purpose, I am using a SELECT statement to retrieve the latest salary of the employee and assign it to “INCSAL”.
The wonder is that you need not return it, as you generally do using a function. It would automatically reflect (as it is similar to a reference pointer) in the calling application -- in this case, it is an ASP.NET application. I hope that you understood the above stored procedure. Now, we shall see about accessing it using ODP.NET, in the next section.
Next: Accessing the IN OUT parameter using ODP.NET >>
More ASP.NET Articles
More By Jagadish Chaterjee
