Adding Software Oracle Linux 5

I downloaded the one of the Oracle Developer Days VM and was trying to install subversion via the Add/Remove Software dialog (which is basically a GUI for yum). It turns out the VM you download points to Oracles internal yum servers. To fix this follow these steps (as root, the default password is oracle):

# cd /etc/yum.repos.d
# mv ULN-Base.repo to ULN-Base.repo.old
# wget http://public-yum.oracle.com/public-yum-el5.repo

Open the public-yum-el5.repo file in a text-editor and change the enabled flag for the first repository to enabled=1

Foreign Keys with missing Indexes

The following script will show any Foreign Keys that do not have a corresponding Index:

select fk.owner, fk.table_name, fk.constraint_name, fkc.column_name
from   all_constraints  fk
     , all_cons_columns fkc
where  fk.owner = :owner
and    fk.constraint_type = 'R'
and    not exists ( select null 
                    from   all_indexes ind 
                    where  ind.index_name = fk.constraint_name
                    and    ind.owner      = fk.owner )
and    fkc.owner           = fk.owner
and    fkc.table_name      = fk.table_name
and    fkc.constraint_name = fk.constraint_name
and    not exists ( select null
                    from   all_constraints  pk
                         , all_cons_columns pkc
                    where  pk.constraint_type  = 'P'
                    and    pk.owner            = fk.owner
                    and    pk.table_name       = fk.table_name
                    and    pkc.owner           = pk.owner
                    and    pkc.table_name      = pk.table_name
                    and    pkc.constraint_name = pk.constraint_name
                    and    fkc.column_name     = pkc.column_name )
order by owner, table_name, constraint_name;

CTAS (Create Table As Select)and NOT NULL Constraints

Stumbled across YAOF (Yet Another Oracle Feature) today; When performing a CTAS (Create Table As Select) NOT NULL Constraints are usually created, for example:

CREATE TABLE ctas_test
  ( id   NUMBER NOT NULL
  , name VARCHAR2(30) NULL );

CREATE TABLE ctas_test_copy AS
  SELECT * FROM ctas_test;
  
SELECT table_name, column_name, nullable 
FROM   all_tab_columns 
WHERE  table_name LIKE 'CTAS_TEST%' 
ORDER BY table_name, column_name;

As you can see the Not Null Constraints have been successfully replicated to the CTAS_TEST_COPY table. However if I now execute:

CREATE TABLE ctas_test_minus AS
  SELECT * FROM ctas_test
  MINUS
  SELECT * FROM ctas_test;
  
SELECT table_name, column_name, nullable 
FROM   all_tab_columns 
WHERE  table_name LIKE 'CTAS_TEST%' 
ORDER BY table_name, column_name;

The Not Null Constraints are missing from the CTAS_TEST_MINUS table.

Bulk Data Loading with Hibernate

The most efficient way of loading large amounts of data into Hibernate is using a StatelessSession (provided of course you don't want to keep the objects being loaded in the Persistent Context). The StatelessSession essentially works like a plain JDBC connection with the added bonus of being able to use your mapped persistent classes. You should be able to copy/paste the following into your code:

public void bulkCreate(Collection<?> entities) {
  // Open a Stateless Session (this doesn't have a persistence context cache and
  // doesn't interact with any other second-level or query cache).  Everything
  // executed with a Stateless session results in an immediate SQL operation.
  StatelessSession session = HibernateUtil.getSessionFactory().openStatelessSession();
  session.beginTransaction();
  Iterator<?> it = entities.iterator();
  while (it.hasNext()) {
    session.insert(it.next());
  }
  session.getTransaction().commit();
  session.close();
}

Confguring Java mail with JBoss AS 5 and gmail

I've been reading the excellent book: JBoss AS 5 Development and the Example in Chapter 4 creates a Mailer EJB, however the book gives no indication on how to configure Java Mail in JBoss, so I thought I'd share.

1. Navigate to:



$JBOSS_HOME/server/default/deploy



2. Open the file: mail-service.xml in your favorite text editor
   
3. Edit it as follows:



<server>

  <mbean code="org.jboss.mail.MailService" name="jboss:service=Mail">
    <attribute name="JNDIName">java:/Mail</attribute>
    <attribute name="User">${username}@gmail.com</attribute>
    <attribute name="Password">${password}</attribute>
    <attribute name="Configuration">
      <!-- A test configuration -->
      <configuration>
        <!-- Change to your mail server prototocol -->
        <property name="mail.store.protocol" value="pop3" />
        <property name="mail.transport.protocol" value="smtp" />

        <!-- Change to the user who will receive mail  -->
        <property name="mail.user" value="${username}@gmail.com" />

        <!-- Change to the mail server  -->
        <property name="mail.pop3.host" value="pop.gmail.com" />

        <!-- Change to the SMTP gateway server -->
        <property name="mail.smtp.host" value="smtp.gmail.com" />
        <property name="mail.smtp.auth" value="true" />
        <property name="mail.smtp.user" value="${username}@gmail.com" />
        <property name="mail.smtp.password" value="${password}" />
        <property name="mail.smtp.ssl.enable" value="true" />
        <property name="mail.smtp.starttls.enable" value="true" />
        <property name="mail.smtp.socketFactory.class" 
                     value="javax.net.ssl.SSLSocketFactory" />
     
        <!-- The mail server port -->
        <property name="mail.smtp.port" value="465" />
     
        <!-- Change the default address mail will be from  -->
        <property name="mail.from" value="${username}@gmail.com" />

        <!-- Enable debugging output from the javamail classes -->
        <property name="mail.debug" value="false" />
      </configuration>
    </attribute>
    <depends>jboss:service=Naming</depends>
  </mbean>
</server>

NOTE: If you are using Google Apps, simply change the gmail.com extension to your-domain.com.

Testing for a number in the ksh

Quick script for testing for a number in the ksh

#!/bin/ksh

if [[ $1 = ?([+-])+([0-9]) ]]; then
  echo "true";
else
  echo "false";
fi

Sending a mail with an attachment

I always end up googling this; so thought I'd add it to my blog - leaving me only one place to look!

To mail a single attachment (with no message):

uuencode filename.txt.gz filename.txt.gz | mailx -s subject email_address

To mail a single attachment with a message:

(cat message.txt; uuencode filename.txt.gz filename.txt.gz) | mailx -s subject email_address

To mail multiple attachments:

(cat message.txt; uuencode filename01.txt.gz filename02.txt.gz && uuencode filename02.txt.gz filename02.txt.gz) | mailx -s subject email_address