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