Joe Lennon

In this article, you will learn about MongoDB, the open source, document-oriented database management system written in C++ that provides features for scaling your databases in a production environment. Discover what benefits document-oriented databases have over traditional relational database management systems (RDBMS). Install MongoDB and start creating databases, collections, and documents. Examine Mongo’s dynamic querying features, which provide key/value store efficiency in a way familiar to RDBMS database administrators and developers.

In recent years, we have seen a growing interest in database management systems that differ from the traditional relational model. At the heart of this is the concept of NoSQL, a term used collectively to denote database software that does not use the Structured Query Language (SQL) to interact with the database. One of the more notable NoSQL projects out there is MongoDB, an open source document-oriented database that stores data in collections of JSON-like documents. What sets MongoDB apart from other NoSQL databases is its powerful document-based query language, which makes the transition from a relational database to MongoDB easy because the queries translate quite easily.

Read the full article on IBM developerWorks at http://www.ibm.com/developerworks/opensource/library/os-mongodb4/index.html.

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

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.

In this developerWorks article, I explore the concepts behind Apache CouchDB – an open source, document-oriented database management system. CouchDB uses a RESTful JSON API and JavaScript views to interact with and report on the data stored in the database. Unlike the relational model, the model CouchDB is built on is designed specifically for use in document-oriented web applications such as blogs, wikis and discussion forums. This makes it an exciting prospect as a potential de-facto database for publishing-oriented web applications. The following is the abstract from IBM developerWorks:

Relational databases define a strict structure and provide a rigid way to maintain data for a software application. Apache’s open source CouchDB offers a new method of storing data, in what is referred to as a schema-free document-oriented database model. Instead of the highly structured data storage of a relational model, CouchDB stores data in a semi-structured fashion, using a JavaScript-based view model for generating structured aggregation and report results from these semi-structured documents. CouchDB has been developed from the ground up with Web applications as the primary focus and has its sights on becoming the de-facto database for Web application development.

Read the article in full at http://www.ibm.com/developerworks/opensource/library/os-couchdb/

In order to develop PL/SQL applications on Oracle XE, we are going to need to install a web server (Apache) and a PL/SQL gateway for Apache called mod_owa. On a regular Oracle Application Server you would probably be using Oracle HTTP Server (a modified Apache) and mod_plsql, amd you should also be able to follow my future tutorials if you have this type of setup.

Once again I will be assuming throughout this guide that you have followed my Oracle XE installation tutorial. If you didn’t, you may need to change usernames, passwords and service names to fit your setup. I will be walking through the setup process on a Windows XP machine, but it should be similar on another Windows version. Let’s get started!

IMPORTANT NOTE: If you are looking for the download for mod_owa, please note that the website for it has moved since this blog post was originally written. You can now find mod_owa on the Oracle OSS website at the following URL: http://oss.oracle.com/projects/mod_owa/dist/documentation/modowa.htm. I have updated this blog post to reflect this change.

The first thing you’ll need to do is download the relevant software. The easiest way to install Apache is to download and run the installer binary from the Apache website. At the time of writing, the latest stable version of Apache available is 2.2.9, but again the process should be similar for whatever version you are installing. You can download Apache from http://httpd.apache.org/download.cgi. You will also need to grab the Apache PL/SQL Gateway Module (mod_owa) from http://oss.oracle.com/projects/mod_owa/dist/documentation/modowa.htm. Click on the link for “Zip file for Windows”, which includes the source code and binaries for the Windows version of Apache.

The first stage in setting up our PL/SQL web development environment is the installation of the Apache web server. To start the installation, run the installer package you downloaded earlier (the filename should be something like apache_2.2.9-win32-x86-no_ssl-r2.msi). The screen should look similar to the one in Figure 3a below:

Figure 3a - Apache HTTP Server 2.2 Installation Wizard

Click “Next >” to continue. On the screen that follows, read the license agreement and if you are happy with the terms, select “I accept the terms in the license agreement” and click “Next >” to move on to the readme screen, from which you can simply press “Next >” again. You should now be presented with the following screen:

Figure 3b - Apache HTTP Server 2.2 Installer - Server Information

Unless you are installing Apache for production use, it doesn’t matter too much what you enter into Network Domain, Server Name or Administrator’s email, but be sure to select the option to Install Apache “for All Users, on Port 80, as a Service”. Installing on Port 8080 would likely cause a conflict between Apache and the Application Express software that comes with Oracle XE, which runs on port 8080. As soon as you are ready, click “Next >” to continue.

Figure 3c - Apache HTTP Server 2.2 Setup Type

On the “Setup Type” screen, you can safely leave the default option of “Typical” selected and click “Next >” to continue with the installation. Advanced users may wish to tweak their installation using the Custom option, but for the sake of this tutorial, there is no need to do so.

Figure 3d - Apache HTTP Server 2.2 Destination Folder

The Apache installer will now ask you where to install Apache on your hard disk. By default Apache is installed into a subfolder of the Program Files folder, but I find it easier to install Apache into a folder just beneath the root of the C drive, which I usually name “httpd”. You can safely accept the default destination, but from here on I will refer to all Apache configuration files as if you chose to install to C:\httpd\. To install to this directory, click on the “Change” button as in Figure 3d, which should popup a screen like the one in Figure 3e. In the textbox for Folder Name, enter C:\httpd\ and click “OK”. This will close the popup window and return you to the screen shown in Figure 3d. You can now click on “Next >”.

Figure 3e - Apache HTTP Server 2.2 - Change Current Destination Folder

You can now safely click on the “Install” button, which will start the installation process. This should not take long, especially if you are using a modern PC. During the installation you should see a screen similar to that in Figure 3f below.

Figure 3f - Installing Apache HTTP Server 2.2.9

When the installation has completed, simply click the “Finish” button to exit the installer. You should now have a new icon in your system tray, like the one highlighted in Figure 3g. It should have a tiny green play symbol in it. If it has a red square stop symbol instead, left-click on the icon, and from the Apache2.2 menu, click “Start” to start the Apache service.

Figure 3g - Apache icon in System Tray

Before we move on to installing mod_owa, we will first check that Apache is up and running and functioning correctly. To do so, open your favourite web browser (I use Firefox), enter http://localhost/ in the address bar and press enter. If Apache is working, you will see a message like in Figure 3h:

Figure 3h - Apache Test Page - It Works!

Now that we have Apache up and running, it is time to install our PL/SQL gateway, mod_owa. The first thing we need to do is to unzip the archive we downloaded earlier, usually named windows_all.zip. You don’t need to worry about where you extract the files to, as we are only really interested in one of them. When the archive has extracted, open the folder you extracted them, and go into the modowa folder. From here, double click on apache22 and right-click on mod_owa.dll and click Copy.

We will now paste this into the modules folder under the Apache installation directory. Hold down the Windows key and press R to open the Run dialog and enter C:\httpd\modules\ as shown in Figure 3i:

Figure 3i - Open Apache modules folder

This will open the modules folder. Go to the Edit menu and click on Paste to put a copy of the mod_owa.dll file here. Now that we have stored the module DLL file, we now need to tell Apache to load this module into the web server and set up a new Apache Location, which will act like an Oracle Document Access Descriptor (DAD) from where we can run our PL/SQL web applications. If none of this makes any sense to you at this point, don’t worry about it, just follow the instructions closely and you’ll be fine.

To open the Apache configuration file (httpd.conf) open the Run dialog once again (Windows+R or Start->Run). This time, enter notepad c:\httpd\conf\httpd.conf and click the OK button. Notepad should launch, with the Apache config file opened and ready for editing. Scroll down to the bottom of this file (or hold down the Ctrl button and press the End button) and below all other code, insert the text from listing 3a:

LoadModule owa_module modules/mod_owa.dll

<Location /somando>
    AllowOverride  None
    Options        None
    SetHandler     owa_handler
    OwaUserid      somando/somando1
    OwaNLS         WE8ISO8859P1
    OwaDiag        COMMAND ARGS CGIENV POOL SQL MEMORY
    OwaLog         "/usr/local/apache/logs/mod_owa.log"
    OwaPool        20
    OwaStart       "doc_pkg.homepage"
    OwaDocProc     "doc_pkg.readfile"
    OwaDocPath     docs
    OwaUploadMax   10M
    OwaCharset     "iso-8859-1"
    order          deny,allow
    allow          from all
</Location>
/

When you have added the text above to your httpd.conf file, save the file, and on the Apache icon in your system tray, left-click and from the Apache2.2 menu choose the “Restart” option to restart the Apache service. If all has gone well, Apache will restart just fine and you will have a play icon in your system tray icon once again. If you did not follow my guide to install Oracle XE, you will need to change the OwaUserid parameter in the text above to your own database’s connect identifier.

Once Apache has been restarted, mod_owa should now be running from http://localhost/somando. To test this, enter that address into your favourite web browser, and if mod_owa was installed you should see an error message like the one displayed in Figure 3j. If you see a different error message, you may not have installed mod_owa correctly.

The final thing we are going to do to make sure our Apache+PL/SQL setup is working correctly is create a sample application which performs a database SELECT and displays the output in a HTML table. Before we create the PL/SQL procedure to do this, let’s run the query from the SQL*Plus command line to see what data we should expect to appear in our sample app. To open SQL*Plus, go to Start -> Programs -> Oracle Database 10g Express Edition -> Run SQL Command Line. When the command line appears log on by typing CONNECT and pressing Enter. When prompted, enter your database username and password (Username: somando Password: somando1 if you followed our Oracle XE installation guide) and as soon as you are connected, enter the SQL statement from Listing 3b to query the database.

SELECT INITCAP(LOWER(object_type)) type, COUNT(*) count
FROM all_objects
GROUP BY object_type;

You should see a result similar to that illustrated in Figure 3j:

Figure 3j - Executed SQL Statement

Now we are going to create a PL/SQL procedure that will output this data to a HTML table that we can access using our web browser. From the SQL Command Line, enter the command ed test_page. Notepad will open and say that it cannot find the test_page.sql file, asking if you would like to create a new file. Click on the “Yes” button, and paste the text from Listing 3c into the Notepad window:

CREATE OR REPLACE PROCEDURE test_page IS
    CURSOR get_data IS
        SELECT INITCAP(LOWER(object_type)) type, COUNT(*) count
        FROM all_objects
        GROUP BY object_type;
BEGIN
    htp.p('<table border="1">');
    htp.p('<tr><th>Type:</th><th>Count:</th></tr>');
    FOR i IN get_data LOOP
        htp.p('<tr><td>'||i.type||'</td><td>'||i.count||'</td></tr>');
    END LOOP;
    htp.p('</table>');
END test_page;
/

When you have pasted the above code into Notepad, click on File -> Save and then quit Notepad. Back in the SQL Command Line, you should have an SQL prompt. Here, enter the command @test_page to run the script we just created. If all goes according to plan, you should see a message “Procedure created”. We can now test this procedure from our web browser by navigating to http://localhost/somando/test_page which should display something like what you see in Figure 3k below:

Figure 3k - Our sample PL/SQL web application

That’s it! You are now ready to start developing PL/SQL web applications. In our next tutorial I will look at installing Oracle SQL Developer and how to set up a development environment for PL/SQL web application programming. Very shortly I will be writing tutorials on creating some neat web applications in PL/SQL.

Click here to view a printer friendly version of this tutorial. To download this tutorial to disk, right click here and choose “Save Target As” or “Save Link As”. You will need Adobe Reader to open this file.