Joe Lennon

When developing Web applications using Oracle XE and the embedded PL/SQL gateway, you may find that debugging is a pain due to the lack of log files (like you’d normally find in Apache folders in a full Oracle application server install). Well, the good news is, that you can configure errors to be reported directly in the browser, so that rather than getting very unhelpful 404 and other HTTP error messages, you get a full debug trace of the error that occurred. Full credit for this find goes to Dietmar Aust, who has a great blog on Oracle XE and Application Express. See his original post for even more insight into this.

To switch on error reporting and the printing of debug messages to the browser, simply issue the following commands. Of course, be sure to replace “embosa” on line 3 with your own DAD name. Also, be sure to turn this back off again in a production environment, this should only be used for development and testing purposes.

begin
dbms_epg.set_global_attribute('log-level', 3);
dbms_epg.set_dad_attribute('embosa', 'error-style', 'DebugStyle');
end;
/

To test, simply go to a URL that doesn’t exist, and you should see a useful error message instead of a unhelpful “Not Found” message. See the screenshot below for an example of what this error message looks like.

Useful PL/SQL error message

In a previous post, you learned how to create functions to split a string into an “array” by a delimiting character, and also to join an “array” of strings together using a glue character. In this post, you will create a function that counts the number of words in a string, by using the split_string function you created previously. If you didn’t follow the previous post, don’t worry, all of the code for that function will also be shown here. This code has been tested and verified on Oracle Database 10g Express Edition, and new code has been highlighted, should you already have the original emb_string package created in previous posts.

Package specification:

create or replace package emb_string as
    type string_array is table of varchar2(32767);
    function join_string(str_array in string_array, glue in char default ',') return varchar2;
    function split_string(str in varchar2, delimiter in char default ',') return string_array;
    function word_count(str in varchar2) return number;
end emb_string;

Package body:

create or replace package body emb_string as
    function join_string(str_array in string_array, glue in char default ',') return varchar2 is
        return_value         varchar2(32767);
    begin
        for i in 1..str_array.count loop
            if i = 1 then
                return_value := str_array(i);
            else
                return_value := return_value||glue||str_array(i);
            end if;
        end loop;
        return return_value;
    end join_string;

    function split_string(str in varchar2, delimiter in char default ',') return string_array is
        return_value         string_array := string_array();
        split_str            long default str || delimiter;
        i                    number;
    begin
        loop
            i := instr(split_str, delimiter);
            exit when nvl(i,0) = 0;
            return_value.extend;
            return_value(return_value.count) := trim(substr(split_str, 1, i-1));
            split_str := substr(split_str, i + length(delimiter));
        end loop;
        return return_value;
    end split_string;

    function word_count(str in varchar2) return number is
        words          string_array;
    begin
        if str is null or length(str) < 1 then
            return 0;
        end if;

        words := split_string(str, ' ');
        return words.count;
    end word_count;
end emb_string;

Sample Usage:

The function is extremely easy to use, and can be called directly from a SQL statement as shown in the following example.

select emb_string.word_count('hello world how are you today?') from dual;

Output:

6

This is a quick and easy PL/SQL function for repeating a string several times, with or without a separator between. This has been tested on Oracle Database 10g Express Edition.

Function definition:

create or replace function repeat_string(str in varchar2, times in number default 1, delimiter in char default '') return varchar2 is
    return_value		varchar2(32767);
begin
    if times = 0 then
        return '';
    else
        for i in 1..times loop
            if i > 1 then
                return_value := return_value||delimiter||str;
            else
            	return_value := return_value||str;
            end if;
        end loop;
        return return_value;
    end if;
end repeat_string;

Sample Usage:

select repeat_string('hello', 20, ' ') from dual;

Output:

hello hello hello hello hello hello hello hello hello hello hello hello hello hello hello hello hello hello hello hello

In the last blog post, you learned how to split a string into an “array” of strings by a delimiter such as a comma. In this post, you will create a function that will do the reverse of this operation – take the “array” of strings and join it so that you end up with a single string, tied together by a specified delimiting character. This function will be added to the package that was created in the previous post for convenience. Again, it has been tested on Oracle Database 10g Express Edition. New code in the package specification and body is highlighted for your convenience.

Package specification:

create or replace package emb_string as
    type string_array is table of varchar2(32767);
    function join_string(str_array in string_array, glue in char default ',') return varchar2;
    function split_string(str in varchar2, delimiter in char default ',') return string_array;
end emb_string;

Package body:

create or replace package body emb_string as
    function join_string(str_array in string_array, glue in char default ',') return varchar2 is
        return_value         varchar2(32767);
    begin
        for i in 1..str_array.count loop
            if i = 1 then
                return_value := str_array(i);
            else
                return_value := return_value||glue||str_array(i);
            end if;
        end loop;
        return return_value;
    end join_string;

    function split_string(str in varchar2, delimiter in char default ',') return string_array is
        return_value         string_array := string_array();
        split_str            long default str || delimiter;
        i                    number;
    begin
        loop
            i := instr(split_str, delimiter);
            exit when nvl(i,0) = 0;
            return_value.extend;
            return_value(return_value.count) := trim(substr(split_str, 1, i-1));
            split_str := substr(split_str, i + length(delimiter));
        end loop;
        return return_value;
    end split_string;
end emb_string;

Sample Usage 1:

In this example, you create an array of strings with the numbers 1 to 5 using a for loop. You then join this array together as a single string using the default glue character (a comma).

set serveroutput on
declare
    v_string_array          emb_string.string_array := emb_string.string_array();
begin
    for i in 1..5 loop
        v_string_array.extend;
        v_string_array(i) := i;
    end loop;
    dbms_output.put_line(emb_string.join_string(v_string_array));
end;
/

Output:

1,2,3,4,5

Sample Usage 2:

In this example, you instantiate the string array with a list of strings in the declare block. You then use the join_string function to join each element to each other using the specified glue character, colon.

set serveroutput on
declare
    v_string_array emb_string.string_array := emb_string.string_array('hello','world','how','are','you');
begin
    dbms_output.put_line(emb_string.join_string(v_string_array, ':'));
end;
/

Output:

hello:world:how:are:you

In software development, there is often a need to split a string into multiple segments based on a delimiter. This functionality is not provided out of the box with PL/SQL, but fortunately it is relatively easy to implement. The following code has been tested on Oracle Database 10g Express Edition.

Package specification:

create or replace package emb_string as
    type string_array is table of varchar2(32767);
    function split_string(str in varchar2, delimiter in char default ',') return string_array;
end emb_string;

Package body:

create or replace package body emb_string as
    function split_string(str in varchar2, delimiter in char default ',') return string_array is
        return_value         string_array := string_array();
        split_str            long default str || delimiter;
        i                    number;
    begin
        loop
            i := instr(split_str, delimiter);
            exit when nvl(i,0) = 0;
            return_value.extend;
            return_value(return_value.count) := trim(substr(split_str, 1, i-1));
            split_str := substr(split_str, i + length(delimiter));
        end loop;
        return return_value;
    end split_string;
end emb_string;

Sample Usage 1:

In this example, you split a string using the default delimiter character, the comma.

set serveroutput on
declare
    v_split_string       emb_string.string_array;
begin
    v_split_string := emb_string.split_string('hello,world,how,are,you');
    if v_split_string.count > 0 then
        for i in 1..v_split_string.count loop
            dbms_output.put_line(v_split_string(i));
        end loop;
    end if;
end;
/

Output:

hello
world
how
are
you

Sample Usage 2:

In this example, you split a string by specifying the semi-colon character as the delimiter.

set serveroutput on
declare
    v_split_string       emb_string.string_array;
begin
    v_split_string := emb_string.split_string('very;good;thank;you;very;much', ';');
    if v_split_string.count > 0 then
        for i in 1..v_split_string.count loop
            dbms_output.put_line(v_split_string(i));
        end loop;
    end if;
end;
/

Output:

very
good
thank
you
very
much

Sample Usage 3:

This function does not provide a means by which you can “escape” the delimiter character. The easiest way to get around this is to use an obscure delimiter character in your data. In this example, you split a string by the character sequence ~*. You’ll notice that the string does not split when it encounters the tilde or asterisk character alone, but only when they are together in sequence.

set serveroutput on
declare
    v_split_string       emb_string.string_array;
begin
    v_split_string := emb_string.split_string('this~*is~*an~other~*exa*mple~*', '~*');
    if v_split_string.count > 0 then
        for i in 1..v_split_string.count loop
            dbms_output.put_line(v_split_string(i));
        end loop;
    end if;
end;
/

Output:

this
is
an~other
exa*mple