PL/SQL Interview Questions- Part 6

PL/SQL Interview Questions- Part 6

PL/SQL is a powerful tool in the Oracle ecosystem, widely used for developing applications that interact with the database. As a procedural extension to SQL, it helps developers create structured and efficient code for data processing and business logic. If you’re targeting a career in backend development, Oracle support, or database administration, you’ll likely face PL/SQL questions in your interview.

This page includes a collection of interview questions designed to test your understanding of PL/SQL basics, advanced logic, and real-world problem-solving. These questions cover areas like anonymous blocks, cursors, error handling, triggers, and stored procedures.

Whether you are just entering the job market or aiming to move up in your current role, knowing how to answer these questions clearly and confidently can set you apart from other candidates. Use this resource to boost your preparation and make a strong impression in your next tech interview.

Answer:

Primary Key Unique Key
A Primary Key refers to a unique identifier for rows of the table. Unique Key is a unique identifier for rows of the table when the Primary Key is not present.
A Primary Key cannot be NULL. A Unique Key can be NULL.
Just one Primary Key can be present in the table. Multiple Unique Keys can be present in the table.
The selection using a Primary key creates a clustered index. The selection using a Unique Key creates a non-clustered index.

Answer:

ON DELETE CASCADE implies that when a row is deleted in the parent table, the dependent rows in a child table will also get deleted. ON DELETE SET NULL converts a foreign key value to null when a parent value is removed. Without the ON DELETE SET NULL or ON DELETE CASCADE, the row in a parent table cannot be deleted if referenced in a child table.

Answer:

A Candidate Key is a specific field in the relational database that can independently identify each unique record of the other data. To select a Candidate Key from the set of super keys, you need to look at the super key set. A table can have several Candidate keys but only one Primary Key.

Answer:

Views in SQL are a type of virtual table that has columns & rows. We can create a view by choosing fields from one or multiple tables in a database. It can either have all the rows of the table or certain rows based on a specific condition. A good database should contain views because of the given reasons:

  • Restricting data access: Views provide another level of table security by restricting access to the predetermined set of table columns & rows.
  • Hiding data complexity: Views can hide the complexity that exists in the multiple tables join.
  • Simplify commands for the user: Views enable a user to select information from the multiple tables without needing the users to know how to perform the join.
  • Store complex queries: Views help to store complex queries.
  • Rename Columns: Views also enable you to rename columns without affecting the base tables, so the no. of columns in a view must match the no. of columns specified in a SELECT statement. Thus, renaming helps to hide the column names of the base tables.
  • Multiple view facility: It is possible to create different views on the same table for different users.

Answer:

Sr. No. Terms of Differences Simple View Complex View
1 Definition Simple View is based on one or a single base table. Complex View is based on more than one table or multiple base tables.
2 Associations No major associations are required to be applied in Simple View as only one table is in context. However, in Complex View, general associations are needed to be applied like order by clause, join conditions, a group by clause as it has multiple tables in the context.
3 Group Functions In Simple View, we cannot use group functions such as COUNT(), MAX(), etc. In Complex View, because of multiple tables, we can use several group functions.
4 Operations allowed DML operations can be easily performed. DML operations cannot always be performed in Complex View.
5 Alteration As Simple View supports DML operations, INSERT, DELETE & UPDATE are possible directly. We cannot apply INSERT, DELETE & UPDATE in the Complex View.
6 NULL columns In Simple View, we cannot include NOT NULL columns from the base table. In the Complex View, it is possible to include NOT NULL columns.

Answer:

Below are few DML operations restrictions on Views:

One cannot DELETE a row if the View includes:

  • A Group By clause
  • The Distinct Keyword
  • Group Functions
  • Pseudo column ROWNUM Keyword

One cannot MODIFY data in the View if it contains:

  • Columns defined by expressions
  • A Group By clause
  • Distinct Keyword
  • Group Functions
  • Pseudo column ROWNUM Keyword

Answer:

Triggers are used for the following purposes:

  • For automatically generating derived column values;
  • To enforce referential integrity;
  • To store information or data on the table access & event logging;
  • Auditing;
  • Synchronous replication of the tables;
  • To impose security authorizations;
  • For preventing invalid transactions;

Answer:

A Global Variable refers to a variable defined outside the function or subroutine. Due to its global scope, the Global Variable holds its value throughout the program’s lifetime. Thus, it is accessible throughout the program by any function defined within the program until it is shadowed.

Answer:

Flashback Query enables users to see the view of the past data. So, if the user deleted some table or data, then the flashback query provides us with a chance to view that deleted data again & perform manipulations over it. In flashback queries, we have a flash area in which we store the deleted information that can be viewed if needed in the future. Your server needs to be configured as per the automatic undo management if you want to use the flashback query feature. In case your system supports the traditional rollback approach, then you cannot perform a flashback query.

Answer:

A Join is an SQL operation performed to build a connection between two or more database tables on the basis of their matching columns, thus creating a relationship between the tables. Most complex queries involve Join commands in an SQL database management system.

Answer:

A view refers to a subset of a database generated from a query & stored as a permanent object. Though a view’s definition is permanent, its data is dynamic based on the point in time at which the view is accessed.

Answer:

The Cascade Constraints in SQL Server helps to set related values to the NULL or set affected values to the delete columns or original default values.

Answer:

Table functions were designed to return a set of rows by PL/SQL logic, but they’re intended to be used as a normal view or table in a SQL statement. The table functions are also used to pipeline data & information in the ETL process.

Answer:

Materialized views are objects reduced sets of information that have been grouped, aggregated, or summarized from the base tables. They are mainly used in decision support systems or data warehouses.

Answer:

  • Temporary tablespace stores the temporary segments used for huge transactions that need a sorting space where the temporary segments are used.
  • Permanent tablespace stores database table data, schema objects, or permanent data.

Answer:

An Anonymous block is a PL/SQL block that does not have any name. It cannot be stored in a database. The anonymous blocks can call other blocks, but they cannot be called by the other blocks, as anonymous blocks don’t have a name. An anonymous block is executed either by storing a block code in the file or writing a block code at a SQL prompt.

Answer:

SAVEPOINT command helps to temporarily save a transaction so you can roll back to that point whenever needed. With the SAVEPOINT command, you can name different states of data into any table & then roll back to that state using the ROLLBACK command when required.

Answer:

A pseudo column acts as a table column but is not actually stored in the table. We can select from the pseudo columns but cannot update, insert, or delete their values. It is similar to a function without arguments. The following are a few pseudo columns used in the procedural statements:

  1. CURRVAL & NEXTVAL: You can refer to a sequence value in the SQL statements with these pseudo columns:
    • CURRVAL: It returns a sequence’s current value.
    • NEXTVAL: It increments a sequence & then returns the next value.
  1. LEVEL: For every row returned by the hierarchical query, a LEVEL pseudo column returns one for a root node, two for a child of the root, & so on.
  2. ROWNUM: Oracle engine maintains the no. of each record inserted by the users in a table. By using the ROWNUM clause, you can access the data as per the record inserted.
  3. ROWID: For every row in a database, the ROWID pseudo column returns the row’s address. ROWID contains:
    • FileNo: It means table number.
    • DataBlockNo: It is the space assigned by the Oracle engine to save a record.
    • RecordNo: Oracle engine maintains the record no. for each record.

Answer:

The SELECT FOR UPDATE clause is used to lock rows in the table when delete or an update transaction is performed on an active set. It selects the table’s rows that are yet to be modified & locks each row in the result set. It prevents other users from accessing the same set of records when modification is executed on a table. You must add the NOWAIT clause to a SELECT FOR UPDATE statement to prevent the procedure from indefinitely waiting if the lock cannot be acquired. If a NOWAIT clause is used, the control returns to a program if any other user has locked the requested rows.

Answer:

Sr. No. Key Triggers Stored procedures
1 Basic A database trigger refers to a stored procedure that runs automatically when some events occur (e.g., update, insert, delete) A stored procedure is a piece of the code written in PL/SQL to execute specific tasks.
2 Running Methodology It can automatically execute based on the events. It needs to be invoked explicitly by the users.
3 Parameter Triggers cannot take input as a parameter. Stored procedures can take input as a parameter.
4 Transaction statements We can’t use a transaction statements inside a database trigger. We can use transaction statements such as commit transaction, begin transaction, and rollback inside the stored procedure.
5 Return Triggers cannot return values Stored procedures can the return values.