Forum Stats

  • 3,780,462 Users
  • 2,254,398 Discussions
  • 7,879,337 Comments

Discussions

Advantages of procedure over packages

Deepak patra
Deepak patra Member Posts: 15
edited Jun 27, 2018 2:26PM in SQL & PL/SQL

Hello Folks ,

Can anyone explain me about the best advantages of procedure over packages ?

Thank you !!

Frank KulashDeepak patragaverillMustafa_KALAYCIBilly Verreynne
«1

Answers

  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited Jun 26, 2018 10:56AM
    Deepak patra wrote:Hello Folks ,Can anyone explain me about the best advantages of procedure over packages ?Thank you !! 

    Allows you to create even more objects in your DB, makes you look like a good developer if you are responsible for thousands of procedures rather than 10s of packages.

    In all serious though, there are none. Packages are better in every way.

    Frank Kulash
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,370 Red Diamond
    edited Jun 26, 2018 10:57AM

    Hi,

    Deepak patra wrote:Hello Folks ,Can anyone explain me about the best advantages of procedure over packages ?Thank you !! 

    There aren't many advantages of stand-alone procedures over procedures in packages.

    It's slightly easier to call a stand-alone procedure:

    proc_name (arg1, arg2);

    rather than a procedure in a package:

    pkg_name.proc_name (arg1, arg2);

    You can create synonyms for stand-alone procedures.

    I hardly ever use stand-alone procedures.

    Deepak patra
  • jaramill
    jaramill Member Posts: 4,299 Gold Trophy
    edited Jun 26, 2018 11:12AM
    Deepak patra wrote:Hello Folks ,Can anyone explain me about the best advantages of procedure over packages ?Thank you !! 

    Adding to what others have said, read the official wording from the documentation

    Reasons to Use Packages

    --> https://docs.oracle.com/cd/E11882_01/appdev.112/e25519/packages.htm#LNPLS00902

    Deepak patra
  • Unknown
    edited Jun 26, 2018 12:23PM
    Can anyone explain me about the best advantages of procedure over packages ?

    Sorry - but those are two VERY DIFFERENT things.

    Can you tell us the advantage of an orange over a basket?

    Pointless to compare two things like that.

    An orange is a piece of fruit.

    A basket is a container that might hold pieces of fruit and might hold other things as well.

    A procedure executes code.

    A package is a container that might hold procedures and might hold other things as well.

    Billy Verreynne
  • jaramill
    jaramill Member Posts: 4,299 Gold Trophy
    edited Jun 26, 2018 12:27PM

    To add to rp0428's comments, the question you should be asking is "What is/are the advantage(s) of writing a stand-alone sub-program, instead of a package with a sub-program?"  Sub-program being a procedure or function.

    Deepak patra
  • Sven W.
    Sven W. Member Posts: 10,534 Gold Crown
    edited Jun 26, 2018 1:07PM
    Deepak patra wrote:Hello Folks ,Can anyone explain me about the best advantages of procedure over packages ?Thank you !! 

    Short answer: There is no advantage.

    Always use packages with packaged procedures.

    Packaged procedures are superior to standalone procedures in terms of security, performance and maintainability.

    (ok I know some scenarios where packages are not an option, but I do not want to mention those).

  • Mark D Powell
    Mark D Powell Member Posts: 5,914 Blue Diamond
    edited Jun 26, 2018 1:24PM

    Deepak, a stored procedure is appropriate for use when you have a specific standalone action to be performed. 

    - -

    "a subprogram that performs a specific action"

    https://docs.oracle.com/database/121/LNPLS/create_procedure.htm#LNPLS01373

    - -

    Where you have several closely related actions that need to be performed to complete a business process but each is a specific activity then you can collect these together as part of a package for better manageability.  Packages also provide a means of ordering and controlling access to the stored procedures by providing both public and private procedures.

    - -

    In part the choice between the two is a matter of preference, but packages help organize and manage code while stored procedures are good for standalone functionality.

    - -

    IMHO -- Mark D Powell --

  • Etbin
    Etbin Member Posts: 8,968 Gold Crown
    edited Jun 26, 2018 1:57PM
  • Mustafa_KALAYCI
    Mustafa_KALAYCI Member Posts: 3,373 Bronze Crown
    edited Jun 26, 2018 3:31PM

    for me, I would prefer package but I wouldn't say "nothing" over procedures. packages are copied into session pga area. I know it is not much but for a db which has thousands of sessions and hundreds of packages may require too much pga area in the memory. secondly if you change a package spec/body which may cause to an re-validation, every session which has used this package will get an error: "ORA-04068: existing state of packages has been discarded" and require recall the package. in that case user may get confused about the error but procedures does not raise this error because they are not copied into pga area of sessions. that way they consume less memory (of course this is arguable, memory usage of package vs procedures might be insignificant) because there will be only one copy.

    gaverill
  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited Jun 26, 2018 6:13PM
    Mustafa KALAYCI wrote:for me, I would prefer package but I wouldn't say "nothing" over procedures. packages are copied into session pga area. I know it is not much but for a db which has thousands of sessions and hundreds of packages may require too much pga area in the memory. secondly if you change a package spec/body which may cause to an re-validation, every session which has used this package will get an error: "ORA-04068: existing state of packages has been discarded" and require recall the package. in that case user may get confused about the error but procedures does not raise this error because they are not copied into pga area of sessions. that way they consume less memory (of course this is arguable, memory usage of package vs procedures might be insignificant) because there will be only one copy. 

    The ORA-04068 error will only raise if your package has global variables, something you can't achieve with stand alone procedures. This error will only occur if you've instantiated the package (by calling it in some way), it gets recompiled, and then you call it again. In an ideal world, you would be using EBR so only new sessions would be touching the new edition of a package, so this would never happen anyway.

    The separation of package specification and package body means that dependencies are much less prone to invalidation - dependencies are against the specification, not the body; so when you tweak how you do something in a procedure/function, you don't need to recompile everything else that uses it. This is not possible with stand alone procedures.

    Difference in memory requirements are going to be negligible, it's only going to be a waste if you have one package that you use to contain very different stored procedures, and even then no one is every going to notice this. 

    I'm not sure if you were saying there was a difference in where packages / stand alone procs live in memory: all are compiled into the sga and when either is executed by a session they must allocate space in pga.

    Mustafa_KALAYCI
This discussion has been closed.