1) What is the difference between ON DELETE SET NULL clause and ON DELETE CASCADE clause in ORACLE.
ON DELETE SET NULL:
drop table students;
create table Students
(
id int,
name varchar(10),
name varchar(10),
dept_id int constraint c1 primary key);
insert into students values(1,'vishnu',11);
insert into students values(2,'vinay',12);
insert into students values(3,'Teja',13);
insert into students values(4,'Ashwin',14);
drop table dept;
create table dept
(
dept_id int constraint c2 references students(dept_id) on delete set null,
dept_name varchar(10),
subject_name varchar(18));
insert into dept values(11,'science','Biology');
insert into dept values(11,'science','Physics');
insert into dept values(11,'science','Chemistry');
insert into dept values(12,'social','social studies');
insert into dept values(13,'languages','English');
insert into dept values(13,'languages','Spanish');
delete from students where id=1;
select * from dept;
ON DELETE CASCADE;
drop table students;
create table Students
(
id int,
name varchar(10),
dept_id int constraint c1 primary key);
insert into students values(1,'vishnu',11);
insert into students values(2,'vinay',12);
insert into students values(3,'Teja',13);
insert into students values(4,'Ashwin',14);
drop table dept;
create table dept
(
dept_id int constraint c2 references students(dept_id) on delete cascade,
dept_name varchar(10),
subject_name varchar(18));
insert into dept values(11,'science','Biology');
insert into dept values(11,'science','Physics');
insert into dept values(11,'science','Chemistry');
insert into dept values(12,'social','social studies');
insert into dept values(13,'languages','English');
insert into dept values(13,'languages','Spanish');
delete from students where id=1;
select * from dept;
2) What is Function based index.
Index is created on the column when a function is included in where clause of a sql query.
CREATE INDEX members_last_name_fi ON members(UPPER(last_name));
3) Decode function
drop table students;
create table Students (
id int,
name varchar(10),
dept_id int);
insert into students values(1,'vishnu',11);
insert into students values(2,'vinay',12);
insert into students values(3,'Teja',13);
insert into students values(4,'Ashwin',14);
select id,name, decode(id,
1,'science',
2,'maths',
3,'social'
) as subjects from students;
4) Tell us if NULL is equal to NULL in ORACLE ?
select decode(null,null,'true equal in oracle','false not equal in oracle') from dual;
5) Complex views in ORACLE.
CASE 1:
View created on more than one base table (i.e join of two tables) with key constraint.
drop table students;
create table Students
(
id int,
name varchar(10),
dept_id int constraint c1 primary key);
insert into students values(1,'vishnu',11);
insert into students values(2,'vinay',12);
insert into students values(3,'Teja',13);
insert into students values(4,'Ashwin',14);
drop table dept;
create table dept
(
dept_id int constraint c2 references students(dept_id),
dept_name varchar(10),
subject_name varchar(18));
insert into dept values(11,'science','Biology');
insert into dept values(11,'science','Physics');
insert into dept values(11,'science','Chemistry');
insert into dept values(12,'social','social studies');
insert into dept values(13,'languages','English');
insert into dept values(13,'languages','Spanish');
select a.id,a.name,b.dept_name,b.subject_name from students a, dept b where a.dept_id=b.dept_id;
create view v1 as select a.id,a.name,b.dept_name,b.subject_name from students a, dept b where a.dept_id=b.dept_id;
select * from v1;
update v1 set subject_name='Maths' where id=1;
select * from students;
select * from dept;
view updated successfully and base tables also updated as per where criteria.
CASE 2:
create view v2 as select b.subject_name,count(b.subject_name) as count from students a, dept b where a.dept_id=b.dept_id group by b.subject_name;
update v2 set subject_name='Telugu' where subject_name='Maths';
update is illegal on this view because update cannot be performed on the view which has aggregate function and group by clause.
select * from v2;
When can we cannot update a view?
The SELECT statement which is used to create the view should not include the following
1) GROUP BY clause or ORDER BY clause.
2) DISTINCT keyword.
3) An aggregate or analytic function.
4) A collection expression in a SELECT list.
5) A sub-query in a SELECT list.
6) The view should not be created using nested queries or complex queries.
7) view cannot update if view contains pseudo columns or expressions.
6) How to replace user defined values in place of NULL.
USING NVL function.
a) select NULL+100 from dual gives null as output
b) select 0+100 from dual gives 100 as output.
How to get 100 in the first query.
select NVL(NULL,0) + 100 from dual;
USING NVL2 function.
We can replace user defined value in place of NULL and exact value or not null in place of NOT Null value.
select(a1,a2,a3) from dual;
if a1 is NULL then it prints a3.
if a1 is NOT NULL then it prints a2.
7) What is the use of COALESCE function.
This function gives first not null value in the parameter list.
drop table students;
create table Students
(
id int,
name varchar(10),
phone_num int,
land_num int);
insert into students values(1,'vishnu',123,NULL);
insert into students values(2,'vinay',456,92345);
insert into students values(3,'Teja',NULL,95432);
select id,name,coalesce(phone_num,land_num) from students;
8) Mutating table error.
When a trigger is raised for performing the DML operations on the table and the same time DML operations are performed on the same table then this mutating table error occurs.
drop table Employee;
create table Employee(id int,name varchar2(10),sal int);
insert into Employee values(1,'Vinay',4500);
create or replace trigger mutating_trig
after insert or update or delete on Employee
for each row
enable
begin
if inserting then
insert into Employee(id,name,sal) values(:new.id,:new.name,:new.sal);
elsif deleting then
delete from employee where id=:old.id;
elsif updating then
update Employee set id=:old.id where id=:new.id;
end if;
end;
insert into Employee values(2,'Teja',45000);
delete from Employee where id=1;
update Employee set id=2 where id=1;
10) How to delete duplicate records from table without Distinct Clause
drop table Employee;
create table Employee(id int ,
dept_id int,
name varchar2(10),
sal int,
constraint c1 UNIQUE(id),
constraint c2 primary key(dept_id));
Now try to insert below rows.
1) insert into Employee values(1,11,'Vishnu',2300);
row inserted successfully.
2) insert into Employee values(2,null,'Vishnu',2300);
cannot insert NULL in primary key column.
3) insert into Employee values(null,22,'Vishnu',2300);
unique key accepts NULL. Row inserted.
4) insert into Employee values(null,33,'Vishnu',2300);
row inserted as unique key column accepts multiple null values.
12) What Is Cursor?Types Of Cursor? And Diff B/W Cursors?
Cursor is a pointer which points result set and retrieve each row at a time from the result set.
Types of Cursors:
Implicit
Explicit
Implicit cursor:
It is used when sql statement return only one row.
It has 4 attributes
SQL%ROWCOUNT
SQL%FOUND
SQL%NOTFOUND
SQL%ISOPEN.
Example:
drop table Employee;
create table Employee(id int ,dept_id int,name varchar2(10),sal int,constraint c1 UNIQUE(id),constraint c2 primary key(dept_id));
insert into Employee values(1,11,'Vishnu',230);
insert into Employee values(2,22,'Vinay',130);
insert into Employee values(3,33,'Teja',24000);
insert into Employee values(4,44,'Ashwin',2500);
select * from Employee;
declare
v_row Employee%ROWTYPE;
begin
select * into v_row from Employee where id=1;
DBMS_OUTPUT.PUT_LINE(v_row.id|| ' ' ||v_row.dept_id|| ' ' ||v_row.name|| '' ||v_row.sal);
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);
if SQL%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE('No row selected');
elsif SQL%FOUND then
DBMS_OUTPUT.PUT_LINE('1 row selected');
end if;
end;
output:
Explicit cursor:
It is used when sql statement returns more than one row.
It has also 4 attributes.
CUR_NAME%ROWCOUNT
CUR_NAME%FOUND
CUR_NAME%NOTFOUND
CUR_NAME%ISOPEN.
Example:
declare
v_row Employee%ROWTYPE;
cursor c1 is select * from Employee;
begin
open c1;
if (c1%ISOPEN) then
loop
fetch c1 into v_row;
DBMS_OUTPUT.PUT_LINE(v_row.id|| ' ' ||v_row.dept_id|| ' ' ||v_row.name|| ' ' ||v_row.sal);
exit when c1%NOTFOUND;
end loop;
DBMS_OUTPUT.PUT_LINE(c1%ROWCOUNT);
end if;
close c1;
end;
This is because when the cursor coming to the last row, fetch statement will give the last value to v_row,then prints v_row.
Notice at this time, %NOTFOUND returns false because there is a row,so loops one more time.
This time, fetch statement will not give anything to v_row,v_row stays the same as last time, which is the last row,then print out v_row again.
This time, %NOTFOUND returns true, terminate the loop.
To avoid the above failure change the order as below.
declare
v_row Employee%ROWTYPE;
cursor c1 is select * from Employee;
begin
open c1;
if (c1%ISOPEN) then
loop
fetch c1 into v_row;
exit when c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_row.id|| ' ' ||v_row.dept_id|| ' ' ||v_row.name|| ' ' ||v_row.sal);
end loop;
DBMS_OUTPUT.PUT_LINE(c1%ROWCOUNT);
end if;
close c1;
end;
2) What is Function based index.
Index is created on the column when a function is included in where clause of a sql query.
CREATE INDEX members_last_name_fi ON members(UPPER(last_name));
3) Decode function
drop table students;
create table Students (
id int,
name varchar(10),
dept_id int);
insert into students values(1,'vishnu',11);
insert into students values(2,'vinay',12);
insert into students values(3,'Teja',13);
insert into students values(4,'Ashwin',14);
select id,name, decode(id,
1,'science',
2,'maths',
3,'social'
) as subjects from students;
4) Tell us if NULL is equal to NULL in ORACLE ?
select decode(null,null,'true equal in oracle','false not equal in oracle') from dual;
5) Complex views in ORACLE.
CASE 1:
View created on more than one base table (i.e join of two tables) with key constraint.
drop table students;
create table Students
(
id int,
name varchar(10),
dept_id int constraint c1 primary key);
insert into students values(1,'vishnu',11);
insert into students values(2,'vinay',12);
insert into students values(3,'Teja',13);
insert into students values(4,'Ashwin',14);
drop table dept;
create table dept
(
dept_id int constraint c2 references students(dept_id),
dept_name varchar(10),
subject_name varchar(18));
insert into dept values(11,'science','Biology');
insert into dept values(11,'science','Physics');
insert into dept values(11,'science','Chemistry');
insert into dept values(12,'social','social studies');
insert into dept values(13,'languages','English');
insert into dept values(13,'languages','Spanish');
select a.id,a.name,b.dept_name,b.subject_name from students a, dept b where a.dept_id=b.dept_id;
create view v1 as select a.id,a.name,b.dept_name,b.subject_name from students a, dept b where a.dept_id=b.dept_id;
select * from v1;
update v1 set subject_name='Maths' where id=1;
select * from students;
select * from dept;
view updated successfully and base tables also updated as per where criteria.
CASE 2:
create view v2 as select b.subject_name,count(b.subject_name) as count from students a, dept b where a.dept_id=b.dept_id group by b.subject_name;
update v2 set subject_name='Telugu' where subject_name='Maths';
update is illegal on this view because update cannot be performed on the view which has aggregate function and group by clause.
select * from v2;
When can we cannot update a view?
The SELECT statement which is used to create the view should not include the following
1) GROUP BY clause or ORDER BY clause.
2) DISTINCT keyword.
3) An aggregate or analytic function.
4) A collection expression in a SELECT list.
5) A sub-query in a SELECT list.
6) The view should not be created using nested queries or complex queries.
7) view cannot update if view contains pseudo columns or expressions.
6) How to replace user defined values in place of NULL.
USING NVL function.
a) select NULL+100 from dual gives null as output
b) select 0+100 from dual gives 100 as output.
How to get 100 in the first query.
select NVL(NULL,0) + 100 from dual;
USING NVL2 function.
We can replace user defined value in place of NULL and exact value or not null in place of NOT Null value.
select(a1,a2,a3) from dual;
if a1 is NULL then it prints a3.
if a1 is NOT NULL then it prints a2.
7) What is the use of COALESCE function.
This function gives first not null value in the parameter list.
drop table students;
create table Students
(
id int,
name varchar(10),
phone_num int,
land_num int);
insert into students values(1,'vishnu',123,NULL);
insert into students values(2,'vinay',456,92345);
insert into students values(3,'Teja',NULL,95432);
select id,name,coalesce(phone_num,land_num) from students;
8) Mutating table error.
When a trigger is raised for performing the DML operations on the table and the same time DML operations are performed on the same table then this mutating table error occurs.
drop table Employee;
create table Employee(id int,name varchar2(10),sal int);
insert into Employee values(1,'Vinay',4500);
create or replace trigger mutating_trig
after insert or update or delete on Employee
for each row
enable
begin
if inserting then
insert into Employee(id,name,sal) values(:new.id,:new.name,:new.sal);
elsif deleting then
delete from employee where id=:old.id;
elsif updating then
update Employee set id=:old.id where id=:new.id;
end if;
end;
insert into Employee values(2,'Teja',45000);
delete from Employee where id=1;
update Employee set id=2 where id=1;
For all the above three DML statements, mutating table error occurs as below.
ORA-04091: table SQL_BDJTXDPJXHYOBFTDBMDGFJKON.EMPLOYEE is mutating, trigger/function may not see it ORA-06512: at "SQL_BDJTXDPJXHYOBFTDBMDGFJKON.THIRD_SAL", line 7
ORA-06512: at "SYS.DBMS_SQL", line 1721
9) select count(1) From Dual;What Is Output?
1
Using row id
drop table Employee;
create table Employee(id int,name varchar2(10),sal int);
insert into Employee values(1,'Vishnu',2300);
insert into Employee values(1,'Vishnu',2300);
insert into Employee values(1,'Vishnu',2300);
insert into Employee values(1,'Vinay',4500);
insert into Employee values(3,'Teja',4500);
insert into Employee values(4,'Ashwin',1100);
insert into Employee values(5,'Raj',45000);
select rowid,id,name,sal from Employee;
select max(rowid) from Employee group by id,name,sal;
delete from Employee where rowid not in ( select max(rowid) from Employee group by
id,name,sal);
11) Difference between Primary Key and Unique Key.
Primary key is the combination of unique key and not null constarint.
Unique key accepts multiple nulls and unique values.
Below is the example:
Table Employee has unique constraint on id column and primary key constraint on dept_id column.
create table Employee(id int ,
dept_id int,
name varchar2(10),
sal int,
constraint c1 UNIQUE(id),
constraint c2 primary key(dept_id));
Now try to insert below rows.
1) insert into Employee values(1,11,'Vishnu',2300);
row inserted successfully.
2) insert into Employee values(2,null,'Vishnu',2300);
cannot insert NULL in primary key column.
3) insert into Employee values(null,22,'Vishnu',2300);
unique key accepts NULL. Row inserted.
4) insert into Employee values(null,33,'Vishnu',2300);
row inserted as unique key column accepts multiple null values.
12) What Is Cursor?Types Of Cursor? And Diff B/W Cursors?
Cursor is a pointer which points result set and retrieve each row at a time from the result set.
Types of Cursors:
Implicit
Explicit
Implicit cursor:
It is used when sql statement return only one row.
It has 4 attributes
SQL%ROWCOUNT
SQL%FOUND
SQL%NOTFOUND
SQL%ISOPEN.
Example:
drop table Employee;
create table Employee(id int ,dept_id int,name varchar2(10),sal int,constraint c1 UNIQUE(id),constraint c2 primary key(dept_id));
insert into Employee values(1,11,'Vishnu',230);
insert into Employee values(2,22,'Vinay',130);
insert into Employee values(3,33,'Teja',24000);
insert into Employee values(4,44,'Ashwin',2500);
select * from Employee;
declare
v_row Employee%ROWTYPE;
begin
select * into v_row from Employee where id=1;
DBMS_OUTPUT.PUT_LINE(v_row.id|| ' ' ||v_row.dept_id|| ' ' ||v_row.name|| '' ||v_row.sal);
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);
if SQL%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE('No row selected');
elsif SQL%FOUND then
DBMS_OUTPUT.PUT_LINE('1 row selected');
end if;
end;
output:
Explicit cursor:
It is used when sql statement returns more than one row.
It has also 4 attributes.
CUR_NAME%ROWCOUNT
CUR_NAME%FOUND
CUR_NAME%NOTFOUND
CUR_NAME%ISOPEN.
Example:
declare
v_row Employee%ROWTYPE;
cursor c1 is select * from Employee;
begin
open c1;
if (c1%ISOPEN) then
loop
fetch c1 into v_row;
DBMS_OUTPUT.PUT_LINE(v_row.id|| ' ' ||v_row.dept_id|| ' ' ||v_row.name|| ' ' ||v_row.sal);
exit when c1%NOTFOUND;
end loop;
DBMS_OUTPUT.PUT_LINE(c1%ROWCOUNT);
end if;
close c1;
end;
While Executing the above block i got two identical rows at the last.
1 11 Vishnu 230
2 22 Vinay 130
3 33 Teja 24000
4 44 Ashwin 2500
4 44 Ashwin 2500
Notice at this time, %NOTFOUND returns false because there is a row,so loops one more time.
This time, fetch statement will not give anything to v_row,v_row stays the same as last time, which is the last row,then print out v_row again.
This time, %NOTFOUND returns true, terminate the loop.
To avoid the above failure change the order as below.
declare
v_row Employee%ROWTYPE;
cursor c1 is select * from Employee;
begin
open c1;
if (c1%ISOPEN) then
loop
fetch c1 into v_row;
exit when c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_row.id|| ' ' ||v_row.dept_id|| ' ' ||v_row.name|| ' ' ||v_row.sal);
end loop;
DBMS_OUTPUT.PUT_LINE(c1%ROWCOUNT);
end if;
close c1;
end;
Expected output:
1 11 Vishnu 230
2 22 Vinay 130
3 33 Teja 24000
4 44 Ashwin 2500
4
13) What Is Exception? Types Of Exceptions?
An exception is an error which occurs at run time.
Predefined
User defined
Predefined exceptions are the exceptions in Oracle Standard Package maintained by Oracle server.
User defined Exceptions are raised explicitly in PL/SQL Program.
They are defined in three ways.
1) Using variable of Exception data type.
2) Using Raise_Application_Error method.
3) Using Pragram Exception_INIT function.
Lets see 1st way using variable of Exception data type.
declare
v_row Employee%ROWTYPE;
/*declaration of exception data type*/
v_excep exception;
cursor c1 is select * from Employee;
begin
open c1;
if (c1%ISOPEN) then
loop
fetch c1 into v_row;
exit when c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_row.id|| ' ' ||v_row.dept_id|| ' ' ||v_row.name|| ' ' ||v_row.sal);
end loop;
end if;
DBMS_OUTPUT.PUT_LINE(c1%ROWCOUNT);
/* raise of exception block*/
if(c1%ROWCOUNT < 5)
then
raise v_excep;
END IF;
/* Exception handler bloc*/
exception when v_excep then
DBMS_OUTPUT.PUT_LINE(c1%ROWCOUNT || ' row count is less than 10');
close c1;
end;
Output:
Statement processed.
1 11 Vishnu 230
2 22 Vinay 130
3 33 Teja 24000
4 44 Ashwin 2500
4
4 row count Is less than 10
14) What Is Sqlcode And Sqlerr?
SQLCODE : Number of the error
SQLERR: message related to the SQL Code.
Example:
drop table Employee;
create table Employee(id int ,dept_id int,name varchar2(10),sal int,constraint c1 UNIQUE(id),constraint c2 primary key(dept_id));
insert into Employee values(null,null,'Ashwin',2500);
In the above example , inserted Null value in the primary key column. Below is the error displayed.
15) What Is Ref_Cursor? Type Of Ref_Cursors And Diff B/W?
Reference to a cursor. It is a user defined datatype in PL/SQL that refers to a special type of cursor variable.
strong ref cursor = creating ref cursor datatype with return type as record + creating cursor variable with ref cursor datatype
weak ref cursor= creating ref cursor datatype with no return type + creating cursor variable with ref cursor datatype
sys ref cursor = creating cursor variable with sys ref cursor data type
Types:
Strong Ref Cursor: Strong ref cursors have return type and return type should be record data type.
Weak Ref Cursor: Do not have any return type
Sys Ref cursor is an Oracle built in cursor variable.
Strong referential cursor:
/* define the user defined ref cursor data type as below */
declare
type my_refcur is ref cursor return Employee%ROWTYPE;
/* declare cursor variable with the user defined ref cursor data type as below */
c1 my_refcur;
/* declare a local variable same as a referetial cursor */
v_fetch Employee%ROWTYPE;
begin
open c1 for select * from Employee where id=1;
fetch c1 into v_fetch;
dbms_output.put_line(v_fetch.id|| ' ' || v_fetch.dept_id|| ' ' || v_fetch.name);
close c1;
end;
sys referential cursor:
A PL/SQL Code contains SQL statements and PL/SQL Statements. PL/SQL code gets processed in PL/SQL Engine and SQL code gets processed in SQL Engine. While processing the block of code ,PL/SQL Engine comes across the SQL statements. Context switching happens between PL/SQL engine to SQL engine in order to process SQL Code. Performance of the code reduces if number of context switches increases between PL/SQL engine and SQL engine. To overcome this we use BULK Collect.
Example:
declare
type nt_type is table of varchar2(10);
nt_var nt_type;
begin
select name bulk collect into nt_var from Employee;
end;
14) What Is Use Of Limit Clause In Bulk Collect? What Is Forall?
Using BULK COLLECT, the query performance increases as it minimises the context switching between sql engine and pl/sql engine but performance of database decreases . This is because when the rows are fetched at once, memory overload happens which in turn degrades the performance of database despite the query performance. So LIMIT clause is used which limits the number of rows to be fetched or retrieved at once. Hence the memory over load decreases and performance of query and database increases.
BULK Collect clause can be used in FETCH into statement.
declare
cursor c1 is select name from Employee;
type nt_type is table of varchar2(10);
nt_var nt_type;
begin
open c1;
fetch c1 bulk collect into nt_var limit 3;
for ix in 1..nt_var.count
loop
dbms_output.put_line(nt_var(ix));
end loop;
close c1;
end;
FORALL statement is also used to reduce the context switching between PL/SQL Engine and SQL Engine similar to BULK collect but in reverse manner.
1) BULK collect fetches records from table to collection where as FORALL fetches records from collection to tables.
Below example inserts data in table through collection variable called Associative Array.
drop table Employee;
create table Employee(id int);
declare
type col_var is table of number index by pls_integer;
usr_var col_var;
begin
for i in 1..10
loop
usr_var(i) := i;
end loop;
forall ix in 1..10
insert into Employee(id) values(usr_var(ix));
end;
select * from Employee;
15) What Is Merge Statement? Give Me Syntax?
drop table Employee;
create table Employee(id int,dept_id int,subject varchar(20));
insert into Employee values(1,11,null);
insert into Employee values(2,22,null);
insert into Employee values(3,22,null);
insert into Employee values(4,33,null);
insert into Employee values(5,33,null);
insert into Employee values(6,44,null);
create table dept(dept_id int, subject varchar(20));
insert into dept values(11,'Science');
insert into dept values(22,'Maths');
insert into dept values(33,'Chemistry');
Merge into Employee e1
using dept d1
on (d1.dept_id=e1.dept_id)
when matched then
update set e1.subject=d1.subject
when not matched then
insert values(d1.dept_id,d1.subject);
16)What Is View? Use Of View's?
19) Which Will Fire Default First Statement Level Or Row Level Trigger?
An exception is an error which occurs at run time.
Predefined
User defined
Predefined exceptions are the exceptions in Oracle Standard Package maintained by Oracle server.
User defined Exceptions are raised explicitly in PL/SQL Program.
They are defined in three ways.
1) Using variable of Exception data type.
2) Using Raise_Application_Error method.
3) Using Pragram Exception_INIT function.
Lets see 1st way using variable of Exception data type.
declare
v_row Employee%ROWTYPE;
/*declaration of exception data type*/
v_excep exception;
cursor c1 is select * from Employee;
begin
open c1;
if (c1%ISOPEN) then
loop
fetch c1 into v_row;
exit when c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_row.id|| ' ' ||v_row.dept_id|| ' ' ||v_row.name|| ' ' ||v_row.sal);
end loop;
end if;
DBMS_OUTPUT.PUT_LINE(c1%ROWCOUNT);
/* raise of exception block*/
if(c1%ROWCOUNT < 5)
then
raise v_excep;
END IF;
/* Exception handler bloc*/
exception when v_excep then
DBMS_OUTPUT.PUT_LINE(c1%ROWCOUNT || ' row count is less than 10');
close c1;
end;
Output:
Statement processed.
1 11 Vishnu 230
2 22 Vinay 130
3 33 Teja 24000
4 44 Ashwin 2500
4
4 row count Is less than 10
14) What Is Sqlcode And Sqlerr?
SQLCODE : Number of the error
SQLERR: message related to the SQL Code.
Example:
drop table Employee;
create table Employee(id int ,dept_id int,name varchar2(10),sal int,constraint c1 UNIQUE(id),constraint c2 primary key(dept_id));
insert into Employee values(null,null,'Ashwin',2500);
In the above example , inserted Null value in the primary key column. Below is the error displayed.
ORA-01400: cannot insert NULL into ("SQL_RVHJEHXHUAGFREPISOPPDRICY"."EMPLOYEE"."DEPT_ID") ORA-06512: at "SYS.DBMS_SQL", line 1721
ORA-01400 : SQLCODE
cannot insert NULL into ("SQL_RVHJEHXHUAGFREPISOPPDRICY"."EMPLOYEE"."DEPT_ID") ORA-06512: at "SYS.DBMS_SQL", line 1721 : SQLERR
15) What Is Ref_Cursor? Type Of Ref_Cursors And Diff B/W?
Reference to a cursor. It is a user defined datatype in PL/SQL that refers to a special type of cursor variable.
strong ref cursor = creating ref cursor datatype with return type as record + creating cursor variable with ref cursor datatype
weak ref cursor= creating ref cursor datatype with no return type + creating cursor variable with ref cursor datatype
sys ref cursor = creating cursor variable with sys ref cursor data type
Types:
Strong Ref Cursor: Strong ref cursors have return type and return type should be record data type.
Weak Ref Cursor: Do not have any return type
Sys Ref cursor is an Oracle built in cursor variable.
Strong referential cursor:
/* define the user defined ref cursor data type as below */
declare
type my_refcur is ref cursor return Employee%ROWTYPE;
/* declare cursor variable with the user defined ref cursor data type as below */
c1 my_refcur;
/* declare a local variable same as a referetial cursor */
v_fetch Employee%ROWTYPE;
begin
open c1 for select * from Employee where id=1;
fetch c1 into v_fetch;
dbms_output.put_line(v_fetch.id|| ' ' || v_fetch.dept_id|| ' ' || v_fetch.name);
close c1;
end;
Weak referential cursor:
declare
type my_refcur is ref cursor;
c1 my_refcur;
v_fetch Employee%ROWTYPE;
begin
open c1 for select * from Employee where id=1;
fetch c1 into v_fetch;
dbms_output.put_line(v_fetch.id|| ' ' || v_fetch.dept_id|| ' ' || v_fetch.name);
close c1;
end;
sys referential cursor:
drop table Employee;
create table Employee(id int,dept_id int constraint c2 references Department(dept_id),name varchar2(10),sal int);
insert into Employee values(1,11,'Vishnu',230);
insert into Employee values(2,22,'Vinay',130);
insert into Employee values(3,33,'Teja',24000);
declare
my_refcur SYS_REFCURSOR;
v_fetch Employee%ROWTYPE;
begin
open my_refcur for select * from Employee where id=1;
fetch my_refcur into v_fetch;
dbms_output.put_line(v_fetch.id|| ' ' || v_fetch.dept_id|| ' ' || v_fetch.name || v_fetch.sal);
close c1;
end;
16) How to Give Privilege to One Procedure In Package?
GRANT EXECUTE ON SCHEMA.Package_1.Procedure_1 TO USER
17) What Is %Type & %Rowtype? What Is Main Use?
% TYPE is the data type of a variable or a database column to that variable.
% ROWTYPE is the record type that represents a entire row of a table or view or columns
selected in the cursor.
GRANT EXECUTE ON SCHEMA.Package_1.Procedure_1 TO USER
17) What Is %Type & %Rowtype? What Is Main Use?
% TYPE is the data type of a variable or a database column to that variable.
% ROWTYPE is the record type that represents a entire row of a table or view or columns
selected in the cursor.
declare
d_id department.dept_id%type;
v_row employee%ROWTYPE;
BEGIN
select dept_id into d_id from Department where dept_id=11;
select * into v_row from Employee where id=1;
dbms_output.put_line(v_row.id || ' ' || v_row.name || ' ' ||d_id);
end;
Statement processed.
1 Vishnu 11
13) BULK COLLECT
Example:
declare
type nt_type is table of varchar2(10);
nt_var nt_type;
begin
select name bulk collect into nt_var from Employee;
end;
14) What Is Use Of Limit Clause In Bulk Collect? What Is Forall?
Using BULK COLLECT, the query performance increases as it minimises the context switching between sql engine and pl/sql engine but performance of database decreases . This is because when the rows are fetched at once, memory overload happens which in turn degrades the performance of database despite the query performance. So LIMIT clause is used which limits the number of rows to be fetched or retrieved at once. Hence the memory over load decreases and performance of query and database increases.
BULK Collect clause can be used in FETCH into statement.
declare
cursor c1 is select name from Employee;
type nt_type is table of varchar2(10);
nt_var nt_type;
begin
open c1;
fetch c1 bulk collect into nt_var limit 3;
for ix in 1..nt_var.count
loop
dbms_output.put_line(nt_var(ix));
end loop;
close c1;
end;
FORALL statement is also used to reduce the context switching between PL/SQL Engine and SQL Engine similar to BULK collect but in reverse manner.
1) BULK collect fetches records from table to collection where as FORALL fetches records from collection to tables.
2) The FORALL allows to perform the DML operations on data in bulk.
It is similar to that of FOR loop statement except in FOR loop things happen at the record level whereas in FORALL there is no LOOP. Instead the entire data present in the given bounds is processed at the same time.
Below example inserts data in table through collection variable called Associative Array.
drop table Employee;
create table Employee(id int);
declare
type col_var is table of number index by pls_integer;
usr_var col_var;
begin
for i in 1..10
loop
usr_var(i) := i;
end loop;
forall ix in 1..10
insert into Employee(id) values(usr_var(ix));
end;
select * from Employee;
15) What Is Merge Statement? Give Me Syntax?
drop table Employee;
create table Employee(id int,dept_id int,subject varchar(20));
insert into Employee values(1,11,null);
insert into Employee values(2,22,null);
insert into Employee values(3,22,null);
insert into Employee values(4,33,null);
insert into Employee values(5,33,null);
insert into Employee values(6,44,null);
create table dept(dept_id int, subject varchar(20));
insert into dept values(11,'Science');
insert into dept values(22,'Maths');
insert into dept values(33,'Chemistry');
Merge into Employee e1
using dept d1
on (d1.dept_id=e1.dept_id)
when matched then
update set e1.subject=d1.subject
when not matched then
insert values(d1.dept_id,d1.subject);
16)What Is View? Use Of View's?
View is a virtual table which can be derived based on a query
Uses:
Readability and confidentiality
19) Which Will Fire Default First Statement Level Or Row Level Trigger?
First, statement level triggers are fired, and then row level triggers are fired when more than one trigger is fired by a single SQL statement.
20) Can you alter procedure with in package?
All objects in a package are stored as a unit, the ALTER PACKAGE statement
re compiles all package objects. You cannot use the ALTER PROCEDURE statement or ALTER FUNCTION statement to recompile individually a procedure or function that is part of a package.
drop table dept;
create table dept(dept_id int, subject varchar(20));
insert into dept values(11,'Science');
insert into dept values(22,'Maths');
insert into dept values(33,'Chemistry');
create or replace package pkg is
function usr_fun return varchar;
procedure usr_proc(b int,ab varchar);
end pkg;
create or replace package body pkg is
function usr_fun return varchar is
begin
return 'vishnu';
end usr_fun;
procedure usr_proc(b int,ab varchar) is
begin
insert into dept values(b,ab);
end usr_proc;
end pkg;
begin
dbms_output.put_line(pkg.usr_fun);
pkg.usr_proc(44,'Physics');
end;drop table dept;
create table dept(dept_id int, subject varchar(20));
insert into dept values(11,'Science');
insert into dept values(22,'Maths');
insert into dept values(33,'Chemistry');
create or replace package pkg is
function usr_fun return varchar;
procedure usr_proc(b int,ab varchar);
end pkg;
create or replace package body pkg is
function usr_fun return varchar is
begin
return 'vishnu';
end usr_fun;
procedure usr_proc(b int,ab varchar) is
begin
insert into dept values(b,ab);
end usr_proc;
end pkg;
begin
dbms_output.put_line(pkg.usr_fun);
pkg.usr_proc(44,'Physics');
end;
21) Is it possible to open cursor which is in package in another procedure?
21) Is it possible to open cursor which is in package in another procedure?
Yes its possible.
Below is the example.
Below is the example.
drop table Employee;
create table Employee(id int,dept_id int,subject varchar(20));
insert into Employee values(1,11,null);
insert into Employee values(2,22,null);
Creation of package 1
create or replace package pkg1 is
cursor c_emp is select * from Employee;
r_emp c_emp%ROWTYPE;
end;
/
--Here is a different package that references the cursor
create or replace package pkg2 is
procedure usr_proc;
end pkg2;
create or replace package body pkg2 is
procedure usr_proc is
begin
open pkg1.c_emp;
loop
fetch pkg1.c_emp into pkg1.r_emp;
exit when pkg1.c_emp%NOTFOUND;
DBMS_OUTPUT.put_line(pkg1.r_emp.id);
end loop;
close pkg1.c_emp;
end;
end;
22) What is substr()&instr()?
Substring from a main string.
SELECT substr('ORACLE',3,2) FROM dual;
23) Difference between case and decode?
drop table Employee;
create table Employee(id int,dept_id int);
insert into Employee values(1,11);
insert into Employee values(2,22);
select id,dept_id,
(case
when id=1 then 'Maths'
when id=2 then 'Science'
else 'no sub'
end) as subjects
from Employee;
Example 2:
select * from Employee where id<4 order by id,case when id=2 then dept_id else id end;
24) Can you use sysdate in check constraints? If no, why?
drop table Student;
create table Student(id int,dept_id int,results varchar(10));
insert into Student values(1,11,'Fail');
insert into Student values(2,22,'Fail');
insert into Student values(3,22,'pass');
insert into Student values(4,11,'Fail');
insert into Student values(5,33,'pass');
select * from Student pivot ( count(id) for dept_id in (11,22,33)) order by results;
28) SQL loader
declare
age number;
begin
age := 20;
if(age <21)
then
raise_application_error(-20008,'Your age is below limit');
end if;
end;
Note: The 1st parameter of raise_application error procedure should always be in range between -20,000 to -20,999.
31) Difference between RANK() and Dense_Rank () function.
drop table Student;
create table Student(id int,dept_id int,marks int);
insert into Student values(1,11,90);
insert into Student values(2,22,71);
insert into Student values(3,22,71);
insert into Student values(4,11,32);
insert into Student values(5,33,10);
select id,dept_id,marks,rank() over (order by marks desc) from Student;
select id,dept_id,marks,dense_rank() over (order by marks desc) from Student;
32) Can we avoid index by using hint?
36) If the view is updated, is it updated in base table.
create table Employee(id int,dept_id int,name varchar2(10),sal int);
declare
type nt_type is table of varchar(10);
nt_var nt_type;
begin
select name bulk collect into nt_var from Employee;
dbms_output.put_line(nt_var(1));
end;
39) How to create a Varray collection.
It is a Persistent collection which can be create as a seperate database object or can be created in PL/SQL block. They are bounded collection. Hence in the below example the varray holds only fixed amount of elements as highlighted.
declare
type nt_type is varray(5) of varchar(10);
nt_var nt_type;
begin
select name bulk collect into nt_var from Employee;
dbms_output.put_line(nt_var(1));
end;
Below example inserts data in table through collection variable called Associative Array.
drop table Employee;
create table Employee(id int);
declare
type col_var is table of number index by pls_integer;
usr_var col_var;
begin
for i in 1..10
loop
usr_var(i) := i;
end loop;
forall ix in 1..10
insert into Employee(id) values(usr_var(ix));
end;
select * from Employee;
43) How to raise pragma Exception INIT.
declare
excep_age exception;
age number := 17;
pragma exception_init(excep_age,-20008);
begin
if (age<18)
then
raise_application_error(-20008,'age is below limit');
end if;
Exception when excep_age then
dbms_output.put_line(SQLERRM);
end;
/
44) Example of predefined exception in pl/sql.
create table Employee(id int,dept_id int,subject varchar(20));
insert into Employee values(1,11,null);
insert into Employee values(2,22,null);
Creation of package 1
create or replace package pkg1 is
cursor c_emp is select * from Employee;
r_emp c_emp%ROWTYPE;
end;
/
--Here is a different package that references the cursor
create or replace package pkg2 is
procedure usr_proc;
end pkg2;
create or replace package body pkg2 is
procedure usr_proc is
begin
open pkg1.c_emp;
loop
fetch pkg1.c_emp into pkg1.r_emp;
exit when pkg1.c_emp%NOTFOUND;
DBMS_OUTPUT.put_line(pkg1.r_emp.id);
end loop;
close pkg1.c_emp;
end;
end;
22) What is substr()&instr()?
INSTR and SUBSTR are string functions.
Position of a sub string in a main string.
SELECT INSTR('ORACLE','L') FROM DUAL;Substring from a main string.
SELECT substr('ORACLE',3,2) FROM dual;
23) Difference between case and decode?
CASE is a statement where as DECODE is a function.
CASE can be used in both SQL and PLSQL . But DECODE can be used only in SQL.
CASE is used in where clause But you cant use DECODE in where clause.
Example 1:
create table Employee(id int,dept_id int);
insert into Employee values(1,11);
insert into Employee values(2,22);
select id,dept_id,
(case
when id=1 then 'Maths'
when id=2 then 'Science'
else 'no sub'
end) as subjects
from Employee;
Example 2:
select * from Employee where id<4 order by id,case when id=2 then dept_id else id end;
24) Can you use sysdate in check constraints? If no, why?
Answer:
A check constraint cannot refer a function like SYSDATE.
You can get a different result every time you call it.
25) Difference between column level constraints & table level constraints?
Column constraints and table constraints have the same function. the difference is in how
you specify them.
Table constraints allow you to specify constraints in more than one column in a PRIMARY KEY, UNIQUE, CHECK, or FOREIGN KEY constraint definition.
Column-level constraints (except for check constraints) refer to only one column.
26) What is optimiser?
The optimiser generates a set of plans for the SQL statement based on available access paths and hints.
The optimiser estimates the cost of each plan based on statistics in the data dictionary.
The optimiser compares the plans and chooses the plan with the lowest cost.
27) What is pivot operator?
PIVOT:
Pivot operator transposes an aggregated row of a table into a column.
The distinct row values become the columns in the output and aggregated column value
places itself under the appropriate pivoted column.
create table Student(id int,dept_id int,results varchar(10));
insert into Student values(1,11,'Fail');
insert into Student values(2,22,'Fail');
insert into Student values(3,22,'pass');
insert into Student values(4,11,'Fail');
insert into Student values(5,33,'pass');
select * from Student pivot ( count(id) for dept_id in (11,22,33)) order by results;
28) SQL loader
SQL Loader loads data from an external flat file into a table in the database.
The input data file stores delimited or raw data like CSV, tab-delimited, and pipe-delimited.
The parameter file stores the location of the input/output files.
The control file specifies on how data is loaded.
The parameter file specify the user id that will connect to the Oracle database.
The email.bad file stores invalid data.
The discard file contains records that did not match any record-selection criteria specified in
the control file.
29) What is Raise_application_error.
It is a procedure of package DBMS_STANDARD that allows user to raise user_defined error messages from pl/sql block.
age number;
begin
age := 20;
if(age <21)
then
raise_application_error(-20008,'Your age is below limit');
end if;
end;
Note: The 1st parameter of raise_application error procedure should always be in range between -20,000 to -20,999.
30) How many triggers can be applied to a table?
A maximum of 12 triggers applied to one table.
Rank Function provides rank to each value in a column based on the column specified by
order by clause.
create table Student(id int,dept_id int,marks int);
insert into Student values(1,11,90);
insert into Student values(2,22,71);
insert into Student values(3,22,71);
insert into Student values(4,11,32);
insert into Student values(5,33,10);
select id,dept_id,marks,rank() over (order by marks desc) from Student;
Dense Rank function:
Similar to RANK function however the DENSE_RANK function does not skip any ranks
when the ranks are similar in the preceding rows.
32) Can we avoid index by using hint?
Optimiser hint is a code written with in an sql query which controls or instructs the optimizer.
Instructs the optimiser to not to use the named index in determining a plan.
NO_INDEX(<table_name> < index_name>)
SELECT /*+ NO_INDEX(emp emp_ix) */ empno, ename FROM emp, dept WHERE
emp.deptno = dept.deptno;
33) Can we delete primary key of one table without changing foreign key of second
table?
Yes Using on delete cascade.
ON DELETE CASCADE;
drop table students;
create table Students
(
id int,
name varchar(10),
dept_id int constraint c1 primary key);
insert into students values(1,'vishnu',11);
insert into students values(2,'vinay',12);
insert into students values(3,'Teja',13);
insert into students values(4,'Ashwin',14);
drop table dept;
create table dept
(
dept_id int constraint c2 references students(dept_id) on delete cascade,
dept_name varchar(10),
subject_name varchar(18));
insert into dept values(11,'science','Biology');
insert into dept values(11,'science','Physics');
insert into dept values(11,'science','Chemistry');
insert into dept values(12,'social','social studies');
insert into dept values(13,'languages','English');
insert into dept values(13,'languages','Spanish');
delete from students where id=1;
select * from dept;
34) How to print all dates of particular month in a table (if it is not present in column)
select to_date('01.12.2019','dd.mm.yyyy')+level-1 from dual
connect by level <= TO_CHAR(LAST_DAY(to_date('01.12.2019','dd.mm.yyyy')),'DD');
35) How can we print 1 to 10 numbers in single query.
select level from dual connect by level<=10
36) If the view is updated, is it updated in base table.
Yes, base table is updated
drop table emp;
create table emp(id int,name varchar2(10),location varchar2(10));
insert into emp(id,name,location) values(1,'vishnu','chennai');
insert into emp(id,name,location) values(2,'priya','US');
drop view v1;
create view v1 as select id,location from emp;
update v1 set location='Bhuva' where id=2;
select * from emp;
1 vishnu chennai
2 priya Bhuva
37) What are collections in Oracle.
A collection is an ordered group of elements having the same data type.
There are two types of collection:
1) Persistent.
2) Non Persistent.
Persistent collection are stored physically in database and can be accessed when needed.
where as Non Persistent collection are available only for one session.
PL/SQL provides three collection types.
Persistent collection:
Nested table
Variable-size array or Varray
Non Persistent collection:
Associative array
38) How to create a Nested table collection.
It is a Persistent collection which can be create as a seperate database object or can be created in PL/SQL block. This is an unbounded collection.
create table Employee(id int,dept_id int,name varchar2(10),sal int);
insert into Employee values(1,11,'Vishnu',230);
insert into Employee values(2,22,'Vinay',130);
insert into Employee values(3,33,'Teja',24000);
type nt_type is table of varchar(10);
nt_var nt_type;
begin
select name bulk collect into nt_var from Employee;
dbms_output.put_line(nt_var(1));
end;
39) How to create a Varray collection.
It is a Persistent collection which can be create as a seperate database object or can be created in PL/SQL block. They are bounded collection. Hence in the below example the varray holds only fixed amount of elements as highlighted.
declare
type nt_type is varray(5) of varchar(10);
nt_var nt_type;
begin
select name bulk collect into nt_var from Employee;
dbms_output.put_line(nt_var(1));
end;
40) How to create an Associative Array
It is a Non Persistent collection which cannot be create as a separate database object. It can be created in PL/SQL block. They are unbounded collection similar to Nested table with an additional clause index by.
drop table Employee;
create table Employee(id int);
declare
type col_var is table of number index by pls_integer;
usr_var col_var;
begin
for i in 1..10
loop
usr_var(i) := i;
end loop;
forall ix in 1..10
insert into Employee(id) values(usr_var(ix));
end;
41) Difference between CHAR,VARCHAR,VARCHAR2
VARCHAR can stores up to 2000 bytes of characters and on the other hand VARCHAR2
can stores the 4000 bytes of characters.
VARCHAR occupy space for the NULL values and on the other hand VARCHAR2 not
occupy the space.
CHAR is fixed length, right padded with spaces and VARCHAR is fixed length, right padded
NULL and more over the VARCHAR2 is variable length and no storage space is taken for null values.
42) Difference between Raise ,Raise_application_error and pragma exception_init in oracle.
RAISE is also a standard built-in procedure by Oracle but it is mostly used to raise user-
defined exception. It is also used to raise standard oracle exception. Without showing an
actual message of exception, RAISE will just raise an exception and fails the execution of
the code.
RAISE_APPLICATION_ERROR is a standard built-in procedure by Oracle that raises an
error with number and user understandable error message. Exception includes both ORA
error and Application error.
The pragma EXCEPTION_INIT associates an exception name with an Oracle error
number.Exception includes ORA error.
declare
excep_age exception;
age number := 17;
pragma exception_init(excep_age,-20008);
begin
if (age<18)
then
raise_application_error(-20008,'age is below limit');
end if;
Exception when excep_age then
dbms_output.put_line(SQLERRM);
end;
/
44) Example of predefined exception in pl/sql.
NO_DATA_FOUND
ZERO_DIVIDE
TOO_MANY_ROWS
INVALID_CURSOR
CURSOR_ALREADY_OPEN.
45) What are INSTEAD of triggers?
The INSTEAD OF triggers are the triggers written especially for modifying views, which
cannot be directly modified through SQL DML statements.
drop table employee;
drop table dept;
create table Employee(id int);
create table dept(dept_name varchar(10));
insert into Employee values(1);
insert into dept values('maths');
create view v1 as select id,dept_name from Employee,dept;
insert into v1 values(2,'Science');
create or replace trigger instead_trig
instead of insert on v1
for each row
begin
insert into Employee values(:new.id);
insert into dept values(:new.dept_name);
end;
insert into v1 values(2,'Science');
select * from v1;
select * from Employee;
select * from dept;
46) Difference between Raise_application_error and pragma exception_init in
oracle.
Both the procedures are used to raise user defined exceptions but in raise_application_error procedure we cannot name the exception where as in pragma exception_init we can name the exception. We cannot name the exception in raise_application_error instead we use others as the exception name like below example.
Below are the examples of both.
pragma exception_init
declare
excep_age exception;
age number := 17;
pragma exception_init(excep_age,-20008);
begin
if (age<18)
then
raise_application_error(-20008,'age is below limit');
end if;
Exception when excep_age then
dbms_output.put_line(SQLERRM);
end;
/
raise_application_error
declare
age number := 17;
begin
if (age<18)
then
raise_application_error(-20008,'age is below limit');
end if;
Exception when others then
dbms_output.put_line(SQLERRM);
end;
/