Archive for the ‘Oracle’ Category
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
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
