From Oracle Forms to APEX

Oracle Forms have a legacy of well over a decade in Oracle application development. The main reason for their popularity is their tight integration with the Oracle Database. The application development can be enormously fast when using them alongside Oracle Database. But like I said in my earlier post that the problem with Oracle development tools is that they are not up to the mark. Same applies for Forms and Reports Developer. Since 6i nothing has been done to improve them. Although Oracle has incepted a web interface starting from 9i but that does not seem to be much. It uses Java in the form of JInitiator which hangs more and works fewer. I have done quite an extensive development using Forms and Reports Developer for well over two years and still doing to a great extent. I also had to manage Oracle Application Server which is hosting applications built using these two tools. So, quite a relationship: to say the least. But as the matter of fact now a day I am sick and tired of this platform.

It seems that Oracle has also backed down from these tools or at least I haven't heard anything about these tools from Oracle news sources for a very long time. Instead Oracle is supporting a shift from Forms to Oracle ADF and JDeveloper. But problem for me is that I hate anything that has anything to do with Java. So this migration is not in my concerns. However I have made a shift and the shift is from Forms to APEX. Oracle Application Express (simply APEX) formally known as HTML DB is a web based RAD tool to build application that use Oracle Database has their data source. I used the word RAD because in my opinion application development is faster as compared to Oracle Forms and in turn to any tool used with Oracle Database. The biggest pleasure for me is less effort on interface designing and yet better attractive interface. Also according to my users it is easy to use and HTML makes it run much better in browser then Forms applications.

The thing that made me think of making this transition was PL/SQL. It is common in both. All of your knowledge of PL/SQL applies to one hundred percent here. But beware of the coding style of yours, don't repeat Forms style coding here. The problem with them is that they will not give any errors at compile time but results will not be as you might expect. For example if you have to write a small trigger in Forms to return values of an item you will be quite habitual in typing as:

Return: bind_variable;

No Begin … End block and still it is perfectly ok to write like this. It is also ok in APEX as well as for as compilation goes but it will not work. You need to rewrite it like below to work properly:

Begin

Return: bind_variable;

End;

Now it will work. In more than one ways Oracle APEX is appreciable and one must do that although it has no comparison with big Giants like .Net and J2EE.

Internet Explorer-8

When it comes to Internet browsing Mozilla Firefox is the dominant force. Mostly because almost every new feature usually first appears in Firefox and then in others. It has some amazing features and add-ones that simply rock. Your browsing experience gets a whole new turn and you feel much more fun and joy. Usually these features seem to be more prominent in office rather than at home because you are much busier in office. I was, I am and will always be a great fan of Firefox because it's a love lost for me. My IT department says that you can only use Microsoft products on your PC because they are safe. Highly arguable point especially when it comes to choosing between Firefox and IE. When they first time removed Firefox I got annoyed and felt like in no man's land. I missed a lot of things among which the most important was off-course tabs. To get tabs I downloaded Internet Explorer 7 and installed it and uninstalled it after three hours. I don't know what extra features it provides but it simply fails in providing the most basic service I.e. Fast Browsing. It can't even handle Oracle Applications that run through JInitiator. I am not a fan of Java but all I know is that it has no problems with IE-6 but I tried all the things that I knew but failed to make it run on IE-7. Where is Internet Explorer-8 (topic)?

For two months I used IE-6 without any liking and disliking until one day I saw Internet Explorer-8 beta download on Microsoft website. It must be tabbed so I just jumped to it. After using it for a little while I came to realize that it has same problem while running JInitiator. For my amazement and pleasure there was a solution for the problem. The name of the solution is Compatibility View. To understand it first we need to know that what Microsoft was doing before IE-8 and what they have changed in IE-8.

The biggest complain about IE for many years was, it is not adherent to the W3C standards. So the websites designed according to W3C standards displayed shabby in IE and websites were need to designed separately for IE to target the audience that use Microsoft and IE. Starting from IE-8 Microsoft has finally changed the scheme of things; IE-8 is completely adherent to the W3C standards. Done, but what about the websites that were designed specifically for IE? Those websites will run OK on prior releases of IE but will not run properly on IE-8. This is where Compatibility View comes into play. You can turn on Compatibility View for a website that does not display well on IE-8 and IE-8 will display the website to its best using Compatibility View. You had to do this only once and after that IE-8 will recognize it automatically. So that's it I turned on Compatibility View for JInitiator websites and now they work fine until I try to close them; they never get close gracefully and you had to use Task Manager's kill process weapon. Can't figure out why this is happening.

Another very interesting feature of IE-8 is Suggested Sites. Turn Suggested Sites on to get a list of websites that are related to your current browsing session. Another one is that you can bookmark a page as well as individual paragraphs and lines inside a page. Another one is tab colors. The related tabs (a tab opened from a link in another tab) are colored same. It helps a lot in tab tracking. I think these features are new and others will be following it soon. Furthermore IE-8 now saves a session for you if you accidently lost the session due to power outage and gives you the option of reopening it next you start IE (similar to one in Firefox). And last but not in any mean least is that it is light in use while you are switching between tabs unlike its predecessor i.e. IE-7.

Welcome to Linux

I am recently trying to get my hands smooth on Linux. Fortunately I found a very good book (Linux Administration Handbook) to help me out in this process. The book is not any distribution specific rather covers almost all major distributions. Well I chose Redhat Linux-4 for two reasons. First and the dominant is the availability of software and secondly I was told that it is user friendly. Normally Linux is not associated with the term User Friendly but I think otherwise. I am not an expert just a newbie but the thing I felt during this short experience of mine is that it's all matter of familiarity. We used Windows from day one and never tried Linux or UNIX not even Mac. Windows has become the trademark as for as using Computer is concerned but it is self made. There is no harm in trying others and getting familiar with other desktops. But I must admit that Windows is easier to use then Linux but Linux is not all that difficult as well if you try to use it without taking Windows in your mind. Break the psyche and that's it.

And look Linux really cares about you as well. If you are from Mac background then it has got Gnome for your ease and if your background is Windows then you have got KDE. Mainly my focus is on administration side that I will be in need of at some stage during my Oracle DBA career. And I am feeling much comfortable and fun then it is on Windows. Mainly it is because of the flexibility and power that you have in your hands while doing all that stuff. It's also known as Rootly Powers (term from the book I mentioned earlier). I am really enjoying and I will be updating my blog with some Linux stuff as well in the future.

A very good early lesson that I have learned is that Linux gives you the option of modifying system files by hand. This is lots of fun but be careful because if anything went wrong you will have yourself in series troubles and biggest of them all is there is no restart and get OK option here.

Oracle Database and its Development Tools

In very simple words and in one line they are at the far shores of the river. On the bright side you have got the database with all of its miracle powers that you see Oracle proclaims about on its website. And well on the other side…………. May be I think one more time before saying anything. But I really thought about it a lot. I mean just look at the features of the Oracle Database and then look at the development tools that it had; you will definitely get confuse enough and will start thinking something else. I mean from SQL Developer to Forms Builder to Application Express; all have only one problem and that is they are not up to the task.

I wrote about SQL Developer in my earlier post and there is no need to say anything it anymore. There is nothing wrong with its features they seem to be good. But when you start it; the way it will consumes you machine's resources is too overwhelming. May be using something else then Java may solve the problem. There is nothing wrong with Forms and Reports Developer but they are too old to be used. After 6i nothing has been done to improve them; just a web interface which hangs more and works fewer; again thanks to Java. Although I am new to application express and just a couple of days ago I loved everything of it but till yesterday. Although very frequently it happens that your LOV may stop working but yesterday I discovered something that made me annoyed more then what when I get over my end-user's response. You cannot use updatable Primary keys on tables in application that you are planning to build using Oracle Application Express. Primary keys must be auto update either using a trigger or some PL/SQL function. Don't get confuse that it offers an option because that does not work. Updates will not work. There are works around on internet but in my case all of them failed. The problem is in Application Express 3.0; I don't whether it got fixed in 3.1 or not.

Oracle SQL Developer

A few days back I happened to read an article published in Oracle magazine named as “Power of the Two”. The power being mentioned was of Oracle SQL Developer and Oracle Application Express together. I have already talked about the SQL Developer on the same blog but that post made me to rethink my previous decision to not to use Oracle SQL Developer. For the practice of the mentioned article I downloaded the latest release of the tool that runs with JDK. By the way I am playing and trying to learn more and more about Oracle APEX these days and this was one of the reasons why I opt to explore SQL Developer again.
As a query development tool I feel a great deal of comfort with PL/SQL Developer from “allroundautomations”. I found a few new features in this release of SQL Developer including the topic about which I was reading the article about in first place, the integration of Oracle APEX objects with SQL Developer. The other feature I liked was Database copy which allows moving schemas from one database to the other without export, import traditional approach. You can also generate a script of the entire schema via Database Export feature. Also some interface changes were good enough to make my mind of using it along side PL/SQL Developer. Especially browsing of database objects was really good and easy.
Despite from all this I think the only drawback which can be good enough to quit using this tool is that it is build using Java (as much as I understood). It is horribly heavy. If you minimize it then it will take about 5 to 10 minutes to get back normal at least on my machine that has fairly good hardware support including 1.5GB RAM. This one problem makes the use and the entire array of features that Oracle proclaims about it totally useless, at least for me if not for anybody else.

Microsoft Office 12

Ever since Microsoft has released Windows Vista you may have seen that IT Gurus have un-leashed hell on Microsoft. I did the same. The reasons are quite understandable and clear for now and even Microsoft has realized it and now they have started talking about Windows 7. It is said that 90%of people or organizations that upgraded their desktops to Vista have now downgraded to XP again. Personally I am also not a fan of XP as well because in itself it is nothing as well. It is just a replica of Windows 2000 with some graphical enhancements. However I have a great deal of liking for Windows 2000. Windows XP however has the usability advantage over Vista and that is why it is still there. With Vista Microsoft also released its new Office suite (12) namely as Microsoft Office 2007.

So you are guessing it wrong, because today I am not to bash at Microsoft. Because MS Office is one of those things that I think Microsoft is really good at. Although this new release is really expensive in terms of memory and CPU usage but that's what the software's are tend to be with their new releases. I am using this office suite on Windows XP now days and did not found its interface all that difficult. Mainly I use Word, Excel and Outlook. I really don't have much time to discover its new features but one thing is there and that is with its new interface I found to be a real fun working on it. Outlook really looks amazing. As for as Excel is concerned well the thing I liked about it is listing of almost all the formulas in a separate tab. Also it has intelligence features that it provides while you type a formula. It reminds me of VS.Net which is simply awesome. And the last thing that I like the most is the ability to do blogging right from Microsoft Word. When you click on New it gives you two options of whether you want to create a Word document or a Blog Post. Well with powerful as the Word is in terms of editing I can make my blog posting much easier and much good looking.

Ora-Dim Utility

Oradim is an Oracle utility that is used to manage Oracle instance. It can be used to create, edit and delete an instance. Its typical use is when you had to manually create the instance for any reason. Among the reasons is when instance gets corrupted or when you are recovering your database after a media failure. For example if you want to create an instance then open the command prompt and issue the following command.

C:\oradim –new –sid crimson –syspwd oracle

The above command will create an instance named as crimson and the password for its sys account will be oracle. Now if you want to delete an instance then issue the following command.

C:\oradim –delete –sid crimson

This command will delete the instance. I was quite used to with these commands but here is something new that I discovered a few days earlier. Oradim can be used not only to start Oracle instance but also to start the database service at operating system well. The problem that I was facing was that I had Oracle 10g installed at home for playing. Normally I turned its services off and whenever required I turn them on. Every now and then I had to go to MMC and do the specific job. That is quite hectic, isn't it? So I searched and then devised a script that will do all the stuff for me with just a single click. It looks like this:

rem Start Listener

lsnrctl start

rem Start Oracle Database

oradim -startup -sid orcl -syspwd oracle -starttype srvc, inst

rem Start EM

set ORACLE_SID=ORCL

emctl start dbconsole

This script is for Windows. It first starts the Listener and then uses Oradim to start the database service and database itself. The parameter –starttype will define what to start. In this case both will get started. (srvc=service, inst=Instance)

Now if want to shutdown all these things then here it is:

rem Stop Oracle Database

oradim -shutdown -sid orcl -syspwd oracle -shuttype srvc, inst -shutmode i

rem Stop Listener

lsnrctl stop

rem Stop EM

set ORACLE_SID=ORCL

emctl stop dbconsole

For further options just type Oradim in command prompt provided that you have Oracle installed.

Oracle on Windows

Oracle and Microsoft are considered to be bitter rivals. Both are leading the software market of the world so the rivalry is understandable. But as the matter of fact both are forced to consolidate each other by their business needs. Just the hint of it, is the growing intentions of Oracle to support more and more .Net platform and Microsoft is doing vice versa as well. In this post I am not going to discuss their business policies about each other rather I will try to explain the specific Oracle architecture on Windows. Although Oracle is platform independent and most of the management tools of it run similar on every platform but of course there are differences as well. It is so because every operating system has its own way of handling resources and applications. Furthermore process and memory management are different as well.

Unlike Linux which is process based, Windows is thread based. In Windows process is not the work horse rather it is just a container to hold threads. Threads are created by the process that contains those threads. It then uses those threads for the real work. So when Oracle gets started a process initiates and then it creates the threads for the real work. So the different background processes that Oracle uses like PMON, SMON, DBWR etc are treated as threads by Windows. So you will not be able to see these processes in Task Manager, the same thing is possible in Linux. In Task Manager you will only see one process and mainly it will be named as Oracle.exe. This process will create individual threads for the working.

What this means for the DBA is to understand that when you start Oracle Database service from MMC then it does not mean that Oracle Database gets started. Although it is necessary to start the service but this does not guarantee the startup of Oracle Database. Normally when the Oracle service is on automatic start then it does both the tasks. But when you had to manually start then first you start the Oracle service and then issue startup command from SQL*PLUS while logging as sys. The first will create the Oracle process and startup command will start your instance (Oracle Background processes (individual threads on Windows) and Shared Global Area).

Google, the King!

Well the title of the post is huge statement especially when you talk about something as dynamic as web is. But I think and I believe that this is huge as well as it is true. Whatever Google has done in the web technologies, it has done it well and better then its competitors. Its search engine is known to the people who are not even aware of web even. And mail from Google better known as Gmail, well it simply rocks. Before Gmail I used Yahoo mail. With all due respect I found Gmail a much better option. With unlimited capacity, mail attachment of 20 MB, Ajax enabled interface, it is simply fun to use. Although similar type of options are available in other mail providers but they started it after Gmail took the initiative. That; is what makes it great.

Although my overall experience with Gmail is good enough but initially there were three simple things that made me fan of it. Those are the ability to use “.” in login name, its interface and the quickness with which it transfers large files. This last task really frustrated me when I was using Yahoo. It took hell of time to attach files and many times it just stopped giving an error message. The same thing happens with Gmail as well but not frequently. Further more and more web sites are using Google account for authentication so it adds a little more value to your Gmail account. And last but not least is iGoogle. It has some really wonderful gadgets. They will keep you updated on almost anything. It’s my homepage by the way.

The way Google has got to the top is amazing and the way it is staying up there is even more amazing and alarming for its competitors like Yahoo and Microsoft etc.

Read Carefully

Frequently it happens that while reading we ignore many things. Many times it just works but we should know what we are ignoring. A couple of weeks ago I had an interview for the post of Oracle DBA. I was really excited and was pretty much confident. In the session the person who was interviewing me asked the question that what is Instance Recovery and when and why it is done. In explaining my answer I said that when the changes of a committed transaction left uncommitted due to power failure or whatever then on next startup the SMON background process does the Instance Recovery and applies the changes of the committed transactions. The very next question was then what does the commit do?

He further explained the question that when you issue the commit command and you get reply of commit completed, then why the changes are not saved for the transaction. The question confused me at first but after a couple of minutes I figured out that commit only forces the writing of redo logs back to redo log files. It does not force the DBWR to write back to database files. So it ensures the data consistency without actually writing to the data files.

The point is that I have come through this concept multiple times but just not thinking about it caused confusion in my mind, although temporarily. Just early this morning I read it again and remembered the confusion that I encountered. So the tip of the day is to read, read, and read carefully.

OTN V/S MSDN

Normally I hate to say anything against Oracle. Well you may be guessing that I am against OTN. That’s exactly the case. The reason is its confusing documentation. I have found some really good things about Oracle from OTN but overall my experience with it is not at all pleasant. The things that can be explained very simply are written in an extremely difficult way. You will find bulk and massive amount of information but still you won’t be able to figure what exactly it is trying to say. However the equally good are Oracle Press Books. I have limited access to them but they are simply great. If you are interested in any Oracle Technology and you have got Oracle Press Book on that Technology then believe me you really need nothing else. But they don’t come for free.

Microsoft has always remained under series criticism and pretty much rightly so but here I will tell you two things what I love about the biggest giant of software industry. The things are named as Microsoft .Net and MSDN. Its not because of I hate Java but it is really true that C# .Net and VB.Net are amongst the very best languages of modern application development. And when you have the tool like MS Visual Studio then as a developer you are in heaven.

The other good thing is the documentation of MSDN. It is simply amazing. It rocks. You don’t even need Internet if you have it installed on your PC. Comparing MSDN and OTN is of no use as my words are saying everything. There are a lot of good things about OTN but the thing I am talking about is really confusing i.e. documentation. Oracle should try to simplify things rather then complicating. OTN can only help if you are well aware of the technicalities of under mention technology and you have to figure out some ordinary problems you face while working. I have never find it good enough to learn something from scratch.

Problems with Large Database

Data growth is one of the problems that the DBA of today’s age faces. As the world moves toward the information age the amount of data is increasing significantly everyday. With massive amount of data the problems that pop up are space issues, performance issues and management issues. Not only data is increasing but it is also expanding its dimensions because people tend to know more and more now a days. Databases are designed for one solo purpose and that is data storage and manipulation. So no matter what, data is increasing or not the DBA had to tackle it somehow.

I will not talk much about the problems that the DBA faces with large databases because I have no practical experience of that. However I have the experience of working as a developer on large tables. Well the bottom line is experience counts and it counts more then anything. You have read all the stuff related to SQL and PL/SQL and you think that you can merely do anything with it. Most probably you are thinking wrong because knowing and working with tools available are both different things. I am not saying that both SQL and PL/SQL are something really difficult but when you are using them against large databases, problems will occur and you will be wondering that am I doing something wrong?

I am quoting an example of my own here to explain what I am trying to say. A few days back I got an ad-hoc query to answer. The query was to find all those Mobile numbers which are in table “Test1” suppose and are not in “Test2”. I wrote the following query for that:

SQL>select a.mobileno from test1 a where a.mobileno not in (

2 select b.mobileno from test2);

There is nothing wrong with the logic or query. The only thing wrong is that “Not In” operator is too slow to be used in the above query because I have 44 million records in table “Test1” and 10 million records in “Test2”. This query ran well over 10 hours without results. How can I do this? This question made me almost punching my head down. One of my senior colleagues from whom I have learnt a lot of SQL tricks told me the master blaster query which was as under.

SQL>select a.mobileno from test1 a, test2 b

2 where a.mobileno=b.mobileno (+) and b.mobileno is null;

I copied it pasted it and in under 5 minutes or so I got the required results. Now I use this query in so many ways to enhance performance in my applications and I rarely and I mean rarely use the “Not In” operator. The point I want to make is that you will not find this in any documentation or book because they are not meant for that. You can only learn this by experience.

Cricket! A Love Lost

Since my childhood I used to love cricket. I used to watch it and I used to play as well. That love is long lost. If I had to figure out then it would be the World Cup ’99 that made me allergic of it. The cricket of Pakistan now a day is shameful. We have not an even a single world class player. Take a look at our team and you will have no desire to sit in front of your TV watching your heroes mugging around. Gone are the days when we had players like Wasim Akram, Waqar Younis, Saeed Anwar, Aamer Sohail etc who used to win matches for us. I even have some strange feelings while talking about cricket of Pakistan right now.

Bad time comes on everything. It’s not at all shameful but I think this is not simply a bad time for Pakistani cricket. When you had a president who does not know heck about cricket then what else you can expect. The problems are in wide range and the most basic of them all is that we in Pakistan have undeserving people at the top in each and every field of life and PCB is no exception. PCB has never done anything useful for the cricket. They mishandled each and every issue. The recent issue regarding Shoaib Akhtar is one prime example of that. Don’t even think for a second that I am a fan of this so called cricketer. I think the problem with him is that he made a wrong decision when he decided to be a cricketer. He should have been a model or an actor but who knows what he had done there.

This may seem a bit harsh especially considering the fact that we have cleaned sweep a series. I will not mention the team because it will generate another conspiracy. May be we should invite Holland or Zimbabwe again and then again Bangladesh so Shoaib Malik could have an unbreakable winning streak as a captain.

Oracle 11g

Oracle database 11g! For me it is still hype. Not because I have not liked it but because I does not know what is so special about it. I heard about its numerous features but do not know how they can be useful. Oh, then I am talking about Oracle database who already had many, many salient features even before 11g. And I don’t know them all. It has been quite some time that Oracle database 11g has been released and I feel myself somewhat incompetent to know that I have not gone through it yet. But upon further stress I can figure out two problems relating that. First is because of less time I have due to my job and the second is I can’t find a book about it here in Pakistan. It’s hard to find any good book here and especially oracle related.

Well I have installed oracle many and I mean it many times. So as for as installation goes I don’t need anything no matter what version it is. And with the introduction of Basic Installation method since 10g life is even easier. But like most installations the first attempt was unsuccessful. Every thing went well except EM which refused to configure; pointing out some listener specific error. Well quite frankly I was unable to figure out what the problem was because I tried every possible thing that I know to configure my database with EM but to no avail. Lastly I thought that as 10g was not uninstalled properly it may be because of that. So I cleaned my Computer from everything containing the word Oracle; even the poor JIniator got hammered out. I made a fresh install and everything went good enough and installation was successful. I took a deep breathe and cajoled myself that now I will start exploring Oracle 11g because I got it installed and running.

Bind Variables

When a SQL statement is executed, it passes through three phases. Those three phases are:

1. Parse
2. Execution Plan
3. Fetching

By parsing means to check whether the syntax is correct. If that is done then Oracle generates execution plans, determines the cost of each plan and finally decides which plan the query will follow. Remember here that the actual fetching is not done here. The only decision made here is the semantics of the query. The exact logic is determined and plan is generated. Before this Oracle computes the hash value of the query based on some predefined formula and checks whether the query with the same hash values is present or not. If it is present then Oracle skips the second phase and goes to the third phase.

Bind variables can play a vital role in this scenario. Let’s clarify it how? Suppose you run a query:

SQL>select * from emp where empno=7349;

When you run this query for the first time Oracle performs all the three phases and returns the result back. Now immediately after that you gave the following command:

SQL> select * from emp where empno=7548;

This query is very similar to the first one, but only for us. For Oracle it is a different query for which all the three phases are performed. By using bind variables instead of hard coded values you can make Oracle treat both queries similar and thus skip the second phase. With bind variable the above queries will look like:

SQL> select * from emp where empno=:v_emp;

The fact that the values of the bind variables are determined at run time rather then at the time of generating execution plan helps us here. And Oracle effectively treats both the queries similar and skips the second phase and hence improves response time.

Variable Scope and Visibility

Among the most difficult things that I perceived in the very beginning of my career was the variable scope and visibility. So I decide to write about this topic last night. As I previously mentioned I am reading PL/SQL in my free time now a days. Talking about free time, by the way it’s really hard for me now days. Back to the topic, PL/SQL has a very compact and tight syntax. I mean for every Begin there must be an End. That is really impressive. And the optional parts of variable declaration and exception handling make your code very handsome looking. And if the code is written in Caps then it just kills. Try it out.

Variable scope means the boundaries in which a specific variable can be used. For example if you declare a variable then the variable will only be visible in the specific block you declared the variable in. This means is that the variable’s scope is limited to that block only. You cannot use the variable outside that block because when the block will end that variable will also end and the memory and other resources are returned back to the OS. So referring that variable outside the block will result in an error. If you have nested blocks (blocks inside other blocks) and you have declared a variable in the outer block then that variable can also be used inside the inner block, it is possible because inner block lies within the outer block and is a part of it.

Sometime it happens that a variable is in scope but is not visible. This can happen if you have declared a variable with the same name in both the outer and inner blocks of a nested blocks structure. Now if you try to use that variable in the inner block then the variable declared in the inner block will be used. The variable in the outer block will be hidden. In this type of situation we can say that the variable declared in the outer block is in scope but is not visible. Now if you want to use the variable of the outer block then you can use Labels. Labels are identifiers that identify the variables of specific blocks. They are written as:

<>,

Include this line in the declaration section of the outer block and you will be able to access the variables of outer block using this label anywhere no matter if the names are same in the inner blocks. To use this you will write:

Outer block.variable_name,

This will give the value of variable declared in the outer block.

PL/SQL v/s JAVA

The more I love Oracle the more I hate Java despite the fact that Oracle all the time supports Java. Usually I used to show respect everything related to Computer and especially Oracle, but I am not been able to control my behavior in this matter. By the way one more thing I dislike about Oracle is their insincerity towards their products especially Application Server. Well talking about java I only used it during my courses in BS-IT. I started it as enthusiastically as I can be. But soon I started to get annoying with its complex syntax and lengthy coding. The thing you can do in one line in C, you had to write 6 to 7 lines of java to do that same thing.

One thing I would like to say to all those who may be reading this and are java developers is that my purpose is not dishearten anybody but rather I am just describing my own feelings which can be perfectly wrong. While there is another name in the topic heading i.e. PL/SQL. I like PL/SQL not because it has more features then any damn language in the world but because it is more then what it seems to be. It seems to be an extension of Oracle SQL but it is a lot more then that. Just go there and read and practice. You will be amazed of what can be done with it. Starting from Oracle 9i release of database PL/SQL and SQL share the same parser. That adds something really extra in terms of performance to an already highly Oracle Database integrated language.

When Oracle Database started with built in support for java rumors were there that PL/SQL is leaving. In reality this is not the case. Oracle is certainly not doing anything in terms of enhancing Oracle Forms and Reports Developer but still one thing is for sure that PL/SQL will be there. It seems to be very much true especially if you look at Oracle’s dedication to the language. Because they are adding new and new features to the language for every now and then. And yes we already know that it has support for Objects and object related technologies. As for as java is concerned I have no plans of using it. I never wanted to be an application developer all the time and even if I had to I will choose C# .Net instead of Java.

Starting With PL/SQL

The more I hesitate about a thing the more it proves to be useful for me. This is the philosophy that I have developed about myself over the years. Although in professional life you should not hesitate in doing something and I try my best to follow this approach. But there is a thing about how you conceive a thing. From the start I always hesitated to work with Oracle. But when I did it, then it was more then just a work. It was an experience of lifetime. Going through Oracle I always found it difficult to conceive PL/SQL. I must say that I under estimated it until and unless I started some real series application development. Well I must point out here that I am not good in development. I am just starting with it. I always did DBA tasks more then development tasks and feel more confident in doing them. But I have started thinking about it seriously.

One of the reasons is PL/SQL. I started liking PL/SQL when I heard and read about its features in different articles and blogs. The other motivation was that Oracle continues to empower it as a full fledge programming language rather then just an extension to SQL. Now one may wonder that what is the origin of PL/SQL and why the word SQL is included in naming it. While I will talk about the history of PL/SQL in a separate post, here is why this language was introduced at all.

Oracle is an object relational database management system, the first ever RDBMS in history. Like any other RDBMS the primary interface to the Oracle database is through SQL. And believe me you can retrieve almost all kind of data stored in a database through SQL. But despite all the enhancements of Oracle in ANSI SQL, the fact remains is that SQL is still a data retrieval mechanism rather then a programming language. It still lacks the features of a programming language like looping through records processing one at a time, encrypting data for security, storing code on server rather then on client side and unable to handle arrays, collections and other similar data types. So this was the primary motive behind PL/SQL. To enable the developer to use programming language features with tight integration of database. And believe me; I am saying it after thinking for a moment that PL/SQL has become a very powerful programming language for applications that use Oracle as back end database.