Exceptions in the Declarative Section !

In pl/sql programming , we all know that exceptions occuring in the executable section of the code , Here i'll discuss about the exception occuring in the declarative section.

Here is the sample code that'll raise an ORA-06502. PL/SQL: numeric or value error.[Copy Paste the code and try executing it].

DECLARE
local_variable NUMBER(3) := 9999;
BEGIN
DBMS_OUTPUT.PUT_LINE('Im in Executable Section');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Im in Exception Section');
END;

The above given code snippet will raise an error when you try executing it. This is because, in the declarative part the precision is specified as NUMBER(3) for local variable and initialized to a constant of large size. This causes an numeric or value error ,which in turn looks for a exception section for handling the exception raised.

If the exception is raised in the executable part of the code, it will be handled by exception section in the block, in this scenario exception occured in the declarative part, and since there is no exception section to handle it , control comes out of the block of code and the exceptin becomes an unhandled exception and you will receive the error message in the console.

Such kind of exception can also be handled in a simple way. [Copy paste the code and try executing it].

BEGIN -- Outer Executable Block

DECLARE
local_variable NUMBER(3) := 9999;
BEGIN
DBMS_OUTPUT.PUT_LINE('Im in Executable Section');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Im in Inner Exception Section');
END;

EXCEPTION -- Outer Exeception Block
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Im in Outer Exception Section');
END;

In this case, the exception is raised in the declarative section. Compiler searches for the exception block to handle the exception , it'll not find anything in the same block, but here there is another exception block to handle the exception raised. Now this part of outer exception section will be executed and the exception raised in the declarative section will be handled properly.

“N” Maximum or Minimum entries in a table

This is a famous question in SQL, very often asked in the interview process. During my college days, most of the companies used to ask the question. More important, we might have the same business requirement as well in our industry life.

So, I have to plan to write a series of articles on the above and also analysing the performance of the queries.

In order to facilitate with a working example, we will make use of the table employeeDetails (the schema is provided as part of bulid scripts). The sample entries/rows to work with are also provided as part of the bulid scripts. All you to have to do, just copy the bulid files to any desired location and save it. Execute the file in the SQL prompt as follows:

SQL> @Absolute Location of the file

Bulid Script:

DROP TABLE EmployeeDetails;
COMMIT;
//

CREATE TABLE EmployeeDetails(
EmployeeName varchar2(30),
EmployeeID number);
//

INSERT INTO EmployeeDetails VALUES('Ram',2);
INSERT INTO EmployeeDetails VALUES('Rahul',3);
INSERT INTO EmployeeDetails VALUES('Sharma',57);
INSERT INTO EmployeeDetails VALUES('Nishi',28);
INSERT INTO EmployeeDetails VALUES('Sathya',46);
INSERT INTO EmployeeDetails VALUES('Shreeya',49);
INSERT INTO EmployeeDetails VALUES('Sandhya',24);
INSERT INTO EmployeeDetails VALUES('Sandip',47);
INSERT INTO EmployeeDetails VALUES('Kiran',19);
INSERT INTO EmployeeDetails VALUES('Prabhu',30);
INSERT INTO EmployeeDetails VALUES('Jasmine',41);
INSERT INTO EmployeeDetails VALUES('Satyendra',47);

COMMIT;
//

Copy the above contents to sample.sql stored in c:\.
In the SQL prompt, exceute the following code:
SQL> @ c:\sample.sql

The creation of the table and the insertion of the sample entries are done. Explore the table structure and contents to understand better about the schema and the contents.
Lets start with the objective of this article having spent enough time on the background.
Problem:
The table EmployeeDetails contains two columns namely employeeName (stores the name of the employee) and employeeID (stores the ID of the employee). Our objective would be getting the maximum/minimum Nth entries from the table based on the column employeeID.

Method 1:
Lets go step by step to understand the query better.

Step 1:
select emp1.employeeName, emp1.employeeID from
employeeDetails emp1, employeeDetails emp2

It performs a join between the same table employeeDetails (type of join? Ans: self join) with no conditions (no where clause in the SQL query). If you have n rows in your table, the result would be n*n rows.

Step 2:
Lets add the appropriate filter condition in the where clause.

select emp1.employeeName, emp1.employeeID from
employeeDetails emp1, employeeDetails emp2
where emp1.employeeID <= emp2.employeeID The where clause makes a comparison on the same fields but on the different aliases of the table. Step 3: Lets add the group by clause to the query: select emp1.employeeName, emp1.employeeID, count(*) from employeeDetails emp1, employeeDetails emp2 where emp1.employeeID <= emp2.employeeID group by emp1.employeeName, emp1.employeeID order by emp1.employeeID asc The group by clause makes grouping based on emp1.employeeName, emp1.employeeID and uses the count(*) method to get the total count based on the grouping clause. The order by clause is used for better readability (to display the results in an order). If you had understood upto this, you would have got the answer by this time. Lets go ahead. Step 4: select empDetails.employeeName, empDetails.employeeID from (select emp1.employeeName, emp1.employeeID, count(*) from employeeDetails emp1, employeeDetails emp2 where emp1.employeeID <= emp2.employeeID group by emp1.employeeName, emp1.employeeID order by emp1.employeeID asc) empDetails where rownum <= &n Finally we got the solution. The understanding is left as an exercise for the reader. The solution helps us to find the N minimum entries in the table. How to get the N maximum entries in the table? Simple! Just change the condition. select empDetails.employeeName, empDetails.employeeID from (select emp1.employeeName, emp1.employeeID, count(*) from employeeDetails emp1, employeeDetails emp2 where emp1.employeeID >= emp2.employeeID
group by emp1.employeeName, emp1.employeeID
order by emp1.employeeID desc) empDetails
where rownum <= &n

Hope you would have understood. Happy Programming.

Before you move off, these are few questions to test yourself.

1) The above solution is for n maximum/minimum entries. Suppose the user needs to get the nth maximum/minimum entry, what should he do? Just change the condition from rownum <= &n to rownum = &n
It does not work? Why?

Clue:
It’s to do with the internal implementation of the rownum.

2) How about the performance of this query?

Clue:
It uses joins, group by, order by, where condition. Definitely a performance issue.

(In the forthcoming articles, let us discuss alternate ways of the doing the same).

Basics for Tuning SQL Statements

To get the maximum performance from our application, we need to
tune our sql statements.Tuning a sql statement means discovering the execution plan.
Once we know the execution plan,we can attempt to improve it.

Discovering the execution plan for a statement can be done in at least two ways
  * Issue an EXPLAIN PLAN statement from SQL* Trace.
  * Using SQL* PLUS Autotrace.

Using Explain Plan Feature :
-----------------------------------
Oracle's Explain Plan feature allows us to discover the
execution plan for a sql statement.This can be done by using a
sql statement,EXPLAIN PLAN, to which we append the query of interest.

Example :
EXPLAIN PLAN
SET statement_id='stmt_id'
FOR
SELECT * FROM table_name;

After issuing this statement, we can query a special table known
as PLAN TABLE to find the execution plan that oracle intends to use
for the query.Before using this table we need to create a plan table to
hold the results.

CREATING THE PLAN TABLE:
------------------------------------
 The easiest way to create a plan table is to use the Oracle supplied script "utlxplan.sql".
The script will be in the location $ORACLE_HOME/rdbms/admin directory.

Example:
SQL>@e:\oracle\ora92\rdbms\admin\utlxplan

The name of the table created by the script is PLAN_TABLE. This is also the default
table name used by EXPLAIN PLAN.

EXPLAINING A QUERY :
------------------------------
EXPLAIN PLAN
SET statement_id='user_defined_statement_id'
FOR query;

Note : Explaining the plan does not execute the sql statement.

DELETING DATA FROM PLAN_TABLE :
----------------------------------------------
DELETE
FROM plan_table
WHERE statement_id='user_defined_statement_id';

Deleting is necessary because EXPLAIN PLAN always adds to the plan table.
Deleting helpls to avoid possibilities of Duplication.

DISPLAYING THE EXECUTION PLAN FOR A QUERY:
---------------------------------------------------------------
 We can make use of the following query to display an execution plan once
it has been generated.
SELECT id,
   LPAD(' ', 2*(LEVEL-1)) || operation
                || ' ' || options
   || ' ' || object_name
                || ' ' ||
                 DECODE(id,0,'Cost = ' || position)   step_description
   FROM plan_table
                START WITH id=0 AND statement_id='statement_id' 
   CONNECT BY PRIOR id=parent_id
   AND statement_id = 'statement_id'
                ORDER BY id,position;

USING SQL*PLUS AUTOTRACE:
-------------------------------------
 The process is simple, we turn autotrace on and we issue the query as we normally would.

SQL> SET AUTOTRACE ON EXPLAIN
SQL> SELECT field_name1
FROM table_name
ORDER BY field_name1;

Using Autotrace is convinient because we don't need to manually query the plan
table, it is done automatically.However the query will be executed.
We can avoid seeing the results of the query executed by using TRACEONLY setting.

Example:
SQL> SET AUTORACE TRACEONLY EXPLAIN 

Note : Even when we use TRACEONLY, sql plus still sends the query to database where it is executed.
The TRACEONLY  setting just prevents sql plus from pulling back the results.

Keeping History of PL/SQL code changes

-- Create a Table for logging the modifications
CREATE TABLE source_hist 
    AS SELECT SYSDATE change_date ,user_source.* 
        FROM user_source WHERE 1=2;

--Trigger for tracking the changes
CREATE OR REPLACE TRIGGER change_hist
AFTER CREATE ON scott.schema -- Change SCOTT to your schema name
DECLARE 
                BEGIN
                    IF DICTIONARY_OBJ_TYPE
  IN ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY','TYPE') THEN
                        INSERT INTO source_hist
                                SELECT sysdate,user_source.*
                            FROM user_source
                                WHERE TYPE=DICTIONARY_OBJ_TYPE
                                AND NAME=DICTIONARY_OBJ_NAME;
                    END IF;
                EXCEPTION
                WHEN OTHERS THEN 
                    raise_application_error(-20000,SQLERRM);
                END;

PINNING THE OBJECTS IN THE SHARED POOL

Shared Pool is a part of SGA which contains p-code of compiled subprograms.
First time a stored subprograms is called , p-code is loaded from the disk to the shared pool.
Once the object is no longer referenced, it is free to be aged out from the shared pool.

DBMS_SHARED_POOL package allows us to pin objects in the shared pool. When an object is pinned,
it will never be aged out until we request it.

* Pinning the objects can improve performance,as it takes time to reload a package from disk.
* Helps in minimizing fragmentation of shared pool.

DBMS_SHARED_POOL package has 4 procedures : KEEP
                                                                         UNKEEP
                                                                         SIZES
                                                                         ABORTED_REQUEST_THRESHOLD

KEEP :- This helps in pinning the objects in the shared pool
             PROCEDURE KEEP(name VARCHAR2,
                                      flag CHAR  DEFAULT 'P');
                                      name : Name of the object
                                      flag    : Determines the type of the object

UNKEEP :- If the object is pinned in the shared pool, only possible way to remove the object is to make use of procedure UNKEEP.
                PROCEDURE UNKEEP(name VARCHAR2,
                                              flag CHAR DEFAULT 'P');

SIZES :- This procedure will echo the contents of the shared pool to the screen.
            PROCEDURE SIZES(minsize NUMBER);
                                       minsize :object with a size greater that minsize will be returned. 

ABORTED_REQUEST_THRESHOLD :- When the database determines that there is not enough memory in the shared pool,
it will begin aging objects out until there is enough memory. If objects are aged out, this can have a performance impact on other
database sessions.

ABORTED_REQUEST_THRESHOLD can be used for remedy.
                                   PROCEDURE ABORTED_REQUEST_THRESHOLD(threshold_size NUMBER);

Once the procedure is called, Oracle will not start aging objects from the pool unless atleast threshold_size is needed.

*Terminologies/Words  marked in Blocked red color requires certain references for understanding.

How can you Track the modifications done to DB objects

SELECT object_name AS "Object Name",
object_type AS "Object Type",
status AS "Status",
created AS "Created",
last_ddl_time AS "Last Modified"
FROM user_objects
WHERE last_ddl_time >= :p_check_date


The above query will retrieve all the object(s) modified on or after the date provided (p_check_date)

Concept of Polymorphism implemented with 'NULL'

In PL/SQL Syntax for basic IF-THEN-ELSE is as follows

IF(expression) THEN
Statement 1;
ELSE
local_variable:= Rvalue;
END IF;

Cosidering the above syntax to appreciate the concept of Polymorphism implemented through "NULL"

NULL AS STATEMENT
--------------------------
IF(condition is true) THEN
NULL; -- Null acts as a Statement
ELSE
execute this statement;
END IF;


NULL AS RVALUE
----------------------
IF(condition is true) THEN
local_variable := NULL; -- Null acts as a Rvalue
ELSE
execute this statement;
END IF;

The following example is only to appreciate the concept of Polymorphism

NULL AS AN EXPRESSION
-------------------------------
BEGIN
IF(NULL) THEN -- NULL acts as an expression
DBMS_OUTPUT.PUT_LINE('I am Null');
ELSE
DBMS_OUTPUT.PUT_LINE('I am not Null');
END IF;
END;

If the above autonomous procedure is executed it will always provide an o/p : I am not Null

Query to Retrieve 'n' th row from a table

SELECT field_name1,field_name2

FROM table_name

WHERE ROWID = (SELECT ROWID

FROM table_name

WHERE ROWNUM <= :nthrow

MINUS

SELECT ROWID

FROM table_name

WHERE ROWNUM < :nthrow)

The above shown query is one of the way to retrieve the 'n' th row from a table. The same query can be modified in order to provide the rows provided with upper limit and lower limit. say, rows from 5-10.

Life in IT industry

Its almost one and half years in IT industry. I took sometime to think about how this time went/passed by. No doubt, people working in IT industry are always respected in the society for the lucartive money they earn. But many argue that they loss their culture..... (please think urself... whether u contribute for this factor)

In the one and half years, i have not learnt much. If somebody asks... What is the plan you have for the next five years?. Most of the people does not have the answer to this or they have never thought about this question (due to the money and other benefits they get... once they take up the job). and I too... belong to the larger group.

Point: Don't get fascinated by the money you earn in IT industry and loss your focus. Have answers for the following:

• What do u want to become in the next five years?.... How to achieve this?....Have short plans.. (Write down in a paper and paste it somewhere, so that you get reminded everytime you see it)
• Have proper investment plans with your money.

I have always felt, whenever i am free, i am simply wasting my time surfing/chatting in the internet. Have Proper plans for the day so that you learn something useful.

Point: Plan for some certifications in the technology u work in. This would help in achieving the goals.

Remebering the basics. I recently attended an interview where i messed up the answers for some of the basic stuffs. Always spare some time to remember the things that you have learnt.

People always say... they lack in motivation, confidence and interest. These all are associated with yourself(self-..........). You may get these, when somebody talks about something...but they are shortlived (Pertain to a particular scope... may be day or so). Remember to bulid these by yourself.

Hey Navaneet..... Its a technical blog...... but to have a great start, i am starting off with this stuff.... to put you all in right track for greater achievements.

Tip for the day: You might have great ideas, but unless you speak out, the ideas are in recyle bin... Speak out before somebody cleans the recycle bin (they speak out the samething after some time)

Keep rocking and have a nice weekend...... and just find some time in between to think about yourself.

Views in Oracle

What is a view ?
Ans :One simple answer is "Views are Virtual Tables".

What is a Virtual Table ?
Ans :One Simple Answer is a Table that does not have a Physical Memory Storage in the database.

If view does not have a physical memory storage, then how the data is retrieved from a view ?
From Where the data is retrieved ? How the data is retrieved ? What really happens when you query a view? How does a view help in performance ?

If you do not have answers to the above questions , do consider the following

A view is simply the representation of a SQL statement that is stored in memory, so
that it can be reused.

SELECT empid FROM emp;

If the above query is most frequently used, then I would like to make it a view.

For creating a view,

CREATE VIEW view_emp
AS
SELECT empid FROM emp;

The above statement will create a view and does not result in anything that is actually
stored in database, except for an entry in the data dictionary entry that defines this view.

For querying this view,

SELECT * FROM emp_view WHERE empid BETWEEN 500 AND 1000;

Oracle will transform this query as

SELECT * FROM (SELECT empid FROM emp) WHERE empid BETWEEN 500 AND 1000;

Oracle stores a view's definition in the data dictionary as the text of the query that defines the view.

When you reference a view in a SQL statement, Oracle:

1. Merges the statement that references the view with the query that defines the view
2. Parses the merged statement in a shared SQL area
3. Executes the statement

Oracle parses a statement that references a view in a new shared SQL area only if no existing shared SQL area contains a similar statement. Therefore, you get the benefit of reduced memory use associated with shared SQL when you use views.

Since view does not have a physical memory storage they are said to be "Virtual Tables".
We are querying the data which is already stored in the database ; rendering data from a view is relatively faster.

Shared SQL Area : A Shared SQL area is required, to process every unique SQL statement submitted to
a database and contains information such as the parse tree and execution plan for the corresponding statement.

This is a basic information regarding views. We will be discussing more interesting concepts.