6 Replies Latest reply: Feb 18, 2013 5:57 AM by Suri RSS

    Quick question about functions

    789736
      Hi, I was wondering if you could help me out. I am writting a package which makes a call to a function. The function is basically a cursor which returns the id of a row if a row meets the given criteria in the select statement.

      Before I make the call to the function I have a variable in my package, lets call it v_variable. and lets say it has a value of 10.

      My question is if I assign v_variable to the value returned from the function, what happens if the cursor in the function does not retrieve any rows, does my variable stay 10 or get assigned null. ie.

      Declare

      v_variable number;

      Begin

      v_variable := 10;

      v_variable := test_funct;

      End;

      function test_funct return number is

      l_test_id number;

      BEGIN
      select id
      into l_test_id
      from table
      where ..
      and..

      return l_test_id
      END;
        • 1. Re: Quick question about functions
          jeneesh
          You will get ora-1403, NO_DATA_FOUND exception - since exception is not handled
          • 2. Re: Quick question about functions
            APC
            jeneesh wrote:
            You will get ora-1403, NO_DATA_FOUND exception - since exception is not handled
            This is correct. If the calling block handles NO_DATA_FOUND the value of v_variable will remain whatever it was prior to the call, which is <s>20</s>10 in the example.

            Cheers, APC

            Edited by: APC on Feb 18, 2013 11:11 AM

            Tip o' the hat to Jeneesh for spotting my bloomer
            • 3. Re: Quick question about functions
              jeneesh
              APC wrote:

              which is 20 in the example.
              You mean 10 ? ;)
              • 4. Re: Quick question about functions
                APC
                jeneesh wrote:
                APC wrote:

                which is 20 in the example.
                You mean 10 ? ;)
                It was twenty in my example but I suppose you'll complain that you can't see my screen ;)

                Cheers, APC
                • 5. Re: Quick question about functions
                  BluShadow
                  APC wrote:
                  jeneesh wrote:
                  You will get ora-1403, NO_DATA_FOUND exception - since exception is not handled
                  This is correct. If the calling block handles NO_DATA_FOUND the value of v_variable will remain whatever it was prior to the call
                  Unless it 'handles' it by returning a default value that's something else. ;)
                  • 6. Re: Quick question about functions
                    Suri
                    Hi,

                    You will get NO_DATA_FOUND exception.

                    If you handle the exception using Exception Handler in function, then variable in calling block will have what ever the value you are returning in EXCEPTION block.

                    See below code
                    function test_funct return number is 
                    
                     l_test_id number;
                     
                     BEGIN
                     select id 
                     into l_test_id
                     from table
                     where ..
                     and..
                     
                     return l_test_id
                    
                    exception    -- Added by suri
                       
                       when no_data_found then
                           return (some_value);           
                     
                     END;