Oracle Interview Questions and Answers
126. DOES
ORACLE SUPPORTS PROCEDURE OVERLOADING?
Ans: NO.
127. WHAT
IS A PACKAGE AND PACKAGE BODY?
Ans:
Package is declarative part of the functions and procedures stored
in that
package and package body is
the
definition part of the functions and procedures of that package.
128. WHAT
IS ADVANTAGE OF PACKAGE OVER PROCEDURE OR FUNCTION?
Ans:
Packages provides Functions or Procedures Overloading facility and
security to
those Functions or
Procedures.
129. IS IT
POSSIBLE TO HAVE A PROCEDURE AND A FUNCTION WITH THE SAME NAME?
Ans: NO if
it is out side a Package, YES if it is within a Package.
130. DOES
ORACLE SUPPORTS RECURSIVE FUNCTION CALLS?
Ans: YES.
131. WHAT
IS A TRIGGER? HOW IT IS DIFFERENT FROM A PROCEDURE?
Ans:
Trigger: A Trigger is a stored PL/SQL
program unit associated
with a
specific database table.
Procedure: A Procedure is to be explicitly
called by the user
whereas
Triggers are automatically called implicitly
by Oracle itself whenever event Occurs.
132. WHAT IS
DIFFERENCE BETWEEN A TRIGGER AND A CONSTRAINT?
Ans:
Constraints are always TRUE whereas Triggers are NOT always TRUE
and
Constraints has some limitations whereas Trigger has no limitations.
133. WHAT
ARE DIFFERENT EVENTS FOR A TRIGGER AND THEIR SCOPES?
Ans:
Insert, Update or Delete.
134. WHAT
IS DIFFERENCE BETWEEN TABLE LEVEL AND ROW LEVEL TRIGGERS?
Ans: Table
level Triggers execute once for each table based transaction
whereas Row
level Triggers will execute once FOR EACH ROW.
135.
WHAT ARE AUTONOMOUS TRIGGERS?
Ans:
Supports to provide Commit statement in Triggers. Triggers a declared as
independent
Transactions.
136. WHAT
IS AN "INSTEAD OF" TRIGGER?
Ans: These
Triggers are used with the Complex Views only to make
possible of
Insert, Update and Delete on those Views.
137. HOW
MANY TRIGGERS CAN BE CONFIGURED ON A TABLE AND VIEW?
Ans: 18
Triggers
138. WHAT
IS "TABLE MUTATING" ERROR? HOW TO SOLVE IT?
Ans:
ORA-04091: Table name is mutating,
trigger/function may not see it
Cause : A trigger
or a user-defined PL/SQL function that is referenced
in the
statement attempted to query or modify a table that was in the middle of being
modified by the statement that fired the trigger.
Action :
Rewrite the trigger or function so it does not read the table.
139. WHEN
TO USE ":NEW" AND ":OLD" SPECIFIERS?
Ans: The prefix :old is used to refer to values
already present in the
table. The
prefix :new is a correlation name that refers to the new value that is inserted / updated.
141. HOW
TO CREATE A USER-DEFINED VARIABLE
IN PL
Ans: Define variable in declaration section
142. HOW TO
CREATE AN ARRAY VARIABLE IN PL
Ans: Using
CREATE [OR REPLACE] TYPE <type name>
AS VARRAY
(size) OF ELEMENT_TYPE (NOT NULL) Command;
143. HOW
TO MAKE A USER-DEFINED DATA TYPE GLOBAL IN PL/SQL?
Ans:
Declare the variable in a Package
144. HOW TO
CREATE AN OBJECT IN ORACLE?
Ans: Using
CREATE [OR REPLACE] TYPE <type name> AS OBJECT (ATTRIBUTE
NAME DATA
TYPE,..) Command
145. WHAT
IS A TRANSIENT AND PERSISTENT OBJECT?
Ans: The
Object created in a table is called Persistent Object.
Object created on execution of PL/SQL
block is called Transient Object.
146. WHAT
IS A COLUMN OBJECT AND TABLE OBJECT?
Ans: A
Column Object is only a Column of a table.
147. HOW TO
GRANT PERMISSION ON AN OBJECT TO OTHER USER?
Ans: GRANT
<permission> ON <object name> TO <user name>.
148. WHAT
IS A COLLECTION OF ORACLE?
Ans:
Varray, Nested Table is a collection of Oracle.
149. WHAT
IS DIFFERENCE BETWEEN VARRAY AND NESTED TABLE?
Ans: Varray
has a fixed size.
Nested tables can carry any number of
values.
150. HOW TO
MODIFY CONTENTS OF A VARRAY IN ORACLE?
Ans: To
modify a stored VARRAY it has to selected into a
PL/SQL variable and then inserted back into
the table.
151. WHAT
IS USE OF "THE" OPERATOR FOR NESTED TABLE?
Ans: THE
operator allows nested tables to be manipulated using DML when
it is
stored in a Table.
152. WHICH
PACKAGE IS USED FOR FILE INPUT/OUTPUT IN ORACLE?
Ans:
UTL_FILE Package is used for File input/output in Oracle.
153. NAME
SOME METHODS AND PROCEDURES OF FILE I/O PACKAGE?
Ans: FOPEN,
FCLOSE, FFLUSH, IS_OPEN, GET_LINE,
PUT_LINE, PUTF, NEW_LINE
154. WHAT
IS SQLJ? HOW IT IS DIFFERENT FROM JDBC CONNECTIVITY?
Ans: SQLJ
is basically a Java program containing embedded static SQL
statements
that are compatible with Java design philosophy.
155. WHAT
IS AN ITERATOR? Name some TYPES OF ITERATORS?
Ans: SQLJ
Iterators are basically record groups generated during
transaction,
which requires manipulation of more than one records from one or more tables.
There are two types Iterators namely Named Iterator and Positional Iterator. 
.jpg)
0 comments:
Post a Comment