Archive for February, 2010
PL/SQL and the Twitter API
In this post, you will use the UTL_HTTP database package and the PL/JSON library to connect to the Twitter API and retrieve status updates from the Twitter microblogging service.
This post assumes you have access to an Oracle database with the UTL_HTTP package available (Oracle Database 10g Express Edition does) and that you have installed the PL/JSON library, a set of tools for working with JSON data in PL/SQL. For a guide to installing PL/JSON and getting to grips with the basics of the library, see this previous post.
The UTL_HTTP package is not usually granted to database users other than SYS, so the first step is to log on to your Oracle database as SYS and run the following command:
grant execute on utl_http to embosa;
Of course, be sure to replace “embosa” with your Oracle username. You should now have access to the UTL_HTTP package. Next, let’s verify that UTL_HTTP can connect to the Twitter API and return data from it. Execute the following PL/SQL block to do so:
declare
req utl_http.req;
resp utl_http.resp;
value varchar2(32767);
begin
req := utl_http.begin_request('http://api.twitter.com/1/statuses/public_timeline.json');
resp := utl_http.get_response(req);
loop
utl_http.read_line(resp, value, true);
dbms_output.put_line(substr(value, 1, 255));
end loop;
utl_http.end_response(resp);
exception
when utl_http.end_of_body then
utl_http.end_response(resp);
end;
/
This should return a response similar to the following:
[{"in_reply_to_user_id":67594046,"in_reply_to_status_id":null,"created_at":"Sat Feb 27 11:30:06 +0000 2010","favorited":false,"source":"web","geo":null,"in_reply_to_screen_name":"pemoutinho","truncated":false,"contributors":null,"user":{"geo_enabled":fals
Note that in the above example, the output has been trimmed down to 255 characters so that the DBMS_OUTPUT buffer would not overflow. In a moment you will parse the entire response as JSON and access individual properties using the PL/JSON library.
Now that you have verified that UTL_HTTP can connect to the Twitter API, let's parse the JSON and print out the status updates in a more meaningful format:
declare
req utl_http.req;
resp utl_http.resp;
value varchar2(32767);
jsonArray json_list;
jsonObj json;
begin
begin
req := utl_http.begin_request('http://api.twitter.com/1/statuses/public_timeline.json');
resp := utl_http.get_response(req);
utl_http.read_text(resp, value);
utl_http.end_response(resp);
exception
when utl_http.end_of_body then
utl_http.end_response(resp);
end;
jsonArray := json_list(value);
dbms_output.put_line('---------------------------------------------------------------');
for i in 1..jsonArray.count loop
jsonObj := json.to_json(jsonArray.get_elem(i));
dbms_output.put_line(json_ext.get_varchar2(jsonObj, 'text'));
dbms_output.put_line('Posted by ' ||json_ext.get_varchar2(jsonObj, 'user.screen_name'));
dbms_output.put_line('Posted on ' ||json_ext.get_varchar2(jsonObj, 'created_at'));
dbms_output.put_line('---------------------------------------------------------------');
end loop;
end;
/
In this example, you get the entire response from the Twitter API using UTL_HTTP.READ_TEXT, and parse it using PL/JSON's json_list object (as Twitter's API is a JSON array containing several JSON objects). You then loop through each item in this list, extracting the text, user screen name and created at properties for each status update and outputting them. Each tweet is separated by a line of hyphens to make it easier to read. The final output should look something like the following:
--------------------------------------------------------------- Am reading this blog and a girl mentione Habana Outpost. Oh Brooklyn, sometimes I miss you. though I heard Habana was wack now... Posted by thefashionbomb Posted on Sat Feb 27 11:55:17 +0000 2010 --------------------------------------------------------------- @0oo \u75b2\u308c\u3061\u3083\u3044\u307e\u3057\u305f\uff1f\u3061\u3087\u3063\u3068\u4f11\u307f\u307e\u3057\u3087\u3046\u304b Posted by naoko_bot Posted on Sat Feb 27 11:55:17 +0000 2010 --------------------------------------------------------------- @papodebola sobre o horario dos jogos em sampa, uma frase que todo politico teme: Ninguem aguentaria 15 min de fama no Jornal NacionalPosted by ReeFzera Posted on Sat Feb 27 11:55:17 +0000 2010 --------------------------------------------------------------- @abogado_kaito \u308f\u304b\u308c\u3070\u3044\u3044\u306e\u3088\uff01 Posted by renkon_0 Posted on Sat Feb 27 11:55:17 +0000 2010 --------------------------------------------------------------- Just write something about Nicks cars:D Posted by love_ncarter Posted on Sat Feb 27 11:55:17 +0000 2010 --------------------------------------------------------------- @Reema226: The book store? Posted by amitgupta Posted on Sat Feb 27 11:55:17 +0000 2010 --------------------------------------------------------------- \u3057\u304b\u3057TV\u306e\u30d6\u30e9\u30c7\u30a3\u30de\u30f3\u30c7\u30a4\u306f\u30d5\u30a1\u30eb\u30b3\u30f3\u5f31\u3044\u306a\u301c\u3002 Posted by kazuwitter Posted on Sat Feb 27 11:55:17 +0000 2010 --------------------------------------------------------------- ya abisnya kamu rese di;iatin org tau
RT @ninomayday: yehee siapa suruh coba lagian maksa dasar lu yaang hahaha :p Posted by karinutriansyah Posted on Sat Feb 27 11:55:17 +0000 2010 --------------------------------------------------------------- \u3053\u3046\u3044\u3046\u3053\u3068\u77e5\u3063\u3066\u308b\u304b\u3089\u306a\u306b\uff1f\u3063\u3066\u601d\u3044\u307e\u3059\u3002\u81ea\u5206\u306e\u884c\u52d5\u3068\u306e\u9023\u9396\u3092\u8003\u3048\u3082\u3057\u306a\u3044\u3067\u3002\u3042\u3068\u81ea\u5206\u306e\u56fd\u306e\u8ca7\u56f0\u3082\u77e5\u3089\u306a\u3044\u306e\u306b\u304b\u308f\u3044\u305d\u3046\u3068\u304b\u4e2d\u9014\u534a\u7aef\u3059\u304e\u308b\u3068\u601d\u3044\u307e\u3059\u3002RT @gaitifujiyama \u65e5\u30c6\u30ec\u306e\u4e16\u754c\u4e00\u53d7\u3051\u305f\u3044\u6388\u696d\u3001\u6642\u305f\u307e\u3044\u3044\u4e8b\u3084\u308b\u3093\u3060\u3088\u306d\u3002\u4eca\u3001\u6b63\u306b\u305d\u308c\u3084\u3063\u3066\u3044\u308b Posted by machuiii Posted on Sat Feb 27 11:55:17 +0000 2010 --------------------------------------------------------------- @_rica \uc544 \uc774\uac74 tiltshiftgen \uc774\ub77c\ub294 \uc571\uc758 \ud798\uc744 \ube4c\ub9b0 \uc870\uc791[..] \uc2e4\uc81c \uce74\uba54\ub77c\uc5d0\uc11c\ub294 \ud2f8\ud305 \ub80c\uc988\uac00 \uc774\ub7f0 \ubbf8\ub2c8\uc5b4\ucc98 \ud6a8\uacfc\ub97c \ub0b4\uc900\ub2e4\uace0 \ud558\ub354\uad70\uc694. Posted by jstrane Posted on Sat Feb 27 11:55:17 +0000 2010 --------------------------------------------------------------- @micaelquental, fala maluco,,, Posted by Jeankarllus Posted on Sat Feb 27 11:55:17 +0000 2010 --------------------------------------------------------------- Ask me anything http://formspring.me/CassiGLAMOUR Posted by Cassi_Hopeful Posted on Sat Feb 27 11:55:17 +0000 2010 --------------------------------------------------------------- I killed a plant, and I disliked it! Posted by Malantur Posted on Sat Feb 27 11:55:17 +0000 2010 --------------------------------------------------------------- Can someone give me Ro's link to his page on here just with the @ so I get onto it since I can't do it on the search bar!? =/ x Posted by Feehilys_Angelx Posted on Sat Feb 27 11:55:17 +0000 2010 --------------------------------------------------------------- RT @terrycavanagh: Really excited by the concept behind Jason Rohrer's new game - check it out: http://sleepisdeath.net/ Posted by sonneveld Posted on Sat Feb 27 11:55:17 +0000 2010 --------------------------------------------------------------- @zzz_ral \u53ef\u611b\u3044\u3001\u3063\u3066\u3044\u3046\u304b\u30a8\u30ed\u3044\u3001\u30c9\u30a8\u30ed\u3060\u3088\u306d\uff01 Posted by chamber11 Posted on Sat Feb 27 11:55:17 +0000 2010 --------------------------------------------------------------- @nano_limpid %\u8077\u306f\u5927\u5909\u3063\u3059\u306d\uff57\uff57\uff57\uff57\uff57 Posted by Syana Posted on Sat Feb 27 11:55:17 +0000 2010 --------------------------------------------------------------- Aku suka perkataan cacu tadi sore... Posted by masagakenaldema Posted on Sat Feb 27 11:55:17 +0000 2010 --------------------------------------------------------------- @dildillaa dih pede geelaaahhh hahha Posted by satriavisabrina Posted on Sat Feb 27 11:55:17 +0000 2010 --------------------------------------------------------------- @555hamako \u3042\u307c\u30fc\u3093\u3068\u30ad\u30dc\u30f3\u30cc\u304c\u3054\u3063\u3061\u3083\u306b\u306a\u3063\u3066\u307e\u3059\u306d\u3048w\u3002 Posted by florestan854 Posted on Sat Feb 27 11:55:17 +0000 2010 ---------------------------------------------------------------
You may notice that some tweets seem to be made up of gibberish with \u all over the place. These are Unicode characters and usually represent non-Latin characters such as Chinese characters, which Twitter stores as Unicode.
There you have it, in this post you have created a basic Twitter public timeline reader using PL/SQL, the UTL_HTTP package and the PL/JSON library. It should be relatively straightforward to build on this foundation to create a more complete Twitter client using PL/SQL. For more information about the Twitter API see the official Twitter API wiki.
Oracle and JSON: Using PL/JSON
JSON (JavaScript Object Notation) is a lightweight data format that is very well suited for transmitting data over the Internet. Despite the reference to JavaScript in its name, JSON is a language-independent syntax and native support for it has been included in many modern programming languages. In fact, JSON is so popular nowadays that entire database management systems have built their record structure around it, for example Apache CouchDB.
As a Web developer who does a lot of work with PL/SQL, I have recently found myself creating PL/SQL procedures that output JSON data, and making these procedures available as RESTful HTTP services that are then consumed by Ajax functions in the front-end of my applications. This can be a bit of a pain, however, as I need to output the JSON data manually, making it very prone to errors and very difficult to debug in many cases.
Thankfully, an open source library for PL/SQL called PL/JSON is available that resolves some of the issues associated with working with JSON data in an Oracle application. In this post, you will learn how to install and use PL/JSON to work with JSON data in your own PL/SQL applications.
Download and install the library
The latest version of PL/JSON, at the time of writing, is version 0.8.6. The compressed archive is less than 200KB in size. You can download PL/JSON from SourceForge at http://sourceforge.net/projects/pljson/files/. Unzip the library to your hard drive and install it in a SQL*Plus session using the following command:
@install
You should see the following output:
----------------------------------- -- Compiling objects for PL/JSON -- ----------------------------------- PL/SQL procedure successfully complete Type created. No errors. Type body created. No errors. Type created. No errors. Type body created. No errors. Type created. No errors. Type created. No errors. Type created. No errors. Type created. No errors. Type created. No errors. Type created. No errors. Package created. Package body created. Package created. Package body created. Type body created. No errors. Type body created. No errors. Package created. Package body created.
Assuming you see no errors from running the install script, PL/JSON is now installed in your Oracle database.
Using PL/JSON
With PL/JSON installed, you can now start working with the traditional “Hello, world” example, using the following PL/SQL code:
set serveroutput on
declare
jsonObj json;
begin
--Create a new JSON object, passing the JSON code as the constructor
jsonObj := json('{"greeting":"Hello World"}');
--Output the value for the JSON data with the key "greeting"
dbms_output.put_line(json_ext.get_varchar2(jsonObj, 'greeting'));
end;
/
This produces the following output:
Hello, World
As you can see from the above output, PL/JSON parses JSON data. But what if you want to create a JSON object without actually providing the data in JSON format. Take the following example:
set serveroutput on
declare
jsonObj json;
begin
--Use an empty constructor to create a blank JSON object
jsonObj := json();
-- Use the put procedure to add a string, number, boolean and null property to the JSON object
jsonObj.put('name', 'Joe Lennon');
jsonObj.put('age', 24);
jsonObj.put('awesome', json_bool(true));
jsonObj.put('children', json_null());
--Print the string representation of the JSON object (pretty-print)
jsonObj.print;
dbms_output.put_line('Am I awesome?');
--Use getters to retrieve the value of the boolean property "awesome" and print the result
dbms_output.put_line(json_ext.get_json_bool(jsonObj, 'awesome').to_char);
end;
/
The output for the above looks like:
{
"name" : "Joe Lennon",
"age" : 24,
"awesome" : true,
"children" : null
}
Am I awesome?
true
JSON Arrays
JSON objects can contain data in several types – number, string, boolean, null or array. At this point, you’ve seen how to work with the first four of these types, but not arrays. PL/JSON refers to these as a json_list. Working with json_list objects is very similar to working with a regular JSON object, as shown in the following example:
set serveroutput on
declare
jsonArray json_list;
jsonObj json;
begin
jsonArray := json_list('[{"name":"Joe","age":24},{"name":"Jill","age":26}]');
jsonObj := json.to_json(jsonArray.get_elem(1));
dbms_output.put_line(json_ext.get_varchar2(jsonObj, 'name'));
jsonObj := json.to_json(jsonArray.get_elem(2));
dbms_output.put_line(to_char(json_ext.get_number(jsonObj, 'age')));
end;
/
The above code create a JSON array (or json_list) containing two objects with two properties, name and age. The first object has the name “Joe” and age 24, the second has name “Jill” and age 26. The get_elem function is used to return an item in the JSON array, and the to_json function casts this is a JSON object. In the above example, you first assign jsonObj to the first object in the JSON array, printing out the value of the name property for this object (in this case, the string value “Joe”). Next, you assign jsonObj to the second object in the array, and print out the value of the age property (in this case, age is 26). The output for this example is as follows:
Joe 26
To wrap up this post, let’s have a look at a more complex scenario for using JSON in PL/SQL. Let’s say that you want to create a JSON representation of the result of a SQL statement. The following example demonstrates just that:
set serveroutput on
declare
jsonArray json_list;
jsonObj json;
cursor get_data is
select initcap(lower(object_type)) name, count(*) count
from all_objects
where upper(object_type) like 'INDEX%'
group by object_type;
begin
jsonArray := json_list();
for objType in get_data loop
jsonObj := json();
jsonObj.put('object_type', objType.name);
jsonObj.put('count', objType.count);
jsonArray.add_elem(jsonObj.to_anydata);
end loop;
jsonArray.print;
dbms_output.new_line;
for i in 1..jsonArray.count loop
dbms_output.put_line(
json_ext.get_varchar2(
json.to_json(jsonArray.get_elem(i)),
'object_type'
)||'->'||
to_char(json_ext.get_number(
json.to_json(jsonArray.get_elem(i)),
'count'
))
);
end loop;
end;
/
In the example above, you first initialize the JSON array to an empty json_list. Next, you loop through the get_data cursor, which selects back the counts for all object types in the database that begin with “INDEX”. For each iteration of this loop, a JSON object is created, the object type name and count are added as properties to the object, and the object is added to the JSON array. Then the string representation of the array is printed as output. Next, you loop through the JSON array itself, and print out the object_type and count properties for each item in the list. The overall output should look as follows:
[{
"object_type" : "Index Partition",
"count" : 99
}, {
"object_type": "Index",
"count" : 1411
}, {
"object_type" : "Indextype",
"count" : 8
}]
Index Partition->99
Index->1411
Indextype->8
It’s important to note that the “print” function which outputs a string representation of JSON objects or arrays merely uses the DBMS_OUTPUT.PUT_LINE Oracle function to dump data to the SQL prompt. There are buffer limitations with this function, particularly on clients before 10g Release 2 (10.2). As a result, on large sets of data you may get an error:
ORA-06502: PL/SQL: numeric or value error: host bind array too small
This is not an issue with PL/JSON, but rather an issue with the Oracle DBMS_OUTPUT.PUT_LINE function. Your JSON objects can be used in other means perfectly fine, but the print member function for the json and json_list objects suffer from this limitation.
PL/JSON provides a ton of excellent features for working with JSON in PL/SQL. The best way to grips with it is to get your hands dirty and try it out for yourself. This post should be a good base to start from, and you can find out more by reading the doc.pdf file that comes with PL/JSON. Also, be sure to check out the examples folder, as it contains some good sample usages that are ready for you to test out. Finally, check the source code itself, particularly the type specification files:
- json.typ
- json_list.typ
As these will give you a good idea of the member functions available for json and json_list objects. If you have any specific questions about PL/JSON, leave a comment and I will do my best to answer them.
Flex 4 and PL/SQL
I was recently asked by someone to give them some help with connecting to a MySQL database from Adobe Flex 4. In Flex Builder 3, there was a feature which allowed you to create a Flex application based on the tables in a MySQL database, taking care of the PHP code in the middle for you. In Adobe FlashBuilder 4 Beta 2 (which is basically the new version of Flex Builder), this feature does not exist, so you’re kind of left on your own.
Thankfully, on the Adobe Labs website there are a set of good tutorials on getting up and running with Flex connecting to a MySQL database via PHP using Zend AMF (Action Message Format) remoting. The following is a PDF document including all of those tutorials:
Working with data stored in a database management system such as MySQL is very easy if you develop applications in PHP, ColdFusion or Java. But, what if you use a more obscure language such as PL/SQL? You’re not completely out of luck, as Flex can also consume any HTTP service that responds with XML or JSON data. In this post, you will create a PL/SQL web procedure that outputs JSON data, and then you will create a Flex application that displays this data in a DataGrid component.
In order to follow this guide, you will need the following:
- An Oracle database, Oracle Database 10g Express Edition (XE) will do fine.
- A suitable PL/SQL web deployment environment, with a DAD (Document Access Descriptor) configured. This can be either an Oracle HTTP Server which uses mod_plsql, the Apache HTTP Server with the open source mod_plsql variant mod_owa, or even Oracle XE’s APEX web server, with a DAD set up using the DBMS_EPG package. Basically you need to be able to access a PL/SQL package via a URL. If none of this makes sense to you, you probably don’t need this guide!
- Adobe FlashBuilder 4 Beta 2
The first thing you need to do is create the PL/SQL stored procedure that will be used to return the JSON data that we will use later to fill the Flex DataGrid component. This procedure uses a cursor to get a list of the different object types present in the Oracle database, and how many objects of each type is currently stored. The procedure then loops through this cursor and produces JSON output.
create or replace procedure json_object_types as
cursor get_data is
select initcap(lower(object_type)) object_type, count(*) as object_count
from all_objects
group by object_type;
i number := 0;
begin
htp.init;
owa_util.mime_header('application/json', false);
owa_util.http_header_close;
htp.p('[');
for obj_type in get_data loop
i := i + 1;
if i = 1 then
htp.p('{');
else
htp.p(', {');
end if;
htp.p('"object_type": "'||obj_type.object_type||'",');
htp.p('"object_count": '||obj_type.object_count);
htp.p('}');
end loop;
htp.p(']');
end json_object_types;
This code should produce an output like the following. Note that I have formatted the JSON code to make it somewhat easier to read, and also the counts shown here will most likely differ from the values returned for your own database.
[
{ "object_type": "Consumer Group", "object_count": 2 },
{ "object_type": "Sequence", "object_count": 7 },
{ "object_type": "Schedule", "object_count": 1 },
{ "object_type": "Procedure", "object_count": 24 },
{ "object_type": "Operator", "object_count": 45 },
{ "object_type": "Window", "object_count": 2 },
{ "object_type": "Package", "object_count": 247 },
{ "object_type": "Library", "object_count": 15 },
{ "object_type": "Package Body", "object_count": 1 },
{ "object_type": "Xml Schema", "object_count": 13 },
{ "object_type": "Job Class", "object_count": 1 },
{ "object_type": "Table", "object_count": 70 },
{ "object_type": "Synonym", "object_count": 2765 },
{ "object_type": "View", "object_count": 1166 },
{ "object_type": "Function", "object_count": 159 },
{ "object_type": "Window Group", "object_count": 1 },
{ "object_type": "Indextype", "object_count": 8 },
{ "object_type": "Type", "object_count": 767 },
{ "object_type": "Evaluation Context", "object_count": 1 }
]
Be sure to note the URL you use to access this procedure – in my case it was http://localhost:8080/embosa/json_object_types – where embosa is the DAD name I have configured on my web server.
With the PL/SQL procedure created, we can now create a Flex project in Adobe FlashBuilder that will consume the JSON data that the procedure produces. Fire up FlashBuilder and create a new Flex project (File -> New Flex Project). In the dialog box for “New Flex Project”, enter “PlSqlService” as the Project name, make sure that “Web” is selected under Application type and that “None/Other” is selected for Application server type. Press the Finish button to create the project.
The next thing you need to do is connect to the PL/SQL procedure you created earlier. To do this, open Data -> Connect to Data/Service. This will open a dialog which gives you a number of service type options – BlazeDS, ColdFusion, HTTP, LCDS, PHP and Web Service. Select the HTTP option and press Next.
You will now be required to configure the HTTP service. To do this, you need to define the operation for the service and give the service a name. Under Operations, change the Operation name to “getObjectTypes”, keep “GET” selected as the Method, and enter the URL for your PL/SQL procedure that you earlier noted in the URL field. Then, under Service details, give the Service a name “PlSqlService”. The Service package will be created automatically based on your service name. Press “Finish” to create the connection.
In the Data/Services window you should now see an entry named PlSqlService with the function getObjectTypes() listed. Right-click on the getObjectTypes() item in this window, and select “Configure Return Type”. In the dialog box, make sure that “Auto-detect the return type from sample data” is selected and press the Next button. On the next screen, just accept the default option (“Enter parameter values and call the operation”) and press the Next button again. You should now see that the Return Type was detected successfully. Under Data type, choose to enter a name to create a new data type and enter the value “ObjType” in the textbox. You’ll notice that the object_type and object_count properties have been automatically detected and their correct types (String and int, respectively) have been picked up by FlashBuilder. You don’t need to change anything else here, so just press the Finish button to return to the main FlashBuilder window.
The file PlSqlService.mxml is open – but probably in “Source” view. Switch to “Design” mode, and from the Components window, drag a DataGrid from the controls list to the empty canvas area. By default it should have three columns named Column 1, Column 2 and Column 3. Right-click on the DataGrid and select the option “Bind to Data” from the context menu. This will open a dialog box for binding the DataGrid control to a data service. The option “New service call” should be selected by default, and you’ll notice that the “PlSqlService” and “getObjectTypes()” service and operation have been automatically selected. Accept these defaults and press the OK button to bind the control to this data.
You’ll notice that the DataGrid has changed to only show two columns: object_type and object_count. Let’s change the Column headings so they look a bit better. With the DataGrid selected, click the “Configure Columns” button in the Properties window. In here you can select each column, and change the “Header text” property to modify the text that displays in each column’s header. Change the text for the object_type column to “Type” and for the object_count column to “Count”. Press the OK button when you are finished.
Save your work (File->Save or Ctrl+S) and then run the project by pressing the green Run button on the FlashBuilder toolbar, or by selecting Run->Run PlSqlService. Your default Web browser will launch and you will see a page with a DataGrid, populated with the data from the Oracle database. The end result looks like the screenshot below.
The Final Result
The DataGrid control is simple but powerful, and allows you to adjust the width of each columns and sort the data by clicking on the column headers.
So there you have it, a Flex DataGrid connecting to an Oracle database using a PL/SQL web procedure – if you have any questions about the techniques covered in this guide, feel free to leave a comment.
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
