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)
3. What are the constraints in the Oracle?
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?
7. What are the Data Definition Language (DDL) Statements?
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
10. What are the types of UNION?
11. What are the types of JOIN?
12. What are the Oracle Set Operators?
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?
16. Which are the parts of an explicit cursor?
17. What are the attributes of a cursor?
18. What are the parts of a PL/SQL block?
18. What are the LOOP statements?
20. What are the types of exceptions?
User defined exceptions
21. Which are the most used predefined exceptions?
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?
33. What are the Oracle Aggregate Functions?
34. What are the Oracle Character Functions?
35. What are the Oracle Conversion Functions?
36. What are the Oracle Date Functions?
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)
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.