100+ Plsql Developer Interview Questions and Answers

A procedure in PL/SQL is a named block of code that can be called and executed multiple times.

• Syntax: CREATE [OR REPLACE] PROCEDURE procedure_name [(parameter1 [mode1] datatype1 [, parameter2 [mode2] datatype2]...)] IS

• Difference between procedure and function: Procedures do not return a value, while functions return a value.

• Procedures are used to perform an action, while functions are used to calculate and return a value.

• Procedures can have OUT or IN OUT parameters to pass va...read more

Temp table is a table created temporarily in memory. Temp variable is a variable that holds temporary data.

• Temp table is used to store data temporarily during a session

• Temp variable is used to hold temporary data that is not needed after a certain point

• Temp table and variable are created using the 'CREATE GLOBAL TEMPORARY' and 'DECLARE' statements respectively

• Example: CREATE GLOBAL TEMPORARY TABLE temp_table (id NUMBER, name VARCHAR2(50));

• Example: DECLARE temp_var VARCHAR2(50...read more

PL/SQL program to print numbers in descending order from 103 to 3

• Use a loop to iterate from 103 to 3

• Print each number in the loop

• Decrement the loop counter by 3 in each iteration

A mutating table or mutating trigger occurs when a trigger tries to update a table that is currently being modified.

• Mutating table occurs when a trigger references the table that is being modified.

• It can happen when a trigger is fired by an INSERT, UPDATE, or DELETE statement on the table.

• This can lead to unpredictable results or errors, such as ORA-04091: table is mutating, trigger/function may not see it.

• To avoid mutating table errors, use row-level triggers instead of stat...read more

SQL query to delete duplicate records from a table.

• Use the DELETE statement with a subquery to identify and delete duplicate records.

• The subquery should select the duplicate records based on the criteria for duplication.

• Ensure to keep at least one copy of the duplicate records to avoid deleting all instances.

Autonomous transaction is a separate transaction initiated by a parent transaction.

• It allows a subtransaction to commit or rollback independently of the parent transaction.

• It is useful for logging or auditing purposes.

• It can be created using the PRAGMA AUTONOMOUS_TRANSACTION statement.

• Example: A parent transaction updates a table, while an autonomous transaction logs the changes made.

• Example: An autonomous transaction sends an email notification after a parent transaction com...read more

To eliminate duplicates without using the distinct command in PL/SQL, we can use the GROUP BY clause.

• Use the GROUP BY clause to group the data by the columns that you want to eliminate duplicates from.

• Select the columns you want to display in the result set.

• Aggregate functions like COUNT, SUM, AVG, etc. can be used to perform calculations on the grouped data.

• The GROUP BY clause ensures that only unique combinations of the grouped columns are returned.

The interview questions cover topics such as Oracle instance recovery, file systems, SQL/PL/SQL queries, Unix commands, and ITIL concepts.

• An instance in Oracle refers to the memory structures and background processes that manage a database.

• Instance recovery is the process of bringing a database instance back to a consistent state after a failure.

• Oracle has both logical and physical file systems. The logical file system includes tablespaces, data files, and control files, whil...read more

Joins are used to combine rows from two or more tables based on related columns. There are different types of joins.

• Types of joins: inner join, left join, right join, full outer join, cross join

• Joins are used to retrieve data from multiple tables based on a related column

• Natural join is a type of join that automatically matches columns with the same name in both tables

• Example: SELECT * FROM employees NATURAL JOIN departments;

Global temp table is a temporary table that can be accessed by multiple sessions.

• Global temp table is created using CREATE GLOBAL TEMPORARY TABLE statement.

• Data in global temp table is visible to all sessions but is deleted when the session that created it ends.

• Global temp table can be used to store intermediate results in a multi-step process.

• In my project, I used global temp table to store data temporarily while performing complex calculations.

Soft parsing is reusing the existing execution plan while hard parsing is generating a new execution plan.

• Soft parsing is faster as it avoids the overhead of generating a new execution plan.

• Hard parsing is slower as it involves generating a new execution plan.

• Soft parsing occurs when the SQL statement is already in the shared pool.

• Hard parsing occurs when the SQL statement is not in the shared pool or needs a new execution plan.

• Soft parsing saves CPU and memory resources.

• Hard...read more

Procedure to create dummy tables of whole schema

• Loop through all tables in schema

• Create a new table with same structure and name as original table

• Insert dummy data into new table

Implicit cursors are automatically created by the Oracle server, while explicit cursors are explicitly declared by the programmer.

• Implicit cursors are used for single-row queries, while explicit cursors are used for multi-row queries.

• Implicit cursors are automatically opened, fetched, and closed by the Oracle server, while explicit cursors need to be manually opened, fetched, and closed by the programmer.

• Implicit cursors are less flexible and have limited control, while expli...read more

Indexing is a technique used in databases to improve the performance of queries by creating a data structure that allows for faster data retrieval.

• Indexing creates a separate data structure that contains a subset of the data in the database, organized in a way that allows for efficient searching and retrieval.

• Indexes are created on one or more columns of a table and can be used to quickly locate data based on the values in those columns.

• By using indexes, database systems can ...read more

A view is a virtual table created from one or more tables. It can be used to simplify complex queries and provide a customized view of data.

• A view is a stored query that can be treated as a table

• Types of views include simple views, complex views, and materialized views

• A complex view is a view that involves multiple tables or subqueries

• Complex views can be used to combine data from different tables or apply complex calculations

• Example of a complex view: CREATE VIEW employee_de...read more

HAVING is used to filter groups while GROUP BY is used to group rows based on a column.

• HAVING is used with GROUP BY to filter groups based on a condition

• GROUP BY is used to group rows based on a column

• HAVING is used after GROUP BY in a query

• GROUP BY is used before HAVING in a query

• Example: SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 5000;

To copy the structure of a table without copying the data, you can use the CREATE TABLE AS SELECT statement.

• Use the CREATE TABLE AS SELECT statement to create a new table with the same structure as the original table.

• Specify the columns and their data types in the SELECT statement, but exclude the actual data from the original table.

• Example: CREATE TABLE new_table AS SELECT * FROM original_table WHERE 1=0;

Truncate removes all data, delete removes selected data.

• Truncate is faster than delete as it doesn't log individual row deletions.

• Truncate cannot be rolled back, delete can be.

• Truncate resets identity columns, delete doesn't.

• Truncate doesn't fire triggers, delete does.

• Truncate is a DDL operation, delete is a DML operation.

A trigger is a PL/SQL block that is automatically executed in response to a specific event. A procedure is a named PL/SQL block that performs a specific task. A cursor is a database object used to retrieve data from a result set.

• A trigger is used to automatically execute a set of SQL statements when a specific event occurs, such as inserting, updating, or deleting data from a table.

• A procedure is a reusable block of code that can be called multiple times to perform a specific...read more

SQL optimization, normalization, and pragma usage are important concepts in PL/SQL development.

• SQL optimization involves improving the performance of SQL queries by analyzing and modifying the query structure, indexes, and data access patterns.

• Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity.

• Pragma is a compiler directive that provides additional information to the compiler to optimize code performance.

• Examples of p...read more

The query displays employee records with the same salary.

• Use the GROUP BY clause to group the records by salary.

• Use the HAVING clause to filter the groups with more than one employee.

• Select the necessary columns to display the employee records.

Indexes are database structures used to improve query performance.

• Indexes are created on one or more columns of a table.

• They allow for faster data retrieval by reducing the number of disk I/O operations.

• However, they can slow down data modification operations such as INSERT, UPDATE, and DELETE.

• Indexes can also take up significant disk space and memory.

• Different types of indexes include B-tree, bitmap, and hash indexes.

Triggers are database objects that automatically execute in response to certain events.

• Triggers can be used to enforce business rules, audit data changes, and maintain referential integrity.

• They can be defined to execute before or after an event, such as a row being inserted, updated, or deleted.

• Triggers can also be nested, meaning one trigger can execute another trigger.

• Examples of triggers include automatically updating a timestamp when a row is modified, or preventing a de...read more

Cursors are used to retrieve and manipulate data from a database in PL/SQL.

• Cursors are like pointers to a result set, allowing us to fetch and process rows one by one.

• There are two types of cursors: implicit and explicit.

• Implicit cursors are automatically created by Oracle when executing a SQL statement.

• Explicit cursors are declared and used by the programmer.

• Explicit cursors provide more control and flexibility compared to implicit cursors.

• Cursors can be used to fetch data f...read more

Procedures and functions are both PL/SQL program units, but they have some key differences.

• Procedures do not return a value, while functions do.

• Procedures can have OUT parameters to pass values back to the caller, while functions cannot.

• Functions can be used in SQL statements, while procedures cannot.

• Functions must return a value, while procedures do not necessarily have to.

• Procedures are typically used for performing actions, while functions are used for calculations or data...read more

Foreign key, primary key, and unique key are all constraints used in database tables to enforce data integrity.

• Primary key is a column or a set of columns that uniquely identifies each row in a table.

• Foreign key is a column or a set of columns in one table that refers to the primary key in another table.

• Unique key ensures that the values in a column or a set of columns are unique across all the rows in a table.

A cursor is a database object used to retrieve data from a result set one row at a time.

• Types of cursors: Implicit, Explicit, Ref, and Dynamic

• Implicit cursor is used for single row queries

• Explicit cursor is used for multi-row queries

• Ref cursor is used to point to a cursor variable

• Dynamic cursor is used to execute dynamic SQL statements

A cursor is a pointer to a private SQL area that stores information about the processing of a SELECT or DML statement. A trigger is a stored program that is executed automatically in response to certain events or actions.

• A cursor is used to retrieve data from a result set one row at a time

• A trigger can be used to enforce business rules, audit changes to data, or replicate data to other tables

• Triggers can be classified as row-level or statement-level triggers

• Trigger performanc...read more

SQL query to find the nth highest salary

• Use the ORDER BY clause to sort the salaries in descending order

• Use the LIMIT clause to select the nth highest salary

Using GROUP BY clause with aggregate functions can obtain distinct values without using the DISTINCT keyword.

• Use GROUP BY clause with aggregate functions like COUNT, SUM, AVG, etc.

• Example: SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name;

Indexes should be created on columns that are frequently used in WHERE, JOIN, and ORDER BY clauses.

• Indexes should be created on columns with high selectivity

• Indexes should not be created on columns with low cardinality

• Indexes should not be created on columns with frequent updates

• Composite indexes can be created on multiple columns

• Indexes can be created on virtual columns

VARCHAR can store up to 2000 bytes while VARCHAR2 can store up to 4000 bytes.

• VARCHAR can store variable-length character strings up to 2000 bytes.

• VARCHAR2 can store variable-length character strings up to 4000 bytes.

• VARCHAR will occupy space for NULL values while VARCHAR2 will not.

• VARCHAR2 is more efficient in terms of storage and performance compared to VARCHAR.

Explanation of SQL Loader, Procedure, Package, Function, Bulk Collect, Cursor

• SQL Loader is a tool used to load data from external files into Oracle database tables

• Procedure is a named block of PL/SQL code that performs a specific task

• Package is a collection of related procedures, functions, variables, and cursors

• Function is a named block of PL/SQL code that returns a value

• Bulk Collect is a method used to fetch multiple rows of data from a query into a collection

• Cursor is a po...read more

GTT stands for Global Temporary Table, which is a temporary table used to store data temporarily during a session.

• GTT is a temporary table that is created and used within a session

• Data in GTT is visible only to the session that created it

• GTT is automatically dropped at the end of the session or when the transaction is committed or rolled back

Explanation of triggers and their types in PL/SQL

• Triggers are database objects that are automatically executed in response to certain events

• Types of triggers include DML, DDL, and system triggers

• Syntax for creating a trigger: CREATE [OR REPLACE] TRIGGER trigger_name {BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON table_name [FOR EACH ROW] [WHEN condition] BEGIN ... END;

I have used both implicit and explicit cursors in PL/SQL development.

• Implicit cursors are used for single-row queries while explicit cursors are used for multi-row queries.

• I have used explicit cursors with parameters to make the query more dynamic.

• I have also used cursor variables to pass cursors as parameters to procedures and functions.

• Examples of cursor types I have used include FOR LOOP, FETCH, and UPDATE cursors.

The PLSQL code snippet to replace only the third character with * in a given string.

• Use the SUBSTR function to extract the first two characters of the string.

• Concatenate the extracted characters with '*' and the remaining characters starting from the fourth position using the SUBSTR function.

• Assign the modified string back to the original variable.

RDBMS is a type of DBMS that stores data in a structured manner using tables with relationships.

• DBMS is a software system that manages data stored in a computer's memory or disk storage.

• RDBMS is a type of DBMS that uses a relational model to store data in tables with relationships.

• RDBMS supports ACID properties (Atomicity, Consistency, Isolation, Durability) for data integrity.

• Examples of RDBMS include Oracle, MySQL, SQL Server, and PostgreSQL.

• Examples of DBMS include MongoDB...read more

Constraints are rules enforced on data in a table, while triggers are actions performed in response to certain events.

• Constraints ensure data integrity and consistency, while triggers automate actions based on specific events.

• Constraints are defined at the time of table creation, while triggers are created separately.

• Constraints can be used to enforce rules such as unique values, primary keys, and foreign keys, while triggers can be used to perform actions such as logging cha...read more

Using PL/SQL to create a procedure with a ref cursor to find the average and factorial of a given number.

• Create a procedure that takes in a number as input and returns the average of that number using a ref cursor.

• Use the EXISTS function to check if a factorial exists for a given number.

• Write a block of code to calculate the factorial of a number using a loop.

Stored procedure is a precompiled program that is stored in a database and can be called by other programs.

• Stored procedures are used to encapsulate business logic and improve performance.

• PL/SQL is a procedural language used to write stored procedures in Oracle databases.

• Stored procedures can accept input parameters and return output parameters or result sets.

• They can also be used to enforce security and data integrity rules.

• Example: CREATE PROCEDURE get_employee_details (emp...read more

A cursor is a database object used to retrieve data from a result set one row at a time.

• There are two types of cursors in PL/SQL: Implicit and Explicit.

• Implicit cursors are automatically created by Oracle when a SQL statement is executed.

• Explicit cursors are defined by the programmer using the DECLARE, OPEN, FETCH, and CLOSE statements.

• Cursors are used to process individual rows returned by a query, allowing for more control over data manipulation.

Delete is a DML operation that removes specific rows from a table, while truncate is a DDL operation that removes all rows from a table.

• Delete is slower than truncate as it generates undo logs and triggers

• Delete can be rolled back, while truncate cannot be rolled back

• Delete operation maintains the integrity of the transaction log, while truncate operation does not

• Delete operation fires delete triggers, while truncate operation does not

• Delete operation can have a WHERE clause ...read more

Exception handling is a mechanism to handle errors or unexpected events in a program.

• Syntax: BEGIN -- code block EXCEPTION -- exception handling block END;

• Exceptions can be predefined or user-defined

• Common predefined exceptions include NO_DATA_FOUND, TOO_MANY_ROWS, etc.

• User-defined exceptions can be declared using the EXCEPTION keyword

I wrote a procedure to calculate the average salary of employees in a department.

• Used cursor to fetch employee data

• Calculated total salary using loop

• Divided total salary by number of employees

• Handled exceptions for zero employees in department

Delete, drop, and truncate are SQL commands used to remove data from a table, but they differ in their functionality.

• DELETE is used to remove specific rows from a table based on a condition.

• DROP is used to remove an entire table from the database.

• TRUNCATE is used to remove all rows from a table, but keeps the structure intact.

• DELETE and TRUNCATE can be rolled back, but DROP cannot.

• DELETE triggers the delete trigger, while TRUNCATE and DROP do not.

SQL is a query language used to interact with databases, while PL/SQL is a procedural language extension for SQL.

• SQL is used for querying and manipulating data in databases.

• PL/SQL is used for writing procedural code like loops, conditions, and functions.

• SQL statements are executed one at a time, while PL/SQL blocks can contain multiple statements.

• SQL is declarative, while PL/SQL is procedural.

• Example: SQL - SELECT * FROM employees; PL/SQL - DECLARE x NUMBER := 10; BEGIN DBMS_...read more