Database Interaction with PL/SQL, Working with TABLE in Sub-programs, Parameter Modes - What are IN types of parameters?
(Page 3 of 5 )
Let us consider the following program:
declare
procedure dispValue(a number) is
b number;
begin
a := a + 10;
b := a * a;
dbms_output.put_line('Value : ' || b);
end;
begin
dispValue(10);
end;
/
Is the above program syntactically wrong? Is it logically wrong? But, the program raises an error! The error message would be “expression 'A' cannot be used as an assignment target.” Try to remove the statement “a := a + 10” and execute the program again. No doubt, it gets executed successfully. What is the problem?
Actually, we can rewrite the same program with the specification of IN as the following (which is actually the default):
declare
procedure dispValue(a IN number) is
b number;
begin
b := a * a;
dbms_output.put_line('Value : ' || b);
end;
begin
dispValue(10);
end;
If you don’t specify anything to the parameter, IN becomes the default behind the screens. That means it just behaves like an “IN”PUT to the procedure. But it could not be modified within the sub-program. The value present in a variable which is defined with “IN” can never be modified within the sub-program. Now I hope you can understand why you received the error. It not only affects ordinary types of variables, it could even be related to %TYPE or %ROWTYPE or RECORD or even TABLE.
That is why, to remove the confusion, it is always suggested that you use the type of parameter along with the parameter which could be either IN or OUT or even both IN OUT together (dealt with later).
Next: What are OUT types of parameters? >>
More Oracle Articles
More By Jagadish Chatarji
