Oracle interview questions
Explain the difference between
trigger and stored procedure.
Trigger in
act which is performed automatically before or after a event occur
Stored
procedure is a set of functionality which is
executed when it is explicitly invoked.
Explain Row level and statement
level trigger.
Row-level: - They get fired once for each row in a table affected
by the statements.
Statement: - They get fired once for each triggering statement.
Advantage of a stored procedure
over a database trigger
Firing
of a stored procedure can be controlled whereas on the other hand trigger will
get fired whenever any modification takes place on the table.
What are cascading triggers?
A Trigger that contains
statements which cause invoking of other Triggers are known as cascading
triggers. Here’s the order of execution of statements in case of cascading
triggers:
·
Execute all BEFORE statement triggers that apply to the
current statement.
·
Loop for each row affected statement.
·
Execute all BEFORE row triggers that apply to the current statement in
the loop.
·
Lock and change row, perform integrity constraints check; release
lock.
·
Execute all AFTER row triggers that apply to the current statement.
·
Execute all AFTER statement triggers that apply to
the current statement.
What is a JOIN? Explain types of JOIN in oracle.
A JOIN is used to
match/equate different fields from 2 or more tables using primary/foreign keys.
Output is based on type of Join and what is to be queries i.e. common data
between 2 tables, unique data, total data, or mutually exclusive data.
Types of JOINS:
JOIN Type
|
Example
|
Description
|
Simple
JOIN
|
SELECT p.last_name, t.deptName
FROM person p, dept t
WHERE p.id = t.id;
|
Find name and department name of students who have been allotted
a department
|
Inner/Equi/Natural
JOIN
|
SELECT * from Emp INNER JOIN Dept WHERE Emp.empid=Dept.empid
|
Extracts data that meets the JOIN conditions only. A JOIN is by
default INNER unless OUTER keyword is specified for an OUTER JOIN.
|
Outer
Join
|
SELECT distinct * from Emp LEFT OUTER JOIN Dept Where
Emp.empid=Dept.empid
|
It includes non matching rows also unlike Inner Join.
|
Self
JOIN
|
SELECT a.name,b.name from emp a, emp b WHERE a.id=b.rollNumber
|
Joining a Table to itself.
|
What is object data type in oracle?
New/User defined objects
can be created from any database built in types or by their combinations. It
makes it easier to work with complex data like images, media (audio/video). An
object types is just an abstraction of the real world entities. An object has:
·
Name
·
Attributes
·
Methods
Example:
Create type MyName as object (first varchar2(20), second
varchar2(20));
Now you can use this
datatype while defining a table below:
Create table Emp (empno number(5),Name MyName);
One can access the
Atributes as Emp.Name.First and Emp.Name.Second
What is composite data type?
Composite data types are
also known as Collections .i.e RECORD, TABLE, NESTED TABLE, VARRAY.
Composite data types are
of 2 types:
PL/SQL RECORDS
PL/SQL Collections-
Table, Varray, Nested Table
Differences between CHAR and NCHAR in Oracle
NCHAR allow storing of
Unicode data in the database. One can store Unicode characters regardless of
the setting of the database characterset
Differences between CHAR and VARCHAR2 in Oracle
CHAR is used to store
fixed length character strings where as Varchar2 can store variable length
character strings. However, for performance sake Char is quit faster than
Varchar2.
If we have char name[10]
and store “abcde”, then 5 bytes will be filled with null values, whereas in
case of varchar2 name[10] 5 bytes will be used and other 5 bytes will be freed.
Differences between DATE and TIMESTAMP in Oracle
Date is used to store date
and time values including month, day, year, century, hours, minutes and
seconds. It fails to provide granularity and order of execution when finding
difference between 2 instances (events) having a difference of less than a
second between them.
TimeStamp datatype
stores everything that Date stores and additionally stores fractional seconds.
Date: 16:05:14
Timestamp: 16:05:14:000
Define CLOB and NCLOB
datatypes.
CLOB: Character large object. It is 4GB in length.
NCLOB: National Character large object. It is CLOB datatype for
multiple character sets , upto 4GB in length.
What is the BFILE datatypes?
It refers to an external binary file and its size is limited by
the operating system.
What is Varrays?
Varrays
are one-dimensional, arrays. The maximum length is defined in the declaration
itself. These can be only used when you know in advance about the maximum
number of items to be stored.
For
example: One person can have multiple phone numbers. If we are storing this
data in the tables, then we can store multiple phone numbers corresponding to
single Name. If we know the maximum number of phone numbers, then we can use
Varrays, else we use nested tables.
What is a cursor? What are its
types?
Cursor is used to access the access the result set present in the
memory. This result set contains the records returned on execution of a query.
They are of 2 types:
1.
Explicit
2.
Implicit
Explain the attributes of
implicit cursor
- %FOUND
- True, if the SQL statement has changed any rows.
- %NOTFOUND
- True, if record was not fetched successfully.
- %ROWCOUNT
- The number of rows affected by the SQL statement.
- %ISOPEN
- True, if there is a SQL statement being associated to the cursor or the
cursor is open.
Explain the attributes of
explicit cursor.
- %FOUND
- True, if the SQL statement has changed any rows.
- %NOTFOUND
- True, if record was not fetched successfully.
- %ROWCOUNT
- The number of rows affected by the SQL statement.
- %ISOPEN
- True, if there is a SQL statement being associated to the cursor or the
cursor is open.
What is the ref cursor in Oracle?
REF_CURSOR allows
returning a recordset/cursor from a Stored procedure.
It is of 2 types:
Strong REF_CURSOR: Returning columns with datatype and length
need to be known at compile time.
Weak REF_CURSOR: Structured does not need to be known at
compile time.
Syntax till Oracle 9i
create or replace package REFCURSOR_PKG as
TYPE WEAK8i_REF_CURSOR IS REF CURSOR;
TYPE STRONG REF_CURSOR IS REF CURSOR RETURN EMP%ROWTYPE;
end REFCURSOR_PKG;
Procedure returning the
REF_CURSOR:
create or replace procedure test( p_deptno IN number , p_cursor OUT
REFCURSOR_PKG.WEAK8i_REF_CURSOR)
is
begin
open p_cursor FOR
select *
from emp
where deptno = p_deptno;
end test;
Since Oracle 9i we can use SYS_REFCURSOR
create or replace procedure test( p_deptno IN number,p_cursor OUT SYS_REFCURSOR)
is
begin
open p_cursor FOR
select *
from emp
where deptno = p_deptno;
end test;
For Strong
create or replace procedure test( p_deptno IN number,p_cursor OUT REFCURSOR_PKG.STRONG
REF_CURSOR)
is
begin
open p_cursor FOR
select *
from emp
where deptno = p_deptno;
end test;
What are the drawbacks of a cursor?
Cursors allow row by row
processing of recordset. For every row, a network roundtrip is made unlike in a
Select query where there is just one network roundtrip. Cursors need more I/O
and temp storage resources, thus it is slower.
What is a cursor variable?
In case of a cursor,
Oracle opens an anonymous work area that stores processing information. This
area can be accessed by cursor variable which points to this area. One must
define a REF CURSOR type, and then declare cursor variables of that type to do
so.
E.g.:
/* Create the cursor type. */
TYPE company_curtype IS REF CURSOR RETURN company%ROWTYPE;
/* Declare a cursor variable of that type. */
company_curvar company_curtype;
What is implicit cursor in Oracle?
PL/SQL creates an
implicit cursor whenever an SQL statement is executed through the code, unless
the code employs an explicit cursor. The developer does not explicitly declare
the cursor, thus, known as implicit cursor.
E.g.:
In the following
UPDATE statement, which gives everyone in the company a 20% raise, PL/SQL
creates an implicit cursor to identify the set of rows in the table which would
be affected.
UPDATE emp
SET salary = salary * 1.2;
Can you pass a parameter to a cursor? Explain
with an explain
Parameterized cursor:
/*Create a table*/
create table Employee(
ID
VARCHAR2(4 BYTE)NOT NULL,
First_Name
VARCHAR2(10 BYTE)
);
/*Insert some data*/
Insert into Employee (ID, First_Name)
values (‘01’,’Harry’);
/*create cursor*/
declare
cursor
c_emp(cin_No NUMBER)is select count(*) from employee where id=cin_No;
v_deptNo
employee.id%type:=10;
v_countEmp
NUMBER;
begin
open
c_emp (v_deptNo);
fetch
c_emp into v_countEmp;
close
c_emp;
end;
/*Using cursor*/
Open c_emp (10);
What is a package cursor?
A Package that returns a
Cursor type is a package cursor.
Eg:
Create or replace package pkg_Util
is
cursor
c_emp is select * from employee;
r_emp
c_emp%ROWTYPE;
end;
/*Another package using this
package*/
Create or replace package body pkg_aDifferentUtil
is
procedure
p_printEmps is
begin
open
pkg_Util.c_emp;
loop
fetch
pkg_Util.c_emp into pkg_Util.r_emp;
exit
when pkg_Util.c_emp%NOTFOUND;
DBMS_OUTPUT.put_line(pkg_Util.r_emp.first_Name);
end
loop;
close
pkg_Util.c_emp;
end;
end;
Explain why cursor variables are easier to use
than cursors.
Cursor variables are
preferred over a cursor for following reasons:
A cursor variable is not tied to a
specific query.
One can open a cursor variable for
any query returning the right set of columns. Thus, more flexible than cursors.
A cursor variable can be passed as a
parameter.
A cursor variable can refer to
different work areas.
What is locking, advantages of
locking and types of locking in oracle?
Locking is a mechanism to ensure data integrity while allowing
maximum concurrent access to data. It is used to implement concurrency control
when multiple users access table to manipulate its data at the same time.
Advantages of locking:
a.
Avoids deadlock conditions
b.
Avoids clashes in capturing the
resources
Types of locks:
a.
Read Operations: Select
b.
Write Operations: Insert, Update and Delete
What are transaction isolation
levels supported by Oracle?
Oracle supports 3 transaction isolation levels:
a.
Read committed (default)
b.
Serializable transactions
c.
Read only
What is SQL*Loader?
SQL*Loader
is a loader utility used for moving data from external files into the Oracle database
in bulk. It is used for high performance data loads.
What is Program Global Area
(PGA)?
The
Program Global Area (PGA): stores data and control information for a server
process in the memory. The PGA consists of a private SQL area and the session
memory.
What is a shared pool?
The shared pool is a key component. The shared pool is like a
buffer for SQL statements. It is to store the SQL statements so that the
identical SQL statements do not have to be parsed each time they're
executed.
38. What is snapshot in oracle?
A snapshot is a recent copy of a table from db or in some cases, a
subset of rows/cols of a table. They are used to dynamically replicate the data
between distributed databases.
What is a synonym?
A synonym is an alternative name tables, views,
sequences and other database objects.
What is a schema?
A schema is
a collection of database objects. Schema objects are logical structures created
by users to contain data. Schema objects include structures like tables, views,
and indexes.
What are Schema Objects?
Schema object is a
logical data storage structure. Oracle stores a schema object logically within
a tablespace of the
database.
What is a sequence in oracle?
Is a column in a table that allows a faster retrieval of data from
the table because this column contains data which uniquely identifies a row. It
is the fastest way to fetch data through a select query. This column has
constraints to achieve this ability. The constraints are put on this column so
that the value corresponding to this column for any row cannot be left blank
and also that the value is unique and not duplicated with any other value in
that column for any row.
Difference between a hot backup
and a cold backup
Cold backup:
It is taken when the database is closed and not available to
users. All files of the
database are copied (image copy). The datafiles cannot be changed during
the backup as they are locked, so the database remains in sync upon restore.
Hot backup: While taking the backup, if the
database remains open and available to users then this kind of back up is
referred to as hot backup. Image copy is made for all the files. As, the
database is in use the entire time, so there might be changes made when backup
is taking place. These changes are available in log
files so the database can be kept in sync
What are the purposes of Import
and Export utilities?
Export and Import are the utilities provided by oracle in order to
write data in a binary format from the db to OS files and to read them back.
These utilities are used:
·
To take backup/dump of data in
OS files.
·
Restore the data from the
binary files back to the database.
·
move data from one owner to
another
Difference between ARCHIVELOG
mode and NOARCHIVELOG mode
Archivelog mode is a mode in which backup is taken for all the
transactions that takes place so as to recover the database at any point of
time.
Noarichvelog mode is in
which the log files are not written. This mode has a disadvantage that the
database cannot be recovered when required. It has an advantage over archivelog
mode which is increase in performance.
What are the original Export
and Import Utilities?
SQL*Loader,
External Tables
What are data pump Export and
Import Modes?
It is used for fast and bulk data movement within oracle
databases. Data Pump utility is faster than the original import & export
utilities.
What are SQLCODE and SQLERRM
and why are they important for PL/SQL developers?
SQLCODE: It returns the error number for the last encountered error.
SQLERRM: It returns the actual error message of the last encountered
error.
Explain user defined exceptions
in oracle.
A User-defined exception has to be defined by the programmer.
User-defined exceptions are declared in the declaration section with their type
as exception. They must be raised
explicitly using RAISE statement, unlike pre-defined exceptions that are raised
implicitly. RAISE statement can also be used to raise internal exceptions.
Exception:
DECLARE
userdefined
EXCEPTION;
BEGIN
<Condition on which exception is to be
raised>
RAISE userdefined;
EXCEPTION
WHEN userdefined THEN
<task to perform when exception is raised>
END;
Explain the concepts of
Exception in Oracle. Explain its type.
Exception is the raised when an error occurs while program
execution. As soon as the error occurs, the program execution stops and the
control are then transferred to exception-handling part.
There are two types of exceptions:
1.
Predefined : These types of exceptions
are raised whenever something occurs beyond oracle rules. E.g. Zero_Divide
2.
User defined: The ones that occur based on
the condition specified by the user. They must be raised explicitly using RAISE statement, unlike
pre-defined exceptions that are raised implicitly.
How exceptions are raised in
oracle?
There are four ways that you or the PL/SQL runtime engine can
raise an exception:
·
Exceptions are raised automatically by the program.
·
The programmer
raises a user defined exceptions.
·
The programmer raises pre defined exceptions
explicitly.
What is tkprof and how is it
used?
tkprof
is used for diagnosing performance issues. It formats a trace file
into a more readable format for performance analysis. It is needed because
trace file is a very complicated file to be read as it contains minute details
of program execution.
What is Oracle Server
Autotrace?
It is a utility that provides instant feedback on successful
execution of any statement (select, update, insert, delete). It is the most basic utility to test the
performance issues.
