|
Oracle Interview Questions and Answers
What is syntax for dropping a procedure and a
function .Are these operations possible?
Drop Procedure procedure_name
Drop Function function_name
How will you delete duplicating rows from a base table?
delete from table_name where rowid not in (select
max(rowid) from table group by
duplicate_values_field_name); or delete
duplicate_values_field_name dv from table_name ta where
rowid <(select min(rowid) from table_name tb where ta.dv=tb.dv);
Difference between database triggers and form triggers?
-Data base trigger(DBT) fires when a DML operation is
performed on a data base table. Form trigger(FT) Fires
when user presses a key or navigates between fields on
the screen
-Can be row level or statement level No distinction
between row level and statement level.
-Can manipulate data stored in Oracle tables via SQL Can
manipulate data in Oracle tables as well as variables in
forms.
-Can be fired from any session executing the triggering
DML statements. Can be fired only from the form that
define the trigger.
-Can cause other database triggers to fire. Can cause
other database triggers to fire, but not other form
triggers.
What is a cursor for loop?
Cursor For Loop is a loop where oracle implicitly
declares a loop variable, the loop index that of the
same record type as the cursor's record.
How you will avoid duplicating records in a query?
By using DISTINCT
What is a view ?
A view is stored procedure based on one or more tables,
it’s a virtual table.
What is difference between UNIQUE and PRIMARY KEY
constraints?
A table can have only one PRIMARY KEY whereas there can
be any number of UNIQUE keys. The columns that compose
PK are automatically define NOT NULL, whereas a column
that compose a UNIQUE is not automatically defined to be
mandatory must also specify the column is NOT NULL.
What is use of a cursor variable? How it is defined?
A cursor variable is associated with different
statements at run time, which can hold different values
at run time. Static cursors can only be associated with
one run time query. A cursor variable is reference type
(like a pointer in C).
Declaring a cursor variable:
TYPE type_name IS REF CURSOR RETURN return_type
type_name is the name of the reference type,return_type
is a record type indicating the types of the select list
that will eventually be returned by the cursor variable.
How do you find the numbert of rows in a Table ?
A bad answer is count them (SELECT COUNT(*) FROM
table_name)
A good answer is :-
'By generating SQL to ANALYZE TABLE table_name COUNT
STATISTICS by querying Oracle System Catalogues (e.g.
USER_TABLES or ALL_TABLES).
The best answer is to refer to the utility which Oracle
released which makes it unnecessary to do ANALYZE TABLE
for each Table individually.
What is the maximum buffer size that can be specified
using the DBMS_OUTPUT.ENABLE function?
1,000,00
What are cursor attributes?
-%ROWCOUNT
-%NOTFOUND
-%FOUND
-%ISOPEN
There is a % sign in one field of a column. What will be
the query to find it?
'' Should be used before '%'.
What is ON DELETE CASCADE ?
When ON DELETE CASCADE is specified ORACLE maintains
referential integrity by automatically removing
dependent foreign key values if a referenced primary or
unique key value is removed.
What is the fastest way of accessing a row in a table ?
Using ROWID.CONSTRAINTS
What is difference between TRUNCATE & DELETE ?
TRUNCATE commits after deleting entire table i.e., can
not be rolled back. Database triggers do not fire on
TRUNCATEDELETE allows the filtered deletion. Deleted
records can be rolled back or committed. Database
triggers fire on DELETE.
What is a transaction ?
Transaction is logical unit between two commits and
commit and rollback.
What are the advantages of VIEW ?
To protect some of the columns of a table from other
users.To hide complexity of a query.To hide complexity
of calculations.
How will you a activate/deactivate integrity constraints
?
The integrity constraints can be enabled or disabled by
ALTER TABLE ENABLE constraint/DISABLE constraint.
Where the integrity constraints are stored in Data
Dictionary ?
The integrity constraints are stored in USER_CONSTRAINTS.
What is the Subquery ?
Sub query is a query whose return values are used in
filtering conditions of the main query.
How to access the current value and next value from a
sequence ? Is it possible to access the current value in
a session before accessing next value ?
Sequence name CURRVAL, Sequence name NEXTVAL.It is not
possible. Only if you access next value in the session,
current value can be accessed.
What are the usage of SAVEPOINTS ?value in a session
before accessing next value ?
SAVEPOINTS are used to subdivide a transaction into
smaller parts. It enables rolling back part of a
transaction. Maximum of five save points are allowed.
What is ROWID ?in a session before accessing next value
?
ROWID is a pseudo column attached to each row of a
table. It is 18 character long, blockno, rownumber are
the components of ROWID.
Explain Connect by Prior ?in a session before accessing
next value ?
Retrieves rows in hierarchical order.e.g. select empno,
ename from emp where.
How many LONG columns are allowed in a table ? Is it
possible to use LONG columns in WHERE clause or ORDER BY
?
Only one LONG columns is allowed. It is not possible to
use LONG column in WHERE or ORDER BY clause.
What is Referential Integrity ?
Maintaining data integrity through a set of rules that
restrict the values of one or more columns of the tables
based on the values of primary key or unique key of the
referenced table.
What is a join ? Explain the different types of joins ?
Join is a query which retrieves related columns or rows
from multiple tables.Self Join - Joining the table with
itself.Equi Join - Joining two tables by equating two
common columns.Non-Equi Join - Joining two tables by
equating two common columns.Outer Join - Joining two
tables in such a way that query can also retrieve rows
that do not have corresponding join value in the other
table.
If an unique key constraint on DATE column is created,
will it validate the rows that are inserted with SYSDATE
?
It won't, Because SYSDATE format contains time attached
with it.
Page Numbers :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34 35
Have a Question ?
post your questions here. It
will be answered as soon as possible.
Check
Job Interview Questions
for more Interview Questions with Answers
|
