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

What are IN 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.

TABLE OF CONTENTS:
  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
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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).



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

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- 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: