oracle pl sql interview questions and answers free download

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/SQL?

Ans:  Define variable in declaration section

142. HOW TO CREATE AN ARRAY VARIABLE IN PL/SQL?

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.