OraNA :: Oracle News Aggregator

In this month’s Database Journal piece we look at the spectrum of MySQL storage engines available, and examine what some of their strengths and weaknesses are.

View the article here: Survey of MySQL Storage Engines

Nexus is a maven repository manager. You can use Nexus to host your own maven repository for artifact created in your company, or for caching external artifacts.

Getting started with Nexus is pretty easy. Download the application. The package contains a webserver, so you don’t have to have a java container running. Simply unpack the downloaded archive and start the applications:

cd nexus/nexus-webapp-1.5.0/bin/jsw/linux-x86-32/
nexus start

Nexus has a web interface for managing your maven repository. The url for this web app is: http://localhost:8081/nexus/. The preconfigured admin account is admin, password: admin123.

According to the Nexus documentation you need to enable remote index downloads on a new installation, but it seems that this has been removed in the latest Nexus release. I couldn’t find the checkbox mentioned in Nexus 1.5.0.

Next you need to tell maven to use your maven repository. To do this, modify $HOME/.m2/settings.xml as follows:

<?xml version="1.0" encoding="UTF-8"?>
<settings xmlns="http://maven.apache.org/POM/4.0.0"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/settings-1.0.0.xsd">
  <mirrors>
    <mirror>
      <id>nexus</id>
      <mirrorOf>*</mirrorOf>
      <url>http://localhost:8081/nexus/content/groups/public</url>
    </mirror>
  </mirrors>
  <proxies></proxies>
  <servers></servers>
  <pluginGroups></pluginGroups>
  <profiles>
    <profile>
      <id>nexus</id>
      <!--Enable snapshots for the built in central repo to direct -->
      <!--all requests to nexus via the mirror -->
      <repositories>
        <repository>
          <id>central</id>
          <url>http://central</url>
          <releases><enabled>true</enabled></releases>
          <snapshots><enabled>true</enabled></snapshots>
        </repository>
      </repositories>
      <pluginRepositories>
        <pluginRepository>
          <id>central</id>
          <url>http://central</url>
          <releases><enabled>true</enabled></releases>
          <snapshots><enabled>true</enabled></snapshots>
        </pluginRepository>
      </pluginRepositories>
    </profile>
  </profiles>
  <activeProfiles>
    <activeProfile>nexus</activeProfile>
  </activeProfiles>
</settings>

To make sure you are actually using dependencies downloaded through nexus, delete all dependencies maven has already cached on your system:

rm -rf ~/.m2/repository

To test this Nexus setup, we can create a little java application using maven. The following create a java web application:

mvn archetype:create -DarchetypeGroupId=org.apache.maven.archetypes -DarchetypeArtifactId=maven-archetype-webapp -DgroupId=nl.iteye -DartifactId=App1
cd App1

You should now be able to compile and package your application:

mvn clean install

Next we’ll add the embedded glassfish maven plugin to our pom so we can test the web application. Add the plugin in the build section of your pom:

    <plugins>
         <plugin>
            <groupId>org.glassfish</groupId>
            <artifactId>maven-embedded-glassfish-plugin</artifactId>
            <version>3.0</version>
            <configuration>
               <app>${project.build.directory}/${build.finalName}.war</app>
               <port>7070</port>
               <containerType>web</containerType>
            </configuration>
         </plugin>
    </plugins>

When you try to run your application using the embedded glassfish plugin you’ll run into an error as maven will not be able to download all the required dependencies:

$:~/projects/jee/App1$ mvn embedded-glassfish:run
[INFO] Scanning for projects...
Downloading: http://localhost:8081/nexus/content/groups/public/org/glassfish/maven-embedded-glassfish-plugin/3.0/maven-embedded-glassfish-plugin-3.0.pom
[INFO] Unable to find resource 'org.glassfish:maven-embedded-glassfish-plugin:pom:3.0' in repository central (http://central)
Downloading: http://localhost:8081/nexus/content/groups/public/org/glassfish/maven-embedded-glassfish-plugin/3.0/maven-embedded-glassfish-plugin-3.0.pom
[INFO] Unable to find resource 'org.glassfish:maven-embedded-glassfish-plugin:pom:3.0' in repository central (http://central)
[INFO] ------------------------------------------------------------------------
[ERROR] BUILD ERROR
[INFO] ------------------------------------------------------------------------
[INFO] Error building POM (may not be this project's POM).

Project ID: org.glassfish:maven-embedded-glassfish-plugin

Reason: POM 'org.glassfish:maven-embedded-glassfish-plugin' not found in repository: Unable to download the artifact from any repository

  org.glassfish:maven-embedded-glassfish-plugin:pom:3.0

from the specified remote repositories:
  nexus (http://localhost:8081/nexus/content/groups/public)

 for project org.glassfish:maven-embedded-glassfish-plugin

To fix this problem we need to add the maven repository containing the glassfish plugin to Nexus. Log into Nexus using the admin account and, select repositories,and then add a proxy repository. Specify the following repository:

Next we need to add the glassfish proxy repository to the Public Repositories group:

Move the Glassfish Maven 2 Repository from available repositories to ordered group repositories:

Now you should be able to start Glassfish using maven, as Nexus will be able to find the required dependencies:

$:~/projects/jee/App1$ mvn embedded-glassfish:run
[INFO] Scanning for projects...
Downloading: http://localhost:8081/nexus/content/groups/public/org/glassfish/maven-embedded-glassfish-plugin/3.0/maven-embedded-glassfish-plugin-3.0.pom
4K downloaded  (maven-embedded-glassfish-plugin-3.0.pom)
[WARNING] *** CHECKSUM FAILED - Checksum failed on download: local = '7e111e395ec93ac48034fdf599552d547dc27618'; remote = '085185fbf2f1b3d0d36e557f4d42c9f47edc4d2c' - RETRYING
Downloading: http://localhost:8081/nexus/content/groups/public/org/glassfish/maven-embedded-glassfish-plugin/3.0/maven-embedded-glassfish-plugin-3.0.pom
4K downloaded  (maven-embedded-glassfish-plugin-3.0.pom)
[WARNING] *** CHECKSUM FAILED - Checksum failed on download: local = '7e111e395ec93ac48034fdf599552d547dc27618'; remote = '085185fbf2f1b3d0d36e557f4d42c9f47edc4d2c' - IGNORING
Downloading: http://localhost:8081/nexus/content/groups/public/org/glassfish/maven-embedded-glassfish-plugin/3.0/maven-embedded-glassfish-plugin-3.0.jar
16K downloaded  (maven-embedded-glassfish-plugin-3.0.jar)
[WARNING] *** CHECKSUM FAILED - Checksum failed on download: local = 'd6e53dbbc063ccb40aa673f0a56bafa0c2c225db'; remote = '0469be7e503a1c3df4c8ddf199b1fba3a998cca2' - RETRYING
Downloading: http://localhost:8081/nexus/content/groups/public/org/glassfish/maven-embedded-glassfish-plugin/3.0/maven-embedded-glassfish-plugin-3.0.jar
16K downloaded  (maven-embedded-glassfish-plugin-3.0.jar)
[WARNING] *** CHECKSUM FAILED - Checksum failed on download: local = 'd6e53dbbc063ccb40aa673f0a56bafa0c2c225db'; remote = '0469be7e503a1c3df4c8ddf199b1fba3a998cca2' - IGNORING
[INFO] ------------------------------------------------------------------------
[INFO] Building App1 Maven Webapp
[INFO]    task-segment: [embedded-glassfish:run]
[INFO] ------------------------------------------------------------------------
Downloading: http://localhost:8081/nexus/content/groups/public/org/glassfish/extras/glassfish-embedded-all/3.0/glassfish-embedded-all-3.0.pom
10K downloaded  (glassfish-embedded-all-3.0.pom)
Downloading: http://localhost:8081/nexus/content/groups/public/org/glassfish/extras/extras/3.0/extras-3.0.pom
2K downloaded  (extras-3.0.pom)
Downloading: http://localhost:8081/nexus/content/groups/public/org/glassfish/glassfish-parent/3.0/glassfish-parent-3.0.pom
53K downloaded  (glassfish-parent-3.0.pom)
...
INFO: Security service(s) started successfully....
classLoader = WebappClassLoader (delegate=true; repositories=WEB-INF/classes/)
SharedSecrets.getJavaNetAccess()=java.net.URLClassLoader$7@181c4eb
Mar 9, 2010 9:34:51 PM com.sun.enterprise.web.WebApplication start
INFO: Loading application App1 at /App1
Hit ENTER to redeploy, X to exit

That’s about it.

If you are just starting out with JEE CDI (Weld), it’s easy to forget the required beans.xml file. If you don’t include it, you may run into problems like this:

SEVERE: javax.el.PropertyNotFoundException: /index.xhtml @8,67 action="#{crm.ping}": Target Unreachable, identifier 'crm' resolved to null
javax.faces.el.EvaluationException: javax.el.PropertyNotFoundException: /index.xhtml @8,67 action="#{crm.ping}": Target Unreachable, identifier 'crm' resolved to null
	at javax.faces.component.MethodBindingMethodExpressionAdapter.invoke(MethodBindingMethodExpressionAdapter.java:95)
	at com.sun.faces.application.ActionListenerImpl.processAction(ActionListenerImpl.java:102)
	at javax.faces.component.UICommand.broadcast(UICommand.java:315)
...

The beans.xml file (in META-INF/classes or WEB-INF/) can be empty or contain the following:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://java.sun.com/xml/ns/javaee"
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xsi:schemaLocation="http://java.sun.com/xml/ns/javaee  http://java.sun.com/xml/ns/javaee/beans_1_0.xsd">
</beans>

I ran into the following problem yesterday while building a rest service using resteasy: the code would compile ok, but the unit tests wouldn’t run. I got the following exception in the output of the unit tests:

java.lang.ClassFormatError: Absent Code attribute in method that is not native or abstract in class file javax/ws/rs/ext/RuntimeDelegate
	at java.lang.ClassLoader.defineClass1(Native Method)
	at java.lang.ClassLoader.defineClass(ClassLoader.java:621)
	at java.security.SecureClassLoader.defineClass(SecureClassLoader.java:124)

The problem is caused by the javeee-api dependency in my maven pom. As described here, the following dependency provides you with the java-ee api’s, not the implementation. This is ok for compiling, but can’t be used for executing code.

<dependency>
  <groupId>javax</groupId>
  <artifactId>javaee-api</artifactId>
  <version>6.0</version>
</dependency>

I thought i’d solved the issue by adding a scope of provided or even compile, but this doesn’t fix the problem. The maven build still fails with the Absent code attribute… error. Removing the dependency completely solves the issue for now, but will probably give me some problems when i add more code which uses jee6.

I just released ruby-plsql version 0.4.2 which mainly adds support for more PL/SQL procedure parameter types. See change history file for more detailed list of changes.

Object types and object methods

Now you can use ruby-plsql to construct PL/SQL objects and call methods on these object. For example, if you have the following type defined:

CREATE OR REPLACE TYPE t_address AS OBJECT (
  street    VARCHAR2(50),
  city      VARCHAR2(50),
  country   VARCHAR2(50),
  CONSTRUCTOR FUNCTION t_address(p_full_address VARCHAR2)
    RETURN SELF AS RESULT,
  MEMBER FUNCTION display_address(p_separator VARCHAR2 DEFAULT ',') RETURN VARCHAR2,
  MEMBER PROCEDURE set_country(p_country VARCHAR2),
  STATIC FUNCTION create_address(p_full_address VARCHAR2) RETURN t_address
);

Then you can construct PL/SQL objects and call methods on them:

# call default constructor with named parameters
address = plsql.t_address(:street => 'Street', :city => 'City', :country => 'Country')
# call default constructor with sequential parameters
address = plsql.t_address('Street', 'City', 'Country')
# call custom constructor
address = plsql.t_address('Street, City, Country')
address = plsql.t_address(:p_full_address => 'Street, City, Country')

# returned PL/SQL object is Hash object in Ruby
address == {:street => 'Street', :city => 'City', :country => 'Country'}

# but in addition you can call PL/SQL methods on it
address.display_address == 'Street, City, Country'
address.set_country('Other') == {:street => 'Street', :city => 'City', :country => 'Other'}

# or you can call object member methods also with explicit self parameter
plsql.t_address.display_address(:self => {:street => 'Street', :city => 'City', :country => 'Other'}, :p_separator => ',') == 'Street, City, Country'

# or you can call static methods of type
plsql.t_address.create_address('Street, City, Country') == {:street => 'Street', :city => 'City', :country => 'Country'}

Record types and table of record types inside packages

Now you can call Pl/SQL procedures with parameters which have record or table of record type that is defined inside PL/SQL package. For example if you have the following package:

CREATE OR REPLACE PACKAGE test_records IS
  TYPE t_employee IS RECORD(
    employee_id   NUMBER(15),
    first_name    VARCHAR2(50),
    last_name     VARCHAR2(50),
    hire_date     DATE
  );
  TYPE t_employees IS TABLE OF t_employee;
  TYPE t_employees2 IS TABLE OF t_employee
    INDEX BY BINARY_INTEGER;
  FUNCTION test_employee (p_employee IN t_employee)
    RETURN t_employee;
  FUNCTION test_employees (p_employees IN t_employees)
    RETURN t_employees;
  FUNCTION test_employees2 (p_employees IN t_employees2)
    RETURN t_employees2;
END;

Then you can call these package functions from Ruby:

employee = {
  :employee_id => 1,
  :first_name => 'First',
  :last_name => 'Last',
  :hire_date => Time.local(2010,2,26)
}
# PL/SQL record corresponds to Ruby Hash
plsql.test_records.test_employee(employee) == employee
# PL/SQL table corresponds to Ruby Array
plsql.test_records.test_employees([employee, employee]) == [employee, employee]
# PL/SQL index-by table corresponds to Ruby Hash
plsql.test_records.test_employees({1 => employee, 2 => employee}) == {1 => employee, 2 => employee}

If you will use table types defined inside PL/SQL packages then ruby-plsql will dynamically create session specific temporary tables which will be used to pass and get table parameter values. To ensure that these session specific temporary tables will be dropped you need to explicitly call plsql.logoff to close connection. For example, if you use ruby-plsql-spec for PL/SQL unit testing then in spec_helper.rb include

at_exit do
  plsql.logoff
end

to ensure that connection will be closed with plsql.logoff before Ruby script will exit. But in case of some script failure if this was not executed and you notice that there are temporary tables with RUBY_ prefix in your schema then you can call plsql.connection.drop_all_ruby_temporary_tables to drop all temporary tables.

Establish new connection

Now there is simpler connect! method how to establish new ruby-plsql connection when you need a new connection just for ruby-plsql needs. You can do it in several ways:

plsql.connect! username, password, database_tns_alias
plsql.connect! username, password, :host => host, :port => port, :database => database
plsql.connect! :username => username, :password => password, :database => database_tns_alias
plsql.connect! :username => username, :password => password, :host => host, :port => port, :database => database

And the good thing is that this method will work both with MRI 1.8 or 1.9 or with JRuby – you do not need to change the way how you are establishing connection to database.

Savepoints

Now there is simpler way how to define savepoints and how to rollback to savepoint:

plsql.savepoint "before_something"
plsql.rollback_to "before_something"

Check validity of database objects

Now ruby-plsql will check if referenced database object is valid before trying to call it. And if it will not be valid then corresponding compilation error will be displayed. For example, if you have invalid database object:

CREATE OR REPLACE FUNCTION test_invalid_function(p_dummy VARCHAR2) RETURN VARCHAR2 IS
  l_dummy invalid_table.invalid_column%TYPE;
BEGIN
  RETURN p_dummy;
END;

then when trying to call it

plsql.test_invalid_function('dummy')

you will get the following error message:

ArgumentError: Database object 'HR.TEST_INVALID_FUNCTION' is not in valid status
Error on line    2:   l_dummy invalid_table.invalid_column%TYPE;
     position   11: PLS-00201: identifier 'INVALID_TABLE.INVALID_COLUMN' must be declared
     position   11: PL/SQL: Item ignored

Other improvements

See History.txt file for other new features and improvements and see RSpec tests in spec directory for more usage examples.