Nested Procedures/Functions in Oracle ! [message #35195] |
Mon, 31 January 2005 18:17 |
Anthuvan
Messages: 37 Registered: August 2004
|
Member |
|
|
Hi,
1) Is't possible to write one procedure within another procedure similarly one function within another function (or) procedure within a function and function within a procedure without using a package.
2) How to return the control back to the calling procedure if the inner procedure not have any return statement?
3) From outside of the nested procedure can i directly call the inner procedure/function?
If possible can u tell me wht's the need of it or any rules for creating those kind of nested objects.
rgds,
Anthuvan.
|
|
|
Re: Nested Procedures/Functions in Oracle ! [message #35197 is a reply to message #35195] |
Mon, 31 January 2005 19:36 |
Jai Vrat Singh
Messages: 205 Registered: September 2002 Location: Singapore
|
Senior Member |
|
|
1) No
2) Procedure cannot have a return value like a function. Control is automatically passed back from where it is called. You can get the modified values by providing OUT or IN OUT paramater in the parameter list of the called procedure. The changes made to these parameter in the called procdure will be there. This value can be used by the calling procedure.
3) There is nothing like inner function procedure..This is remnant of question 1..
4) Whenever you need such related objects to be created, use packages.
|
|
|
Re: Nested Procedures/Functions in Oracle ! [message #35207 is a reply to message #35197] |
Mon, 31 January 2005 22:32 |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
Correction:
ONTW>create or replace procedure tst
2 as
3 l_variable varchar2(100);
4 procedure inner
5 ( p_variable in varchar2
6 ) is
7 begin
8 dbms_output.put_line('In inner: '||p_variable);
9 end;
10 begin
11 l_variable := 'Hi!';
12 dbms_output.put_line('In outer: '||l_variable);
13 inner(l_variable);
14 end;
15 /
Procedure created.
ONTW>exec tst
In outer: Hi!
In inner: Hi!
PL/SQL procedure successfully completed.
So, the answer is YES. It is possible to create a nested procedure.
hth
|
|
|
Re: No Suitable Answers, Anybody pls clarify it ! [message #35218 is a reply to message #35195] |
Tue, 01 February 2005 05:13 |
Anthuvan
Messages: 37 Registered: August 2004
|
Member |
|
|
Hi,
Without confident dont post ur answers bcos v r all know there is nested procedures & functions in oracle. I want to know more in details about it so, pls dont interfere the forum without assured knowledge or with of ur own assumption.
Anthuvan.
|
|
|
Re: No Suitable Answers, Anybody pls clarify it ! [message #35223 is a reply to message #35218] |
Tue, 01 February 2005 06:35 |
Ajendra
Messages: 165 Registered: February 2004
|
Senior Member |
|
|
Anthuvan
It may not be correct that all people going through this forum knows this particular answer. I recall at certain point of time I had to learn it. Before that the answer to this question would have been of help to me.
And I feel the forum for people like us is to get assured knowledge rahter than the other way.
Ajendra
|
|
|
Re: No Suitable Answers, Anybody pls clarify it ! [message #35232 is a reply to message #35223] |
Tue, 01 February 2005 15:51 |
Jai Vrat Singh
Messages: 205 Registered: September 2002 Location: Singapore
|
Senior Member |
|
|
Why do you need that! man-made things can change on demand .. this is in one version... can be off in some other.. no need to tell why standards are made... You cannot guess somebody's dog's name until you come to know somehow.. You very well know the products made by a human being are not universal as mathematics... try to find out the redundatnt info and let us know....
|
|
|
Re: Nested Procedures/Functions in Oracle ! [message #35233 is a reply to message #35207] |
Tue, 01 February 2005 15:57 |
Jai Vrat Singh
Messages: 205 Registered: September 2002 Location: Singapore
|
Senior Member |
|
|
Hi Frank,
I am unaware of this. Which scenarios are they used and are best suitable for ? Is that to share global variables and for putting related objects together? why to prefer this for a package ? or these are two ways to achieve the same objective?
Thanks.
|
|
|
Re: Nested Procedures/Functions in Oracle ! [message #35238 is a reply to message #35233] |
Tue, 01 February 2005 21:17 |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
IMHO this should indeed not be used. I find it a horrible way to code and I prefer to use packages.
I've seen it being used a lot to define a small helper function, like a logging procedure, a numeric tester etc.
Indeed, in the inner procedure/function you have access to the variables (might call them 'globals') declared in the main procedure/function.
Besides the fact procedures should not be used if possible (packages are safer, less compilation-dependencies etc), this construct should be avoided.
But, then again, this is just my humble opinion...
hth
|
|
|
|
|