More DBA job interview questions and answers at
http://dba.fyicenter.com/Interview-Questions/
(Continued from previous question...)
What are different modes of parameters used in functions and procedures?
There are three different modes of parameters: IN, OUT, and IN OUT.
IN - The IN parameter allows you to pass values in to the module, but will not pass anything out of the module and back to the calling PL/SQL block. In other words, for the purposes of the program, its IN parameters function like constants. Just like constants, the value of the formal IN parameter cannot be changed within the program. You cannot assign values to the IN parameter or in any other way modify its value.
IN is the default mode for parameters. IN parameters can be given default values in the program header.
OUT - An OUT parameter is the opposite of the IN parameter. Use the OUT parameter to pass a value back from the program to the calling PL/SQL block. An OUT parameter is like the return value for a function, but it appears in the parameter list and you can, of course, have as many OUT parameters as you like.
Inside the program, an OUT parameter acts like a variable that has not been initialised. In fact, the OUT parameter has no value at all until the program terminates successfully (without raising an exception, that is). During the execution of the program, any assignments to an OUT parameter are actually made to an internal copy of the OUT parameter. When the program terminates successfully and returns control to the calling block, the value in that local copy is then transferred to the actual OUT parameter. That value is then available in the calling PL/SQL block.
IN OUT - With an IN OUT parameter, you can pass values into the program and return a value back to the calling program (either the original, unchanged value or a new value set within the program). The IN OUT parameter shares two restrictions with the OUT parameter:
An IN OUT parameter cannot have a default value.
An IN OUT actual parameter or argument must be a variable. It cannot be a constant, literal, or expression, since these formats do not provide a receptacle in which PL/SQL can place the outgoing value.
(Continued on next question...)
