?
Solved

Oracle Procedure Parameter

Posted on 2003-03-04
3
Medium Priority
?
621 Views
Last Modified: 2008-02-01
Hi All,

Can any one tell me about the difference and functionality of IN , OUT , INOUT type parameter in procedure , what will be the functionality for each one and how they will effect for package and my program.

ajay
0
Comment
Question by:ajay_erdci
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 5

Expert Comment

by:jpkemp
ID: 8070114
IN: parameter is passed in
OUT: parameter is passed out
IN OUT: parameter is passed in and passed out

For IN, the argument value in the caller will not change, even if the procedure changes it internally.

For OUT, the argument value cannot be read by the procedure until the procedure itself writes it.

For OUT and IN OUT, the argument must be a writeable variable (i.e. not a literal).

Jeff
0
 
LVL 1

Author Comment

by:ajay_erdci
ID: 8070353
Dear Jeff,

can you explain it with any example then only it will be fisible for me.

Thanks in advance,
ajay
0
 
LVL 5

Accepted Solution

by:
jpkemp earned 300 total points
ID: 8076353
PROCEDURE myfunction (
  a IN NUMBER,
  b OUT NUMBER,
  c IN OUT NUMBER ) IS
BEGIN
  dbms_output.put_line('In myfunction');
  dbms_output.put_line('A=' || a);
  --dbms_output.put_line('B=' || b); --This is illegal
  dbms_output.put_line('C=' || c);
  dbms_output.put_line('Processing');
  --a := 1; --This is illegal
  b := 2;
  c := 3;
  dbms_output.put_line('A=' || a);
  --dbms_output.put_line('B=' || b); --This is illegal
  dbms_output.put_line('C=' || c);
END;

...

PROCEDURE test
  x NUMBER;
  y NUMBER;
  z NUMBER;
BEGIN
  x := 4;
  y := 5;
  z := 6;
  dbms_output.put_line('X=' || x);
  dbms_output.put_line('Y=' || y);
  dbms_output.put_line('Z=' || z);
  myfunction(x, y, z);
  dbms_output.put_line('In test');
  dbms_output.put_line('X=' || x);
  dbms_output.put_line('Y=' || y);
  dbms_output.put_line('Z=' || z);
  --myfunction(1,2,3); -- this is illegal
END;

-----

Expected output of test:

X=4
Y=5
Z=6
In myfunction
A=4
C=6
Processing
A=4
C=3
In test
X=4
Y=2
Z=3

Analyse the above output and you'll see.
The last function call ("myfunction(1,2,3)") is illegal because OUT and IN OUT parameters must be given arguments which are writable variables, not literals or constants.

Jeff
0

Featured Post

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question