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.