Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: Function Philosophy

    Does anyone have a preference over creating stand-alone functions, vs collecting multiple functions into a single package? Or perhaps a general rule-of-thumb as to when to create one over the other?

    Thanks,
    Chuck

  2. #2
    Join Date
    Jan 2004
    Posts
    492
    Personally I think its best to encapsulate "similar" function in packages just for logical grouping. For instance based on the sample schema provided by Oracle, you could have an emp_functions package. One function could return the employee for a given employee number. Another could provide the manager's name...etc etc.

    I hate having standalone functions simply b/c its sometimes to tell what the function's purpose is without having to give it a huge title.

    The purpose of a package is to logically group similar program units, so use them as intended! Of course there is no performance difference, its just whatever makes the most sense in your situation.
    Oracle OCPI (Certified Practicing Idiot)

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I agree with ss659, always use packages for "production" code. It reduces the number of database objects to be managed - e.g. you can grant execute privilege to many functions at once when they are in a package. Then you can add further functions to the package without having to do any further grants, create synonyms, etc.

    As an analogy: it's a bit like deciding whether to organise your PC files into folders, or just keep them all on C:\.
    Last edited by andrewst; 10-07-04 at 06:29.

  4. #4
    Join Date
    Feb 2004
    Posts
    108
    Yes, you can bunch-up similar functions under one package.
    But, do keep in mind that call to one function from the package will make the package loaded in memory.

  5. #5
    Join Date
    Mar 2004
    Posts
    23
    I think you can make arguments either way. If you have stand-alone functions and want to modify one, you just re-compile that one function. If you have many functions in a package (some might call this a library) and need to change one of them, you have to re-compile the whole package. However, maintaining one package is a lot easier than maintaining 100 stand-alone functions. I personally have never written a stand-alone function but use a function package/library and many applications I have written use this library and the performance is good. I believe you can designate a package to be 'pinned' in memory also.

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I wouldn't be too concerned about packages using more memory - as long as the packages are designed sensibly, with logically related procedures. If you just had one package called everything_pkg with 5000 procedures in it, that would be silly!

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by lluf44
    I think you can make arguments either way. If you have stand-alone functions and want to modify one, you just re-compile that one function. If you have many functions in a package (some might call this a library) and need to change one of them, you have to re-compile the whole package. However, maintaining one package is a lot easier than maintaining 100 stand-alone functions. I personally have never written a stand-alone function but use a function package/library and many applications I have written use this library and the performance is good. I believe you can designate a package to be 'pinned' in memory also.
    It's not so much the maintaining as the managing that is made easier by packages. Instead of having 5000 procedures and functions swishing around in your schema, each with one or more GRANT EXECUTEs and possible its own public or private synonym(s), you have 50 or 100 packages. That's an order of magnitude or two easier to manage.

    I believe the need to pin packages disappeared from 8i onwards.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •