4 Replies Latest reply: Jul 19, 2013 5:25 AM by Sven W. RSS

    violates its associated pragma

    user9359353

      Hi,

      Please find below the example code


      Oracle version 11.2.0.3.0


      1. Package test which I've declared the global variable

      CREATE OR REPLACE PACKAGE test IS

        "TEST" CONSTANT BOOLEAN := TRUE;

      END test;

       

      2. Package sepcification

      CREATE OR REPLACE PACKAGE test2 IS

      FUNCTION m (

         x IN VARCHAR2 ) RETURN NUMBER;

      PRAGMA restrict_references(m, WNDS);

      END test2;

       

      3. Package Body

      CREATE OR REPLACE PACKAGE BODY test2 IS

       

        FUNCTION m(x IN VARCHAR2) RETURN NUMBER IS

          b_ BOOLEAN;

        BEGIN

        b_ := TEST."TEST";  -- This is the place where I'm calling to the CONSTANT and the reason for the pragma violation

         return 1; 

      END m;

       

      END test2;

       

      I'm wondering why I get this pragma violation error, I'm not changing any database state here.

       

      I'm confused need help.

       

      Thanks in advance and cheers,

      Dark.

        • 1. Re: violates its associated pragma
          Sven W.

          To call a global constant from a different package seems to violate the rnds and wnds pragma.

          Why? No idea.

          On the other hand, why do you need the pragma? I never had the need for it anymore since Oracle 7.

           

          ...

           

          I think I understand it now. The compiler encounters a package that has no pragma set, therefore it assumes that this different package is writing database state. And this is correct! Since after the second package has been compiled, you could recompile the first package body and add some ddl to it (even in the main section).

           

          You can workaround this using two options.

          1) Don't use pragma.

          2) Use a getter method and declare this getter method also as WNDS.

           

          example

           

          drop package test1;
          drop package test2;
          
          CREATE OR REPLACE PACKAGE test1 IS
            c_TEST varchar2(1) := 'Y';
            function getTest return varchar2; 
            PRAGMA restrict_references(getTest, WNDS);
          END test1;
          /
          CREATE OR REPLACE PACKAGE body test1 IS
            function getTest return varchar2
            is
            begin
                 return c_test; 
            end getTest;
          END test1;
          /
          CREATE OR REPLACE PACKAGE test2 IS
            c_TEST2 varchar2(1) := 'Y';
            
            FUNCTION m (x IN VARCHAR2 ) RETURN NUMBER; 
            PRAGMA restrict_references(m, WNDS);
          END test2;
          /
          CREATE OR REPLACE PACKAGE BODY test2 IS
            FUNCTION m(x IN VARCHAR2) RETURN NUMBER IS
              b varchar2(1);
            BEGIN
              b := test1.getTEST;  -- This is the place where I'm calling to the CONSTANT and the reason for the pragma violation
              return 1;  
            END m;
          END test2;
          /
          
          package TEST1 gelöscht.
          package TEST2 gelöscht.
          PACKAGE TEST1 kompiliert
          PACKAGE BODY TEST1 kompiliert
          PACKAGE TEST2 kompiliert
          PACKAGE BODY TEST2 kompiliert
          
          
          
          

           

          Message was edited by: SvenW. -- added explaination and workaround

          • 2. Re: violates its associated pragma
            user9359353

            Thank you for your answer,

             

            Here I'm just only concerning about the this error, not the workaround

             

            I didn't get your point exactly could you give me more details?

             

            Cheers,

            Dark.

            • 3. Re: violates its associated pragma
              user9359353

              Few things I've found

               

              When you add a function declaration like below it won't give any errors anymore...  Is this an oracle bug.. ?

               

              CREATE OR REPLACE PACKAGE test IS

                "TEST" CONSTANT BOOLEAN := TRUE;

               

                FUNCTION mm(x IN VARCHAR2) RETURN NUMBER;

                PRAGMA restrict_references(mm, WNDS);

              END test;

               

               

              Or obviously if you add pragma to the package like below it won't give any errors 

              PRAGMA restrict_references(test , WNDS);


              Cheers,

              Dark.

              • 4. Re: violates its associated pragma
                Sven W.

                ...  Is this an oracle bug.. ?

                 

                It is not a bug, it is working as intended.

                 

                Reason is, as I tried to explain, that the decision needs to be done at compile time.

                That means, the compiler will check at compile time if any objects are called that violate the pragma.

                 

                If a function in package2 has the WNDS pragma, then it is not allowed to call any other functions, procedures or packages that potentially can violate this pragma.

                The call of another package is such a violation, because there no pragma was set. Changes to the first packge body would not trigger a recompilation of the second package. Therefor the pragma in the specification of the first package is needed.

                 

                I wonder if it would be possible to set the pragma for the whole package to avoid implementing those getter functions.