One of the things I wind up doing a lot in my code is looping thru some data structure — say an HTML option list or a SQL result set — and concatenating the results into a single string so that it can be printed to a text file or an HTML stream. I often want to separate the results with a comma, so the pattern I always use is that I check to see if my output string is zero-length. If it is, then this is the first time concatenating to the string so I do not put a comma, but on all subsequent passes I do prepend a comma. This simple pattern isn’t possible to implement in PL/SQL because I have not found a way to represent a zero-length string.

Let’s look at how this would work in a language that does support zero-length strings. (I’ll use JavaScript because it’s simple.) Say you’ve got a multi-select like this:

<form id="SelectExample">

<select id="sample" name="sample" multiple>


<option value="1">One</option>


<option value="2">Two</option>


<option value="3">Three</option>


</select>


<input type="submit" value="Go!" onclick="try_me();">

</form>

You can see that the submit button has an onclick, so instead of submitting the form, it will run a little JavaScript function:

<script>

function try_me() {


var theOptions = document.getElementById("sample").options;


var selectedOptions = "";


for (var i=0; i<theOptions.length; i++


) {


if (theOptions[i].selected) {


if (selectedOptions.length == 0) {


selectedOptions = theOptions[i].value;


} else {


selectedOptions = selectedOptions + ", " + theOptions[i].value;


}


}


}


alert ("Selected options were: " + selectedOptions);


}

</script>

The inner if statement is my zero-length check:

if (selectedOptions.length == 0) { ... }

This way, when I add the first item to the string, I do not prepend the comma — I include the comma for every other iteration thru the loop. I’m sure there are more efficient ways to do this, but this pattern has served me well for years. So naturally, when I found myself needing to do something similar in PL/SQL, I assumed I could dust off this old thing. The problem is that what I thought would be zero-length turns out to be a null string. I couldn’t believe what I was seeing, so I wrote a little test code to let me play around and try to find a way to create the zero-length string I wanted:

declare

v_my_string   varchar2(200);


begin


v_my_string := '';


if (v_my_string is null) then


dbms_output.PUT_LINE('v_my_string is null');


else


dbms_output.PUT_LINE('Length: ' || length(v_my_string));


end if;

end;

If you run this code, the output you get tells you that the variable is null; even though in most other languages like Java and JavaScript, setting the variable like I did here would have created a zero-length string. If you put something in between the single-quotes, you get a string object. For example, if you put a space inside, you’ll get a string of length 1:

  v_my_string := ' ';

This got me thinking that maybe, if I create this single-character string and then trim out the one character inside, it would be the zero-length string I was after. I know… way more work than necessary, but at this point, I was determined to try and get that silly zero-length string.

It turns out that it does not work that way. I substituted this into my test code:

  v_my_string := ' ';
v_my_string := trim(v_my_string);

When I ran my test code, it told me that the variable had become null again. Trimming out the one character in the string had not created a zero-length string as I had hoped; instead, it made the string object null again.

I’ve given up at this point. For PL/SQL, I’m just going to test for the null object instead of my zero-length string. It’s a little annoying that this trusty pattern has to be modified to work in PL/SQL, but I don’t see a choice.

Does anyone know how I can get that darned zero-length string out of PL/SQL?