4 Replies Latest reply: Oct 12, 2011 5:11 AM by Pleiadian RSS

    compiling recursive standalone function

    Pleiadian
      Hi,

      I am a bit confused by the following. If you have a recursive standalone function, e.g.
      create or replace function test return number is
      begin
        if true then
          return 0;
        else
          return test;
        end if;
      end test;
      It won't compile, because Oracle does not (yet) recognise the reference to the function itself.

      I get two different errors:

      >
      PLS-00905: object "test" is invalid
      Cause: An invalid package specification or stored subprogram was referenced. A package specification or stored subprogram is invalid if its source code or any database object it references has been DROPped, REPLACEd, or ALTERed since it was last compiled.
      Action: Find out what invalidated the package specification or stored subprogram, then make sure that Oracle can recompile it without errors.
      >

      after dropping the invalid object and try to recompile it returns
      >
      PLS-00201: identifier "test" must be declared
      Cause: You tried to reference either an undeclared variable, exception, procedure, or other item, or an item to which no privilege was granted or an item to which privilege was granted only through a role.
      Action: 1) Check your spelling and declaration of the referenced name. 2) Verify that the declaration for the referenced item is placed correctly in the block structure. 3) If the referenced item is indeed declared but you don"t have privileges to refer to that item, for security reasons, you will be notified only that the item is not declared. 4) If the referenced item is indeed declared and you believe that you have privileges to refer to that item, check the privileges; if the privileges were granted only via a role, then this is expected and documented behavior. Stored objects (packages, procedures, functions, triggers, views) run in the security domain of the object owner with no roles enabled except PUBLIC. Again, you will be notified only that the item was not declared.
      {quote}

      The second error suggests a privilege issue, but I am not sure how can this be an issue when the function is created in the schema of the user...

      BTW, we are on Oracle Database 10g Enterprise Edition Release 10.2.0.4.0

      Thanks you in advance!

      Rob

      addition:

      You can compile this in two steps:

      first

      {code}
      create or replace function test return number is
      begin
      if true then
      return 0;
      else
      null;
      --return test;
      end if;
      end test;{code}

      and then

      {code}
      create or replace function test return number is
      begin
      if true then
      return 0;
      else
      return test;
      end if;
      end test;{code}


      Edited by: Pleiadian on Oct 12, 2011 11:40 AM -- clarification, additional example
        • 1. Re: compiling recursive standalone function
          32685
          Hello

          It works fine for me
          SQL> create or replace function test return number is
            2  begin
            3    if true then
            4      return 0;
            5    else
            6      return test;
            7    end if;
            8  end test;
            9
           10
           11
           12  DROP FUNCTION test
           13
           14
           15  .
          SQL> create or replace function test return number is
            2  begin
            3    if true then
            4      return 0;
            5    else
            6      return test;
            7    end if;
            8  end test;
            9  /
          
          Function created.
          
          Elapsed: 00:00:00.06
          SQL> sho err
          No errors.
          SQL>
          SQL> DROP FUNCTION test
            2  /
          
          Function dropped.
          
          Elapsed: 00:00:00.06
          So do you have any other objects named test in your schema i.e. tables, views etc. Just to note, it might be an idea to name your objects something other than "test".

          David
          • 2. Re: compiling recursive standalone function
            Toon Koppelaars
            If you want to define a recursive function it would have to be a packaged function. Inside the package you'll also need to 'forward declare' that function.

            Edit:
            Hmm. I stand corrected. Learned something today. I always do (did) this in packages...

            Edited by: Toon Koppelaars on Oct 12, 2011 11:45 AM
            • 3. Re: compiling recursive standalone function
              Pleiadian
              Thanks David,

              Strange...

              I am getting closer to the problem. It seems to be related to PL/SQL developer. I CAN compile the example directly in SQL*Plus, but not in PL/SQL Developer.

              Of course, the function is not really called test, it's just an anonymised example.
              • 4. Re: compiling recursive standalone function
                Pleiadian
                Bedankt Toon,

                We normally use packages for all code, developers who create standalone functions will be publicly tarred and feathered.

                In this case however, it is a piece of third party code, so we have to take it as is.