Oracle Interview Answers

  1. What are the various types of queries?

The types of queries are:

  • Normal Queries
  • Sub Queries
  • Co-related queries
  • Nested queries
  • Compound Queries
  1. What is a transaction?

A transaction is a set of SQL statements between any two COMMIT and ROLLBACK statements.

  1. What is implicit cursor and how is it used by Oracle?

An implicit cursor is a cursor which is internally created by Oracle. It is created by Oracle for each individual SQL.

  1. Which of the following is not a schema object: Indexes, tables, public synonyms, triggers and packages?

Public synonyms

  1. What is PL/SQL?

PL/SQL is Oracle's Procedural Language extension to SQL. The language includes object oriented programming techniques such as encapsulation, function overloading, information hiding (all but inheritance), and so, brings state-of-the-art programming to the Oracle database server and a variety of Oracle tools.

  1. Is there a PL/SQL Engine in SQL*Plus?

No. Unlike Oracle Forms, SQL*Plus does not have a PL/SQL engine. Thus, all your PL/SQL are send directly to the database engine for execution. This makes it much more efficient as SQL statements are not stripped off and send to the database individually.

  1. What are the various types of Exceptions?

User defined and Predefined Exceptions.

  1. What is the difference between a procedure and a function?

Functions return a single variable by value whereas procedures do not return any variable by value. Rather they return multiple variables by passing variables by reference through their OUT parameter.

  1. What is Over Loading and what are its restrictions?

OverLoading means an object performing different functions depending upon the number of parameters or the data type of the parameters passed to it.

  1. What are the constructs of a procedure, function or a package?

The constructs of a procedure, function or a package are:

variables and constants

cursors

exceptions