What is an IN OUT type of parameter? - Oracle

This is part 15 of a series of articles focusing on database interactions with Oracle PL/SQL. In my previous article, we looked at several examples that covered the use of sub-programs. In this article we will work with PL/SQL TABLE types in between sub-programs. We will also discuss IN, OUT and IN OUT types of parameters in this article.

TABLE OF CONTENTS:

By: Jagadish Chatarji

Poor Best

Rating:

/ 23

August 30, 2005

print this article

SEARCH DEV SHED

TOOLS YOU CAN USE

The IN OUT type of parameter is special type of parameter which can be accessed and modified. Both operations can be done using an IN OUT parameter. We can rewrite the above program as follows with the concept of an IN OUT parameter:

declare

   b          number := 10;
   procedure doSquare(z IN OUT number) is
   begin

z := z * z;

end;

begin

doSquare(b);
dbms_output.put_line('Square : ' || b);
end;

The above program is completely different from all of the earlier programs. Let us examine the life of the variable of ‘b’ in the above program. We can conclude something like the following:

‘b’ is declared as of type number.

‘b’ is immediately initialized to 10.

‘doSquare’ method is called by passing the value of ‘b’ (which is 10 at this moment).

‘z’ receives 10 (which has got permission for both access and modify).

‘z’ gets updated to ‘z * z’ (which is 100 at this moment).

As ‘z’ got modified, automatically ‘b’ in main program gets modified (at this moment ‘b’ is 100 as well).

We are displaying back the value in ‘b’, which is 100.