Home » SQL & PL/SQL » SQL & PL/SQL » Procedure Explanaton
Procedure Explanaton [message #564051] Tue, 21 August 2012 02:04 Go to next message
satheesh_ss
Messages: 61
Registered: July 2012
Member
PROGRAM:

CREATE OR REPLACE PROCEDURE very_confusing (
arg1 IN VARCHAR2
, arg2 IN OUT VARCHAR2
, arg3 IN OUT NOCOPY VARCHAR2
)
IS
BEGIN
arg2 := 'Second value';
DBMS_OUTPUT.put_line ('arg2 assigned, arg1 = ' || arg1);
arg3 := 'Third value';
DBMS_OUTPUT.put_line ('arg3 assigned, arg1 = ' || arg1);
END;

Calling the above procedure in Annanymous block:


DECLARE
str VARCHAR2 (100) := 'First value';
BEGIN
DBMS_OUTPUT.put_line ('str before = ' || str);
very_confusing (str, str, str);
DBMS_OUTPUT.put_line ('str after = ' || str);
END;

str before = First value
arg2 assigned, arg1 = First value
arg3 assigned, arg1 = Third value
str after = Second value

PL/SQL procedure successfully completed.


Hi Friends,
I m little bit confused in the program which i texted above....I need explanation for the this program...Can anyone explain me?

Re: Procedure Explanaton [message #564056 is a reply to message #564051] Tue, 21 August 2012 02:59 Go to previous messageGo to next message
Michel Cadot
Messages: 59498
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous topic:

Michel Cadot wrote on Thu, 19 July 2012 08:30
Michel Cadot wrote on Tue, 17 July 2012 15:04
Michel Cadot wrote on Tue, 17 July 2012 11:43
Michel Cadot wrote on Tue, 17 July 2012 11:36
From your previous topic:

Michel Cadot wrote on Tue, 17 July 2012 11:35
From your previous topic:

Michel Cadot wrote on Sat, 14 July 2012 16:11
None.

From your previous topics:

Michel Cadot wrote on Thu, 12 July 2012 19:52
...
You not only have to read the Concepts manual but also the Database SQL Reference.

Regards
Michel


Michel Cadot wrote on Thu, 12 July 2012 15:11
From your previous topic:

Michel Cadot wrote on Thu, 12 July 2012 14:08
Please feedback to your previous topics and thanks people that (try to) help you.
Also from your previous topics:

Michel Cadot wrote on Thu, 12 July 2012 08:39
All your questions are answered in Database Concepts.
Please read it.

Regards
Michel


Michel Cadot wrote on Thu, 12 July 2012 08:47
...
Database Concepts

Regards
Michel






Regards
Michel


In addition, this is a FAQ already posted a hundred time, so SEARCH before posting (accordingly to the guide).

Regards
Michel



What is your problem in writing this?
With any SQL question, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Use SQL*Plus and copy and paste what you already tried.

Regards
Michel


...



Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.


Michel Cadot wrote on Thu, 19 July 2012 10:43
PL/SQL User's Guide and Reference
Application Developer's Guide - Fundamentals
...


As you are unable to read and comply simple rules, I'm sure you are anyway unable to read and understand any PL/SQL.

Regards
Michel

[Updated on: Tue, 21 August 2012 07:53]

Report message to a moderator

Re: Procedure Explanaton [message #564093 is a reply to message #564056] Tue, 21 August 2012 06:51 Go to previous messageGo to next message
cookiemonster
Messages: 11088
Registered: September 2008
Location: Rainy Manchester
Senior Member
Calling a procedure with the same variable for three parameters is confusing, and silly.
If you post the code formatted as you've been repeatedly asked and state what exactly you don't understand then someone might explain it to you.
Personally though I think your example is pointless.
Re: Procedure Explanaton [message #564096 is a reply to message #564093] Tue, 21 August 2012 07:00 Go to previous message
John Watson
Messages: 4709
Registered: January 2010
Location: Global Village
Senior Member
@CM, I wouldn't say it is pointless: it is a deliberately constructed case that shows what appears to be nonsensical behaviour. I'll try to answer.

Michel is correct to say "read the fine manual" (or words to that effect) but the Oracle docs will not help that much, because they teach you how to program in PL/SQL. This quesion is about programming in general, which is assumed knowledge.

So what is assumed?

@OP, You need to research these terms: formal parameters and actual parameters, pass by reference and pass by value. Then you will realise that in your example the names str, arg1, and arg3 are in fact all referring to the same memory location. So if you updarte one, in effect you update all three. arg2 is a separate memory location, you can copy value into it.
All to do with pointers, you know.
Previous Topic: Bulk Update
Next Topic: def : hierarchy query
Goto Forum:
  


Current Time: Thu Oct 30 12:30:31 CDT 2014

Total time taken to generate the page: 0.05855 seconds