Oracle PL SQL Interview Questions & Answers
1)What is PL/SQL?
Ans:
PL/SQL (Procedural Language/SQL) is basically a procedural extension of Oracle – SQL. PL/SQL helps the user to develop complex database applications using control structures, procedures, function, modules, etc.
2)What is the purpose of using PL/SQL?
Ans:
PL/SQL is an extension of SQL. While SQL is non-procedural, PL/SQL is a procedural language designed by Oracle. It is invented to overcome the limitations of SQL.
3)What are the most important characteristics of PL/SQL?
Ans:
A list of some notable characteristics:
PL/SQL is a block-structured language.
It is portable to all environments that support Oracle.
PL/SQL is integrated with the Oracle data dictionary.
Stored procedures help better sharing of application.
4)What is PL/SQL table? Why it is used?
Ans:
Objects of type tables are called PL/SQL tables that are modeled as database table. We can also say that PL/SQL tables are a way to providing arrays. Arrays are like temporary tables in memory that are processed very quickly. PL/SQL tables are used to move bulk data. They simplifies moving collections of data.
5)What are the datatypes available in PL/SQL?
Ans:
There are two types of datatypes in PL/SQL:
Scalar datatypes Example are NUMBER, VARCHAR2, DATE, CHAR, LONG, BOOLEAN etc.
Composite datatypes Example are RECORD, TABLE etc.
6)What is the basic structure of PL/SQL?
Ans:
PL/SQL uses BLOCK structure as its basic structure. Each PL/SQL program consists of SQL and PL/SQL statement which form a PL/SQL block.
PL/SQL block contains 3 sections.
The Declaration Section (optional)
The Execution Section (mandatory)
The Exception handling Section (optional)
7)What is the difference between FUNCTION, PROCEDURE AND PACKAGE in PL/SQL?
Ans:
Function: The main purpose of a PL/SQL function is generally to compute and return a single value. A function has a return type in its specification and must return a value specified in that type.
Procedure: A procedure does not have a return type and should not return any value but it can have a return statement that simply stops its execution and returns to the caller. A procedure is used to return multiple values otherwise it is generally similar to a function.
Package: A package is schema object which groups logically related PL/SQL types , items and subprograms. You can also say that it is a group of functions, procedure, variables and record type statement. It provides modularity, due to this facility it aids application development. It is used to hide information from unauthorized users.
8)What is exception? What are the types of exceptions?
Ans:
Exception is an error handling part of PL/SQL. There are two type of exceptions: pre_defined exception and user_defined exception.
9)How to write a single statement that concatenates the words ?Hello? and ?World? and assign it in a variable named Greeting?
Ans:
Greeting := ‘Hello’ || ‘World’;
10)Does PL/SQL support CREATE command?
Ans:
No. PL/SQL doesn’t support the data definition commands like CREATE.
11)Write a unique difference between a function and a stored procedure.
Ans:
A function returns a value while a stored procedure doesn?t return a value.
12)How exception is different from error?
Ans:
Whenever an Error occurs Exception arises. Error is a bug whereas exception is a warning or error condition.
13)What is the main reason behind using an index?
Ans:
Faster access of data blocks in the table.
14)What are PL/SQL exceptions? Tell me any three.
Ans:
Too_many_rows
No_Data_Found
Value_error
Zero_error etc.
15)How many types of triggers exist in PL/SQL?
Ans:
There are 12 types of triggers in PL/SQL that contains the combination of BEFORE, AFTER, ROW, TABLE, INSERT, UPDATE, DELETE and ALL keywords.
BEFORE ALL ROW INSERT
AFTER ALL ROW INSERT
BEFORE INSERT
AFTER INSERT etc.
16)what are the two virtual tables available at the time of database trigger execution?
Ans:
Table columns are referred as THEN.column_name and NOW.column_name.
For INSERT related triggers, NOW.column_name values are available only.
For DELETE related triggers, THEN.column_name values are available only.
For UPDATE related triggers, both Table columns are available.
17)What are the different schemas objects that can be created using PL/SQL?
Ans:
Stored procedures and functions
Packages
Triggers
Cursors
18)What do you know by PL/SQL Cursors?
Ans:
Oracle uses workspaces to execute the SQL commands. When Oracle processes a SQL command, it opens an area in the memory called Private SQL Area. This area is identified by the cursor. It allows programmers to name this area and access it?s information.
19)What is the difference between the implicit and explicit cursors?
Ans:
Implicit cursor is implicitly declared by Oracle. This is a cursor to all the DDL and DML commands that return only one row.
Explicit cursor is created for queries returning multiple rows.
20)What will you get by the cursor attribute SQL%ROWCOUNT?
Ans:
The cursor attribute SQL%ROWCOUNT will return the number of rows that are processed by a SQL statement.
21)Explain the SAVEPOINT statement?
Ans:
With SAVEPOINT, only part of transaction can be undone.
22)What is consistency?
Ans:
Consistency simply means that each user sees the consistent view of the data.
Consider an example: there are two users A and B. A transfers money to B’s account. Here the changes are updated in A’s account (debit) but until it will be updated to B’s account (credit), till then other users can’t see the debit of A’s account. After the debit of A and credit of B, one can see the updates. That?s consistency.
23)What is cursor and why it is required?
Ans:
A cursor is a temporary work area created in a system memory when an SQL statement is executed.
A cursor contains information on a select statement and the row of data accessed by it. This temporary work area stores the data retrieved from the database and manipulate this data. A cursor can hold more than one row, but can process only one row at a time. Cursor are required to process rows individually for queries.
24)What are the restrictions of DML operations on Views?
Ans:
Few restrictions of DML operations on Views are:
You cannot DELETE a row if the View contains the following:
Group Functions
A Group By clause
The Distinct Keyword
The Pseudo column ROWNUM Keyword.
You cannot MODIFY data in a View if it contains the following:
Group Functions
A Group By clause
The Distinct Keyword
The Pseudo column ROWNUM Keyword.
Columns defined by expressions (Ex; Salary * 22)
25)What is Global Variables?
Ans: In oracle we are declaring global variables in Package Specification only.
For more Click Here
For Course Content Click Here