Welcome!

Recurring Revenue Authors: Yeshim Deniz, Liz McMillan, Elizabeth White, Xenia von Wedel, Carmen Gonzalez

Related Topics: Recurring Revenue, ColdFusion

Recurring Revenue: Article

A ColdFusion Based Oracle Database Monitor

A ColdFusion Based Oracle Database Monitor

ColdFusion offers developers an easy way to Web-enable client/server applications. This fact has been noted and written about quite often in this journal (see, for instance, Jerry Bradenbaugh's article in the January 1999 issue of CFDJ [Vol.1, Issue 1]). Putting an application on the Web saves you the pain of client-side software installation and maintenance. Besides, the application then becomes truly portable, as it can be accessed from any browser anywhere in the world (at least in theory).

There are several excellent client-server database administration (DBA) tools available on the market - DesktopDBA by the erstwhile Platinum Technologies and DBArtisan by Embarcadero Technologies are just two examples that come to mind. We would like to show you how to build a simple Oracle database monitor (hereby dubbed OraFusion), armed with only a basic knowledge of ColdFusion and a casual acquaintance with the Oracle data dictionary.

Commercially available DBA tools go well beyond providing system and session-monitoring functionality. Most important, such tools have features that allow you to manipulate all kinds of database objects - tables, procedures, indexes. For simplicity, the scaled-down version of OraFusion that we discuss does not include the ability to view and manipulate all possible database objects. In addition to a system and session monitor, our miniversion will include only the ability to extract table/view information from the database and to write and execute your own SQL. However, you will see that it is possible to extend the application to include whatever functionality you may require. For us it is an ongoing project - we add features to it as and when we find the time!

Oracle-Related Issues
Oracle is a popular database of choice for mission-critical applications. Judging from the ever-increasing number of Oracle-related posts on the ColdFusion Forum (http://forums.allaire.com), it is evident that several Oracle installations are using the power of ColdFusion to Web-enable their applications. Now, unlike ColdFusion, it takes a while to become familiar with using the advanced features of the Oracle database server. Fortunately, the Oracle features we need to use can be conveniently discussed in a few paragraphs. We do this below.

Connectivity with ColdFusion
We won't go into the details of how to set up and test the connectivity between ColdFusion and Oracle, as this has been amply discussed in the documentation (see the Allaire Web site for details). However, a few remarks regarding connectivity are perhaps in order. A ColdFusion server can establish a connection to an Oracle database via ODBC or a native driver. The latter is available only with the enterprise edition of ColdFusion. An obvious requirement is that our database monitor should work with either type of driver. The difference between the two is generally an issue only when you invoke stored procedures or use Oracle-specific features within your templates. We will avoid using such features, so our database monitor should work with either type of driver. We have successfully tested the code provided with both types of drivers. (The code listings for this article can be found on the CFDJ Web site, www.sys-con.com/coldfusion/sourcec.cfm.)

Oracle Data Dictionary and Dynamic Views
Oracle stores information about the data and structures in the database (metadata) in a set of special tables collectively known as the data dictionary. The information in the data dictionary can be accessed through a set of views that organize the information in useful ways (note that these views may vary from one version of Oracle to the next).

These views are broadly classified into three categories: USER_ views, ALL_ views and DBA_ views. An example of each of these views is: USER_TABLES, ALL_TABLES and DBA_TABLES. Any user who is allowed to create an Oracle session (i.e., log on to the database) has access to the USER_ and ALL_ views (see Table 1). As the name suggests, the DBA_ views are accessible only to users with DBA privileges. A user querying the USER_ views gets information about objects owned by him or her. The ALL_ views return information on all objects accessible to the user - that is, the objects owned by the user and those objects to which the user has been granted access. The DBA_ views contain information on all database objects. Additionally, some DBA_ views provide more detailed information than the corresponding USER_ and ALL_ views.

The views described in the previous paragraph contain information about database objects. There is another ex-tremely useful set of database instance level views that allow you to monitor database activity and performance. These views, which usually start with the characters V$ (V$PARAMETER, V$SYSSTAT for example), can only be accessed by users with the system privilege "select any table" or with DBA privileges (which would generally include the "select any table" privilege). These views are appropriately termed dynamic views, since they reflect the state of the database up to and at a given time. We will query some of these views to obtain system and session information in OraFusion. See Table 2 for a list of V$ views that we will use in our application.

Application Security
Not all users should have access to all features of OraFusion. Clearly we would want only users with DBA privileges to view system and session information. In contrast, we would want ordinary users to see all the tables and views that they have access to. Fortunately we don't really need to do anything special to implement security, as Oracle ensures that a user will get to see only the data that he or she is authorized to.

  1. We will use ColdFusion's exception handling mechanism to trap application and database errors. This involves enclosing the relevant code within a matching pair of CFTRY tags, and using CFTHROW to throw custom application errors within the code, as appropriate. Database errors are thrown automatically by the ColdFusion server as and when it receives an error message from the database. This convenient mechanism allows us to treat application and database errors in a consistent way.
  2. Users should be able to return to the main menu or the previous page, or log out of the application from any application page. This is most easily implemented by "cfincluding" a file with the required buttons or a toolbar. Note: All "cfincluded" files are in the final listing of this article (see Listing 17).
  3. For reasons of space the code listings provided do not include any HTML code for formatting and display as in the screenshots shown in the article. You can view more screenshots of OraFusion at www.orafusion.com/cfapps.htm.
Now with these preliminaries discussed, let's move on to a discussion of the individual code templates.

User Authentication
The first thing we need is a login system to authenticate users. This consists of a login form and its action template. The login form requires users to input their database user ID, password and the ColdFusion datasource that they want to connect to (see Figure 1). See Listing 1 for code details. We would like to draw your attention to one point in the code: you could make life easy for your users by using the CFREGISTRY to retrieve the names of all data sources that have been registered with the ColdFusion administrator. These could then be displayed in a drop-down select box. However, for security reasons it is probably better not to do this.

In the action page to the login template (see Listing 2) we check inputs and then attempt to run a simple query that should be executable by any user who can log on to Oracle. Note that any query on any of the USER_ or ALL_ views will do, as most Oracle installations automatically grant SELECT privileges on these views to users with the "create session" privilege - the basic privilege required for a user to be able to log on to the Oracle server. If the query is executed successfully, we set session variables to track user and datasource information. Once the user makes it past the checks, we display the contents of the main menu (see Figure 2). If any of the above steps in the authentication process fail, we catch the relevant application or database error and offer the user an opportunity to log in again.

Once a user is logged in, we have to keep track of session timeouts. This is done via an include at the start of every template (except the login and its action template), which checks for the existence of the session variables set above (see Listing 17). This mechanism also serves to catch users who jump directly to a template within the application without logging in first. Note that Oracle provides ways of centrally administering the amount of time a database session can remain inactive before killing the session. It is up to the developer to pick the most appropriate method for controlling session activity.

Finally, our users should be able to log out of the application from any page. This is best implemented as a button or link on every template. On clicking the element the user is sent to the logout template (see Listing 3). In this template we simply loop through the session structure, deleting session variables as we go along. We then display an informational message along with a button that allows the user to go back to the login page.

System Monitor
The session monitor menu template (see Listing 4) begins with three queries that retrieve current database and summary information for the system global area or SGA (essentially the memory being used by Oracle on the server where it is running). The rest of the template simply displays the retrieved information along with a menu with hyperlinks to the following system detail options (see Figure 3):

  1. Initialization parameters: A complete display of current database initialization parameters (see Listing 5 and Figure 4)
  2. Detailed SGA allocation: A breakdown of the SGA allocation (see Listing 6)
  3. System statistics: A list of database statistics since the time the instance was started (see Listing 7)
  4. System waits: A list of "wait events" with total and average wait times for each; a wait associated with a resource occurs when two or more process are in contention for that resource (see Listing 8)

Each of the queries in Listings 5 through 8 go against V$ views. We do not know whether a user has permissions to query these views, as our login procedure does not check for that. However, this doesn't matter because Oracle will throw us an error if the user doesn't have the appropriate permissions, and we already have a mechanism to pass the bad news back to the user. If we want to, we can even trap specific Oracle errors such as ORA-00942: Table or view does not exist (usually indicating that the user does not have appropriate privileges).

Session Monitor
The session monitor consists of two templates - one showing an overview of all current sessions on the database with a drilldown to individual session details (see Listing 9 and Figure 5) and the other showing session details (see Listing 10). Again, the information displayed in these templates is extracted by querying the appropriate V$ views - the relevant ones begin with the characters V$SES. The drilldown to session detail is implemented as a hyperlink; the session ID is passed as a URL parameter.

Object Information
Our scaled-down version of OraFusion allows users to view table and view data and information. Tables and views are quite similar so we can treat them using the same templates, using a session variable to keep track of which of the two we are dealing with.

The implementation of the table/view management options is shown in Listings 11 through 14.

Listing 11 displays a menu of available options. Here we implement only two options - table/view data and table/view information. The first option allows users to see all the data in the table and the second displays a list of table columns (with data types and other information) and constraints. The latter option can be extended to show even more detailed information - storage and statistics, for example.

On making a selection from the table/view menu, the user is taken to a page that displays a complete list of tables/views that are accessible to the user (see Listing 12). This list is obtained by querying the ALL_TABLES view (remember that the ALL_ views contain information of all objects accessible by the user). A short JavaScript function ensures that the user does select a table or view before proceeding to the next template.

Listing 13 is the template for table/view data display. There is one point here that's perhaps worth mentioning. We don't know the column names until the user selects a table. So the display template has to evaluate column names and data values on the fly. This is conveniently done using the variable queryname.columnlist that ColdFusion creates with every query. A note of caution: our implementation will display all data in the table, which can be a dangerous proposition when a large number of records are returned. There are several ways to deal with this but we won't pursue them here.

Finally, Listing 14 is the template for table/view information display. Column information for tables and views is obtained by querying the view ALL_TAB_COLUMNS. For tables, constraint information is obtained from the view ALL_CONSTRAINTS and ALL_CONS_COLUMNS (see Figure 6). For views, the SQL text is obtained by querying ALL_VIEWS.

Custom SQL
The final piece of our application is a feature that allows users to write and execute their own SQL on the Oracle server. The first template (see Listing 15) is a simple form with a text area in which users can compose their SQL. The action template (Listing 16) simply executes the SQL, and then displays the data (using the same technique as in the table data display template) if the statement was a SELECT, or an informational message otherwise. We could check for various types of DDL/ DML statements using ColdFusion's rich set of string search functions, and then display a more informative message. Notice, again, that we do not bother with any error checking within ColdFusion, since the database will return an error if the statement does not make sense, or violates any access permissions.

Wrapping Up
We hope we have shown you that building a basic database monitor using ColdFusion is really quite easy. It is also a good way to learn a bit about the Oracle data dictionary. This article discusses a simple implementation, leaving open several avenues for extending the application. Some of the more obvious extensions as:

  1. Add more object management features such as Users, Roles, Procedures and Functions.
  2. Allow the user to manipulate (ALTER, DROP, for example) objects in addition to displaying object information.
  3. Add performance monitoring features.
In closing, we cannot resist making a final remark on the more general theme of this article. We have described a concrete example of how developers can Web-enable client/server applications using ColdFusion - further possibilities are indeed limitless. However, the lack of a runtime version (or pricing) for the ColdFusion server makes it difficult for developers to shrink-wrap their applications. Hopefully Allaire will address this issue in the near future.

More Stories By Kailasnath Awati

Kailasnath Awati is senior consultant at Williams & Partner
Management Consulting where he manages the U.S. operations. He is an
experienced Internet/database application developer and architect as
well as an Allaire certified ColdFusion developer.

More Stories By Mario Techera

Mario Techera specializes in Internet application design and
development with relational databases. He is one of the founding
partners of Williams & Partner Management Consulting and works out of
Munich, Germany.

Comments (0)

Share your thoughts on this story.

Add your comment
You must be signed in to add a comment. Sign-in | Register

In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.


@ThingsExpo Stories
In his session at @ThingsExpo, Dr. Robert Cohen, an economist and senior fellow at the Economic Strategy Institute, presented the findings of a series of six detailed case studies of how large corporations are implementing IoT. The session explored how IoT has improved their economic performance, had major impacts on business models and resulted in impressive ROIs. The companies covered span manufacturing and services firms. He also explored servicification, how manufacturing firms shift from se...
IoT is at the core or many Digital Transformation initiatives with the goal of re-inventing a company's business model. We all agree that collecting relevant IoT data will result in massive amounts of data needing to be stored. However, with the rapid development of IoT devices and ongoing business model transformation, we are not able to predict the volume and growth of IoT data. And with the lack of IoT history, traditional methods of IT and infrastructure planning based on the past do not app...
Organizations planning enterprise data center consolidation and modernization projects are faced with a challenging, costly reality. Requirements to deploy modern, cloud-native applications simultaneously with traditional client/server applications are almost impossible to achieve with hardware-centric enterprise infrastructure. Compute and network infrastructure are fast moving down a software-defined path, but storage has been a laggard. Until now.
Digital Transformation is much more than a buzzword. The radical shift to digital mechanisms for almost every process is evident across all industries and verticals. This is often especially true in financial services, where the legacy environment is many times unable to keep up with the rapidly shifting demands of the consumer. The constant pressure to provide complete, omnichannel delivery of customer-facing solutions to meet both regulatory and customer demands is putting enormous pressure on...
The best way to leverage your CloudEXPO | DXWorldEXPO presence as a sponsor and exhibitor is to plan your news announcements around our events. The press covering CloudEXPO | DXWorldEXPO will have access to these releases and will amplify your news announcements. More than two dozen Cloud companies either set deals at our shows or have announced their mergers and acquisitions at CloudEXPO. Product announcements during our show provide your company with the most reach through our targeted audienc...
DXWorldEXPO LLC announced today that All in Mobile, a mobile app development company from Poland, will exhibit at the 22nd International CloudEXPO | DXWorldEXPO. All In Mobile is a mobile app development company from Poland. Since 2014, they maintain passion for developing mobile applications for enterprises and startups worldwide.
"Akvelon is a software development company and we also provide consultancy services to folks who are looking to scale or accelerate their engineering roadmaps," explained Jeremiah Mothersell, Marketing Manager at Akvelon, in this SYS-CON.tv interview at 21st Cloud Expo, held Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA.
JETRO showcased Japan Digital Transformation Pavilion at SYS-CON's 21st International Cloud Expo® at the Santa Clara Convention Center in Santa Clara, CA. The Japan External Trade Organization (JETRO) is a non-profit organization that provides business support services to companies expanding to Japan. With the support of JETRO's dedicated staff, clients can incorporate their business; receive visa, immigration, and HR support; find dedicated office space; identify local government subsidies; get...
"We view the cloud not as a specific technology but as a way of doing business and that way of doing business is transforming the way software, infrastructure and services are being delivered to business," explained Matthew Rosen, CEO and Director at Fusion, in this SYS-CON.tv interview at 18th Cloud Expo (http://www.CloudComputingExpo.com), held June 7-9 at the Javits Center in New York City, NY.
DXWorldEXPO LLC announced today that the upcoming DXWorldEXPO | CloudEXPO New York event will feature 10 companies from Poland to participate at the "Poland Digital Transformation Pavilion" on November 12-13, 2018.
The current age of digital transformation means that IT organizations must adapt their toolset to cover all digital experiences, beyond just the end users’. Today’s businesses can no longer focus solely on the digital interactions they manage with employees or customers; they must now contend with non-traditional factors. Whether it's the power of brand to make or break a company, the need to monitor across all locations 24/7, or the ability to proactively resolve issues, companies must adapt to...
As data explodes in quantity, importance and from new sources, the need for managing and protecting data residing across physical, virtual, and cloud environments grow with it. Managing data includes protecting it, indexing and classifying it for true, long-term management, compliance and E-Discovery. Commvault can ensure this with a single pane of glass solution – whether in a private cloud, a Service Provider delivered public cloud or a hybrid cloud environment – across the heterogeneous enter...
DXWorldEXPO LLC announced today that ICC-USA, a computer systems integrator and server manufacturing company focused on developing products and product appliances, will exhibit at the 22nd International CloudEXPO | DXWorldEXPO. DXWordEXPO New York 2018, colocated with CloudEXPO New York 2018 will be held November 11-13, 2018, in New York City. ICC is a computer systems integrator and server manufacturing company focused on developing products and product appliances to meet a wide range of ...
More and more brands have jumped on the IoT bandwagon. We have an excess of wearables – activity trackers, smartwatches, smart glasses and sneakers, and more that track seemingly endless datapoints. However, most consumers have no idea what “IoT” means. Creating more wearables that track data shouldn't be the aim of brands; delivering meaningful, tangible relevance to their users should be. We're in a period in which the IoT pendulum is still swinging. Initially, it swung toward "smart for smart...
Major trends and emerging technologies – from virtual reality and IoT, to Big Data and algorithms – are helping organizations innovate in the digital era. However, to create real business value, IT must think beyond the ‘what’ of digital transformation to the ‘how’ to harness emerging trends, innovation and disruption. Architecture is the key that underpins and ties all these efforts together. In the digital age, it’s important to invest in architecture, extend the enterprise footprint to the cl...
Coca-Cola’s Google powered digital signage system lays the groundwork for a more valuable connection between Coke and its customers. Digital signs pair software with high-resolution displays so that a message can be changed instantly based on what the operator wants to communicate or sell. In their Day 3 Keynote at 21st Cloud Expo, Greg Chambers, Global Group Director, Digital Innovation, Coca-Cola, and Vidya Nagarajan, a Senior Product Manager at Google, discussed how from store operations and ...
Headquartered in Plainsboro, NJ, Synametrics Technologies has provided IT professionals and computer systems developers since 1997. Based on the success of their initial product offerings (WinSQL and DeltaCopy), the company continues to create and hone innovative products that help its customers get more from their computer applications, databases and infrastructure. To date, over one million users around the world have chosen Synametrics solutions to help power their accelerated business or per...
Dion Hinchcliffe is an internationally recognized digital expert, bestselling book author, frequent keynote speaker, analyst, futurist, and transformation expert based in Washington, DC. He is currently Chief Strategy Officer at the industry-leading digital strategy and online community solutions firm, 7Summits.
We are seeing a major migration of enterprises applications to the cloud. As cloud and business use of real time applications accelerate, legacy networks are no longer able to architecturally support cloud adoption and deliver the performance and security required by highly distributed enterprises. These outdated solutions have become more costly and complicated to implement, install, manage, and maintain.SD-WAN offers unlimited capabilities for accessing the benefits of the cloud and Internet. ...
In an era of historic innovation fueled by unprecedented access to data and technology, the low cost and risk of entering new markets has leveled the playing field for business. Today, any ambitious innovator can easily introduce a new application or product that can reinvent business models and transform the client experience. In their Day 2 Keynote at 19th Cloud Expo, Mercer Rowe, IBM Vice President of Strategic Alliances, and Raejeanne Skillern, Intel Vice President of Data Center Group and ...