2nd PL /SQL Program

DECLARE

emp_name_c CHAR(32);
emp_name_v VARCHAR(32);
BEGIN

emp_name_c := 'sateesh.bagadhi';
emp_name_v := 'sateesh.bagadhi';

IF emp_name_c = emp_name_v THEN
DBMS_OUTPUT.PUT_LINE('The names are the same');
ELSE
DBMS_OUTPUT.PUT_LINE('The names are NOT the same');

END IF;
END;
/

output:

Writing and Running PL/SQL Program in windows

Step1) Write the PL/SQL code in the note pad and save it as with extension name .sql
ex: "test.sql"

DECLARE
x NUMBER;
BEGIN
x := 72600;
dbms_output.put_line('The variable X = ');
dbms_output.put_line(x);
END;
/

step 2) oracle sql*plus
type
SET SERVEROUTPUT ON
@ c:\orabook\test.sql

OUTPUT:
SQL> set serveroutput on
SQL> @ c:\orabook\test.sql
The variable X =
72600

PL/SQL procedure successfully completed.

NATIVE DYNAMIC SQL

What is Dynamic SQL?
Generally programmer knows the SQL statements that are to be executed at the time of writing program. However, in some cases the programmer may not precisely know the command that is to be executed at the time of writing program. Instead the command may vary from execution to execution and it is to be constructed dynamically (at runtime). An SQL statement that is constructed “on the fly” is dynamic SQL statement.

Until Oracle8 the only way to execute dynamic SQL was by using DBMS_SQL package. Oracle8i introduced native dynamic SQL, where you can directly place dynamic SQL commands into PL/SQL blocks. Native dynamic SQL is faster then using DBMS_SQL package.

Dynamic SQL should be considered only when static SQL is not possible. Dynamic SQL will impact performance.

Why do we need dynamic SQL?
We should use dynamic SQL where static SQL doesn’t support the operation that we want to perform.

The following are the situations where you may want to use Dynamic SQL:

 You want to execute an SQL DDL or DCL command that cannot be executed statically. That means the command is not allowed in the PL/SQL block. For example, you cannot execute DROP TABLE command from a PL/SQL block.
 You want to access a table or a view but do not know the name of the object until you run the program.

An Example of Dynamic SQL
Let’s look at an example. Assume we have to drop a table. But the name of the table to be dropped is not known. The name of the table is formed using the word SALES and four digits year. For example, it the current year is 2000 then table name would be SALES2000.

That means depending upon the current year the name of the table changes. So the DROP TABLE command should drop the table of the current year and the current year is to be taken from the system date.

The solution to this problem is to construct DROP TABLE command as follows:

cmd := ‘DROP TABLE sales’ || to_char(sysdate,’yyyy’);

The above statement will put the required command into a char variable and then the command in the char variable is to be executed with EXECUTE IMMEDIATE command.

Execute Immediate Statement
This statement is used to execute a command that is a char variable. In the above example we put the required command in a variable called cmd. Now, we can execute the command as follows:

EXECUTE IMMEDIATE cmd;

Now let us examine the complete syntax of Execute Immediate statement.

EXECUTE IMMEDIATE dynamic_string
[INTO {variable[, variable]... | record}]
[USING [IN | OUT | IN OUT] bind_argument
[, [IN | OUT | IN OUT] bind_argument]...];


Dynamic_string Is the string that contains the command to be executed.
Variable | record Is a variable into which values retrieved by SELECT command are to be copied. If a record is given then complete row is copied into the record. Record must be a variable of user-defined data type or %ROWTYPE% record.
Bind_argument Is the value that is to be passed to SQL statement that is being executed. This value will replace a placeholder in the command (more on this later).

The following stored procedure takes the name of the table and drops the table.

create or replace procedure droptable ( tablename varchar2) is
cmd varchar2(100);
begin
cmd := 'drop table ' || tablename;
execute immediate cmd;
end;

You can now call the above procedure to drop a table. You have to supply the name of the table to be dropped. Remember that in PL/SQL DDL commands are not allowed in static SQL, so dynamic SQL is required.

execute droptable('oldsales');

The following is another stored function that takes the year and deletes all rows from the tables whose name is formed as SALESyear. Where year is the value passed to the procedure.

create or replace function deletesalesrows (year number)
return number is
cmd varchar2(100);
begin
cmd := 'delete from sales'|| year;
execute immediate cmd;
return sql%rowcount;
end;

The function takes year number and deletes all rows from sales table of that year. It also returns the number of rows deleted from the table.

You can invoke the function to delete rows of 2000 sales table and display the number of rows deleted as follows:

begin
dbms_output.put_line( deletesalesrows(2000));
end;

Using Placeholders
Placeholders can be used to replace a missing value at runtime. A placeholder is a name preceded by : (colon). The placeholder is to be replaced with a value through USING clause of EXECUTE IMMEDIATE statement.

The following example deletes all rows of the given table where PRODID is equal to the product id passed.


create or replace procedure DeleteSalesRows
(tablename varchar2, prodid number) is
cmd varchar2(100);
begin
cmd := 'delete rows from ' || tablename || ' where prodid = :prodid';
execute immediate cmd using prodid;
end;
/

At the time of executing a command that has placeholders, we must pass values that replace placeholders through USING clause of EXECUTE IMMEDIATE command. In the above example, the value of variable PRODID will replace the placeholder :PRODID and then the command is executed.


Note: The number of placeholders and the number of values passed by USING clause must be equal.

Placeholders and parameters are matched by the position and not by name. That means if the same placeholder is used for twice or more then for each occurrence the value is to be passed by USING clause as show below.

cmd := 'INSERT INTO sometable VALUES (:x, :x, :y, :x)';

EXECUTE IMMEDIATE sql_stmt USING a, a, b, a;

You have to pass four values to the above command although there are only two placeholders – X and Y. This is because of the fact that bind arguments are associated with placeholders by position and not by name. That means the first bind argument is associated with first placeholder and second bind argument with second placeholder and so on.

Note: Placeholders cannot be used for names of schema objects.

The following dynamic SQL statement is INVALID.

cmd := ‘drop table :table_name’;

execute immediate cmd using tablename;

Execute a Query Dynamically
So far whatever we have seen is related to non-query SQL statements. Now, let us see how to execute an SQL query dynamically. The difference between executing a non-query and a query is; a query returns one or more rows. In the beginning we will confine our examples to executing single-row query statements.

When a query in executed dynamically using EXECUTE IMMEDIATE command then we have to supply INTO clause to catch the values retrieved by the query. The following example shows how to use SELECT command with dynamic SQL to retrieve highest and lowest prices at which the given product is sold in the current year.

declare
cmd varchar2(100);
prodid number(5) := 10;
lprice number(5);
hprice number(5);
begin
cmd := ‘select min(price), max(price) from sales’;
cmd := cmd || to_char(sysdate,’yyyy’) || ‘ where prodid = :1’;

-- execute the command by sending product id

execute immediate cmd into lprice, hprice using prodid;

dbms_output.put_line( ‘lowest price is : ‘ || lprice);
dbms_output.put_line(‘highest price is : ‘ || hprice);

end;

While a query is executed using EXECUTE IMMEDIATE command the following rules will apply:

 The number of columns to be retrieved should be known at the time of writing program.
 Only one record can be retrieved as only one set of variable can be passed using INTO.

The following function is used to return salary of the employee from the given table.


create or replace function GetSalary(tablename varchar2, empno number) return number is
cmd varchar2(100);
v_sal number(5);
begin
cmd := 'select sal from ' || tablename || ' where empno = :empno';
execute immediate cmd into v_sal using empno;

return v_sal;

exception
when no_data_found then
return null;
end;


The above function returns NULL if the given employee number is not found in the given table.


Executing multi-row query dynamically
Using simple EXECUTE IMMEDIATE statement we can execute a query that retrieves only one row. However, when we need to execute a query that has the potential to retrieve multiple rows, we need to follow a different process.

To deal with multi-row query dynamically, we have to use a Cursor. All the rows retrieved by the query are copied into cursor. And then using FETCH statement each row will be fetched and processed.

The following are the statements used to execute a multi-row query dynamically.

Open-For statement to open the cursor
The first step is to open a cursor with the query statement. This statement associates a cursor variable with the given query and executes the query. This statement has USING option to pass values to placeholders.

The complete syntax of OPEN-FOR statement is as follows:

OPEN {cursor_variable}
FOR dynamic_string
[USING bind_argument[, bind_argument]...];


Cursor_variable Is a weakly typed cursor variable, i.e. a variable that doesn’t have any return type.
Dynamic_string The SELECT command to be executed.
Bind_argument Is the value to be passed to placeholders of the command.

The following is an example where all rows of SALES table of the current year are retrieved.

DECLARE
TYPE SalesCursorType IS REF CURSOR; -- define weak REF CURSOR type
salescursor SalesCursorType; -- declare cursor variable

BEGIN
OPEN salescursor FOR -- open cursor variable
'SELECT proid, qty, price from sales’ || to_char(sysdate,’yyyy’);
.
.
.

END;

Fetching row from cursor
Fetches one row from the cursor and copies the values of the columns into corresponding variables.

FETCH {cursor_variable } INTO {define_variable[, define_variable] ... | record};

Cursor_variable is the cursor variable with which the result of the query is associated.
Define_variable is the variable into which the value the corresponding columns should be copied.
Record is the variable of a user-defined record type or %ROWTYPE%.


To fetch rows from SALESCURSOR that we have defined in the previous step, we have to use Fetch statement as follows:

Loop
Fetch salescursor into prodid, qty, price;
Exit when sales_cursor%notfound; --exit when no more records exist
-- process the record here
End loop

Closing the cursor
The last step in the process is closing the cursor after it is processed.

CLOSE {cursor_variable};

A sample program
The following program is consolidating all that we have seen regarding how to execute a multi-row query dynamically.

declare
type salescursortype is ref cursor; -- define weak ref cursor type
salescursor salescursortype; -- declare cursor variable

prodid number(5);
qty number(5);
price number(5);

begin

open salescursor for -- open cursor variable
'select proid, qty, price from sales’ || to_char(sysdate,’yyyy’);

loop
fetch salescursor into prodid, qty, price;
exit when sales_cursor%notfound; --exit when no more records exist
-- process the record here
end loop

close salescursor; -- close the cursor after it is processed.

end; -- end of the block


Dynamic PL/SQL Blocks
You can execute an anonymous PL/SQL block dynamically using EXECUTE IMMEDIATE statement. It is useful in cases where the procedure to be invoked is known only at runtime.

The following procedure invokes one of the two procedure based on the name of the company. For this we assume we already have two procedures with names HIKESAL_A and HIKESAL_B where A and B are company names.

create or replace procedure callhikesal(company varchar2)is
begin
execute immediate
'begin
hikesal_' || company;
'end;'
end;

The above procedure calls the procedure with the name HIKESAL_A or HIKESAL_B depending on the name of the company – A or B – passed to the procedure.

The same can be done without dynamic SQL but it becomes length and needs to be updated if more there is any change in number of companies.

The following is non-dynamic SQL version of the same procedure.

create or replace procedure callhikesal(company varchar2)is
begin
if company = ‘A’ then
hikesal_a;
else
hikesal_b;
end if;
end;

But as you can notice, if we add one more company to the list then the procedure is to be modified to accommodate another company.

VARRAY AND NESTED TABLE

What is a collection?
A collection is a group of values where all values are of the same type. Oracle provides three types of collections – Indexed Tables, Nested Tables, and VARRAYs.

All these collections are like a single dimension array. A column of a table can be declared as of a collection type, an attribute of an object can be of collection type and a collection can also contain a collection of object types.

First let us see what is a VARRAY and how to use it.



What is a VARRAY?
VARRAY stands for variable-size array. It is an array that can be either manipulated as a whole or individually as elements. It has a maximum size and can contain 0 to any number of elements up to the maximum specified.

VARRAY is stored in-line. That means the data of a column of VARRAY type is stored along with the remaining data of the row.

You can use VARRAY data type for:

 A column in a relational table
 A PL/SQL variable
 A PL/SQL parameter of procedure or function
 A PL/SQL function return type
 A data attribute of an object type




Using VARRAY
The following procedure illustrates how to declare and use VARRAY.

The following is the syntax to create a data type of VARRAY.

CREATE TYPE array_name AS VARRAY (limit)
OF data_type

Array_name is the name of the VARRAY data type.
Limit is the maximum number of elements that the array can have
Data_type is the type of each element of the array. It can be any standard type or object type.

Example:
First create an object type called PROJECT_TYPE to store the name of a project and the role played by the employee in that project.

create type project_type as object
(
name varchar2(50),
role varchar2(20)
);

Create a VARRAY that can contain up to 5 elements of PROJECT_TYPE.

create type projectlist as VARRAY(5) of project_Type;

Then create EMP table where column PROJECTS is of type PROJECTLIST.

create table emp
( empno number(5),
ename varchar2(30),
projects projectlist
);


The following INSERT command will insert a row into EMP table with data for PROJECTS column.

insert into emp
values(1,'Ellison',
projectlist
(
project_type('Telephone Billing', 'System Analyst'),
project_type('Housing Loans','Oracle DBA')
)
);


While inserting a row into EMP table use constructor of PROJECTLIST and then the constructor of PROJECT_TYPE for each project the employee is handling. The above INSERT command creates a single row in EMP table where employee is involved in two projects.

The following command displays the details of projects of employee with number 1.

SQL> select projects from emp where empno = 1;

PROJECTS(NAME, ROLE)
----------------------------------------------------------------------------------------------------
PROJECTLIST(PROJECT_TYPE('Telephone Billing', 'System Analyst'), PROJECT_TYPE('Housing Loans', 'Oracle DBA'))

However, it is not possible to access the details of a single project as follows:

SQL>select projects(1) from emp where empno= 1;
*
ERROR at line 1:
ORA-00904: invalid column name

So, a VARRAY is always manipulated as a single value in SQL. The following update is used to change the role of employee 1 in Telephone Billing project to Project Leader.


update emp
set projects =
projectlist
(
project_type('Telephone Billing','Project Leader'),
project_type('Housing Loans','Oracle DBA')
)
where empno = 1;


However, it is possible to handle individual elements using collection methods in PL/SQL. The following function is used to check whether an employee is in the part of the given project.


create or replace Function IsPartOfProject(p_empno number, p_projectname varchar2)
return Boolean
is
pl ProjectList; -- VARRAY type to hold projects of an employee
begin

/* Get the VARRAY of projects */
select projects into pl
from emp
where empno = p_empno;

/*check whether the given project name is existing
in the list of projects*/

for idx in 1 .. pl.count
loop

/* check whether project name is same as the parameter */

if pl(idx).name = p_projectname then
-- exit the function by returning true
return true;
end if;

end loop;

/* Return false if project name is not found
in the projects of the employee */

return false;
end;


The above function starts by declaring a variable – PL – as of type PROJECTLIST. Then we read value of column PROJECTS, which is of type PROJECTLIST, into variable PL.

COUNT method of collection is used to return the number of elements in the collection. We used it to set the loop that varies IDX from 1 to count of elements. As it possible to access individual elements in PL/SQL, we used IDX as subscript to access each element of array PL.

The expression PL(IDX).NAME returns the name of the project from the element at the given position. If it is equal to project name parameter then the function returns true and function is terminated. Otherwise after the loop is terminated the function return false.


Note: Currently, you cannot reference the individual elements of a varray in an INSERT, UPDATE, or DELETE statement. However, it is possible to add manipulate data using PL/SQL.

The following procedure is used to add a new project. It takes employee number, project name and role of the employee in the project.

create or replace procedure new_project
(p_empno number, p_projname varchar2, p_role varchar2)
as
pl projectlist;
begin
-- get varray of the employee
select projects into pl from emp2
where empno = p_empno;

pl.extend; -- make room for new project

-- place an object of project_type at the end of the varray
pl(pl.last) := project_type (p_projname, p_role);

-- change the data in the table
update emp2 set projects = pl
where empno = p_empno;
end;
/

Nested Table
Nested table is a table that is stored in database as the data of a column of the table. Nested table is like an Index-By table, but the main difference is that a nested table can be stored in the database and an Index-by table cannot.

Nested table extends Index-by table by allowing the operations such as SELECT, DELETE, UPDATE and INSERT to be performed on nested table.

The following example illustrates steps related to creating and using nested table.

create type project_type as object
(
name varchar2(50),
role varchar2(20)
);

Now create a TABLE data type as follows:

create type ProjectTable as Table of Project_type;

Finally we use PROJECTTABLE type to create a column in EMP table as follows:


create table emp
( empno number(5),
ename varchar2(30),
projects projecttable
)
nested table projects store as projects_nt;


Table EMP contains PROJECTS, which contains a table of PROJECTTABLE type for each row.

NESTED TABLE option is required as we have a nested table column in the table. NESTED TABLE clause specifies the name of the table in which Oracle stores the data of the nested table. In this example PROJECTS_NT is created by Oracle and maintained by Oracle. It contains the data of PROJECTS column.

Note: The data of VARRAY is stored as part of the row, but the data of nested table is stored out-of-row in the table whose name is given in NESTED TABLE option.



The following INSERT command inserts a row into EMP table. It stores two rows into nested table.

insert into emp
values(1,'Ellison',
projecttable
(
project_type('Telephone Billing','System Analyst'),
project_type('Housing Loans','Oracle DBA')
)
);

As you observed, the way we insert row into nested table and VARRAY is same. However there are many differences when it comes to data retrieval and updation.


Note: If the table constructor is called without any parameter then an empty table is created. An empty table is not equal to NULL.

Using DML commands with Nested Table
DML commands can treat a nested table as one of the following.

 As a table and deal with individual rows of the table.
 As an atomic value.

DML treating inner table as atomic value
The following DML commands operate on outer table treating inner table as a single value.

The following PL/SQL block will assign the projects of employee 1 to employee 3.

declare
pl projecttable;
begin
select projects into pl
from emp
where empno = 1;

update emp set projects = pl
where empno = 3;

commit;
end;

The following DELETE command will delete the details of where PROJECTS column is NULL.

delete from emp where projects is null;

DML treating nested table as table
In the previous section we have seen how to manipulate inner table by treating it as an atomic value. Now in this section we will see how to manipulate inner table as a table.

To manipulate the inner table we have to use a new operator TABLE. This operator takes a single operand that is of type nested table or varray.

The following command displays the details of projects of employee 1.

select * from table ( select projects from emp where empno =1);

NAME ROLE
-------------------------------------------------- -------------------
Telephone Billing System Analyst
Housing Loans Oracle DBA

Subquery retrieves PROJECTS column from EMP table. TABLE operator expects a Nested Table or VARRAY column. It then supplies the data of the column in the form a rows to outer query, which displays the data as it is.

In order to displays the names of the projects in which employee 1 is Oracle DBA:

select name from table ( select projects from emp where empno =1 )
where role = 'Oracle DBA';

NAME
--------------------------------------------------
Housing Loans

It is also possible to insert, delete and update individual rows of nested table. Remember that is not permitted for VARRAY.

The following INSERT command adds a new row to projects table of employee 1.

insert into table (select projects from emp where empno = 1)
values ('BillsOnline.com', 'Web Developer');

Similarly, it is possible to change the role of employee 1 in project Telephone Billing to Project Leader.

update table ( select projects from emp where empno = 1)
Set role = ‘Project Leader’
where name= ‘Telephone Billing’;

To delete the project BillsOnline.com from employee 1, enter:

delete from table ( select projects from emp where empno = 1)
where name = 'BillsOnline.com'

Collection Methods
Oracle provides a set of methods that can be used with collections. These methods can be used only in PL/SQL and not in SQL.

The general syntax of these methods is:

collection_name.method_name[(parameters)]

Collection_name Is the name of the collection object
Method_name Is one of the methods listed in the table below
Parameters Are the parameters that are to be sent to method ( if required).

The following is the list of collection methods and their meaning.

Method Meaning
EXISTS(n) Returns true if nth element is existing in the collection.
COUNT Returns the number of elements that a collection currently contains.
LIMIT For Nested table it returns NULL. For VARRAY it returns the maximum number of elements specified.
FIRST Returns the smallest index of the collection. If collection is empty then return NULL. For VARRAY it always returns 1. But for nested table, it may return 1 or if first item is deleted then it will be more than 1.
LAST Same as FIRST, but returns largest index. For VARRAY LAST and COUNT are same but for Nested Tables, it may be more than COUNT, if any items are deleted from Nested table.
PRIOR(n) Returns the index number that precedes the given index. If no index is available then it returns NULL. This method ignores null values.
NEXT(n) Returns the index number that follows the given index. If no index is available then it returns NULL. This method ignores null values. PRIOR and NEXT are useful to traverse a nested table in which some items are deleted.
EXTEND Appends one null element to collection.
EXTEND(n) Appends specified number of items.
TRIM(n) Removes one or more elements from the end of the collection.
DELETE Removes all elements from the collection.
DELETE(n) Removes nth elements.
DELETE(m,n) Removes elements between m and n.

The following examples will show you how to use collection methods on the collections.

declare
-- declare a variable of projecttable type
pl projecttable := projecttable ( project_type('payroll','designer'));

-- procedure to display the values of the table
procedure displist(pl projecttable)
is
begin
dbms_output.put_line(‘Available items’);
-- set the loop to varray from first element to last element
for i in pl.first..pl.last
loop
if pl.exists(i) then -- if item exists then display
dbms_output.put_line( i || ' - ' || pl(i).name);
end if;
end loop;
end;


begin -- beginning of the main block
displist(pl);

-- add two new elements to the collection
pl.extend(2);

-- set values for two new elements
pl(2) := project_type('inventory','network adm');
pl(3) := project_type('law.com','web developer');

displist(pl);

pl.delete(2); -- delete second item

displist(pl);

pl.trim(1); -- remove the last item

displist(pl);
end;
/

OBJECT TYPES

Introduction to Object Types
Object type is composite data type defined by the user. Object type is a user-defined data type that contains the data and code for a particular entity.

An object type is used to model real world entities. For example, using object type you can design a data type to model an account in the bank, or an address of the customer etc.

An instance of the Object Type is called as Object.

An object type is consisting of data attributes and methods. Data attributes store the data of the object type and methods are used to perform operations on the data attributes.

Creating Object Type and Object
An object type is created using SQL command CREATE TYPE.

To create an object type called PROJECT_TYPE, enter:

create or replace type project_type as object
( name varchar2(20),
stdate date
);
/

PROJECT_TYPE object type has two attributes – NAME and STDATE.

An object type is a template. It doesn’t occupy any space. You can now use object type just like how you use any of the scalar data types like NUMBER or CHAR in SQL statements.

Here is the syntax of CREATE TYPE command.

Create type object_type as object
(Attribute data type,
[attribute data_type] ...
[ MEMBER {procedure | function} specification ] ...
[ {MAP | ORDER} MEMBER function specification ]
);


ATTRIBUTE An attribute of an object type and its associated data type.
MEMBER {PROCEDURE | FUNCTION} Member procedure or function of the object type. A member procedure or function can access the data of the object type and must be defined using CREATE TYPE BODY command.
{MAP|ORDER} MEMBER MAP MEMBER and ORDER MEMBER functions are used to return a value, which is used in comparison of objects. More on this later.

Related Data Dictionary Views
The following are the data dictionary views that provide information about Object type.

Table Description
USER_TYPES Contains details of user defined data types.
USER_TYPE_ATTRS Provides detailed information about each data type listed in USER_TYPES.
Table 1: Data dictionary views related to object types.

Using Object Type
After an object type is created, you can use it just like a predefined data type in SQL commands.

The following example will illustrate how to use object type PROJECT_TYPE to define a column of EMP table.

create table emp
( empno number(5),
ename varchar2(20),
project project_type
);


In the above example, column PROJECT is of type PROJECT_TYPE. Each row of the table contains an object of type PROJECT_TYPE.

You can use object type to define the following:

 Columns of relational table
 Variable in PL/SQL block
 Parameters of PL/SQL Sub programs
 Data attributes of another object type (nested objects)
 An object table

It is possible to get the structure of an object using DESCRIBE command as follows:

SQL> desc project_type

Name Null? Type
----------------------------------------------- -------- --------------- NAME VARCHAR2(20)
STDATE DATE

EXECUTE privilege on Object Types
By default only the owner of the object type can refer to it. However, owner of the object type can grant EXECUTE privilege on object type to allow others to access the object type.

If user SRIKANTH owns PROJECT_TYPE and user PRANEETH has to access the object, then the following GRANT command is to be given by SRIKANTH..

grant execute on project_type to praneeth;

Then user PRANEETH can use object type PROJECT_TYPE by using the schema prefix as follows:

create table company
( name number(5),
project srikanth.project_type
);

Inserting data into objects
Every object type contains a constructor created by Oracle. The constructor is a procedure with the same name as the object type. It is used to initialize attributes of the object type.

The constructor should be passed as many parameters as attributes of the object type.

The following example inserts a row into EMP table. As EMP table contains PROJECT column, which is of type PROJECT_TYPE, we have to use constructor of the object type to put data into that column as follows.

insert into emp values
(1,'Larry', project_type('Billing system','12-dec-2000'));

Values 1 and Larry will go into relational columns EMPNO and ENAME. Attributes NAME and STDATE of PROJECT are set to Billing System and 12-dec-2000.

Note: You must give a value for each data attribute of the object type. If you ever want to set an attribute of the object to null then give NULL explicitly in the constructor.

Displaying object data
It is possible to display the data of an object using simple SELECT command. The following SELECT displays the data of PROJECT object from EMP table.

SQL> select project from emp;

PROJECT(NAME, STDATE)
---------------------------------------------------------------
PROJECT_TYPE('Billing system', '12-DEC-00')
PROJECT_TYPE('Taxing', '10-JAN-01')

When a column of the table is of an object type then the column is called as Column Object.

Creating methods
An object type can also contain methods apart from data attributes. Let us now add a method to PROJECT_TYPE.

A method is first declared in CREATE TYPE command using MEMBER option and defined using CREATE TYPE BODY command. The following example demonstrates it.

create or replace type project_type as object
(
name varchar2(20),
stdate date,
member function GetAge return number,
member procedure change_stdate(newstdate date)
);
/

PROJECT_TYPE is created with two attributes and two methods. First method, GETAGE, returns the number of days between system date and project starting date. Second method takes new starting date and changes the starting date of the project to the given date if the given date is not after system date.

The following CREATE TYPE BODY command is used to create body for methods declared in PROJECT_TYPE.


create or replace type body project_type is
member function GetAge return number is
begin
return sysdate - stdate;
end;
member procedure change_stdate(newstdate date)
is
begin
-- make sure new date is not after sysdate
if newstdate > sysdate then
raise_application_error(-20011, 'Invalid starting date');
else
stdate := newstdate;
end if;
end;
end;
/

SELF
Inside the methods, data attributes are accessed without any qualifier. This is because each method is implicitly passed a reference to the object that is invoking the method. This reference is called as self.

So, there is no difference between directly accessing an attribute and using SELF reference, as shown below.

member function getage return number
begin
return sysdate - self.stdate;
end;

Note: CREATE TYPE BODY command is required only when object type contains methods.

Accessing attributes and methods
You can access attributes and method using object and dot as follows:

. [(ParametersList)]


Objectname is a valid object
Method is a method of the object type
ParametersList is list of parameters, if method takes parameters

The following PL/SQL program displays the age of the project which employee 2 is dealing with.

declare
pt project_type;
begin
select project into pt
from emp where empno = 2;

dbms_output.put_line (pt.getage);

-- change stdate of the project
pt.change_stdate( '15-jan-2001');

dbms_output.put_line( pt.getage);
end;

Accessing objects using SQL
You can use SQL commands to access attributes of the objects and manipulate them. When you access attributes of an object you have to use dot (.) operator. When you are referring to an object in SQL you also have to prefix the object with table alias otherwise SQL doesn’t recognize the attribute.

SQL> Select project.name
2 From emp;
Select project.name
*
ERROR at line 1:
ORA-00904: invalid column name

To correct the above command, use alias for the table and use that alias as the prefix for column PROJECT.

Select e.project.name
From emp e;

PROJECT.NAME
--------------------
Billing system
Billing system
Taxing

The following command is used to change the name of the project of employee 1.

update emp e set e.project.name = 'offers.com'
where empno = 1


Calling methods from SQL
It is also possible to call methods of object type from SQL by using dot operator and table alias.


SQL> select ename, e.project.getage() from emp e;

ENAME E.PROJECT.GETAGE()
-------------------- ------------------
Larry 362.27047
Scott 333.27047

The expression e.project.getage() is calling GETAGE function of PROJECT column. The table alias is required to access an attribute or a method of the object type.

Object Type Dependencies
Object types have dependent objects. For example, if you create an object type and then define a column as of the object type then the table is dependent on the object type. In the same way if an attribute of an object type is of another object type then the first object type is said to be dependent on the second object type.

create type marks_type as object
( subject varchar2(10),
marks number(3)
);

create table student_type as object
( sno number(4),
markdet marks_type
);

Now object type STUDENTS_TYPE is dependent on object type MARKS_TYPE.

When an object type has dependents, it cannot be dropped. If you try to delete object type when it has dependents then it displays the following error.

ORA-02303: cannot drop or replace a type with type or table dependents


Note: However, you can drop an object type in spite of having dependencies using FORCE option of DROP TYPE command and the dependent objects become invalid.

Object Tables
An object table is a table whose rows are objects. In other words, a table in which each row is an object is called as object table.

The following command creates an object table that contains details of projects. Each row of this table is of type PROJECT_TYPE.

create table projects of project_type;

Now, each row of the PROJECT table is an object of the type PROJECT_TYPE and the attributes of the object type map to the columns of the table.

The following insert command inserts a row into object table, using the default constructor of PROJECT_TYPE:

insert into projects values ( project_type('Billing System',sysdate));

You can also insert row directly without using constructor as follows:

insert into projects values ('Hotel Management', '10-dec-2001')

The following SELECT command is used to retrieve the details:


SQL> select * from projects;

NAME STDATE
-------------------- ---------
Billing System 09-DEC-01
Hotel Management 10-DEC-01

As the attributes of the objects are treated as columns in the table, it is very easy to use object table. You can also access attributes of objects in object table directly from SQL as shown below.

The following UPDATE command is used to change STDATE of a project.

update projects set stdate = sysdate where name= 'Billing System';

The following DELETE deletes all rows where STDATE is in the month of November,2001.

delete from projects
where stdate between ‘1-nov-2001’ and ’30-nov-2001’;

Constraints on object table
You can define constraints on object table just like how you define constraints on relational table.

The following example create PROJECTS table with NAME begin the primary key.

create table projects of project_type( name primary key);

Object Identifiers and References
Each row in object table contains an object, which is also called as row object. Each row object is assigned an object identifier (OID) by Oracle.

The OID or row object is either system generated or the primary key of the table can server as OID of the row. Whether OID is system generated or derived from primary key of the table is defined in CREATE TABLE command used to create object table.

The following CREATE TABLE command creates PROJECTS table by specifying that the ODI is primary key.

create table projects as project_type ( name primary key) object id primary key;

It is possible to reference a row object using OID of the row object. For this

The following example creates EMP_TYPE where attribute PROJECT is of reference type referencing PROJECT_TYPE object.

create type emp_type as object
( empno number(5),
ename varchar2(20),
project ref project_type
);

Now let us create object table EMP_OBT for employees as follows.

create table emp_obt of emp_type;

Since first two attributes of the object table are normal type, we can input simple values. But third attribute is a reference to an object of PROJECT_TYPE and for this we need to use REF operator to get reference of an object of PROJECTS table.

insert into emp_obj
select 1,'Praneeth', ref(p)
from projects p where p.name = 'Billing System'

The above insert command takes a reference of the object of PROJECTS table where project name is ‘Billing System’ and places that value into PROJECT attribute of the object table. And the remaining two values are literals.

Now it is possible to get the details of employee along with details of project as follows.

select empno, ename, deref(project) from emp_obt

DEREF operator is used to de-reference a reference to get the object to which the reference is pointing.

Using Objects in PL/SQL
It is possible to create object in PL/SQL blocks and use attributes and methods of the object.

The following example creates an object of PROJECT_TYPE and calls its methods.

declare
proj project_type;
begin
proj := project_type('seconds.com','12-jun-2001');
dbms_output.put_line( proj.getage() );
end;

First we created a variable proj as of type PROJECT_TYPE. Then we initialized the attributes of PROJECT_TYPE using the constructor. Project name is set to seconds.com and starting date is set to 12-jun-2001. Then we called GETAGE method of the object type and displayed the result on the screen.

The following example is used to get the number of days between the starting date of project of employee 1 and employee 2.

declare
p1 project_type;
p2 project_type;
begin
select project into p1
from emp where empno = 1;
select project into p2
from emp where empno = 2;
dbms_output.put_line( p1.stdate- p2.stdate);
end;


MAP and ORDER MEMBER functions
It is possible to compare two objects for we need to create a MAP function or ORDER function.

To use operators like >, < etc., you must provide either MAP MEMBER function or ORDER MEMEBR function.

MAP MEMBER function returns the relative position of a given instance in the ordering of all instances of the object. A map method is called implicitly and induces an ordering of object instances by mapping them to values of a predefined scalar type. PL/SQL uses the ordering to evaluate Boolean expressions and to perform comparisons.

ORDER MEMBER Function
Is a member function that takes an instance of an object as an explicit argument and returns either a negative, zero, or positive integer. The negative, positive, or zero indicates that the implicit SELF argument is less than, equal to, or greater than the explicit argument.

An object specification can contain only one ORDER method, which must be a function having the return type NUMBER.

You can define either a MAP method or an ORDER method in a type specification, but not both. If you declare either method, you can compare object instances in SQL.

If neither a MAP nor an ORDER method is specified, only comparisons for equality or inequality can be performed.

The following example shows MAP MEMBER function of PROJECT_TYPE.

create or replace type project_type as object
(
name varchar2(20),
stdate date,
…
map member function map_stdate return date
);
/


create or replace type body project_type is
. . .
map member function map_stdate return date is
begin
return stdate;
end;
end;
/

Since we created a MAP MEMBER function in PROJECT_TYPE it is possible to use objects of PROJECT_TYPE in comparison. For example, if we create EMP table (as shown previously) then it is possible to use PROJECT column in ORDER BY clause as follows.

select ename from emp order by project;

Rows of EMP table are sorted in the ascending order of STDATE of the project as MAP MEMBER function of PROJECT_TYPE returns STDATE.


The following example creates ORDER function in PROJECT_TYPE.

create or replace type project_type as object
(
name varchar2(20),
stdate date,
…
order member function ord_function(obj project_type2) return number
);

create or replace type body project_type is
…
order member function ord_function (obj project_type) return number is
begin
return self.stdate - obj.stdate;
end;
end;

Now also it is possible to use PROJECT column of EMP table in ORDER BY clause as follows.

select ename from emp order by project;

LOBS

Introduction
Oracle supports LOBs(Large Objects) which can hold large amount of raw binary data, such as graphics images, as well as large amount of character data.

Oracle extended SQL DDL and DML to provide support for LOBs. You can also manipulate LOBs using DBMS_LOB package and OCI (Oracle Call Interface).

Depending upon the way in which LOBs are stored they can be classified as follows.

Internal LOBs
These are stored in the database tablespace. They support transaction processing like any other scalar data type. CLOB, BLOB and NCLOB belong to this category.

External LOBs
These are not stored in the database. Instead they are stored in the Operating System files. Only a pointer pointing to the actual data is stored in the database. They do not support transaction processing and integrity checking. BFILE data type belongs to this category.

See figure 1, to understand how data is stored in internal lob and external lob.

LOB Datatypes
The following are the different LOB datatypes that are supported by Oracle8.

Data Type Description
CLOB The data is consisting of single-byte character data.
NCLOB The data is consisting of multi-byte or single-byte fixed length character data that corresponds to the national character set.
BLOB The data is consisting of RAW binary data, such as bitmap images.
BFILE The data is stored in an operating system file. Only a reference to the file is stored in the database. This is the example for External LOB.
Table 1: LOB Datatypes.
LOB Locator
The data of the LOB column is NOT stored in the row along with the other columns of the row, instead only a locator is stored in the database and the actual data is stored elsewhere. The locator is similar to a pointer and points to the location where the data is actually stored. In case of Internal LOB the data is stored within the database, and in case of external LOB the data is stored outside the database as a file in the file system of the operating system.

The value that is stored in the row to point to the actual location of the Internal LOB is called as LOB Locator. It is used to locate the LOB data that is stored elsewhere in the database.

LONG vs. LOBs
LONG datatype and LOBs are similar in some respects and differ in other. LOB data types can be taken as an extension to LONG RAW data type. LONG RAW is the only data type that supported large binary data in Oracle7. The following are the differences between LONG RAW data type and LOB data types.

LONG type LOB type
Can contain up to 2 GB of data Can contain up to 4 GB of data
A table can contain only one LONG column A table can contain more than one LOB column
A sub-query cannot select a LONG column A subquery can select LOB column


Defining and Manipulating LOBs
A LOB column is defined just like any other column. Data of the LOB column can be accessed either directly or through DBMS_LOB package. Let us first see how to create a table with LOB columns.

The following example creates a table with a CLOB type column and a BFILE type column.

create table lob_table
( id number(5),
clob_col clob,
bfile_col bfile
);

The following functions are used to manipulate LOB columns.

EMPTY_BLOB and EMPTY_CLOB Functions
These functions are part of the SQL DML. They are used to initialize LOB locator to empty locator in INSERT and UPDATE commands.

Note: Before you start writing data to a LOB using either OCI or DBMS_LOB package, the LOB column must be initialized to empty locator using these functions.

The following example stores empty locator into CLOB_COL.

insert into lob_table (id, clob_col)
values (100, empty_clob());

BFILENAME Function
This is used to initialize a BFILE column. This is used in SQL INSERT and UPDATE commands.

BFILENAME( Directory_alias, Filename) RETURN BFILE;


Directory_alias Is the alias of the directory in which the file is stored. This directory alias is created using CREATE DIRECTORY command.
Filename Is the name of the file, which contains the contents that are accessed using BFILE column.

insert into lob_table
values( 101, EMPTY_CLOB(), BFILENAME(‘IMAGES’,’BMP1.BMP’));

The above INSERT command inserts a row into LOB_TABLE in which BFILE_COL refers to BMP1.BMP file in directory with alias IMAGES.

Directory Alias
Directory alias is to be created using CREATE DIRECTORY command. Directory alias is used to refer to a physical directory (directory in the file system of the operating system) using an alias in Oracle.

The directory alias can be used to change the physical directory to which alias points, without having to change the alias itself. This makes the job of making BFILE column pointing to the right directory and right file easier, even though the files are moved from one physical directory to another.

create directory “IMAGES” as ‘c:\bitmaps’;

The above statement creates a directory alias called IMAGES, which is an alias to the physical directory C:\BITMAPS.

Note: The directory alias is case sensitive. When using directory alias in BFILENAME function, if proper case is not given then Oracle doesn’t recognize the directory alias. But at the time of creation, Oracle converts the directory alias to uppercase if the name is not given in double quotes (“ “). So if you want to preserve the case of the directory alias then enclose it in double quotes.

Now, the alias IMAGES can be used to refer to the physical directory in BFILENAME function as shown in the example below.

Insert into lob_table
values(101, ‘Some data in CLOB cloumn’,
BFILENAME(‘IMAGES’,’BMP1.BMP’));


Note: it is not possible to display the value of BFILE column using SELECT command in SQL*PLUS.

Users who have to access the directory, should be granted privilege to access the directory as follows:

grant read on directory LOB_DIR to Srikanth;

The above GRANT command grants READ permission on LOB_DIR directory to user SRIKANTH.

Deleting LOBs
When a LOB value is deleted, the locator to the LOB value as well as the LOB value are deleted in case of internal LOBs (CLOB, NCLOB, and BLOB). But in case of BFILE (external LOB) only the locator is deleted and the actual data is not deleted. This is obvious considering the fact that in case of external LOBs database stores only a pointer to the actual content but not the content itself. So for BFILEs you need to delete data manually.

Note: The function discussed above deal with complete LOB column. They do not allow you to modify a part of the LOB column. For information regarding modifying a part of the LOB column, see DBMS_LOB package, later in this chapter.

DBMS_LOB Package
Functions and procedures in DBMS_LOB package can be used to perform various operations related to LOBs. These methods of DBMS_LOB are very important while dealing with LOBs as normal functions such as SUBSTR cannot be used with LOBs.

The following is the list of functions and procedures in DBMS_LOB package.

Method Description
READ(locator,nobytes,offset, output) Reads nobytes of a LOB value starting from offset and places the read piece in output.
WRITE(locator, nobytes, offset, buffer) Writes nobytes from buffer into a LOB starting at offset in a LOB value.
APPEND(dest_locator, source_locator) Appends one LOB value at the end of another LOB value.
ERASE(locator, nobytes, offset) Erases nobytes from the given offset of a LOB value.
TRIM(locator, newlength) Reduces the size of a LOB value by trimming characters on the right.
SUBSTR(locator, nobytes, offset) Extracts a portion of a LOB value and returns that value. The difference between this and READ procedure is that READ procedure places the extracted value in the given variable whereas this returns the value.
COPY(dest,source,nobytes,
dest_offset,source_offset) This is a combination of READ and WRITE procedures. This modifies a LOB value by replacing a part of it with data from another LOB.
GETLENGTH(locator) Returns the length of a LOB value.
INSTR(locator,pattern,offset,occur) Searches for the given pattern and returns the position at which the pattern is found. If offset is given search starts at the offset. And if occur parameter is also given then it looks for occur number of occurrence.
COMPARE(locator1,locator2,nobytes,offset1,offset2) Compares two LOB values and returns 0 if they are same, 1 if first one is bigger than second one, and -1 if second one is bigger than first one. offset1 and offset2 may be used to specify at which position in first and second LOBs the search should begin. nobytes specifies the number of bytes to be compared.
Table 2: DBMS_LOB Functions and Procedures

Examples using DBMS_LOB Package
The following examples use procedures and functions given in the table 2.

INSTR function
The following example looks for the pattern ‘Oracle’ in CLOB_COL column of the row where ID is 10 in LOB_TABLE.

declare
c_lob CLOB;
pos Number(2);
begin
/* get the value of CLOB_LOB column
from the row where ID is 10 */

select clob_col into c_lob
from lob_table
where id = 10;

/* Use INSTR function to find out the
position where the pattern ‘Oracle’
occurs in the LOB value.
Search start at first character and look for the
first occurrence of the pattern
*/

pos := dbms_lob.instr(c_lob,’Oracle’,1,1);

-- if INSTR returns 0 if means pattern is not found
if pos = 0 then
dbms_output.put_line(‘Could not file pattern Oracle’);
else
dbms_output.put_line(‘Found Oracle at =‘ || to_char(pos));
end if;

end;

COPY procedure
The following example copies 10 characters of one LOB to another lob.

declare
source_clob CLOB;
dest _clob CLOB;

begin
/* get CLOB_COLs of ID 20 */
select clob_col into source_clob
from lob_table
where id = 20;

/* retrieve CLOB_COL of id 10 for
updation */

select clob_col into dest_clob
from lob_table
where id = 15
for update; -- required to update LOB value

/*Now copy data with the following parameters
No. of character to be copied = 10
Offset in the destination LOB = 50
Offset in the source LOB = 10 */

dbms_lob.copy( dest_clob,source_clob,10,50,10);

end;

BFILE Related Procedures and Functions
The following are the procedures and functions in DBMS_LOB that are related to BFILE data type.

Procedure or Function Description
FILEOPEN(bfile,mode) Opens the specified file.
FILECLOSE(bfile) Closes the specified file
FILECLOSEALL Closes all open files.
FILEEXISTS(bfile) Checks whether the file referenced by BFILE locator exists.
FILEGETNAME(loc,dir,file) Gets the name of external file referenced by BFILE locator.
FILEISOPEN(bfile) Checks whether external file is currently open.
Table 3: BFILE Related Procedures and Functions in DBMS_LOB Package.

The following example displays the directory and filename of a BFILE column.

declare
bf bfile;
d varchar2(20);
f varchar2(20);
begin
/* get the BFILE_COL value for the ID 20 */
select bfile_col into bf
from lob_table
where id = 20;

/* get directory name into d and filename into f */
dbms_lob.filegetname(bf,d,f);

dbms_output.put_line('Directory: ' || d || ' File : '|| f);
end;

The following example checks whether the file referenced by a BFILE_COL is existing on the disk.

declare
bf bfile;
begin
/* get the BFILE_COL value for the ID 20 */
select bfile_col into bf
from lob_table
where id = 20;

/* FILEEXISTS returns non-zero if the given locator
points to an existing file on the disk */

if dbms_lob.fileexists(bf) <> 0 then
dbms_output.put_line(‘ BFILE for ID 20 is found ‘);
else
dbms_output.put_line(‘ BFILE for ID 20 is not found‘);
end if;

end;

The following program is used to read the content of BFILE_COL of LOB_TABLE.

declare
value BFILE;
buf char(100);
amt BINARY_INTEGER := 100;
pos INTEGER :=1;
BEGIN
SELECT bfile_col INTO value FROM lob_table
WHERE id = 1;
dbms_lob.fileopen(value, dbms_lob.file_readonly);
LOOP
dbms_lob.read(value,amt,pos, buf);
-- process contents of buf
pos := pos + amt;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_lob.fileclose(value);
end;
/

The above program first retrieves BFILE_COL column’s data from LOB_TABLE. Then FILEOPEN method is used to open the file using BFILE locator. Then LOOP is used to read contents from BFILE using READ method until READ method raises NO_DATA_FOUND exception. Each time a chunk of the data is read and processed.

DBMS_LOB.FILE_READONLY constant is used to specify that the file is to be opened file read-only.

PROCEDURES, FUNCTIONS AND PACKAGES

What is a stored procedure?
As we have seen in the introduction to PL/SQL that there are two types of PL/SQL blocks – anonymous and stored procedures.

A stored procedure is a PL/SQL block that is stored in the database with a name. It is invoked using the name. Each procedure is meant for a specific purpose.

A stored procedure is stored in the database as an object. It is also called as database procedure as it is stored in the database.

A procedure may take one or more parameters. If a procedure takes parameters then these parameters are to be supplied at the time of calling the procedure.

What is a function?
A function is similar to procedure, except that it returns a value. The calling program should use the value returned by the function.


Advantages of stored procedures
Stored procedures and functions offer the following benefits.

Reduced network traffic
Stored procedures are stored in the database on the server. In order to invoke a stored procedure a client has to use only the name of the stored procedure. This results in reduced network traffic because only the name is passed to server and not the entire PL/SQL block.

Better Performance
Stored procedures improve performance because the database accessed by stored procedures and stored procedures themselves are stored in the database. Furthermore, because a stored procedure’s compiled form is available in the database, no compilation step is required to execute the code.

Apart from this, stored procedures make use of shared memory. That means if a stored procedure is called by user-1, then it is brought into server’s memory. As a result there is a chance of finding the stored procedure in memory, when user-2 later wants to use the same procedure. In this case stored procedure need not be brought into memory since it is already in the memory. This saves time and increases performance.

Easy Maintenance
If there is any change in the application, only the concerned stored procedures are to be modified to incorporate the change. As all the clients access stored procedures, they automatically use the modified definition. You only have to change business login once in the stored procedure. Therefore stored procedures improve the integrity and consistency of your applications.

Security
Stored procedures help enforcement of data security. You can allow users to access only the stored procedures and not the table that are manipulated by stored procedures.

Whenever a stored procedure runs, it runs with the privileges of the owner and not the user running it. Because users only have the privilege to execute the procedure and not the privileges to access the underlying tables, it increases security.

Owner rights
When a stored procedure is run under the privileges of the owner of the stored procedure, it is called as owner rights.

Invoker rights
When a stored procedure is executed with the privileges of the invoker and not under the privileges of the owner, it is called as invoker rights.

We will discuss about the difference between these two ways of running a stored procedure in detailed later in this chapter.

Creating a stored procedures
A stored procedure is created using CREATE PROCEDURE command.


CREATE [OR REPLACE] PROCEDURE name [(parameter[,parameter, ...])] {IS |AS}
[local declarations]
BEGIN
executable statements
[EXCEPTION
exception handlers]
END [name];


OR REPLACE is used to create a procedure even a procedure with the same name is already existing.

Oracle creates procedure even though the procedure is not valid but it displays the message saying that the procedure is created with errors. Then you have to rectify the errors and recreate the procedure. If OR REPLACE option is not given Oracle doesn’t allow you to replace an existing stored procedure. So, it is better you use OR REPLACE option so that the existing invalid version of the procedure will be replaced with the valid version.


SHOW ERRORS
During the creation of the procedure if there are any errors Oracle displays the message saying procedure is created but with errors with the following message:

Warning: Procedure created with compilation errors.

In order to displays errors in the most recent CREATE PROCEDURE statement, use SHOW ERRORS command.

The following stored procedure inserts a new record into PAYMENTS table with the given roll number and amount.

create or replace procedure newpayment(rollno number, amt number)
is
begin
insert into payments values(rollno, sysdate,amt);
commit;
end;
/

Once a stored procedure is stored in the database, it can be invoked using EXECUTE command by using the name of the procedure.

EXECUTE command
A procedure can be executed using EXECUTE command. To execute a procedure either you must be the owner of the procedure or you must have EXECUTE privilege on the procedure.

The following example shows how to invoke NEWPAYMENT procedure.

SQL> execute newpayment(10,2000);

PL/SQL procedure successfully completed.

In the above example, NEWPAYMENT is invoked by passing 10 and 2000. It inserts a new row into PAYMENTS table with values 10, sysdate, and 2000.

Creating a stored function
A stored function is same as a procedure, except that it returns a value. CREATE FUNCTION command is used to create a stored function.

CREATE [OR REPLACE] FUNCTION name
[(parameter[,parameter, ...])]
RETURN datatype
{IS | AS}
[local declarations]
BEGIN
executable statements

RETURN value;
[EXCEPTION
exception handlers]
END [name];

OR REPLACE is used to create a function even though a function with the same name already exists

RETURN datatype specifies the type of data to be returned by the function.

RETURN statement in the executable part returns the value. The value must be of the same type as the return type specified using RETURN option in the header.

The following stored function takes roll number of the student and return the amount yet to be paid by the student.

create or replace function getdueamt(prollno number)
return number
is
v_fee number(5);
v_amtpaid number(5);
begin
-- get total amount paid by student
select sum(amount) into v_amtpaid
from payments
where rollno = prollno;
-- get course fee of the course into which student joined
select fee into v_fee
from courses
where ccode = ( select ccode from batches
where bcode in
( select bcode from students
where rollno = prollno)
);
-- return the difference
return v_fee - v_amtpaid;
end;
/

The above function can be called from a PL/SQL block as follows:

begin
dbms_output.put_line(getdueamt(10));
end;

User-defined PL/SQL functions can be used in SQL in the same manner as the standard functions such as ROUND and SUBSTR..

For example, the function GETDUEAMT can be invoked from SELECT command as follows:

SQL> select rollno, getdueamt(rollno)
2 from students;

ROLLNO GETDUEAMT(ROLLNO)
--------- -----------------
1 0
2 0
3 0
4 0
5 0
6 0
7 0
8 0
9 1500
10 2000
11 0
12 1500
13 0

Getting source code
It is possible to see the source code of stored procedures and function by using USER_SOURCE data dictionary view.

The following SELECT command will display the source code of NEWPAYMENT stored procedure.

SQL> select text
2 from user_source
3 where name = 'NEWPAYMENT';

TEXT
------------------------------------------------------------------------
procedure newpayment(rollno number, amt number)
is
begin
insert into payments values(rollno, sysdate,amt);
commit;
end;


Privileges required
To create a stored procedure, you must have CREATE PROCEDURE system privilege.

You must also have required object privileges on the objects that are referred in the procedure in order to successfully compile the procedure.

Note: The owner of the procedure CANNOT obtain required privileges on the stored procedure through ROLES.

Recompiling
Stored procedures and functions are compiled at the time of creation and stored in the compiled form. If a procedure becomes invalid afterwards, it is to be recompiled before it is executed. Oracle implicitly compiles the procedure when an invalid procedure is referred. However, it is possible to explicitly recompile

In order to recompile a stored procedure use ALTER PROCEDURE and ALTER FUNCTION to recompile a procedure and function respectively.

ALTER PROCEDURE procedurename COMPILE;
ALTER FUNCTION functionname COMPILE;

The following sequence will illustrate the importance of recompilation.

 Assume user SCOTT has created NEWPAYMENT procedure as follows

create or replace procedure newpayment(rollno number, amt number)
is
begin
insert into book.payments values(rollno, sysdate,amt);
commit;
end;
/

 Since SCOTT doesn’t have INSERT privilege on PAYMENTS table of BOOK, the procedure is created but marked as invalid. You can see the status of the procedure using the following command.

select status from user_objects where object_name = 'NEWPAYMENT';

STATUS
-------
INVALID

 Now, user BOOK has granted INSERT privilege on PAYMENTS table to SCOTT as follows:

GRANT INSERT ON PAYMENTS to SCOTT;

 Then any subsequent reference to NEAPAYMENT procedure in SCOTT will implicitly recompile the procedure. But in order to avoid extra time taken to recompile the procedure at runtime, it can be recompiled using ALTER PROCEDURE command as follows:


ALTER PROCEDURE newpayment COMPILE;


 After recompilation, the procedure will have status VALID as provide by the following query.

select status from user_objects where object_name = 'NEWPAYMENT';

STATUS
-------
VALID


Types of Parameters
Parameters of a procedure are of two types.

 Formal parameters
 Actual Parameters

Formal Parameters
The parameters declared in the definition of procedure are called as formal parameters. They receive the values sent while calling the procedure.

procedure increase_fee (pccode varchar2, pamt number)

In the above procedure, PCCODE, PAMT parameters are called as formal parameters.

Actual Parameters
The values given within parentheses while calling the procedure are called as actual parameters.

increase_feepaid ( v_ccode, 2000);

v_ccode and 2000 are actual parameters. These values are copied to the corresponding formal parameters - pccode and pamt.

Parameter Modes
Parameter mode is used to specify what can be done with formal parameter. The following are the available modes.

 IN
 OUT
 IN OUT

IN mode
IN parameters lets you pass a value to the subprogram being called. The value cannot be changed inside the subprogram. It is like a constant in the subprogram. Therefore it cannot be assigned a value.

procedure increase_fee (pccode in varchar2, pamt number) is
begin
. . .
end;

The actual parameter corresponding to IN parameter can be a variable, constant, or expression.

The default parameter mode is IN. In the above example though we didn’t specify the parameter mode for PAMT is it taken as IN parameter.

OUT Mode
An OUT parameter lets the subprogram pass a value to caller. Inside the subprogram OUT parameter is an uninitialized variable.

Subprogram has to place a value in the OUT parameters, which will be used by caller program. Whatever changes are made to formal parameter, the changes will be made available to actual parameter.

The actual parameter corresponding to OUT parameter must be a variable.

IN OUT Mode
It is same as IN and OUT modes put together. It can get a value from the calling program and return value to calling program. The value of this type of parameter can be used in the subprogram and

The actual parameter corresponding to IN OUT parameter must be a variable.

The following procedure takes course code and returns the dates on which the first and last batches of that course have started.

create or replace procedure get_dates( pccode in varchar2,
first_date out date,
last_date out date) is
begin

select min(stdate) into first_date
from batches
where ccode = pccode;

select max(stdate) into last_date
from batches
where ccode = pccode;

end;

Once the procedure is created, it can be called as follows:

declare
min_date date;
max_date date;

begin

get_dates( ‘ora’, min_date, max_date);
dbms_output.put_line( min_date || ‘:’ || max_date);

end;


The output of the above program will be:

12-JAN-01:15-AUG-01


NOCOPY Compiler Hint
By default, OUT and IN OUT parameters are passed by value. That means, the value of an IN OUT actual parameter is copied into the corresponding formal parameter. Then, if the procedure exits normally, the values assigned to OUT and IN OUT formal parameters are copied into the corresponding actual parameters.

When the parameters hold large data structures such as records, and instances of object types (which we will in later chapters), copying slows down execution and uses up more memory. To prevent that, you can specify the NOCOPY hint, which allows the PL/SQL to pass OUT and IN OUT parameters by reference.

PROCEDURE change(pstudent IN OUT NOCOPY student_type) IS ...

In the above example parameter PSTUDENT is of object type – STUDENT_TYPE. It is now passed by reference as we used NOCOPY option with it.



Invoker Rights vs. definer rights
By default, when a user executes a procedure, the procedure is executed with the privileges of the owner. That means, the privileges of invoking user (invoker) are not taken into account only the privileges of definer (owner) of the procedure will be considered.

If the procedure is to be called by user other than the owner then the use must be granted EXECUTE privilege on the procedure.

When the procedure is executed using the privileges of definer then, it is called as definer rights.

Definer rights is the default option. All object references in the procedure will be referring to objects of definer and not invoker. Let us see an example to understand this point further.

Assume we have procedure ADDMESSAGE created by user SRIKANTH as follows:

create or replace procedure addmessage(msg varchar2)
is
begin
insert into messages values (msg, sysdate);
commit;
end;

Then user SRIKANTH has granted EXECUTE privilege to user PRANEETH as follows:

grant execute on addmessage to praneeth;

Now user PRANEETH can execute the procedure as follows:

execute Srikanth.addmessage('First message');

The message “First message” is inserted into MESSAGES table of SRIKANTH – the definer of the procedure.

What if user PRANEETH also has MESSAGE table with the same structure as MESSAGES table of SRIKANTH? The answer is; even now the message goes to MESSAGES table of SRIKANTH, since all references to objects in the procedure are resolved to definer of the procedure.

Invoke Rights
Oracle8i has introduced invoker rights. In case of invoker rights, procedure is executed with the privileges of invoker and not the definer of the procedure.

If you want the procedure to be executed with invoker rights and not with definer right, then the procedure is to be created with AUTHID CURRENT_USER option as follows.

create or replace procedure addmessage(msg varchar2)
authid current_user as
is
begin
insert into messages values (msg, sysdate);
commit;
end;


AUTHID CURRENT_USER option specifies that the procedure to be executed under the privileges of invoking user. Also remember that all object references are resolved to invoker and not the definer. However, if any object is qualified with schema (username) then it will refer to object in that schema. In other words, all unqualified object are taken from invoker’s schema.

Now if you execute ADDMESSAGE procedure from user PRANEETH, it will fail, as there is no MESSAGES table in that schema.


SQL> execute srikanth.addmessage('Second message');
BEGIN srikanth.addmessage('fine'); END;

*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SRIKANTH.ADDMESSAGE", line 4
ORA-06512: at line 1

As you can understand, the above message is complaining about missing MESSAGES table. If you create MESSAGES table in PRANEETH schema, then the procedure will succeed and the message is inserted into MESSAGES table of PRANEETH account.

The advantage of invoker rights is that it allows you to centralized code whereas the data is stored in individual schema. That means though users use the same common code the data is stored in objects of their schema.


RAISE_APPLICATION_ERROR Procedure
This procedure is used to create your own application error numbers and messages.

When you have to terminate a PL/SQL program, you can display an error message and send error code to host environment using this procedure.

RAISE_APPLICATION_ERROR (errornumber, errormessage);


errornumber is a number in the range -20001 and -20999.

errormessage is the message to be displayed.

The following PL/SQL block displays the error message along with error number (-20100) when NO_DATA_FOUND exception is raised.

declare
v_fee courses.fee%type;
begin

select fee into v_fee
from courses
where ccode = ‘ora’;

-- remaining statements

exception
when no_data_found then
raise_application_error(-20100,‘Invalid course code’);
end;
/

Packages
A package is a collection of related procedures, functions, variables and data types. A package typically contains two parts – specification and body.

Specification
Package specification contains declarations for items, procedure and functions that are to be made public. All public objects of package are visible outside the package. In other words, public objects are callable from outside of the package.

Private items of the package can be used only in the package and not outside the package.

The following is the syntax to create package specification.

CREATE PACKAGE package_name AS

/* declare public objects of package */

END;

Body
Body of the package defines all the objects of the package. It includes public objects that are declared in package specification and objects that are to be used only within the package – private members.

CREATE PACKAGE BODY package_name AS
/* define objects of package */
END;


 procedures declared in the package specification
 functions declared in the package specification
 definition of cursors declared in the package specification
 local procedures and functions, not declared in the package specification
 local variables

Calling a procedure of package
In order to access a public object of a package use the following syntax:

package_name.object_name

package_name is the name of the package whose object you want to access.
object_name is the name of a public object in the package.

Let us now create a package called COURSE_PKG that contains two subprograms – CHANGEFEE and GETBATCHCOURSE.

We have to first create package specification and then body a follows:

create or replace package course_pkg as

procedure changefee (pccode varchar2, newfee number);
function getbatchcourse(pbcode varchar2) return varchar2;

end;
/
The following CREATE PACKAGE BODY statement creates the body of the package.

create or replace package body course_pkg as

procedure changefee (pccode varchar2, newfee number)
is
begin
update courses set fee = newfee
where ccode = pccode;
if sql%found then
commit;
else
raise_application_error(-20010,'Invalid course code');
end if;
end;

function getbatchcourse(pbcode varchar2) return varchar2
is
v_ccode courses.ccode%type;
begin
select ccode into v_ccode
from batches
where bcode = pbcode;

return v_ccode;

exception
when no_data_found then
raise_application_error( -20011,'Invalid batch code');

end;


end;
/

In order to call procedure CHANGEFEE of the package, use the package name followed by procedure name as follows:

Execute course_pkg.changefee(‘ora’,5000);

Initializing package
It is possible to execute some code when a user refers the package for the first time. Any code that you feel is to be executed at the time of a user referring to the package for the first time can be put in initialization part of the package.

The following is the structure of initialization part.

create package body name as

/* definition of package body */

begin
/* code to be executed when package is referred for the first time */
end;

EXCEPTION HANDLING

What is an Exception?
In PL/SQL, errors and warnings are called as exceptions. Whenever a predefined error occurs in the program, PL/SQL raises an exception. For example, if you try to divide a number by zero then PL/SQL raises an exception called ZERO_DIVIDE and if SELECT can not find a record then PL/SQL raises exception NO_DATA_FOUND.

PL/SQL has a collection of predefined exceptions. Each exception has a name. These exceptions are automatically raised by PL/SQL whenever the corresponding error occurs.

In addition to PL/SQL predefined exceptions, user can also create his own exceptions to deal with errors in the applications. Understanding how to handle exception raised by PL/SQL is as important as understanding how to write code to achieve task. Because exception handling is an important part of any application and application is not complete without exception handling.


How to handle exceptions?
When PL/SQL raises a predefined exception, the program is aborted by displaying error message. But if program is to handle exception raised by PL/SQL then we have to use Exception Handling part of the block.

Exception handling part is used to specify the statements to be executed when an exception occurs. Control is transferred to exception handling part whenever an exception occurs. After the exception handler is executed, control is transferred to next statement in the enclosing block. If there is no enclosing block then control returns to Host (from where you ran the PL/SQL block).

The following is the syntax of exception handling part.


WHEN exception-1 [or exception -2] ... THEN
statements;

[WHEN exception-3 [or exception-4] ... THEN
statements; ] ...

[WHEN OTHERS THEN
statements; ]

exception-1, exception-2 are exceptions that are to be handled. These exceptions are either pre-defined exceptions or user-defined exceptions.

The following example handles NO_DATA_FOUND exception. If SELECT statement doesn’t retrieve any row then PL/SQL raises NO_DATA_FOUND exception, which is handled in exception handling part.


declare
…
begin
select …

exception
when no_data_found then
statements;
end;


When two or more exceptions are given with a single WHEN then the statements are executed whenever any of the specified exceptions occur.

The following exception handling part takes the same action when either NO_DATA_FOUND or TOO_MANY_ROWS exceptions occur.

declare
...
begin
select ...

exception
when no_data_found or too_many_rows then
statements;
end;

The following snippet handles these two exceptions in different ways.

declare
...
begin
select ...

exception
when no_data_found then
statements;
when too_many_rows then
statements;
end;


WHEN OTHERS is used to execute statements when an exception other than what are mentioned in exception handler has occurred.

Note: If an exception is raised but not handled by exception handling part then PL/SQL block is terminated by displaying an error message related to the exception.


Sample Programs
The following is an example of exception handler. This program assigns course fee of “C” to course “C++”. If course “C” does not exist then it sets course fee of “C++” to average fee of all courses.

declare
v_fee courses.fee%type;
begin
select fee into v_fee
from courses
where ccode = 'c';

update courses
set fee = v_fee
where ccode='c++';

exception
when no_data_found then
update courses
set fee = ( select avg(fee) from courses)
where ccode = 'c++';
end;
/

If SELECT cannot find a row course code “c” then it raises NO_DATA_FOUND exception. When exception is raised, control is transferred to exception handling part and course fee of “c++” is set to average course fee of all courses. If course code “c” is found then it sets the course fee of course “c++” to the course fee of “c”.

Getting information about error - SQLCODE and SQLERRM
In WHEN OTHERS section of exception handler, you can use SQLCODE and SQLERRM functions to get the error number and error message respectively. As there is no predefined exception for each of Oracle errors, you will not get a particular exception for most of the errors. However, it is possible to know the error code and error message of the most recently occurred error using these two functions. This is one way of knowing which Oracle error has exactly occurred. The other method is associating an exception with an Oracle error. Please see “Associating an exception with Oracle error” section for details.

The following example demonstrates how to use SQLCODE and SQLERRM.

declare
newccode varchar2(5) := null;
begin
update courses
set ccode = newccode
where ccode = 'c';
exception
when dup_val_on_index then
dbms_output.put_line('Duplicate course code');
when others then
dbms_output.put_line( sqlerrm);
end;

If you run the above program, the following output will be generated.

ORA-01407: cannot update ("BOOK"."COURSES"."CCODE") to NULL

PL/SQL procedure successfully completed.

The above output is generated by WHEN OTHERS part of exception handling part. SQLERRMS returns the error message of the most recent error. As we are trying to set CCODE, which is a not null column to NULL value, PL/SQL raises an exception. But as the error (-01407) is not associated with any predefined exception, WHEN OTHERS part of exception handling part is executed.


Note: You cannot use SQLCODE or SQLERRM directly in a SQL statement. Instead, you must assign their values to variables then use the variables in the SQL statement.

Predefined exceptions
PL/SQL has defined certain common errors and given names to these errors, which are called as predefined exceptions. Each exception has a corresponding Oracle error code.

The following is the list of predefined exceptions and the corresponding Oracle error code.


Exception Oracle Error SQLCODE Value
ACCESS_INTO_NULL
COLLECTION_IS_NULL
CURSOR_ALREADY_OPEN
DUP_VAL_ON_INDEX
INVALID_CURSOR
INVALID_NUMBER
LOGIN_DENIED
NO_DATA_FOUND
NOT_LOGGED_ON
PROGRAM_ERROR
ROWTYPE_MISMATCH
SELF_IS_NULL
STORAGE_ERROR
SUBSCRIPT_BEYOND_COUNT
SUBSCRIPT_OUTSIDE_LIMIT
SYS_INVALID_ROWID
TIMEOUT_ON_RESOURCE
TOO_MANY_ROWS
VALUE_ERROR
ZERO_DIVIDE
ORA-06530
ORA-06531
ORA-06511
ORA-00001
ORA-01001
ORA-01722
ORA-01017
ORA-01403
ORA-01012
ORA-06501
ORA-06504
ORA-30625
ORA-06500
ORA-06533
ORA-06532
ORA-01410
ORA-00051
ORA-01422
ORA-06502
ORA-01476
-6530
-6531
-6511
-1
-1001
-1722
-1017
+100
-1012
-6501
-6504
-30625
-6500
-6533
-6532
-1410
-51
-1422
-6502
-1476
Table 1: Predefined Exceptions

The following is the description of some of the pre-defined exceptions.

CURSOR_ALREADY_OPEN Raised if you try to open an already open cursor.
DUP_VAL_ON_INDEX Raised if you try to store duplicate values in a database column that is constrained by a unique index.
INVALID_CURSOR Raised if you try an illegal cursor operation.
INVALID_NUMBER Raised in an SQL statement if the conversion of a character string to a number fails because the string does not represent a valid number.
NO_DATA_FOUND Raised if a SELECT INTO statement returns no rows or if you reference an un-initialized row in a PL/SQL table.
See the section “When NO_DATA_FOUND is not raised?”.
SUBSCRIPT_BEYOND_COUNT Raised when the program references a nested table or varray element using an index number larger than the number of elements in the collection.
TOO_MANY_ROWS Raised if a SELECT INTO statement returns more than one row.
VALUE_ERROR Raised if an arithmetic, conversion, truncation, or size–constraint error occurs.
ZERO_DIVIDE Raised when your program attempts to divide a number by zero.

When NO_DATA_FOUND exception is not raised?
As NO_DATA_FOUND exception is most commonly used exception, let us have a close look at this exception. We have so far understood that NO_DATA_FOUND exception is raised by PL/SQL whenever SELECT command doesn’t retrieve any rows.

In the following cases NO_DATA_FOUND exception is not raised by PL/SQL even though no row is retrieved or effected:

 When a group function is used in the SELECT statement.
 When UPDATE and DELETE commands are used.

When SELECT command uses any group function then NO_DATA_FOUND exception will be not be raised by PL/SQL although no row is retrieved. For example, if SUM function is used in SELECT no record is retrieved by the SELECT command then SUM function returns NULL value but doesn’t raise NO_DATA_FOUND exception. Please see examples given below.

Note: When COUNT function is used in SELECT and no row is retrieved then COUNT function returns 0 and not NULL value.

The following example is used to display the average duration of C++ batches. If no C++ batch has been completed then it displays a message. Since AVG function returns NULL when no row is retrieved by SELECT, we check the return value of AVG and display error message if it is NULL.

declare
v_avgdur number(3);
begin
-- get average duration of C++ batches
select avg( enddate - stdate) into v_avgdur
from batches
where enddate is not null and ccode = ‘c++’;

/* display error if AVG return null */

if v_avgdur is null then
dbms_output.put_line (‘No batch of C++ has been completed’);
else
dbms_output.put_line (‘Average duration of C++ :‘ || v_avgdur);
end if;
end;
/

We will understand how to detect whether UPDATE or DELETE command has affected any row in the table, in the next chapter.


User-defined exception
PL/SQL allows you to create exceptions of your own. These exceptions are available to the block in which they are created. Unlike a predefined exception, which is predefined and automatically raised whenever the corresponding error occurs, a user-defined error has the following steps.
Declaring userdefined exception
A userdefined exception is to be declared in the declare section of the block. The following is the syntax to declare an exception.

exception-name exception;

exception-name is the name of the exception to be created.

The following example declare an exception classed OUT_OF_STOCK.

declare
out_of_stock exception;
begin

statements;
end;


Raising userdefined exception using RAISE command
Unlike predefined exceptions, userdefined exception is to be raised explicitly using RAISE command.

RAISE exception-name;

We have to decide when the user-defined exception has to be raised. For example, if you want to raise OUT_OF_STOCK exception when value of variable QTY is less then 10, give the following:

if qty < 10 then
raise out_of_stock;
end if;

Once a userdefined exception is raised using RAISE command, it is to be handled just like a predefined exception. So handling exception OUT_OF_STOCK is no way different from what we have seen so far.

The following PL/SQL block will declare, raise and handle a user-defined exception.

declare
out_of_stock exception; -- declare exception
begin

if condition then

raise out_of_stock; -- raise userdefined exception

end if;

exception

when out_of_stock then -- handle userdefined exception
. . .

end;


Reraising an exception
RAISE command can also be used to reraise an exception so that the current exception is propagated to outer block. If a sub block executes RAISE statement without giving exception name in exception handler then the current exception is raised again.

The following example will illustrate the process of re-raising an exception.

declare
out_of_stock exception;
begin
...
begin ---------- sub-block (inner block) begins
...
if ... then
raise out_of_stock; -- raise the exception
end if;
.
.
exception
when out_of_stock then
-- handle the error in the sub block
raise; -- reraise the current exception, which is out_of_stock
...
end; ------------ sub-block ends

exception
when out_of_stock then
-- handle the exception (that is reraised) in outer block
...
end;


Note: RAISE statement without exception name is valid only in exception handler.


Associating an exception With An Oracle Error
It is possible to connect a userdefined exception with an Oracle error number so that whenever the Oracle error occurs then the user-defined exception will be raised by PL/SQL automatically.

The following example associates exception NULL_VALUE_ERROR with error number –1407, which occurs when a not null column is set to null value, using PRAGAMA EXCEPTION_INIT statement.

declare
null_value_error exception;
pragma exception_init(no_privilege, -1407);


Now, whenever Oracle error -1407 occurs, NULL_VALUE_ERROR exception is raised by PL/SQL. The following example will illustrate important points related to associating an Oracle error with a user-defined exception.

declare
null_value_error exception;
pragma exception_init(null_value_error, -1407);

newccode varchar2(5) := null;
begin
update courses
set ccode = newccode
where ccode = 'c';

exception

when null_value_error then
dbms_output.put_line(‘trying to set null value to a not null column’);
end;
/


Exception propagation
When an exception is raised by PL/SQL and if it not handled in the current block then the exception is propagated. That means, the exception is sent to enclosing blocks one after another from inside to outside until an error handler is found in one of the enclosing blocks or there are no more blocks to search for handlers.

When an exception is not handled in any of the enclosing blocks then it is sent to host environment.

The following figures illustrate how exceptions propagate.

In figure 1, exception A is raised by inner block. As there is an exception handler for exception A, the exception is handled there itself. After the exception is handled, control resumes with statements after inner block in outer block.

As the exception is handled in the block in which exception is raised, the exception is not propagated and control resumes with the enclosing block.
Exception propagating to outer block
In figure 2, inner block raises exception “A” but as it is not handled in the current block (in inner block) it is propagated to first outer block. As there is an exception handler for “A” in the outer block, control is passed to it and exception is handled in the outer block.
Exception propagating to host
In figure 3, exception “A” is neither handled in the block in which it is raised nor handled in any of the outer blocks. As the result exception is propagated to host (the environment from where you ran the outer most block). When an exception is propagated to host the action taken by host depends on host. Examples for host are SQL* PLUS, Oracle forms, and Oracle Server.

Exception raised in Declaration
When an exception is raised in the declaration of a block, the exception is immediately propagated to enclosing block and not handled in that block.

The following example illustrates how an exception that is raised in the declaration of inner block is propagated to outer block.

begin
dbms_output.put_line('in outer block');
declare
ccode varchar2(5) := 'abcdef';
begin
-- some statements
dbms_output.put_line('in inner block');
exception
when others then
dbms_output.put_line(sqlerrm);
end;
dbms_output.put_line(' back in outer block');
exception
when others then
dbms_output.put_line('Error in outer block: ' || sqlerrm);
end;
/

When you run the above block, the following output will be generated:

in outer block
Error in outer block: ORA-06502: PL/SQL: numeric or value error: character string buffer too small

When is a PL/SQL block successful or failure?
Each PL/SQL block exits either successfully or unsuccessfully. The exit status of PL/SQL block plays a very important role at a later stage (for example in database triggers). But for now let us just understand when a PL/SQL block is considered to be successful and when is it considered to be a failure.

A PL/SQL block is successful if it:

 Exits without raising any exceptions.
 Raises an exception but the exception is handled within the block’s exception handling part.

A PL/SQL block is unsuccessful if it:

 Exits with an unhandled exception. That means the executable part raises an exception (either predefined or user-defined) and it is not handled in the block’s exception handler.
 Executes RAISE_APPLICATION_ERROR procedure to generate an user-defined error.

Detecting where exception is raised
It is sometimes important to know which statement has actually raised the exception. Especially when two or more SELECT statements are there in the block and if one of them has raised NO_DATA_FOUND exception, it is difficult to know which statement has actually caused the problem.

The following example will use a variable to know which SELECT statement has actually raised the exception.

declare
n number(1) :=0;
begin
select ...
n := 1;
select ...
n:= 2;
select ...

exception
when no_data_found then
if n = 0 then
...
elsif n = 1 then
...
else
...
end if
end;
/

In the above example, variable N is set to 0 at the time of declaration. If first SELECT statement raised NO_DATA_FOUND exception then control is transferred to exception handler and the value of N will be 0. If first SELECT succeeds and second SELECT has failed then the value of N will be 1 and similarly the value of N will be 2 if second SELECT also succeeds but third SELECT fails.

In the exception handler, it is possible to know which SELECT has failed by using the value of variable N.