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!