5 Replies Latest reply: Nov 21, 2008 10:44 AM by sabi-in RSS

    difference between package function and stored function

    542647
      Hi can anyone tell me any special difference between package function and stored function other then package function calls with dot notation prefix by package name.
        • 1. Re: difference between package function and stored function
          old dba
          A package can contain multiple procedures, functions, variables, and definitions. You issue grants on the package not on the functions it contains. A major difference is that when the code of a function in a package needs to change, you can replace the package body (create or replace syntax) and this will not cause invalidation of dependant code. A change to a function outside a package will always cause invalidation of dependant code.
          • 3. Re: difference between package function and stored function
            BluShadow
            Also,

            Functions within a package body that are not declared in the package specification are considered "private" functions that can only be used by that package, whereas a stored function (standalone) will be accessible to everything with permissions in the schema.

            Another "special" feature of functions in packages is that you can use function overloading which you can't do with standalone functions...
            SQL> create function transform_x(x number) return number is
              2  begin
              3    return x+1;
              4  end;
              5  /
            
            Function created.
            
            SQL> create function transform_x(x varchar2) return varchar2 is
              2  begin
              3    return chr(ascii(x)+1);
              4  end;
              5  /
            create function transform_x(x varchar2) return varchar2 is
                            *
            ERROR at line 1:
            ORA-00955: name is already used by an existing object
            
            
            SQL> drop function transform_x;
            
            Function dropped.
            
            SQL> ed
            Wrote file afiedt.buf
            
              1  create or replace package tf as
              2    function transform_x (x number) return number;
              3    function transform_x (x varchar2) return varchar2;
              4* end;
            SQL> /
            
            Package created.
            
            SQL> ed
            Wrote file afiedt.buf
            
              1  create or replace package body tf as
              2    function transform_x (x number) return number is
              3    begin
              4      return x+1;
              5    end;
              6    function transform_x (x varchar2) return varchar2 is
              7    begin
              8      return chr(ascii(x)+1);
              9    end;
             10* end;
            SQL> /
            
            Package body created.
            
            SQL> select tf.transform_x(5) from dual;
            
            TF.TRANSFORM_X(5)
            -----------------
                            6
            
            SQL> select tf.transform_x('A') from dual;
            
            TF.TRANSFORM_X('A')
            ------------------------------------------------------------------------------
            B
            
            SQL>
            • 4. Re: difference between package function and stored function
              sabi-in
              One more thing whenever we call any procedure,function of the package whole package body(related procedure functions) load in memory.
              Means it will save our I/O from disk.
              • 5. Re: difference between package function and stored function
                Frank Kulash
                Hi,

                Also, procedures (and functions, but I'll just say "proceudres") in a package can be overloaded, that is, you can have two or more procedures with the same name, but different numbers or types of arguments.
                Stand-alone procedures cannot be overloaded.