Solving package dependency problem using synonyms

Problem I take part in supporting an ODS system. One of the biggest problem during deployment of revisions is the dependency between packages. Suppose a package A has to be changed and that it is referenced be two packages: B1 and B2 which are used in some database sessions. If the the A package is altered and it contained some global variables then the next execution of the packages B1 or B2 in those sessions triggers “ORA-04061: existing state of package has been invalidated” error. This is because global variables preserve their values during database sessions. Alteration of the package code may change the variables, delete some of them, add others. Therefore, they have to be initialized again. Reseting the global variables during the session could lead to inconsistency. In order to prevent this. Oracle throws the ORA-04061 exception. This mechanism is described in details on the page 

http://oraclequirks.blogspot.com/2007/03/ora-04061-existing-state-of-package-has.html

In addition, alteration of the package body or package specification is not possible as long as it used in a session. The replacing package DDL (CREATE OR REPLACE PACKAGE [BODY]) waits until no session uses the package. This may never happen – if new session starts using package before the previous one stops doing that. In order to change the package – all sessions using it must be killed. It is quite common that a session cannot be killed right now, but is moved into MARKED FOR KILLED state. As a result, the deployment of new package version is postponed for indefinite period of time until the session is really killed. I even created a script which kills all sessions which blocks any session started from my computer by my OS user. The script is really crude but it works and is really useful so I am posting it here.

DECLARE
  CURSOR c_sql_hist(pp_machine VARCHAR2, pp_osuser VARCHAR2) IS
    SELECT ash.blocking_session, ash.blocking_session_status, ash.blocking_session_serial#, s.program, s.module, s.action
      FROM v$active_session_history ash
      JOIN v$session s ON ash.blocking_session = s.sid
                      AND ash.blocking_session_serial# = s.serial#
     WHERE ash.session_id in ( select sid from v$session where machine = pp_machine
     and osuser = pp_osuser )
       AND ash.sample_time > SYSDATE - 2 / 24 / 3600
     ORDER BY sample_time DESC;
  v_r c_sql_hist%ROWTYPE;

  v_my_machine v$session.MACHINE%TYPE;
  v_my_osuser v$session.osuser%TYPE;
BEGIN
  select machine, osuser into v_my_machine, v_my_osuser from v$session
  where sid = ( Select Sid from v$mystat where rownum = 1);

  for i in 1..10000 loop  -- just to prevent infinite execution
    dbms_output.put_line('step: ' || i );
    OPEN c_sql_hist(v_my_machine, v_my_osuser);
    FETCH c_sql_hist
      INTO v_r;
    IF NOT c_sql_hist%NOTFOUND THEN
      dbms_output.put_line(v_r.blocking_session || ' ' || v_r.blocking_session_status || ' ' || v_r.blocking_session_serial# || ' ' || v_r.program || ' ' ||
                           v_r.module || ' ' || v_r.action);
      execute immediate ' ALTER SYSTEM KILL SESSION ''' || v_r.blocking_session || ',' || v_r.blocking_session_serial# || '''';
    else
      exit;
    END IF;
    CLOSE c_sql_hist;
    dbms_lock.sleep(0.5); -- sleep is used to get new blocking sessions
  end loop;
END;

In order to reduce the dependency problems described above, various tool packages, which should be located in one central schema, were copied to all schemas. As a result, they may be changed without killing all (only some) processes. However, this solution is terrible from the point of view of code maintenance.

Solution

The solution to this problem is to reference packages by synonyms. If the package need to be changed – a new version of it is installed with a different name. After it is tested – the synonym is altered to point the new version. The nearest execution of this synonym within a session causes the new package to be executed. Its global variables are initialized, their previous values are lost but this is not a problem in our ODS as they work as a cache and does not hold any state between executions. There is an additional limitation that one procedure or package cannot be executed concurrently is its instances use different object through the same synonym. Suppose that after B1 is executed, the A synonym is changed to point to A_v2. If B1 is executed again (directly or indirectly) then the second execution of B1 will not start before the old execution using the old package is finished. However, this is not really a problem. The solution described above allows to deploy database code efficiently. It is however necessary to remember how this mechanism works and what the limitations are. A naming convention needs also to be developed (for example adding the result of TO_CHAR(sysdate, ‘$MM_DD’), which is dollar sing, month and day of deployment, to the package name). It is also necessary to give other schemas the same grant to new package version as were given to the old package version.

You May Also Like

How to automate tests with Groovy 2.0, Spock and Gradle

This is the launch of the 1st blog in my life, so cheers and have a nice reading!

y u no test?

Couple of years ago I wasn't a big fan of unit testing. It was obvious to me that well prepared unit tests are crucial though. I didn't known why exactly crucial yet then. I just felt they are important. My disliking to write automation tests was mostly related to the effort necessary to prepare them. Also a spaghetti code was easily spotted in test sources.

Some goodies at hand

Now I know! Test are crucial to get a better design and a confidence. Confidence to improve without a hesitation. Moreover, now I have the tool to make test automation easy as Sunday morning... I'm talking about the Spock Framework. If you got here probably already know what the Spock is, so I won't introduce it. Enough to say that Spock is an awesome unit testing tool which, thanks to Groovy AST Transformation, simplifies creation of tests greatly.

An obstacle

The point is, since a new major version of Groovy has been released (2.0), there is no matching version of Spock available yet.

What now?

Well, in a matter of fact there is such a version. It's still under development though. It can be obtained from this Maven repository. We can of course use the Maven to build a project and run tests. But why not to go even more "groovy" way? XML is not for humans, is it? Lets use Gradle.

The build file

Update: at the end of the post is updated version of the build file.
apply plugin: 'groovy'
apply plugin: 'idea'

def langLevel = 1.7

sourceCompatibility = langLevel
targetCompatibility = langLevel

group = 'com.tamashumi.example.testwithspock'
version = '0.1'

repositories {
mavenLocal()
mavenCentral()
maven { url 'http://oss.sonatype.org/content/repositories/snapshots/' }
}

dependencies {
groovy 'org.codehaus.groovy:groovy-all:2.0.1'
testCompile 'org.spockframework:spock-core:0.7-groovy-2.0-SNAPSHOT'
}

idea {
project {
jdkName = langLevel
languageLevel = langLevel
}
}
As you can see the build.gradle file is almost self-explanatory. Groovy plugin is applied to compile groovy code. It needs groovy-all.jar - declared in version 2.0 at dependencies block just next to Spock in version 0.7. What's most important, mentioned Maven repository URL is added at repositories block.

Project structure and execution

Gradle's default project directory structure is similar to Maven's one. Unfortunately there is no 'create project' task and you have to create it by hand. It's not a big obstacle though. The structure you will create will more or less look as follows:
<project root>

├── build.gradle
└── src
├── main
│ ├── groovy
└── test
└── groovy
To build a project now you can type command gradle build or gradle test to only run tests.

How about Java?

You can test native Java code with Spock. Just add src/main/java directory and a following line to the build.gradle:
apply plugin: 'java'
This way if you don't want or just can't deploy Groovy compiled stuff into your production JVM for any reason, still whole goodness of testing with Spock and Groovy is at your hand.

A silly-simple example

Just to show that it works, here you go with a basic example.

Java simple example class:

public class SimpleJavaClass {

public int sumAll(int... args) {

int sum = 0;

for (int arg : args){
sum += arg;
}

return sum;
}
}

Groovy simple example class:

class SimpleGroovyClass {

String concatenateAll(char separator, String... args) {

args.join(separator as String)
}
}

The test, uhm... I mean the Specification:

class JustASpecification extends Specification {

@Unroll('Sums integers #integers into: #expectedResult')
def "Can sum different amount of integers"() {

given:
def instance = new SimpleJavaClass()

when:
def result = instance.sumAll(* integers)

then:
result == expectedResult

where:
expectedResult | integers
11 | [3, 3, 5]
8 | [3, 5]
254 | [2, 4, 8, 16, 32, 64, 128]
22 | [7, 5, 6, 2, 2]
}

@Unroll('Concatenates strings #strings with separator "#separator" into: #expectedResult')
def "Can concatenate different amount of integers with a specified separator"() {

given:
def instance = new SimpleGroovyClass()

when:
def result = instance.concatenateAll(separator, * strings)

then:
result == expectedResult

where:
expectedResult | separator | strings
'Whasup dude?' | ' ' as char | ['Whasup', 'dude?']
'2012/09/15' | '/' as char | ['2012', '09', '15']
'nice-to-meet-you' | '-' as char | ['nice', 'to', 'meet', 'you']
}
}
To run tests with Gradle simply execute command gradle test. Test reports can be found at <project root>/build/reports/tests/index.html and look kind a like this.


Please note that, thanks to @Unroll annotation, test is executed once per each parameters row in the 'table' at specification's where: block. This isn't a Java label, but a AST transformation magic.

IDE integration

Gradle's plugin for Iintellij Idea

I've added also Intellij Idea plugin for IDE project generation and some configuration for it (IDE's JDK name). To generate Idea's project files just run command: gradle idea There are available Eclipse and Netbeans plugins too, however I haven't tested them. Idea's one works well.

Intellij Idea's plugins for Gradle

Idea itself has a light Gradle support built-in on its own. To not get confused: Gradle has plugin for Idea and Idea has plugin for Gradle. To get even more 'pluginated', there is also JetGradle plugin within Idea. However I haven't found good reason for it's existence - well, maybe excluding one. It shows dependency tree. There is a bug though - JetGradle work's fine only for lang level 1.6. Strangely all the plugins together do not conflict each other. They even give complementary, quite useful tool set.

Running tests under IDE

Jest to add something sweet this is how Specification looks when run with jUnit  runner under Intellij Idea (right mouse button on JustASpecification class or whole folder of specification extending classes and select "Run ...". You'll see a nice view like this.

Building web application

If you need to build Java web application and bundle it as war archive just add plugin by typing the line
apply plugin: 'war'
in the build.gradle file and create a directory src/main/webapp.

Want to know more?

If you haven't heard about Spock or Gradle before or just curious, check the following links:

What next?

The last thing left is to write the real production code you are about to test. No matter will it be Groovy or Java, I leave this to your need and invention. Of course, you are welcome to post a comments here. I'll answer or even write some more posts about the subject.

Important update

Spock version 0.7 has been released, so the above build file doesn't work anymore. It's easy to fix it though. Just remove last dash and a word SNAPSHOT from Spock dependency declaration. Other important thing is that now spock-core depends on groovy-all-2.0.5, so to avoid dependency conflict groovy dependency should be changed from version 2.0.1 to 2.0.5.
Besides oss.sonata.org snapshots maven repository can be removed. No obstacles any more and the build file now looks as follows:
apply plugin: 'groovy'
apply plugin: 'idea'

def langLevel = 1.7

sourceCompatibility = langLevel
targetCompatibility = langLevel

group = 'com.tamashumi.example.testwithspock'
version = '0.1'

repositories {
mavenLocal()
mavenCentral()
}

dependencies {
groovy 'org.codehaus.groovy:groovy-all:2.0.5'
testCompile 'org.spockframework:spock-core:0.7-groovy-2.0'
}

idea {
project {
jdkName = langLevel
languageLevel = langLevel
}
}