Gearing Up for Production 1.0

The Gather! project is gearing up for it's first production release. The project is pretty solid at this point, but it is lacking in documentation. I will be spending the better part of the next couple weeks on documenting the project so that we can attract new users.

I urge all who are interested in last chance beta testing for Gather! reunion organizational software to go ahead and download RC1 as soon as possible.

Thanks to all who have given feedback...it is appreciated!

Trinidad and Facelets

I am beginning to work with the Apache Trinidad API and Facelets. Thus far, I've been using Netbeans 5.5 for my development since there is now a Facelets plugin available for the IDE. The new plug in allows for Facelets projects to be produced in an easy fashion. If you have ever used Netbeans 5.5 and created a JavaServer Faces project, it is very similar.

At first glance, it appears that my favorite thing about Facelets is going to be the templating feature. This technology offers a new way to create templating within a JSF application so that all pages can have a similar layout. I also have read (but not yet tried) about templating within templating...this allows for one to create a header template, for example, and use it within a page layout template. What would that buy you? It allows for one to create various different headers using the header template and apply them to the main layout template. Take a look at this link for more details! So all in all...Facelets looks promising for templating and probably lots more once I get into it.

The Apache Trinidad project is an open-sourced API for web applications...it used to be owned by Oracle and known as ADF faces. The Trinidad project contains some of the most advanced JSF components available, and I think it is great that Oracle has donated the project to Apache. It integrates seamlessly with MyFaces to allow one the ability for easy creation of JSF applications with a great selection of advanced components and page layouts. Please check the wiki for more information. I will blog more about this great project once I've sunk my teeth into it a bit farther. So far, I can tell you that the configuration with Facelets is fairly simple...there are a few steps but it is not too bad. Netbeans 5.5 appears to function great for this application model since you now have the ability to use the new Facelets plugin. If you are interested in using the original Oracle ADF Faces components within a WYSIWYG environment, JDeveloper offers excellent tools. I have not yet attempted to use Trinidad within JDeveloper, but I will do so once I've gotten it to function as expected within Netbeans 5.5.

Duplicating Oracle DB

I have recently begun using the RMAN utility for backing up all of my organization's Oracle 10g databases. Previously, I've used manual OS commands and database hot backup procedures to perform the same tasks. RMAN is definitely much easier, and it brings many benefits to the table as well. While I am "old school" and still use my legacy backups in lieu of the RMAN backups (standard DBA best practices...or just paranoid), I think that if you are administering an Oracle database and not using RMAN then you are missing out.

My organization has many different databases. One in particular is used for hosting our maintenance management data. We have three instances of this database...production, development, and integration. The users are allowed to "beat up" the development instance and we can try out different features in this instance as well. Integration is a mirror of production, but we use it to apply patches and test prior to roll out on our production database. Obviously we all know what production is, so there is no need to describe that database.

Since I've begun to use RMAN, one of the best new features is the DUPLICATE database feature. This allows me to clone my production database in a matter of minutes. Previously, I had to create a database and then import a recent dump from my production database in order to acheive the same result. Now I just create the duplicate database instance, and then use RMAN to actually duplicate the datafiles, control files, and redo logs for me. It creates an exact replica (different SID and data locations of course) which allows me to easily recreate my integration instance.

Take a look at the bottom of this document for the procedures to follow in order to successfully DUPLICATE your organization's databases. It is worth the read....

Using Jython in Java Without Jythonc

Over the past two months, there have been a few good articles written on the concept of using Jython within Java apps without utilizing the jythonc utility. This is, in fact, the best way to invoke Jython code within a Java app.

Read more about using this technique in these articles written for the Jython Monthly newsletter.

Accessing Jython from Java Without Using Jythonc

Simple and Efficient Jython Object Factories

Netbeans Gets Better...

I've just watched a great flash demo given by Roumen Strobl which shows an upcoming addition for the Netbeans IDE. This upcoming feature is known as the Visual Web Pack and it essentially allows for drag-and-drop creation of JSF applications.

Take a look at his blog and see how sweet it looks!

If you combine this new feature along with the new Hibernate Tools Suite and Facelets Support, Netbeans 5.5 (and beyond) makes for a powerful addition to the world of great IDEs.

Time to Try Facelets

Netbeans has a new Facelets Support add-on. It is time to start learning about this great framework! It sounds like Facelets may be the key to integrating JSF and JSP, and it allows for excellent templating.

Check out facelets at : https://facelets.dev.java.net/

TNS Security

I recently went through a semi-audit where the main focal point was TNS listener security. Although the TNS listener is more secure in Oracle 10g than in previous releases, it is still a good idea to implement some security measures to protect your database. Here are a few of the steps which I have taken to secure my TNS listeners:

1. Password Protect - This one is a given. You should always protect the listener with a password

2. Listener.ora logging - I set logging on the listener so that each attempted connection is logged. This is a good way to see who is trying to access your database. The only issue which may arise with the logging feature is that the log file will continue to grow in size. You must create a job or manually create a new listener log at some point, or your log file will become too large. Creating an automated job to complete log switching is easy, but you must remember that you'll need to stop the listener before you can alter the log file. On a Windows server, you can use a batch script similar to the following (the ping command just pauses the script momentarily...this gives the listener service a chance to stop):

change_log.bat>>
lsnrctl stop
ping -n 11 localhost
echo Change log...
set yymmdd=%date:~12.2%%date:~4.2%%date:~7.2
ren <>\listener.log listener-%yymmdd%.log
lsnrctl start
lsnrctl reload

The above script simply renames your log file and when the listener is restarted, it will create a new log file. You may also wish to copy the archived log file to a new location to free up some space in the TNS log directory.

3. Use the ADMIN_RESTRICTIONS_listener-name=ON listener.ora parameter. This will disable any remote LSNRCTL access.

Hopefully via the usage of these security settings coupled with the server security, the Oracle database TNS listener should be secure.

If anyone has another means of protecting the TNS listener which should be used by default, please let me know. I am always looking for better ways to secure Oracle.

Gather Beta-1 Released

It is a little behind, but the Gather Beta-1 release is now available for download. This beta version includes a fully funcitonal Gather reunion application as well as an administrative console (GatherAdmin) which is used for maintaining the application. The beta-1 release is installable via an MS-DOS/Jython installer, and it is easily configurable.

The intent of the Gather open-source reunion application is to provide a class or organization a means of hosting web content related to a reunion event of any type. The reunion site allows for individuals to register as members, update their account, become a reunion committee member, assist in finding others via outreach submission, and much more. This application makes it possible to completely organize any reunion event with ease.

Give it a try...it is free. If you are interested in developing the Gather project, please contact one of the project administrators. For more information, please visit Gather!

Gather...Free for All!

We've decided to open-source the complete Gather! reunion package. The complete package includes two robust JSF-based applications which allow one to plan and organize any type of reunion event with ease. One of the applications is the actual Gather! reunion site which all members can view and use...the second application is an Administrative console which allows selected members to completely control the reunion site. Gather! promises to be the most complete reunion planning software available today...and it is 100% free!

The BETA-1 release is scheduled for late this week. We plan to release a fully functional Gather! site for BETA-1...minus a few of the newer features. Please visit the site for more details!

Gather - Temporary Site In Place

The temporary web site for the open source Gather! project is online. I've also added another administrator to the project. He is my former high school classmate, and he helped me develop Gather! for our 10 year reunion a few years back. I am glad to have Nathan Nickels on board with the development team.

He will be assisting me with web design for the Gather! site.

Beta-1 is soon to be released...visit the site to find out more!

Gather

I've decided upon making the ReunionWare software open source. The name of the project has changed to Gather, and it will be available for download within the next couple of weeks.

The software aspires to be an open source reunion planning software which will ease the effort of planning a reunion event. The software is comprised of a 100% customizable web application which includes a news page, member registration and account updates, and a member forum post. The software is open source and it will no doubt solve the reunion planning requirements of many.

As a matter of fact, I originally developed Gather for my high school class reunion and it worked great! Stay tuned to http://sourceforge.net/projects/gather for further updates.

Post an Article for Jython Monthly

We need more articles for Jython Monthly - July 2006! If you've got any good content, please feel free to post an article. Great articles are appreciated!

http://wiki.python.org/jython/JythonMonthly/Articles/July2006

Still Using JDBC?

Still using JDBC? I am...I still like the power of using JDBC. I do like the new EJB 3.0 ease of use with both Netbeans and JDeveloper, but I still revert back to JDBC on my older applications. There are several new features in JDBC 3.0 which is part of Java EE 5.0. Check out this article for a look at the WebRowSet.

JSF Managed Bean Binding

When coding any significant JSF application, it is always useful to have access to managed bean values from any other managed bean. This is almost a trivial procedure, but I often forget how to implement the code (after I haven't done it for a while) and end up looking in previous programs. Here is a good quick refrence for solving these issues:

JSF Managed Bean FAQ

Log4J Bug

I painfully found a bug within Log4J while working on a project. It appears that intermittantly, the following error will be thrown while using version 1.2.13:

NullPointerException at org.apache.log4j.NDC.get(NDC.java:209)

There is no work around. The best bet (and my choice of action) is to download and use the repaired version 1.3-alpha 8...even though it is still in test mode.

Building Jython in Netbeans

I've added a guide to building Jython in Netbeans to the Jython Wiki.

Jython Update

I'm a huge fan of the Jython scripting language. In case you've never heard of this excellent language, it is basically Python on the JVM. Well...it is almost Python on the JVM. At this point, Jython is a few steps behind the current Python release in terms of functionality. There are also some issues which are currently under review regarding translation of some Python constructs into the Java platform. I am currently trying to juggle my time between many things (aren't we all), and one of the things I am doing is taking a look at the Jython Bug Listing and trying to find some bugs which I can repair. If you enjoy Python then you should definitely try Jython...and if you are interested in becomming part of the Jython project, submit a bug fix. Here is a listing of the current bugs in Jython.

FACE Some New Changes

Ran into a new CSS concept called FACE in the latest edition of the Informit Newsletter. It looks promising for creating animated effects on websites with no Javascript.

Have a look: http://www.informit.com/guides/content.asp?g=webdesign&seqNum=259

PL/SQL and Faces

I really enjoy coding web applications with PL/SQL. It is straight forward and easy to do. However, sometimes the PL/SQL applications do not seem as easily managed as some of the JavaServer Faces applications which I have written.

Usually I follow the same technique when it comes to coding a PL/SQL web application. I use the PL/SQL Web Toolkit to do most of the front end work. I usually try to keep the code simple, and if possible, I try to keep each application contained within it's own package. Sometimes one package per application does not happen because of code sizing and code reuse from some other "common" code package.

One thing I do not like about PL/SQL apps is that the view and the business logic are almost never separated. There really is no MVC (Model View Controller) structure to coding a PL/SQL web application. Navigation between pages usually involves passing parameters between different PL/SQL procedures to perform different tasks and output the result. Sometimes this gets annoying (similar to that of coding with JSP) because keeping track of parameters can be a pain if you have lots to deal with. All in all, typical PL/SQL web appplications are easy to code, but they are riddled with intertwined PL/SQL code and HTP web toolkit "tags". This leads to code which is difficult to maintain. Especially if you do not look at the code for 6 months and then try to come back and determine what you did in the first place. It can be done...but it takes time to review your work and come up to speed with the tact you took when you wrote the application.

I do not think PL/SQL web applications need to be this way. They should be easier to code and they should follow an MVC structure to keep the view separated from the business logic in different packages. JavaServer Faces is an excellent Java EE coding framework. I'd argue that it could possibly be the most advanced web framework available today. I think PL/SQL needs a framework which is similar.

Why not use objects to pass data in PL/SQL apps instead of passing parameters between procedures? Why can't we code PL/SQL components like those in the JSF API? I propose that we create a framework similar to JSF for PL/SQL.

Java EE SDK & NB 5.5

I've had a chance to work a bit with both the Java EE SDK and Netbeans 5.5 beta. I love the SDK...I've been able to produce EJB 3 code with ease. I love the ease of utilizing Netbeans with the SDK. You literally install and begin coding...very little set up time!

I continue to use JDeveloper at work, and it is also great. Although there is a bit of a learning curve involved. I'd definitely prefer Netbeans if I was a beginner. Its ease of use and its new features make it a very close contender with any other great IDE.

I've also had no issues with the Sun Application Server PE9 thus far. I've always been a fan of this free Sun App Server, and the new release is no let down.

Give it a try!

ORA-07445

Ever since I created my first database on our new production box, I've been receiving an ORA-07445 error intermittantly. For a while (about a month or so), it went away and that is the time frame when we moved into production. About a week later we were getting the ORA error again.

I have done about all that an Oracle DBA could do in this situation. I've raked through the alert log, and taken a look at each trace file. I've also run the Oracle RDA (Remote Diagnostic Assistant) and sent the output to Oracle support. We've narrowed the error down to an issue with XML DB within the database. Now, we do not even use XML DB, but perhaps there is a process running within the database that is utilizing the feature. Of course, there is a known Oracle bug which corresponds to my issue. I am supposed to grant all users the XDBADMIN role in order to prevent the issue from occurring. That is not going to happen.

My solution (I thought) was to create a limited role which had SELECT privileges on XDBCONFIG. That role was granted to each user in the database....nothing was resolved. As a matter of fact, this error has been occurring on and off for the past couple of months and it is still not resolved. It does not appear to be affecting the way in which the database is functioning, and my users are not complaining of any issues. But I am still curious as to why I continue to receive this error. Is it really the Oracle bug which support thinks it is, or is there another cause which we have not yet found?

NB 5.5 Beta

It is now available for download. I will try it out this weekend. I have shyed away from Netbeans lately in favor of JDeveloper. Let's see how Netbeans rates...

And We're Off!

My golfing season just began today...love that sport! I've been in a golf league for the past two years now, and I haven't gotten any better. Well, I take that back...last year I started to get better and then I purchased a new set of RAM golf clubs...been terrible ever since.

It was a great day for golfing and I did bad as usual. Got a couple of pars, but mostly bogey and double-bogey. I think it will be my goal this year to land at least 2 birdies in one game...that is a tough goal for me considering how bad I am!

Even though I am terrible...golf is great!

Decisions, Decisions

As application developers, we come upon far too many decisions within the development of even a simple application. Developers need to make decisions about which type of business logic to use and it what cases to use it. Developers also need to make decisions about how to lay out a user interface and which components will work best for a particular situation.

I've been finding myself in a major decision state when it comes to application development...a decision which must be made prior to making any decisions at all about how the business logic will flow, or how the UI will be constructed. Which language will best suit my needs? As developers, many of us know how to construct applications in more than one language...but which one will do the job best? One of my most difficult decisions is based upon this very question.

For example, last week I was asked to develop a simple layout which will display statistical results for a number of different job crafts. I basically needed to display some data within several different HTML tables. Now, I can easily create this page using a variety of languages: HTML, PL/SQL, JSP, JSF, Python...just to name a few. Which will work best?

I am an advocate of PL/SQL since it is stored within the database, and the speed is unmatched. However, the development time with PL/SQL in such a case may be a bit more lengthy than some other solutions. I also love JSF because of it's clear separation between the view and the business logic, but it may perform a bit slower than PL/SQL. However, my development time with JSF would probably be much shorter than PL/SQL because I can use JDevelopers WYSIWYG JSF solution.

What do you choose in such a situation?

PL/SQL Application Authentication

Recently I ran across a unique way of authenticating users in PL/SQL applications. Usually when I create a PL/SQL application which requires authentication, I end up using the default DAD (data access descriptor) authentication method. I've gotten sick of the pop-up username/password prompt and I wanted to create custom log in pages like I do for my JSF apps. This newly found authentication mechanism allows me to do so.

Take a look at the link asktom.com Q&A ... of course it is from asktom.oracle.com. It describes the methodology that Oracle uses for "retaining" user passwords and how you can create a log in screen which accepts the defacto username/password and then use a stored procedure to check the validity of the user.

It is worth a look if you'd like to do away with the DAD authenication.

Dojo

I've worked with AJAX a bit now without the use of any framework. It is a very powerful, but sometimes cumbersome solution. I've begun creating my own AJAX-enabled JSF components, as well as integrating AJAX into other web apps (PL/SQL, Python, etc.). I believe that I have finally stumbled upon a Javascript toolkit which will make life easier...it is called Dojo.

I haven't dug too deep into it yet, but I have downloaded the toolkit and read some material. I think that this open source toolkit may be exactly what AJAX needs. I'll keep you posted as I delve into the realm of Dojo...as time permits of course! Thanks to java.net for the excellent Dojo article!

Take Five

Have you ever had a critical DBA moment where you are up against the wall and the clock is ticking? Maybe an upgrade didn't go so well and you had to try and revert at the last minute. Or perhaps you've just had a system crash and realized that your archive logs have been deleted or misplaced by a faulty backup.

I've had a couple of critical moments myself, and thankfully I've managed to work through all of them so far. I say so far because really as a DBA, you just live for those critical moments. DBAs are literally paid for maintaining a functionally sound database and overcoming those critical situations in a successful manner. That's your job!

Earlier this week I was able to work through such a case. One of my Oracle databases had just been migrated from 9.2.0.5 to 10.1.0.4 sucessfully. The user community was happily working and everything was fine for about 8 hours. About an hour before I was going home, I began receiving calls...from the entire community. Our third-party application which uses this database was no longer allowing new records or modifications to be made. The database was more or less in a read-only state.

My first thought was that perhaps something was overlooked in the database creation, and my datafiles had filled up without autoextending. All files looked fine. I also checked my tablespaces and they were configured properly. My SGA and PGA looked ok, and there were no database locks. Panic began to set in because now the users were unable to work for about 10 minutes. This is the point where you must take five! Maybe not really five minutes, but at least take a step back and compose yourself if you are ever in this situation. I've heard horror stories of DBAs taking the users out completely because they try to restart the instance. I've even heard of those who try to change SGA size, or tweak initialization parameters in such a situation. Not that those people are terrible DBAs, but pressure can make for some bad decisions. Do not let this pressure determine your outcome.

Back to my situation, I realized that I hadn't even checked for any invalid objects yet. My original assumptions as to what was happening did not lead me into that direction since I just ran utlrp.sql to compile all INVALID objects when I imported the data earlier that morning. Nonetheless, I took a look and found my problem. Someone modified a procedure which broke one of the triggers...they loaded an older version by accident This was the cause of the freeze. Once I loaded the newer version of the procedure, the database was back to normal.

Just another day's work as a DBA...and I got out of work on time too.

JDeveloper Rocks, Netbeans Rolls

Recently, I've become an avid user of the Oracle JDeveloper IDE. I must say...it rocks! I was not so easily persuaded into using the IDE though. As a matter of fact, I downright despised JDeveloper for some time...that was a HUGE mistake on my part.

When I first began Java development, I was actually using it to write stored procedures for one of my Oracle databases. I had the need to read and write external OS files from within one of my apps. There were really no great tools for PL/SQL development that offered Java code ability which matched up to what Java IDE's had to offer. I then took a look at JDeveloper and tried to use it for developing my Java stored procedures. It was a pain in the neck! I had issues trying to create a simple Java class...you need to first create a project...and so forth. The real problem was that I did not have the time or the inclination to read about how the IDE works first...I just wanted to use it. I began to develop JSP-based web applications and also tried to use JDeveloper for that. I hated the fact the the applications were structured into an Oracle proprietary format (by default), and I had to manually manipulate the format of my WAR file in order to make it work with the Sun Java Application Server. This is why I despised the IDE.

At that time, I really preferred Netbeans because it was easy to use and required virtually no "study time" in order to make great apps. I began using Netbeans back in version 3.5, and I thought that it was great. I could create a Java class easily enough, and my JSP apps were ready for deployment straight out of the IDE! I felt that Netbeans blew JDeveloper away at that time. Since then, Netbeans has really come a long way...an already great IDE has been made even better, including Java EE wizards and auto-creation of EJB3 classes in the latest beta version. I love Netbeans for it's ease of use. Netbeans is a great IDE!

Now, I just actually started using JDeveloper a couple of months ago once again because I was learning EJB3, and I saw some great JDeveloper tutorials on the Oracle site. I started to use the IDE and had the same issues once again...I was unable to deploy to Sun Java Application Server easily. I did not like that. Well, I spent a bit of time reading through some documentation, because I could clearly see that JDeveloper had some features that would make my life much easier (visual JSF development, direct deploy to OC4J...which is what my employer uses). After a bit of studying and trial-by-error using the JDeveloper IDE, I have seen it's power and have begun to use it each and every day. Especially in my role as a DBA/Developer, it comes in extra handy. I can create PL/SQL applications and database objects directly in the database using JDeveloper...no more text file development! I can also easily create a Java stored procedure and it's PL/SQL calling procedure within the IDE. I've also converted some of my JSF applications from Netbeans over to JDeveloper easily enough...and I've begun to visually create my JSF apps which cuts my development time way down. Being a DBA, I can use more time on other things than manually coding nested panels within a web page!

All in all, I want to spread the word that Netbeans is great, and JDeveloper is also excellent. I actually advocate each of them and still use them both. I use Netbeans still for any development at home. It also has a great new GUI development tool (Matisse) which makes Swing development much easier. Newbies and professionals who want to simply open an IDE and begin developing with little study time will really like Netbeans for it's ease of use. If you've got some time, I recommend that you read about and learn JDeveloper...especially if you use PL/SQL as well as Java. It will save you lots of time in the end!

Is My Database Safe?

I've just spent the better part of a week securing my "external" database scripts that reside on our production server. Well...actually just securing scripts for one of the databases which I administer. I inherited this particular database from a previous DBA and it has been in place for quite some time now. There were many legacy standards in place when I took over the administration of the database. Unfortunately, clear-text passwords within external database scripts were an issue.

I've got several scheduled jobs which run these external scripts each night for synchronizing data and so forth. Now that I am tasked with "hardening up" the security on the database, I've got to address such issues as clear-text passwords. Therefore, I began by doing some research to see how others were taking care of the issue. I really did not find many clues besides the old "connect up without a password as SYSDBA". That is definitely one way to get around storing a clear text...but then again, I do not want to run these scripts as a DBA.

The solution I came up with is a java program which I wrote that reads in an external property file with connection details in an encrypted format, and then that decrypts the details and connects to the database. I've got an OS batch job which invokes the java program, and that program then makes a database connection and runs the specified script.

VIOLA...I solved my problem of storing clear text passwords since I can now store them in an encrypted format. But, am I any more secure than I was before I wrote the java utility? I think not...if someone hacks into the server, then what is to stop them from modifying my external script to do something undesirable? I guess we just create a "dumb" user who has privileges to run such tasks on an as-needed basis. That way if someone modifies the script, then hopefully my "dumb" user account will not have privileges to harm the database.

OK...but now while we are on the subject of hackers gaining access to the database server, what is to stop them from cracking the security and obtaining access to such files as the alert log, or even worse...my backups!

We can keep on going....so it begs the question...is my database safe?

PL/SQL Web Services Made EASY - (Part 2)

Finally...I have a moment to post an example of a PL/SQL web service! So, I went into the nitty-gritty details of doing so last week...not much to it. Just create a stored procedure which utilizes the OWA htp packages to expose PL/SQL to the web. If you have any questions regarding further detail, please give me a shout. Otherwise, here is a stored procedure example.

This stored procedure is actually a member of a PL/SQL package which contains many web services. I believe it is a good practice to package similar code together when possible. If you include all of your PL/SQL web services in one package, then maintainablility will be easier. I've altered the code slightly to remove any proprietary information which cannot be exposed, however, you should be able to get the general idea.

--*********************************************
-- FUNCTION: EMPLOYEE_SEARCH_SERVICE--***********************************************
PROCEDURE EMPLOYEE_SEARCH_SERVICE(search_text varchar2) IS
CURSOR EMP_SEARCH_CUR IS
SELECT ID, FIRST_NAME, LAST_NAME
FROM MY_TABLE WHERE EMP_STATUS = 'A'
AND ID LIKE '%' search_text '%'
OR FIRST_NAME LIKE upper(search_text) '%'
OR LAST_NAME LIKE upper(search_text) '%'
ORDER BY ID, LAST_NAME, FIRST_NAME;

return_prefix constant varchar(10) := 'OUTER XML TAG (would not post correctly on blog)';
return_elem_pre constant varchar(5) := 'INNER XML TAG';
return_elem_post constant varchar(7) := 'CLOSE INNER XML TAG';
return_suffix constant varchar(11) := 'CLOSE OUTER XML TAG';
return_string varchar2(4000) DEFAULT NULL;
counter number := 0;

BEGIN
return_string := return_prefix;
owa_util.mime_header('text/xml');
htp.htmlopen;
htp.headopen;
htp.headclose;
htp.bodyopen;
htp.print(return_prefix);

FOR emp_search_rec IN emp_search_cur LOOP
htp.print(return_elem_pre ' id="' counter '">');
htp.print('' emp_search_rec.id '');
htp.print('' emp_search_rec.FIRST_NAME '');
htp.print('' emp_search_rec.LAST_NAME '');
htp.print(return_elem_post);
counter := counter + 1;
end LOOP;
htp.print(return_suffix);
htp.bodyclose;
htp.htmlclose;
END EMPLOYEE_SEARCH_SERVICE;
-- *********************************************

You then place the procedure definition in the package header:
procedure employee_search_service(search_text varchar2);

That's all there is to it! At this point, you can load your package into the appropriate database schema, and as long as there are no compile errors you should be able to access the service via a URL. You'll need to use the following syntax for your URL:

http: //your-server-name:port_number/DAD_NAME/package_name.procedure_name?search_text=your_search_criteria

The result should be an XML page which can then be parsed using JavaScript (good for Ajax) or another XML parsing technology.

Now...that was easy!

Great Book!

I know...I haven't forgotten about Part II of the PL/SQL web services blog!

I actually have had lots of my free time lately consumed by reading an excellent book. Which one? "Expert Oracle Database Architecture - 9i and 10g Programming Tips and Techniques" by Tom Kyte. If you are not sure who Tom Kyte is the you must not do any work on Oracle databases. He is truely genious when it comes to Oracle development...PL/SQL, Java, C, etc...

The most recent book touches on many key aspects for developers and DBAs who work on Oracle database. I feel that Tom's views on database app development are right on target--"You must know the database and what it can do for you".

Anyone who develops Oracle applications should give it a read...it will be well worth the time! Thanks for another grat piece of work Tom!

Next blog...PL/SQL web services...Part II...

PL/SQL Web Services Made EASY (Part I)

I've been working on a PL/SQL web application for the past couple of weeks, and it has been a nice break from JAVA. I use the OWA (htp) packages to complete the web code. In my most recent application, I had the need for an auto complete text field which populates with an employee ID and name. Luckily, since PL/SQL web services are a breeze, this was an easy task!
If you've never created a PL/SQL web application before, I suggest you take a look at the references at this site: http://www.oracle-base.com/articles/9i/PLSQLWebToolkit9i.php as they will be able to assist you greatly. They are easy to create once you've got the hang of it.

All that is required to make a PL/SQL web service is a stored procedure. Actually, the best practice is to make a single PL/SQL package which contains all of your web service stored procedures. Your stored procedure is treated as an html page. The procedure simply accepts whatever parameters which are required to fullfill the CURSOR query and then displays the results in XML format.

In Part II of this blog, I will show an example!

Java - EE 5

I am trying my hand at the beta version of Java EE 5. I've downloaded the complete bundle and installed it without any issues. I am anxious to begin develpoing apps with the EJB 3.0 technology, but I've been unable to do so thus far. I have had bad luck with Glassfish so far and the Netbeans 5.5 IDE still seems buggy to me. Don't take my word for it though...give it a shot for yourself. I fear that the issues I've seen thus far have something to do with the Postgresql back end whih I'm trying to use.

I will give the latest Netbeans 5.5 a shot with the beta SDK and report.

Entering the Blogger

I am looking forward to posting my most recent experiences and findings to this blog. I am an experienced database administrator and applications developer. I've worked on various databases including Oracle, Postgresql, SQL Server, and FileMaker Pro.

Java and PL/SQL web based application development is one of favorite aspects of computing. I've developed several of each and even dabbled in the scripting languages such as Python, Jython and Groovy a bit. I am an advocate of the JSF framework for Java EE development. Most recently, I've begun to work with the EJB3 technologies.

PL/SQL is perhaps the most powerful development language available for any Oracle developer/DBA. Ranging from stored procedures to web services...PL/SQL is a robust, organized, and pleasurable language to code.

Please assist me by correcting any mistakes I may make in my tutorials. I am also always looking for the best technologies for the job. So please keep me posted on anything new that I may have overlooked!