Oracle SQL Schema and Text Index Size

I recently had to determine the size of my schema and the size of the text indexes used in that schema. These SQL statements got me the results:

SQL> select sum(bytes) from dba_segments where owner='SCHEMA_NAME' and SEGMENT_NAME like '%$%';

SUM(BYTES)
----------
6663307264

SQL> select sum(bytes) from dba_segments where owner='SCHEMA_NAME';

SUM(BYTES)
----------
9464446976

Oracle SQL Developer and Oracle Text

In the SQL Editor in Oracle's excellent SQL Developer tool, if you are running queries using an Oracle Text Index, you will run into issues when using the CONTAINS operator and searching for terms such as ${MySearchTerm}. The dollar-bracket syntax is used to do stemmng, but the SQL editor interprets them as bind variables and prompts you for their value. To turn off the bind variable interpretation you can precede the SQL statement with the following command:

set define off;

You only need to do this once and it will remain in effect for the rest of your usage of that SQL worksheet.

Unix Math Calculations

I just ran across http://x-bc.sourceforge.net/index.html. If you've ever used the Unix bc calculator utility, this project provides two excellent extensions to the built in capabilities of the calculator. In particular:

http://x-bc.sourceforge.net/extensions_bc.html

http://x-bc.sourceforge.net/scientific_constants_bc.html

You'll get a wealth of math functions available to you by simply including the extension files avaiable at those URLs.

In order to have these extensions always loaded as well turn on the built in math extensions, you can alias your bc command as follows:

alias bc="bc -l ~/bin/*.bc"

Apache Tomcat DBCP Connection Pooling

I've been looking at some performance issues with our Tomcat servers (we have many) and have seen some real inconsistency in the DBCP options that are specified. In general the basic ones you'd expect to see are there: a validation query, minIdle, maxActive. However, other ones are missing. This page http://commons.apache.org/dbcp/configuration.html lists the options and gives a good explanation of each. Everyone using Tomcat and DBCP should review this list and understand them.

A couple of interesting options to point out are those dealing with the validity of idle connections in the pool:

testWhileIdle (default false)

The indication of whether objects will be validated by
the idle object evictor (if any). If an object fails to
validate, it will be dropped from the pool. NOTE - for
a true value to have any effect, the validationQuery
parameter must be set to a non-null string.

timeBetweenEvictionRunsMillis (default -1)

The number of milliseconds to sleep between runs of the
idle object evictor thread. When non-positive, no idle
object evictor thread will be run.

numTestsPerEvictionRun (default 3)

The number of objects to examine during each run of the
idle object evictor thread (if any).

minEvictableIdleTimeMillis (default 1000 * 60 * 30)

The minimum amount of time an object may sit idle in
the pool before it is eliga

A good blog post on Apache DBCP can be found on Roy's Musings under Gotchas with DBCP.

For a comparison of Apache DBCP with another connection pool see Vigil Bose's Blog.

Also check out BoneCP for a connection pool specifically written to be fast.

Change a text cell into a Hyperlink in Excel

I recently imported a CSV file I was given into Excel. The file had two columns (with many rows of data) that contained URLs in the format http://foo.com/bar?phuid=1023, etc. I wanted to make these links clickable. Excel provides a straight forward, but manual way to do this. I asked around for an automated way to do this and was quickly given this macro:

Sub URL_List()
   For Each cell In Selection
       If cell.Value <> "" Then
           If Left(cell.Value, 7) = "http://" Then
               URL = cell.Value
           Else
               URL = "http://" + cell.Value
           End If
           ActiveSheet.Hyperlinks.Add Anchor:=cell, _
             Address:=URL, TextToDisplay:=cell.Value
       End If
   Next cell
End Sub

I don't know the source of this macro it proved useful today. I hope you can make some use of it.

The directions on creating a macro in Excel can be found in Excel's help which I'll repeat here:

Create a macro using Microsoft Visual Basic

1. On the Tools menu in Microsoft Excel, point to Macro, and then click Visual Basic Editor.


2. On the Insert menu, click Module.


3. Type or copy your code into the code window of the module.


4. If you want to run the macro (macro: An action or a set of actions that you can use to automate tasks. Macros are recorded in the Visual Basic for Applications programming language.) from the module window, press F5.


5. When you're finished writing your macro, click Close and Return to Microsoft Excel on the File menu.

Looking for Temporary Java Web Developer

Java Web Developer for temporary position at Stanford



The School of Medicine, Stanford University (http://med.stanford.edu/irt/) is looking for a strong, fast-learning Java Web Developer for a highly visible, complex web application.

Applicants must have the following skills:

- Java 6 or Java 5

- Object Oriented Design

- X/HTML coding

- CSS styling

- JSP, JSTL, and JSTL/EL

- Strong understanding of Java Servlets

- JDBC and SQL

- Hibernate 3.2

- Oracle 11g

- JUnit, Unit Testing

- QA Skills

Applications should have

- A good eye for web page design and layout

- A good eye for consistency in UI design and Java coding

- A strong focus on usability and user advocacy

- A strong work ethic and the ability to work well with a team

- Great communication skills

Applicants should be comfortable with the following tools:

- Eclipse IDE

- Oracle SQL Developer

- Ant Build Tool

- Apache Tomcat Servlet Container

- Apache Web Servers

- Developing on a Windows XP machine


Assignment will start at the beginning of January and last through April 30, 2010. After developing the new features for this applications, the applicant will be part of the QA team testing the application and resolving issues.


This is a non-benefited, temporary position.

Hours: Mon – Fri / approximately 9 to 6 (with an hour lunch break).

Location: Menlo Park, near CalTrain

Pay: $50 - $70/hour based on experience


You would be working in a fast paced environment with an expert team of web developers and report directly to the Director of Engineering.


Please forward your resume and have references, including at least one supervisor, available to:

Don Mitchell
donald.mitchell@stanford.edu
Director of Systems Engineering & Architecture
Stanford University School of Medicine

Vim Tip - replace comma-separated list of values in XML with individually tagged items

A twitter posting at http://stackoverflow.com/questions/1457537/help-with-grep-in-bbedit/ (formerly http://spreadsheets.google.com/viewform?formkey=dFR1ajFXOEVvd0JsRWNTWktVQzNfNUE6MA..) asked how to convert an XML tag that contained a comma-separated list of values into individually tagged items, each on their own line. They asked how to do this in BBEdit, which I don't use. This is how to do it in Vim:

:%s/<dc:subject>\(\_[^<]\+\)<\/dc:subject>/\=substitute(submatch(0), ",[ \t]\*", "<\/dc:subject>\r<dc:subject>", "g")/g

The above Vim search-replace command will handle tags that span multiple lines and multiple tagged lists on the same line. I use [ \t]\* in the substitute() call because the atom \s (any whitespace) did not work. I had to explicitly list a space and a tab as shown.

\_[^<]\+ matches the entire contents between the original opening and closing tag.

The \=substitute(submatch(0)...) replace string executes a new search and replace command on the matched text. It's this that replaces the comma, optionally followed by a space, with a new closing tag and a newline (\r).