RE: Recursive Function

Mark,

It worked. Thanks for your time and suggestion.

San

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Mark Richard
Sent: Thursday, May 06, 2004 4:32 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Recursive Function






You need to consider that the recursive function keeps calling itself in
the ELSE clause of your code until it reaches the correct point.  Then
the
innermost call returns it's parent - code execution returns to the ELSE
clause of the previous call, where you assign the result to a variable
and
do nothing more with it.

The easiest way out is to replace v_val in the ELSE clause with RETURN.
When you look at your code you will see you are never doing anything
with
v_val anyway.  Alternatively leave the "v_val :=3D" line and add "return
v_val" on the line below - depending on your coding style.

Regards,
      Mark.



=20

                      "SRIDHARAN, SAN

                      (SBCSI)"                  To:
<oracle-l@xxxxxxxxxxxxx>

                      <ss4569@xxxxxxx>          cc:

                      Sent by:                  Subject:  Recursive
Function                                                        =20
                      oracle-l-bounce@fr

                      eelists.org

=20

=20

                      07/05/2004 09:19

                      Please respond to

                      oracle-l

=20

=20





Here is a recursive function that I wrote to find the grand-grand parent
value for the input I pass in the function.

However, when I call this function, it returns null all the time. What
is strange is the DBMS_OUTPUT prints the value that I need.

Any comments/suggestions/pointers are most welcome.




CREATE OR REPLACE FUNCTION Recursive_function (p_parent IN NUMBER)
   RETURN NUMBER
IS
   v_parent   NUMBER (5);
   v_val      NUMBER(5);
BEGIN
   SELECT PARENT_ID=3D20
     INTO v_parent
     FROM DSCR_CONTENT
    WHERE DSCR_CONTENT_ID  =3D3D p_parent;

   IF v_parent IS NULL
   THEN
      DBMS_OUTPUT.PUT_LINE (To_char(P_parent));
      RETURN (p_parent);
   ELSE
      v_val :=3D3D Recursive_function (v_parent);
   END IF;
END Recursive_function;
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------





<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Privileged/Confidential information may be contained in this message.
If you are not the addressee indicated in this message (or responsible
for delivery of the message to such person), you may not copy or deliver
this message to anyone.
In such a case, you should destroy this message and kindly notify the
sender by reply e-mail or by telephone on (03) 9612-6999 or (61) 3
9612-6999.
Please advise immediately if you or your employer does not consent to
Internet e-mail for messages of this kind.
Opinions, conclusions and other information in this message that do not
relate to the official business of Transurban Infrastructure
Developments Limited and CityLink Melbourne Limited shall be understood
as neither given nor endorsed by them.
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: