Software Development

I can't get a zero-length string in PL/SQL


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?

9 comments
lgood58
lgood58

Have you tried defining the variable as character instead of varchar2?

erik.van.roon
erik.van.roon

If you're really determined not to write stuff like IF v_my_string IS NOT NULL THEN v_my_string := v_my_string || ','; END IF; v_my_string := v_my_string || v_string_to_add; You could obviously write a function like: CREATE OR REPLACE FUNCTION length_0 (p_string IN VARCHAR2 ) RETURN INTEGER BEGIN RETURN (NVL(LENGTH(p_string),0)); END length_0; and write stuff like: IF length_0(v_my_string) = 0 THEN ..... But first of all, remember that this is PLSQL you're talking about. You obviously already noticed, but it's not java. If it would be like java, act like java, have the same constructions and design as java, they would probably have named it something like, well uh, java, or so. I'm not saying you can't criticize PLSQL. There's a lot there that needs to be better. Same goes for every other language out there. It's just that I don't understand why you are desperately looking for a way to make it work 'the java way' in plsql, while you have enough knowledge of plsql (using 'IS NULL' in your sample code, means you're able to write a simple IF with it) to build something that does what you want it to do. If you're unwilling to accept the syntax, constructions and designs (or design flaws if you wish) of a language, just because it's different then the language(s) you're used to, then by all means stick to the languages you're used to. I saw Justin reply with 'LOL'. I must admit my first reaction was more like COL, crying out load.

dkiz
dkiz

Try this: declare l_buffer VARCHAR2(500) := '' ; l_one VARCHAR2(50) := 'One' ; l_two VARCHAR2(50) := 'Two' ; l_three VARCHAR2(50) := 'Three' ; procedure append_it ( build_string IN OUT VARCHAR2, Add_on VARCHAR2 ) is begin if build_string is null then build_string := add_on ; else build_string := build_string || ',' || add_on ; end if ; end append_it ; begin append_it ( l_buffer , l_one ) ; append_it ( l_buffer , l_two ) ; append_it ( l_buffer , l_three ) ; dbms_output.put_line ( l_buffer ) ; end; /

Peconet Tietokoneet-217038187993258194678069903632
Peconet Tietokoneet-217038187993258194678069903632

Copied from one of the O'Reilly books. He says as follows: A string literal can be composed of zero or more characters from the PL/SQL character set. A literal of zero characters is represented as '' (two consecutive single quotes with no characters between them). At least through Oracle9i, this zero-length string literal has the value NULL,[3] and a datatype of CHAR (fixed-length string). Has anyone else have (or can add) more on the subject.

dkiz
dkiz

although you have already mentioned it: In Oracle, any zero-length string is the same as NULL by definition. When you declare the variable, you instantiate it, but the contents are NULL (unless you initialize using either := or "DEFAULT"). For character data, this inserts a special null character in the field. Since a zero-length string is defined as null, any operation that reduces a string to zero-length puts this character back in. All you need to do is use a test that works. And before anyone gripes at me about Oracle's three-valued logic (true-false-null), let me say two things: 1) it has burned and bothered me, too 2) I didn't write it

RexWorld
RexWorld

That's pretty much what I found in my experiments -- if the string becomes zero-length it turns into a NULL. Which I think is kinda weird, because it means this: length(string) can never return a zero. It can return NULL, or any integer value starting with 1. But never a zero.

manfred.kleander
manfred.kleander

To get the required, you have to use the nvl-function: IF nvl(length(v_my_string), 0) = 0 THEN Then you will have the same situation as in JavaScript.

Justin James
Justin James

Sorry, but this just makes me laugh. BTW, why in the world are you writing your JavaScript like that? Why not just use the join method of the array of options? Like: for (var i=0; i

RexWorld
RexWorld

The reason I prefer my way is that I often have to do something else with the result. For example, wrap each value inside of single-quotes before joining them, so that when it gets passed to the PL/SQL module it is recognized as a string. As long as I'm having to munge the value anyway I figure why not do all the appending myself as well? I know, there's other ways in any language but I like having a single pattern that works pretty much everywhere I need it.

Editor's Picks