Home » SQL & PL/SQL » SQL & PL/SQL » Nested Procedures/Functions in Oracle !
Nested Procedures/Functions in Oracle ! [message #35195] Mon, 31 January 2005 18:17 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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
Re: No Suitable Answers, Anybody pls clarify it ! [message #35239 is a reply to message #35218] Tue, 01 February 2005 21:21 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Could you please explain why my answer is 'no suitable answer' before ranting and insulting people WHO ARE JUST TRYING THEIR BEST TO HELP YOU!
If you are not satisfied by the answers given here, RTFM. It's all there!

hth
Re: No Suitable Answers, Anybody pls clarify it ! [message #35240 is a reply to message #35239] Tue, 01 February 2005 22:05 Go to previous message
Jai Vrat Singh
Messages: 205
Registered: September 2002
Location: Singapore
Senior Member
Well Said Frank!!... Unfriendly people deserve this...

Anthuvan!! if it had been my forum, i would have requested you to leave..
Previous Topic: Bind variable
Next Topic: ORA-00922: missing or invalid option
Goto Forum:
  


Current Time: Fri Apr 26 08:02:00 CDT 2024