PL/SQL Engine

From its birth SQL lacks the procedural processing like other languages have. And the need was felt that if some way these capabilities can be added to SQL then it will be more useful then it is without them. PL/SQL is nothing but Oracle’s answer for that. If you belong to Oracle world then the learning of this language can benefit you more then you can think. Because it is used in all the development tools of Oracle and also you can use it against the Oracle Server as well. Well benefits aside I am going to talk about the PL/SQL engine and how it works.

PL/SQL is present there where PL/SQL is. Suppose you are working on Forms Developer product of Oracle then let us see how a PL/SQL procedure is compiled. The PL/SQL engine breaks the procedure in three different types of blocks of statements. All the statements that consist of procedural constructs like IF, For Loop etc are processed by Procedural Statement Executor in the Forms Developer itself. All the SQL statements are sent to the Database’s SQL Statement Executor. And all the PL/SQL statements stored in the database are sent to process in the Oracle Server’s Procedural Statement Executor. The net result is combined and is sent back to the user or caller function.

Microsoft’s .Net

Since the beginning of this year I am closely attached to .Net platform. I feel that I will be selfish if I do not talk about this revolutionary application development IDE. Well I am not a very good .Net developer and neither I can be in this short amount of time. I am just going to discuss what I feel about it in my little experience. Well in very simple words I would like to say that I like it. And I really appreciate it and love developing in it. All the promising things that you find in Microsoft documentation like ease of development, CLR features and language independence are there.
I have been mostly tightened to VB.Net. The language is great in itself especially with the introduction of Object Oriented Programming. Whether it is pure object oriented or not is an issue that is very daunting in itself but it really has some very sound OOPs implementations which I think are a lot better then its counter part Java or at least they are really simple to use as compared to Java. Another significant enhancement from previous version of VB is the introduction of proper Exception Handling methods. The old “On error resume next” was simply pathetic but that at least made Windows 98 to run despite everything that happen with it internally.
If you are a .Net developer then I would like you to share your thoughts and correct me.

Free Books

Following is a link to a very useful site of free books on almost all topics relating to computers. It also provides links to other sites of of free books.

Click Here to Follow!

Restore Points

Transaction mechanism is one of the most important aspects of any database and likewise every database implements it. Transaction is an atomic block of SQL statements that can be executed against a database. Committing a transaction means that the changes made by the transaction should be made permanent. And likewise rollback means that the changes made by transaction must not be applied to the database. Transaction mechanism works logically in the memory and not on the physical hard disc. It means that once you have committed a transaction or have rolled back then you can’t do the other. Similar for save points; save point allow you to specify specific points within a transaction so that you can perform specific operations on specific points within a transaction. It all sounds good with the obvious limitation described above. Oracle takes this concept to the next level in 10g.
The very simple but still very powerful feature for the DBA is of restore points. Restore points allow you to bring your database back to a previous state. Consider the following situation. Suppose you are working on your database and made a change in one of your tables and also committed it. You shut downed the database and went back home. There you realized that the change you made was a blunder. You can undo the change with Oracle’s flashback query but what if the changes were in hundreds. You need an entire day. No. you did not if you would have implemented just one thing. If you have created the restore points after critical updates every time doing them then you can roll back your database just with a single command. The creation of restore points is through very familiar “create” statement. You can create restore points with the following command.
create restore point restorepointname; But remember this command only works for Oracle Database 10g Release 2. So create restore points after every critical update.

OOP in PL/SQL

The name PL/SQL implies that it is a procedural language. But from 9i onwards PL/SQL started support for objects. The focus of procedural paradigm is on data structures like procedures, functions. On the other hand Object Oriented Programming (OOP) revolves around modeling the real world entities in the form of objects. Objects consist of attributes that describe the object and methods that describe the behavior of the object. Method is a design level terminology that is implemented through procedures and functions.
The implementation of OOP in PL/SQL is almost similar to other languages; of course syntax and terminology differs a bit. All the tenets of OOP like abstraction, encapsulation, inheritance and polymorphism lie there. In Java, VB.Net, C++ and other OOP languages there is a concept of grouping objects in the form of classes. The similar concept in PL/SQL is termed as Object Type. There are two types of objects in PL/SQL. Transient objects are those who get initiated with the start of the program and abolished with the termination of the program. Permanent objects are those that are stored in the database. As you know Oracle database supports objects from its version 8. In database object types are treated like any other schema objects and can be accessed and manipulated like the others.

Extproc-Multithreaded Agent

More for less is the inherent attribute of Oracle technology. Although Oracle is resource hungry software but once it is installed it can be made to use its resources optimally. One of the prime examples of this is shared server architecture. I am not going to discuss the shared server architecture anymore or how it works here. But rather I am going to point out how Oracle optimizes its resources when it sees too many external routines users are connected to the database. Well Oracle does not do this automatically but it can be made through multithreaded agent. The configuration of multithreaded agent is complex but once done then it can be shared between multiple user sessions.
The default behavior of Oracle is to fork a new Extproc agent for every new user session. The default works but consumes too many resources very quickly. Therefore it is recommended that you use multithreaded agent when too many user sessions are expected to be connected to your Oracle database. Regardless of how it is configured here it is how it works. When the multithreaded agent is configured the incoming requests first come to a monitor thread. The monitor thread puts them in a FIFO queue. Monitor thread actually maintains the load balancing information about the dispatcher threads and puts the request in appropriate queue. Here dispatcher thread picks the request and puts it in another queue. Task threads get the request from this queue and accomplish the task and return the result of the request to the appropriate user session.

Native Dynamic SQL

Native Dynamic SQL! When I first heard the term the very first confusion raised in my mind was what Dynamic SQL is. Or at least what is static SQL so that I can differentiate between the two. Here is what I understood. When you know the tables and columns that you are referencing in your SQL then the SQL you are using is static. There are many advantages of using static SQL. First all the errors and exceptions that can occur at runtime are known or can be found. And more importantly all the techniques of SQL tuning are applicable to static SQL only. So why dynamic SQL?
There is another school of thought in application development that says; “you should write robust, reusable and dynamic code modules”. This is object oriented programming. Many times in application development it happens that you don’t know the columns and tables that you will be interacting with. For that purpose and many others you must rely on dynamic SQL. Thus dynamic SQL is SQL that decides at runtime what tables to retrieve data from and what columns to retrieve.
Having said what is dynamic SQL is not enough? You must know how to generate it to be beneficial. Oracle provides two ways for the generation of dynamic SQL. One is DBSM_SQL built in package and the other is Native Dynamic SQL. Which one is better of the two is something that is dependent on your personal judgment. However NDS outperforms DBMS_SQL in performance. However you can’t ignore the DBMS_SQL because it is the legacy way and billions of lines of code have been written using this package; so in order to understand that code you must understand this package. Also NDS does not support the generation of dynamic SQL where name, number or types of arguments is not known. So you must use DBSM_SQL package for those situations.
On the other hand NDS is better then DBMS_SQL in many ways. First and I think the most prominent advantage is that its syntax is pretty similar to normal SQL syntax. Performance is also a lot better then the DBSM_SQL package. And also the NDS is the future that we will be using.

Extproc Architecture

One of the efforts in the application development has been to isolate the information source mainly databases from programming logic. External routines are the natural solution for that. As the leading database in the world Oracle also provides integration with external routines. External procedures are the Oracle’s solution for incorporating the programming modules with your database. Oracle provides a comprehensible architecture for that namely as Extproc architecture. Extproc architecture comes with every database that you install.
The default listener for your database does have a handler for Extproc architecture but you need to setup a separate listener for Extproc agent. Oracle’s Extproc allows you to communicate with any language that is callable from C language. However callback to the database is restricted to those languages only that are supported by OCI (Oracle Call Interface). OCI supports C, C++, COBOL, FORTRON, PL/2, Visual Basic and Java. Now let us put it simple what one had to do to run an external routine from Oracle PL/SQL program.
First the language you are using for your external routines should support shared libraries or DLLs. The shared libraries are program modules that you incorporate in your programs. Also the platform you are using also support shared libraries. You need also to configure a separate listener for Extproc agent. And also you should have PL/SQL shared library that will work as a wrapper and will call the external routine. Here is how it works internally.
The PL/SQL wrapper first translates the data types with the data types of your programming language. Secondly it forks a call (RPC) to the external routine. This call is caught by the Oracle Net Services provided the listener is correctly configured. Oracle Net Services then forks an Extproc agent that will communicate with External Shared library. The result of the Shared library is returned to the PL/SQL wrapper. The PL/SQL wrapper translates the data types again to the native data types and displays the results.

Development Happiness

One thing I really feel about myself is that I am a cheater; and a really good cheater. But the good thing is I do not cheat everyone. As the matter of fact I cheat only one person. And that person is none other then Sir Fahd. I do not feel ashamed because of two reasons. First I think there is no harm in cheating your ideals. If you want to build yourself, your thoughts, and your career then doing what they do and thinking what they think is not a bad thing. Secondly every student or every good student I should say is a cheater of his teacher. The blogging I do is mainly dependent on what I read from Sir Fahd’s blog especially when he talks about computers.
Few days back I read Sir Fahd talking about development pleasures. He said one sentence that is bothering me ever since I read it. That was “Happiness is a key to success and PHP is giving the developers that happiness”. Although technically I can’t speak about PHP much because I am on the far end of world i.e. ASP.Net. I am really having been in amazement what Microsoft has done with ASP in the form ASP.Net. Why there are two or three things where one can suffice. Just to confuse you? I don’t know anybody else but that really confuses me. There are two or three or even more ways to do a thing and more worst is that you need all of them. I really appreciate PHP especially the why it handles the HTML controls. I wonder why the people here in Pakistan are so unaware of the powers of PHP. Back to the Sir Fahd’s sentence. Well I can read between lines what it likes to be of being a web developer in PHP. At least there will be no drag and drop and no “Next” button. I really hope that some day I will get the chance to work on PHP. I am already in love with first love of Sir Fahd (Oracle); will it be the case with me again for PHP. I am afraid it will because it has already started.

Problem during installation and configuration of oracle on Windows Server 2003

Normally Security is considered to be an unknown word in Windows environment. And it is true. Because if you are a Windows user then you know that up till Windows 2000 there is no built in firewall. Quite frankly I am not a system administrator and do not know what is precisely needed to be a secure operating system. But I have lots of experiences relating Oracle on Windows and I have found that most of the time those sayings seem to be true. Except for one day when I tried to install Oracle database 10g release 1 on Windows server 2003.
Before that day I was quite used to install and configure most of the Oracle products on Windows XP. The first mishap which was frustrating me for over 6 or 7 months was not been able to find a right CD of Windows server 2003 (thanks to piracy). When I got one from one of my class fellows (for which I am really thankful to him) because it was not only a right CD but also was a purchased version. I installed it and went straight to Oracle. For my first surprise the installation stopped for reasons I was unable to understand. When I read the documentation service pack 1 was required. So I consulted the same friend of mine again and got it. Installed it and then inserted the Oracle CD again.During the installation of Oracle Windows firewall throws some exceptions. If you block them or unblock them it really does not matter if you are using Windows XP but it does if you are using Windows server 2003. Even simply turning firewall on can cause problems on Oracle side of things? I got this lesson after two or three failed installations and you probably know that uninstalling Oracle is much more difficult then installing. So keep firewall off.
After that Oracle got installed and up and running. I took a long breath and then putted Oracle developer suite CD in and started its installation. It got installed within no time. When I run the form for demo it stuck. When you are using Oracle developer suite 10g the forms will run in browser and first time it will ask for the installation of JInitiator (an Oracle utility to run java applets). But there was no such thing. The browser opened and stuck every time I tried to run the form.I tried my level best to fix the problem but to no avail. I checked the firewall which was off, I tried to start the listener on different ports but to no avail, everything else was just fine enough except the browser gets stuck. Just playing around tired and exhausted I opened the internet options. Turned the browser security level to low and then run the form. It worked. I jumped with joy and wondered that is it the operating system whose name starts with ‘W’.

What constitutes a complete oracle database backup?

Backup and Recovery is the single most important task of a DBA. No matter how efficiently you do all the other tasks like installation, configuration, performance tuning etc. if your data is not safe then they are of no use. While backing up Oracle database first thing you need to know is what to backup.

There are four types of files that are needed to backup.

The first type of files is your relative operating system files. You need to consult your operating system documentation for important files of your operating system. These files do not change over the normal operation of database so you don't have to backup them regularly. Normally a backup after all the upgrades and patches installed is sufficient. 

The second type of files is Oracle product binary files. Like the first category of files they also do not change regularly so you don't have to backup them continuously. A backup after the entire configuration is normally good enough.

Intersession communication using PL/SQL packages

Intersession communication as implied by name refers to the communication between two user sessions. The sessions can belong to a single or they can be of two different users.

There are two ways you can achieve this inside an Oracle database.

• Using database structures
• Using PL/SQL packages

First method requires the use database structures for example tables, synonyms etc. This type of communication is suitable if you want your data to be shared between sessions. Of Course nobody would like to create database structures just for the sake of conveying a simple message. Secondly this type of communication is limited by transaction control which means that in order for another session to see your message you must commit your current transaction.

For message communication and similar requirements between sessions Oracle provides two built in PL/SQL packages namely DBMS_PIPE and DBMS_ALERT. The functionality of both is a bit from one another and each serves a different purpose.