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.
