PL SQL Interview Questions and Answers
1. What is an index?
Index is an object in a database that provides direct access and quick search without scanning the entire table.
The index is used for the most used columns for queries.
2. What are the types of indexes?
B-tree index (default)
Bitmap index
Partition
Function-based
Domain index
Compound index
3. What are the constraints in the Oracle?
PRIMARY KEY
UNIQUE
FOREIGN KEY
CHECK
NOT NULL
4. What is the difference between primary key and unique key?
Primary key can not be NULL.
Unique key Constraint may have a NULL value.
5. What is a Foreign Key?
A foreign key is a column containing the primary key of another table.
6. What are the Data Manipulation Language (DML) Statements?
INSERT
UPDATE
DELETE
MERGE
SELECT
7. What are the Data Definition Language (DDL) Statements?
ALTER
CREATE
DROP
RENAME
TRUNCATE
COMMENT
8. What is the difference between truncate table and delete from table?
Truncated deletes all records in the table and does not allow rollbacks.
After the delete command, you can rollback the deleted records. Delete allows the use of clause where.
9. What are the Transaction Control Statements
SAVEPOINT
COMMIT
ROLLBACK
10. What are the types of UNION?
UNION
UNION ALL
11. What are the types of JOIN?
Left join
Full join
Right join
Self join
Inner join
12. What are the Oracle Set Operators?
UNION
UNION ALL
INTERSECT
MINUS
13. What is the difference between a function and a procedure?
The function returns only one mandatory value.
The procedure returns multiple values using OUT parameters.
Functions can be called from a SELECT statement.
Procedures can not be called from a SELECT statement.
Functions are used for computation.
Procedures are used for business logic.
Procedures always return integer value, 0 default.
14. What is a Cursor?
A cursor is a pointer to unnamed private SQL area.
15. What are the types of cursors?
Implicit cursors
Explicit cursors
16. Which are the parts of an explicit cursor?
Declare Cursor
Open cursor
FETCH statement
CLOSE statement
17. What are the attributes of a cursor?
SQL%ISOPEN
SQL%FOUND
SQL%NOTFOUND
SQL%ROWCOUNT
SQL%BULK_ROWCOUNT
SQL%BULK_EXCEPTIONS
18. What are the parts of a PL/SQL block?
Declaration
Execution
Exceptions
18. What are the LOOP statements?
LOOP
FOR LOOP
WHILE LOOP
20. What are the types of exceptions?
Predefined exceptions
User defined exceptions
21. Which are the most used predefined exceptions?
no_data_found
others
too_many_rows
dup_val_on_index
invalid_number
22. What is the difference between SQLCODE and SQLERRM?
SQLCODE returns the numeric code of the exception.
SQLERRM returns the error message associated with an error code.
23. What is the difference between %ROWTYPE and %TYPE?
%ROWTYPE allow to declare a record that represents a row of a database table or view.
%TYPE allow to declare a data item of the same data type as a previously declared variable or column.
24. What is a Trigger?
A trigger is a PLSQL block that is executed whenever an event occurs.
It fires implicitly whenever the triggering event occurs.
It has similar to stored procedures.
Triggers are implicitly fired by Oracle database when an triggering event occurs.
Triggers are used to prevent invalid transactions, are also used to set business rules or for event logging.
25. How many triggers can be set on a table?
The maximum number of Triggers that can be seted on a table is 12.
26. How do you disable a trigger?
alter trigger trigger_name disable;
27. How do you change the name of a table?
alter table old_table_name rename to new_table_name;
28. What is Mutating Trigger?
Mutating Trigger error occurs when a trigger tries to modify the same triggering table.
A trigger can not change a table that it has read from.
Solution: Use a view. Use autonomus transaction. Use Compound Trigger.
29. What is a View?
A PL/SQL view is a virtual table based on a query of one or more tables.
30. What is a Query?
A PL/SQL query is select statement based on a table or multiple tables.
31. What is a Subquery?
A subquery is a SELECT statement within another statement.
32. What are the Types of Subquery?
Single Row
Multiple Row
33. What are the Oracle Aggregate Functions?
AVG
COUNT
MAX
MIN
SUM
34. What are the Oracle Character Functions?
CONCAT
LENGTH
SUBSTR
INSTR
LOWER
UPPER
REPLACE
35. What are the Oracle Conversion Functions?
CAST
CONVERT
TO_CHAR
TO_DATE
TO_NUMBER
TO_CLOB
TO_LOB
TO_TIMESTAMP
36. What are the Oracle Date Functions?
SYSDATE
ADD_MONTHS
CURRENT_DATE
CURRENT_TIMESTAMP
EXTRACT (datetime)
MONTHS_BETWEEN
TRUNC (date)
LOCALTIMESTAMP
37. What is a Package?
A package is a schema object that groups logically related PL/SQL objects like: types, variables, constants, functions, procedures, cursors, and exceptions.
38. What are the advantages of using packages?
Modularity – Packages allow you encapsulate logically related objects
Hidden Implementation Details – Packages allow you hide the implementation details in the package body.
Added Functionality – Package public objects like variables and cursors can persist for the life of a session.
Better Performance – Oracle Database loads the whole package into memory.
Easier to Grant Roles – You can grant roles on the package, instead of granting roles on each object.
39. What are the types of collections?
Index-by tables (associative arrays)
Nested tables
Varrays (variable size arrays)
40. What is a Synonym?
A synonym is an alias for another schema object.
41. What is Data Dictionary?
Data Dictionary is a catalog that includes a set of readonly tables that provide information about objects in the database.
Objects that compose the Data Dictionary are: tables, views, indexes, clusters, synonyms, sequences, procedures, functions, packages, triggers.
42. List the Oracle Database PL/SQL Packages.
DBMS_ALERT
DBMS_JOB
DBMS_OUTPUT
DBMS_SQL
UTL_ENCODE
UTL_FILE
UTL_MAIL
UTL_SMTP