Private vs Public Global Variables

Recently I posted an article about ORA_Tweet, an Oracle Twitter Client. I was asked by someone reading the code why I put several variables in the BODY of the package rather than the SPEC. The question was posed something like this:

Why not put the variables in the package spec where they are modifiable? That would involve less maintenance.

I specifically put them in the body so that they are not modifiable. I don’t see public global variables as a particularly good thing. To understand why, let’s take a look at the benefits of a package.

First, you can group logical functions and procedures together. This makes maintenance easier by giving a developer one (or just a couple of) place(s) to go when doing maintenance. You can also hide functions and procedures that logically apply to the package function but that aren’t needed outside of that functionality; helper functions.

Second is performance. Run a package and the entire package, and all of its associated functionality, is loaded in memory and available to call.

Most important to my perspective is the dependency chain. When you publish a package specification, anyone can use that spec. You can make modifications to the body and never impact anyone else. It is only when you change the spec that you will cause issues for someone.

The reason I put the variables in the body is to protect them from random changes and to protect my specification. Let’s look at an example. I have seen, and even created, a package of global variables. In my case, a package of global constants. All over my applications, I referenced this global package. It looked something like:

CREATE OR REPLACE PACKAGE test_vars
AS
  g_char_var VARCHAR2(30) := 'ABCD';
END;

Now, that’s fine. But in this case, I did not define my PUBLIC global variable as a constant, which means anyone can change it. Probably not my intention. It will also cause a cascading effect in dependencies when I need to change.

I’ll create a new procedure that takes advantage of my global variable (and yes, normally I would put even this procedure in a package of its own).

CREATE OR REPLACE PROCEDURE test_spec
AS
BEGIN
  DBMS_OUTPUT.PUT_LINE(test_vars.g_char_var);
END;

I compile that and then I check it’s status:

SELECT status 
FROM user_objects
WHERE object_name = 'TEST_SPEC';

STATUS  
------- 
VALID   

1 rows selected

Looks fine. Oops, I need a number variable also. I change my spec and add a number:

CREATE OR REPLACE PACKAGE test_vars
AS
  g_char_var VARCHAR2(30) := 'ABCD';
  g_char_num NUMBER := 1111;
END;

I compile it and check the status of my other procedure (which I did nothing to impact):

CREATE OR REPLACE PACKAGE test_vars
AS
  g_char_var VARCHAR2(30) := 'ABCD';
  g_char_num NUMBER := 1111;
END;
/

SELECT status 
FROM user_objects
WHERE object_name = 'TEST_SPEC';

STATUS  
------- 
INVALID 

1 rows selected

Not so fine. In the normal course of business, the next time my procedure is called, it will recompile automatically (after at least one exception). In a highly available system (24/7), that’s not a good thing. We can break the chain (at least somewhat) by making the PUBLIC globals, PRIVATE globals.

Yes, this will involve some extra code. And it won’t always help. We will create a getter function to encapsulate the variables. In our case of adding a number variable, we would still have to change the spec, but we would only have to change it for the first one.

CREATE OR REPLACE PACKAGE test_vars
AS
  FUNCTION get_char_var(
    p_var_name IN VARCHAR2 )
    RETURN VARCHAR2;
    
  FUNCTION get_num_var(
    p_var_name IN VARCHAR2 ) 
    RETURN NUMBER;
END;

And our package body:

CREATE OR REPLACE PACKAGE BODY test_vars
AS
  g_char_var VARCHAR2(30) := 'ABCD';
  g_char_num NUMBER := 1111;
  
  
  FUNCTION get_char_var(
    p_var_name IN VARCHAR2 )
    RETURN VARCHAR2
  AS
  BEGIN
    IF lower(p_var_name) = 'g_char_var'
    THEN 
      RETURN g_char_var;
    ELSE
      RETURN NULL;
    END IF;
  END;  
    
  FUNCTION get_num_var(
    p_var_name IN VARCHAR2 ) 
    RETURN NUMBER
  AS
  BEGIN
    IF lower(p_var_name) = 'g_char_num'
    THEN 
      RETURN g_char_var;
    ELSE
      RETURN NULL;
    END IF;
  END;  
END;

We now change our procedure to take advantage of the new getter functions. Notice that we can no longer set the values, to do that would require explicit code. If we wanted to set them, we could add a parameter for a new value or create a setter function.

CREATE OR REPLACE PROCEDURE test_spec
AS
BEGIN
  DBMS_OUTPUT.PUT_LINE(test_vars.get_char_var('g_char_var'));
END;

Now, the only way that my procedure will be invalidated is if I change the spec. I can add a new variable and change the body of my get function and the external proc does not invalidate. I have compiled my code and run my valid check:

SELECT status 
FROM user_objects
WHERE object_name = 'TEST_SPEC';

STATUS
-------
VALID

I change the body and add a new variable.

CREATE OR REPLACE PACKAGE BODY test_vars
AS
  g_char_var VARCHAR2(30) := 'ABCD';
  g_char_var2 VARCHAR2(30) := 'GHIJ';
  g_char_num NUMBER := 1111;
  
  
  FUNCTION get_char_var(
    p_var_name IN VARCHAR2 )
    RETURN VARCHAR2
  AS
  BEGIN
    IF lower(p_var_name) = 'g_char_var'
    THEN 
      RETURN g_char_var;
    ELSIF lower(p_var_name) = 'g_char_var'   
    THEN 
      RETURN g_char_var2;
    ELSE
      RETURN NULL;
    END IF;
  END;  
    
  FUNCTION get_num_var(
    p_var_name IN VARCHAR2 ) 
    RETURN NUMBER
  AS
  BEGIN
    IF lower(p_var_name) = 'g_char_num'
    THEN 
      RETURN g_char_var;
    ELSE
      RETURN NULL;
    END IF;
  END;  
END;

I compile that and then run my valid check; it’s still valid.

SELECT status 
FROM user_objects
WHERE object_name = 'TEST_SPEC';

STATUS
-------
VALID

In a highly available environment, availability can override anything, even performance. In this case, not only does it help make the application more available, it also helps performance and maintenance. A win-win for everyone. Obviously, this example is trivially simple. The same concept applies and as the application gets larger and more complex, with more and more interdependencies, thinking about the dependency chain becomes even more critical.

LewisC

Technorati : , , ,

You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.

1 Comment »

 
  • JT says:

    Good post Lewis.  I generally try to avoid globals.  Usually, nothing but headaches later.
    I’m pretty sure having them in the spec will lead to “ORA-04068: existing state of packages has been discarded” errors for any active session.  Which is not a good thing in a HA environment.
    Another exposure is the controlling process changing the value of the global. Meaning between calls to the package the calling program could change the value of the global.
    If memory serves me right, both are due to the package state living for the life of the session, not the transaction or call.