13 Replies Latest reply: Jul 12, 2013 12:35 PM by 9423755 RSS

    Package body variables

    9423755

      Two questions:

      a) Is a package body variable a global construct?

      b) Does a package body variable retain its value throughout the user session?

       

      Thanks.

        • 1. Re: Package body variables
          509894

          For both of them yes

           

          check :

          PL/SQL Packages

          • 2. Re: Package body variables
            9423755

            Thank you meser.

            My book says no to both questions. My own checking leads me to believe the answer to both questions is yes.

            • 3. Re: Package body variables
              John Spencer

              A variable declared in the package body is not really any different than a variable declared in a procedure or function within the package except for the scope.  The global variable is visible to all procedures and functions in the package, but it is not visible outside of the package.  Any procedure in the package can change the value of the variable, which will affect the current session only, other sessions can have different values.

               

              A variable declared in the package specification is the same, except that it is visible outside of the package.

               

              John

              • 4. Re: Package body variables
                myOra_help

                Package body variable is actually PRIVATE to body only (procedures/function of that package can access it) and retains its values to session level. But the variable declared in Package specification is GLOBAL (can access by proc/func/pakag etc if they have execute privilege on it) and retains its values to session level. Once session expires both private/global variable are lost.

                 

                a) Is a package body variable a global construct?

                Ans. No, Global means accessible to all those having execute privilege on package, but package body variable can be accessible only from package body itself, not outside from it.(see above)

                b) Does a package body variable retain its value throughout the user session?

                Ans. yes, as suggested above.

                • 5. Re: Package body variables
                  9423755

                  myOra_help wrote:

                   

                  Package body variable is actually PRIVATE to body only (procedures/function of that package can access it) and retains its values to session level. But the variable declared in Package specification is GLOBAL (can access by proc/func/pakag etc if they have execute privilege on it) and retains its values to session level. Once session expires both private/global variable are lost.

                   

                  a) Is a package body variable a global construct?

                  Ans. No, Global means accessible to all those having execute privilege on package, but package body variable can be accessible only from package body itself, not outside from it.(see above)

                  b) Does a package body variable retain its value throughout the user session?

                  Ans. yes, as suggested above.

                   

                  Hello myOra,

                   

                  Regarding a), this is what Steven Feuerstein writes:

                   

                  A global variable is a data structure that can be referenced outside the scope or block in which it is declared. A variable declared at the package level (outside any
                  individual procedure or function in that package) is global at one of two levels:

                  1. If the variable is declared in the package body, then it is globally accessible to all programs defined within that package.
                  2. If the variable is declared in the package specified, then it is accessible to (and directly referenceable by) any program executed from a schema that has EXECUTE authority on that package.


                  Read more at http://www.codewalkers.com/c/a/Database-Articles/Best-Practices-for-PLSQL-Variables/3/#Z6yHLikXPMMBm7RT.99
                  - See more at: http://www.codewalkers.com/c/a/Database-Articles/Best-Practices-for-PLSQL-Variables/3/#sthash.VWCNxsLI.dpuf

                  • 6. Re: Package body variables
                    9423755

                    Thanks John; that's what I was thinking.

                    • 7. Re: Package body variables
                      BluShadow

                      b) Does a package body variable retain its value throughout the user session?

                      Answer: Not necessarily.

                      Variables declared in a package spec/body are part of the "package state".   If the package or one of it's dependent objects becomes invalidates, it's possible for the package state to be discarded as the package becomes invalidated (there is better granularity of dependencies in 11g onwards so less chance of packages becoming invalidated as easily).

                       

                      I wrote about package states many moons ago...

                       

                      Re: [b]current state of the package has been discarded[/b]

                       

                      (bear in mind that was demonstrated on 10g and as I say, it's slightly different on 11g now)

                       

                      So that is why your book probably says that the answer is No, because the variables are not necessarily existent for the whole of the session as they could end up getting discarded, though it's a little harsh an answer, because if something is coded properly, then the package state shouldn't get discarded.

                      • 8. Re: Package body variables
                        myOra_help

                        Yes I agree, see point number one,

                        • If the variable is declared in the package body, then it is globally accessible to all programs defined within that package.

                         

                        that is what have wrote ...package body variable can be accessible only from its package body and offcourse package body is constructed by its programs (proc/func). Here global means programs declare in package specification as well as programs definded in Package Body only (Private Programs) all are able to access it as it is globally defines for them. It can only be access via programs defined in Package body not via package_name.variable_name.

                         

                        If we donot have any programs/cursor definition to define we do not require the package body at all.

                         

                        Hope it clears your doubt.

                        • 9. Re: Package body variables
                          9423755

                          The problem with these kinds of questions is that they end up on certification exams. Questions (and answer choices) should have zero room for ambiguity, but they probably in many cases do not. So you're left wasting time thinking "well, yes, generally that's true, but do they mean, absolutely always, ever, that that must be the case".  I mean, you can take it to ridiculous levels...what if somebody pulls the plug on the DB for instance.

                           

                          In this case the question is from a book on the old 1z0-101 exam.

                          • 10. Re: Package body variables
                            9423755

                            Ok, thanks.

                            • 11. Re: Package body variables
                              Sven W.

                              Jason_942375 wrote:

                              ...what if somebody pulls the plug on the DB for instance.

                              If somebody pulled the plug then the session has ended. Problem is more, some other session recompiles the package (body). Then the package will loose its session scope as soon as it is not used anymore.

                              • 12. Re: Package body variables
                                BrendanP

                                The term 'global' applied to a variable is probably too simplistic to be useful now. It originated with simple languages like early Basic but now variables in languages like PL/SQL have a wider range of possible scopes than the binary global/local. A package body variable may be best described simply as having package body scope.

                                • 13. Re: Package body variables
                                  9423755

                                  I agree.