PL/SQL Parameter Types Interesting Facts

A parameter is the value that is used in PL/SQL subprograms(procedures,functions), calling programs(could be anonymous PL/SQL blocks, another subprogram,simple EXECUTE procedure_name(parameter),EXECUTE function_name(parameter)). A parameter in calling function/procedure and called program can be any one of the following three types :
1) IN Parameter – It is passed as a read only value from calling program to called program. If we try to modify this IN parameter value in procedures being called, it gives a compile time error
2) OUT Parameter – OUT parameter is the value passed from calling program. It is modified and given back as output to calling program. We can display the value of OUT parameter using DBMS_OUTPUT.PUT_LINE, iSQL*PLUS host variables also called as global variables(in oracle 11g isql*plus is no longer available), using VARIABLE variable name datatype; EXECUTE procedure_name(parameter);PRINT variable name. The variable name is the OUT parameter value returned and displayed in host environment. We can also use this inside oracle forms, reports, java and c applications
3) IN OUT Parameter – It is both value passed into program, variable returned after processing. This is a common type used in most reporting systems where we need to format the output display. A very good example is phone number which when passed as raw value is say 9874563789. After making changes, the output is going to be say 987-456-3789

Free Oracle Database Articles, Tips, Jobs :

Delivered by FeedBurner