Home arrow Oracle arrow Page 4 - Database Interaction with PL/SQL, Working with TABLE in Sub-programs, Parameter Modes

What are OUT types of parameters? - 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.

  1. Database Interaction with PL/SQL, Working with TABLE in Sub-programs, Parameter Modes
  2. Returning PL/SQL TABLE from a FUNCTION to a PROCEDURE
  3. What are IN types of parameters?
  4. What are OUT types of parameters?
  5. What is an IN OUT type of parameter?
By: Jagadish Chatarji
Rating: starstarstarstarstar / 23
August 30, 2005

print this article



This is a bit different (of course quite opposite) from the case we just finished discussing.  The IN parameter could never be modified (but only accessed).  The OUT parameter could never be accessed (but modified and indirectly returned back).  Let us consider the following example:


    b          number;

    procedure doSquare(a IN number,
z OUT number) is

          z := a * a;

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


Even though the above is a very simple program, it gives us a lot to discuss. First of all, you should observe that ‘b’ never gets initialized within the program.  It was just declared as being of type number and we are directly displaying it.  And we received a value of 100.  So, before displaying the value of ‘b’, we are calling a procedure named ‘doSquare’, which performs all of the magic.

The statement ‘doSquare(10,b)’ calls the procedure ‘doSquare’ by sending 10 into the parameter ‘a’.  Just like 10, we are passing ‘b’ to ‘z’ (but as being of type OUT parameter).  Since ‘z’ is an OUT type of parameter, it will not expect any value from ‘b’.  Instead, it in turn reflects back its own value (value of ‘z’) to ‘b’.  That means the parameter ‘z’ returns back its own value directly into ‘b’.  It is another way of returning a value from a procedure (but indirectly, because we cannot use a RETURN statement with a procedure).  So, within the procedure ‘doSquare’, whenever ‘z’ gets modified, the variable ‘b’ also gets modified with the same value, and thus ‘z’ is called an OUT type of parameter.

>>> More Oracle Articles          >>> More By Jagadish Chatarji

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates


Dev Shed Tutorial Topics: