100+ Plsql Developer Interview Questions and Answers

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

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

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

To find if two tables have similar data, compare the records in both tables using a join or a subquery.

• Use a join operation to compare the records in both tables based on a common column.

• If the tables have a primary key, you can join them on that key to check for similar data.

• Alternatively, you can use a subquery to compare the data in both tables and check for matching records.

• Consider using the MINUS operator to find the differences between the two tables.

• You can also use t...read more

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.

Case and decode are conditional expressions in PL/SQL used for data manipulation.

• CASE is used for conditional logic in SQL statements, while DECODE is used for conditional logic in SELECT statements.

• CASE is more flexible and can handle multiple conditions, while DECODE is limited to one condition.

• CASE can be used in both SQL and PL/SQL, while DECODE is specific to SQL.

• Example of CASE: SELECT CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ELSE default_result EN...read more

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

A package in PL/SQL is a collection of related procedures, functions, variables, and other constructs.

• A package consists of two parts: package specification and package body.

• The package specification defines the public interface of the package, including declarations of variables, constants, cursors, procedures, and functions.

• The package body contains the actual implementation of the procedures and functions declared in the package specification.

• It is not possible to use a pa...read more

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

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;

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

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

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.

Writing a SQL query with a subquery involves nesting one query inside another to retrieve specific data.

• Start by writing the main query that will retrieve the primary data

• Identify the criteria for the subquery to filter the results

• Enclose the subquery within parentheses and use it in the WHERE or FROM clause of the main query

• Ensure that the subquery returns a single value or a single column result

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;

Char is fixed length, varchar is variable length with max 4000 bytes, varchar2 is variable length with max 32767 bytes.

• Char is fixed length and always right-padded with spaces, while varchar and varchar2 are variable length.

• Varchar can store up to 4000 bytes of data, while varchar2 can store up to 32767 bytes.

• Char is less efficient in terms of storage compared to varchar and varchar2.

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;

Rank assigns unique ranks to each distinct row, while Dense Rank assigns consecutive ranks without gaps.

• Rank may have gaps in the ranking sequence, while Dense Rank does not.

• Rank assigns the same rank to rows with the same values, while Dense Rank assigns different ranks.

• Rank function is non-consecutive, while Dense Rank function is consecutive.

Yes, I have performed performance tuning by identifying bottlenecks and optimizing queries.

• Identified slow queries using tools like SQL Trace, Explain Plan, and AWR reports.

• Optimized queries by adding indexes, rewriting SQL statements, and reducing unnecessary data retrieval.

• Tuned PL/SQL code by using bulk processing, minimizing context switches, and optimizing loops.

• Utilized database features like partitioning and materialized views for performance improvement.

No, it is not possible to combine two tables with differing data and columns without utilizing joins.

• Joins are necessary to combine tables based on a common column or key.

• Different data and columns require a join to match and merge the data properly.

• Examples of joins include INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.

No, a package body cannot be used without a package specification.

• A package body must always be associated with a package specification.

• The package specification defines the public interface of the package, while the package body contains the implementation details.

• Attempting to use a package body without a corresponding package specification will result in compilation errors.