Archive for the ‘Oracle’ tag
PL/SQL Word Count
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
PL/SQL Repeat String
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
PL/SQL Join Array of Strings
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
PL/SQL Split Strings
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
Oracle acquire Sun
Oracle announced today that they are to buy Sun Microsystems at a valuation of $7.4 billion, or $9.50 cash per share. The deal is expected to be completed this summer. This announcement comes just two weeks after talks broke down in a similar deal between IBM and Sun. The most important aspect of this acquisition, for consumers and developers alike, is that Oracle now has control of the Java programming language and development platform. The Java platform runs on over 1 billion devices across the globe, including computers and mobile phones. In recent years, Oracle has invested more and more into Java, using it to power several of their middleware and developer products.
The deal comes as quite a surprise as many believed that IBM were the only interested party. The acquisition of Sun will result in Oracle’s first steps into the business of selling hardware. While this is new territory for Oracle, their lack of presence in this field means they are much less likely to be plagued by anti-trust issues than IBM would, should they have bought Sun. Oracle have vowed to continue to develop Sun’s hardware products – and I imagine that we will see Oracle offering the complete, integrated, out-of-the-box package before long – Oracle servers and Oracle storage solutions powered by Oracle Solaris running Oracle Database and Oracle J2EE applications.
Sun’s products are not limited to Java, Solaris and their hardware offerings – Oracle will also get their hands on OpenOffice/StarOffice, NetBeans, GlassFish, Sun Studio and perhaps more importantly, the popular MySQL database. In a list of a FAQs available on the Oracle website, Oracle have stated that MySQL will simply join the list of database products that Oracle offer. This list currently includes Oracle Database 11g, BerkeleyDB, TimesTen and the InnoDB storage engine. It’s hard to see Oracle changing much of MySQL in the short-term, but it will be interesting to see how Oracle plans to capitalize on its popularity, if indeed it has such plans. According to InformationWeek, MySQL had 11 million installations up to February 2008. It is the most popular database for web applications and was acquired by Sun in February 2008.
In previous acquisitions, Oracle has made job cuts, but it has yet to comment on whether it intends to cut jobs at Sun Microsystems. The companies will continue to operate independently until the deal closes in the summer, subject to approval by shareholders and US regulatory bodies.
For further information, read the following:
