Alessandro Lacava

on Designing and Developing Software. In love with Functional Programming.

Using Dynamic SQL Statements From PL/SQL

Sometimes you need to execute dynamic SQL statements. Starting from Oracle8i you can accomplish this task using the EXECUTE IMMEDIATE statement. Here are three examples of how you can take advantage of this great statement.

1
2
3
4
sql_select := 'SELECT * FROM your_table WHERE field1 = :1';
EXECUTE IMMEDIATE sql_select
INTO your_cursor
USING your_parameter_for_field1;

In this first example I showed how you can use EXECUTE IMMEDIATE to execute the query and put the result into a cursor. The USING your_parameter_for_field1 part replaces the :1 bind variable with the value contained in the your_parameter_for_field1 parameter.

Now let’s see another example:

1
2
3
4
sql_select := 'SELECT * FROM your_table WHERE field1 = :1 AND field2 = :2';
EXECUTE IMMEDIATE sql_select
INTO your_cursor
USING your_parameter_for_field1, your_parameter_for_field2;

As you can see this example is similar to the previous one but here I showed how you can use more than one parameter.

One last example:

1
2
3
sql_insert := 'INSERT INTO your_table VALUES (:1, :2)';
EXECUTE IMMEDIATE sql_insert
USING your_parameter_for_field1, your_parameter_for_field2;

This last example shows how you can use EXECUTE IMMEDIATE to execute an INSERT statement. Here you don’t need an explicit cursor. Of course, in a similar way, you can use EXECUTE IMMEDIATE to execute UPDATE and DELETE statements as well.

Of course, you could have accomplished the “same” result using string concatenation but binding the variables using the method just shown is much better from a performance viewpoint.

One last note: you CANNOT use bind variables to replace actual object names like table names, columns and so on. In this case you can turn back to string concatenation.

How to Hide/show an HTML Form Element Depending on a Combo Box Choice

This is an example of how you can show/hide an HTML form element depending on a combo box choice.

Put the following JavaScript code between your <head></head> section (or within a .js file if you prefer).

1
2
3
4
5
6
7
8
9
10
11
<script language="javascript" type="text/javascript">
  function hide() {
    var text = document.formName.textBox;
    if(document.formName.combo.value == "hide") {
      text.style.visibility = "hidden";
    }
    else {
      text.style.visibility = "visible";
    }
  }
</script>

The following snippet of code instead is the HTML code to use to call the hide function.

1
2
3
4
5
6
7
<form name="formName">
  <select name="combo" onchange="hide()">
    <option value="show">show</option>
    <option value="hide">hide</option>
  </select>
  <input type="text" name="textBox" id="textBox" />
</form>

It is very simple. When you choose hide the textbox disappears. If you choose show it appears.

POJO (Plain Old Java Object): The simpler… The Better.

A POJO is simply an object built using a Java class that does not implement any special interfaces such as those defined by the EJB 2 framework. An example of a POJO is a class composed by only:

  1. Properties, representing the object’s state.
  2. Getter and setter methods for the properties.
  3. Business methods, representing behaviour.

Some properties can represent associations with other POJOs. Here is an example of implementation of the Person entity using a POJO:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
public class Person {
  //1. properties
  private String firstName;
  private String secondName;
  private Address address;

  //2. getters and setters
  public void setFirstName(String firstName) {
    this.firstName = firstName;
  }
  public String getFirstName() {
    return firstName;
  }
  // ... the other getters and setters

  //3. business methods
  public int computeAge() {
    //here the business logic to compute the person's age
  }
}

As you can see the address property represents an association to another hypothetical POJO.

That’s all! Code that handles database connections and so on shouldn’t be contained within a POJO.

There are many advantages in using POJOs. First of all they’re simple. Second they’re very suitable to represent domain models. Many tools, like Hibernate, work best with domain models implemented using POJOs.

Calling a Function of the Opener Window

It is possible, using JavaScript, to call a function pertaining to the opener window, that is the window that, calling window.open, opened the current window. The code to use is the following:

1
2
3
4
if (window.opener) {
  window.close();
  window.opener.foo("bar");
}

First it checks if the opener window is still open. In this case, it closes the current window and call the foo function on the opener window.

Creating a Class Definition In JavaScript

There are different ways to define classes in JavaScript. However, this is the most widely used and accepted at the moment:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
//class
function Person(sLastName, sFirstName, iAge) {
  this.lastName = sLastName;
  this.firstName = sFirstName;
  this.age = iAge;
  this.phoneNumbers = new Array();
}

//method
Person.prototype.showFullName = function() {
  alert(this.lastName + " " + this.firstName);
};

//instances
var oPerson1 = new Person("Lacava", "Alessandro", 30);
var oPerson2 = new Person("Brown", "John", 50);
oPerson1.phoneNumbers.push("1234567");
oPerson2.phoneNumbers.push("7654321");

oPerson1.showFullName(); //outputs Lacava Alessandro
alert(oPerson1.phoneNumbers); //outputs 1234567
oPerson2.showFullName(); //outputs Brown John
alert(oPerson2.phoneNumbers); //outputs 7654321

How to Force One or More Metacharacters to Be Treated as Ordinary Characters In a Java Regular Expression (RegEx)

When using RegEx in Java you might face the need of treating one or more metacharacters as ordinary characters. As a reminder the metacharacters in a Java RegEx are:

([{^$|)?*+.

If you want to treat them as ordinary characters you have two options:

  1. Escape the metacharacter with a backslash,
  2. Enclose the whole string that contains metacharacters within Q and E

Q means: “quotes all characters until E”, while E ends the quotes.

The following example will hopefully to clarify the subject:

1
2
3
String test = "I want to replace the . with the ,";
String replaced = test.replaceAll(".", ",");
System.out.println(replaced);

What do you expect the above method will do? Do you think the following string will be displayed?

I want to replace the , with the ,

If yes then you might be surprised to find out that what you really get is instead:

,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

The problem is that the replaceAll method of the String class accept a RegExp as its first parameter. Since . means any character, so writing test.replaceAll(".", ","); is translated in: “Replace ANY character of the test string with a comma”. As I said previously you can fix that in two ways. Either you escape the . with a or enclose it within Q and E. What I didn’t say is that, since the is a metacharacter itself, you need to escape it too. :-)

Translating this in Java you have:

1
2
test.replaceAll("\.", ",");
test.replaceAll("\Q.\E", ",");

I prefer to use the first method when the metacharacter is just one. However, when I have more metacharacters or I don’t know at compile time what my string is going to be, I use the second method.

Creating an Instance of the Object Used to Make AJAX Calls

Nowadays, AJAX is a ubiquitous technology in the IT world. When you need to create the object used to send asynchronous requests to a server, you might face the browser-difference problem. Here is a JavaScript function you could use to overcome this problem:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
// The following function creates an XMLHttpRequest object
function createHttpRequest() {
  if (typeof XMLHttpRequest != "undefined") //NOT IE {
    return new XMLHttpRequest();
  }
  else if (window.ActiveXObject) // IE {
    var sVersions = [ "MSXML2.XMLHttp.5.0",
    "MSXML2.XMLHttp.4.0","MSXML2.XMLHttp.3.0",
    "MSXML2.XMLHttp","Microsoft.XMLHttp"
    ];

    //try to get an instance of the newer version.
    //If it is not available go down till the oldest one
    for (var i = 0; i < sVersions.length; i++) {
      try {
        var ret = new ActiveXObject(sVersions[i]);
        return ret;
      }
      catch (oException) {
        //Do nothing. Just go on trying with the older versions
      }
    }
  }
  //if it gets here then no version is available
  alert("XMLHttpRequest object could not be created.");
}

As you can see this function creates the correct instance of the XMLHttpRequest object. If the browser is not Internet Explorer then it just instantiates the XMLHttpRequest object, otherwise it tries to create the correct ActiveX object used by IE to represent XMLHttpRequest. In this case it tries to instatiates the object from the newer version going down till the oldest one.

Note: Finally, IE7 seems to use the same object used by other browsers, that is XMLHttpRequest. However, I advise you to use the above code just to be sure that older browser versions get the correct object in any case–provided that at least one is available!

Using Proguard Obfuscator Through the Wireless Toolkit

When you develop an application you might want to protect your code. A good way to accomplish this is using obfuscation. Proguard is a good open-source tool you can use for this purpose. To use it through the Wireless Toolkit (WTK), after downloading Proguard, you need to tell the WTK where it can find the obfuscator. You can do that by editing the file ktools.properties that you can find under %WTK%wtklibWindows, where %WTK% is the root directory of the Wireless Toolkit. Basically, you just need to add the two following lines to the aforementioned file:

1
2
obfuscator.runner.class.name: proguard.wtk.ProGuardObfuscator
obfuscator.runner.classpath: proguard_path

where proguard_path is the absolute path to the proguard.jar file that you can find under the lib directory of proguard’s home folder. So the proguard_path will be (under a Windows system) something like: C:\Programs\proguard\lib\proguard.jar. After that, creating obfuscated code using the WTK is as easy as selecting Project->Package->Create Obfuscated Package from the WTK menu

The Function Class In JavaScript

Most programmers know how to define and use a function in JavaScript. For example the following function displays an alert containing the string passed in as a parameter:

1
2
3
4
5
function displayAlert(sText) {
  alert(sText);
}
// Then you call it this way
displayAlert("Hello World!");

How many developers, however, know that JavaScript functions are actually objects? Indeed, you can define the previous function using the Function class:

1
2
3
var displayAlert = new Function("sText", "alert(sText);");
//...and you call it the same way
displayAlert("Hello World!");

For those who didn’t figure it out, the syntax of the Function class is the following:

1
var functionName = new Function(arg_1, arg_2, ..., arg_n, functionBody);

where arg_i (i = 1,…,n) is the i-th argument and functionBody is (you guessed it) the body of the function. Although you can define functions using the Function class it’s best to avoid it because it’s less performant than the traditional way. Anyway, knowing that even a function is an object is useful because you know you can call on it the valueOf() and toString() methods shared by all objects. However, when called on a function, these two methods return the source code for the function as a string. You can also call the one property defined in ECMAScript which is length. This property returns the number of arguments as defined in the function. For example:

1
2
3
4
5
function displayAlert(sText) {
  alert(sText);
}
//display an alert containing the number of arguments (in this case 1)
alert(displayAlert.length);

How to Store and Extract XML Data In and From an Oracle DataBase (DB)

Here are some snippets of code showing how to accomplish this:

1
CREATE TABLE SampleTable (id number primary key, person XMLType)

This first example creates a table with only two columns: id and person. The first is the PK of the table and the second is of XMLType type. The latter is going to contain our XML data.

Now let’s insert one row in the table.

1
INSERT INTO SampleTable VALUES (1, XMLType('XMLString'))

Where you must replace XMLString with any string representing XML. For example, you can replace it with:

1
2
3
4
5
6
7
<persons>
  <person>
    <surname>Lacava</surname>
    <name>Alessandro</name>
    <date_OF_BIRTH DAY="31" MONTH="01" YEAR="1976" />
  </person>
</persons>

I decided to decode the date of birth that way in order to show you (in the next example) how to retrieve, apart from a common XML node, an attribute as well.

1
2
3
SELECT extractValue(person, '/PERSONS/PERSON/SURNAME') AS surname,
  extractValue(person, '/PERSONS/PERSON/DATE_OF_BIRTH/@YEAR') AS year_of_birth
FROM SampleTable

This last example extracts the surname and the year of birth from the XML data represented by the person column that is of XMLType type. As you can see to extract the XML data you use regular XPath expressions.

Note: If your XML is using a namespace you will have to indicate it using the following syntax:

1
extractValue(column_name, XPath_Expression, 'xmlns="namespace_URI"')

where XPath_Expression is the XPath expression used to extract the value you’re interested in and namespace_URI is the URI indicating your namespace.

One last note: You can use XML Data inside an Oracle DB from version 9.0.2 on.