BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

   
  Oracle Tips by Burleson

IN, OUT and INOUT Modes

Values are passed to a procedure in three modes; IN, OUT and INOUT.  The mode which a variable is passed defines how the variables can be used inside the procedure.  Let’s take a closer look at each mode.

IN Mode

A variable passed as mode IN is always read-only.  A variable using IN mode can be read and used by the procedure/function but can not be changed and it cannot be the receiver of an assignment operation.  Internal to the scope of the procedure or function, variables pass using IN mode can be considered a constant.  The IN mode is the default mode to pass a variable, however it is recommended for maintainability reasons to always define the variable passing mode when you define the variable.  Variables passed IN can also be assigned a default value as discussed above.

In the example below, three variables are defined as IN variables.  Note that on line 7 the code attempts to assign the variable n_1 the sum of the other two variables.  This procedure fails on compile because n_1 was assigned a mode of IN and therefore can not be used in an assignment.

SQL> create or replace procedure example_defaults
  2    (n_1 in number := 5,
  3     n_2 in number := 6,
  4     n_3 in number := 7)
  5  as
  6  begin
  7    n_1 := n_2 + n_3;
  8  end;
  9  / 

Warning: Procedure created with compilation errors. 

SQL> show errors
Errors for PROCEDURE EXAMPLE_DEFAULTS: 

LINE/COL ERROR
-------- ------------------------------------------------7/3      PLS-00363: expression 'N_1' cannot be used as an
assignment target 

7/3      PL/SQL: Statement ignored

OUT Mode

A variable passed in OUT mode  is used to pass information back from the procedure to the calling program.  It is a write-only variable and has no value until the block assigns it a value.  Internally, an OUT variable is created and not initialized when the procedure is called.  When the procedure ends, the variable value (upon ending) is copied to the variable passed in the call.  As such, a variable passed in OUT mode can not be assigned a default value nor can it be read inside the procedure.  Because the variable value is copied back to the passed variable when the procedure terminates, the calling code can not pass an OUT variable a literal value.  If the procedure raises an exception that is not caught, it will result in the OUT variable not being copied when the procedure terminates. 

SQL> create or replace procedure example_defaults
  2    (n_1 in number := 5,
  3     n_2 in number := 6,
  4     n_3 out number := 7)
  5  as
  6  begin
  7    null;
  8  end;
  9  / 

Warning: Procedure created with compilation errors. 

SQL> show errors
Errors for PROCEDURE EXAMPLE_DEFAULTS:
 

LINE/COL ERROR
-------- -------------------------------------------
4/4      PLS-00230: OUT and IN OUT formal parameters may not have default expressions

INOUT Mode

A variable passed in INOUT mode has characteristics of both the IN and the OUT mode.  The variable value is passed in and can be read by the procedure.  The procedure can also change the value and it will be copied back to the passed variable when the procedure completes.  Like a variable passed in OUT mode, an INOUT variable can not have a default value and can not be passed as a literal.  If the procedure terminates abnormally (as in an exception) the INOUT variable will not be copied back to the variable passed in.


The above book excerpt is from:

Easy Oracle PL/SQL Programming

Get Started Fast with Working PL/SQL Code Examples

ISBN 0-9759135-7-3   

John Garmany 

http://www.rampant-books.com/book_2005_1_easy_plsql.htm

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.