RE: Recursive Function

From: SRIDHARAN, SAN (SBCSI) <ss4569_at_sbc.com>
Date: Thu, 6 May 2004 16:41:55 -0700
Message-ID: <D363ADA29513DD4C879B4275920327DE667053@cafrfd1msgusr02.itservices.sbc.com>

Mark,

It worked. Thanks for your time and suggestion.

San

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mark Richard Sent: Thursday, May 06, 2004 4:32 PM
To: oracle-l_at_freelists.org
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_at_freelists.org>

                      <ss4569_at_sbc.com>          cc:

                      Sent by:                  Subject:  Recursive
Function                                                        =20
                      oracle-l-bounce_at_fr

                      eelists.org

=20

                      07/05/2004 09:19

                      Please respond to

                      oracle-l

=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_at_freelists.org 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_at_freelists.org
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_at_freelists.org
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


-----------------------------------------------------------------

Received on Thu May 06 2004 - 18:39:14 CDT