Plsql Developer
100+ Plsql Developer Interview Questions and Answers
Popular Companies
Q1. What is procedure in plsql and it's syntax and difference between procedure and function?
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
Q2. What is temp table and temp variable in plsql?
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
Plsql Developer Interview Questions and Answers for Freshers
Q3. A plsql programme to print 103,99,96...3?
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
Q4. What is mutating table or mutating trigger?
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
Q5. How do you find if two table having similer data
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
Q6. Write a sql to delete duplicate records from a table
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.
Share interview questions and help millions of jobseekers 🌟
Q7. Write a code to pull-up mass data from one table and load into our rough table ? Written the code efficiently Again asked how to tune this code with performance efficiency. Answered correctly about more depth .
Code to pull-up mass data from one table and load into another table efficiently
Use INSERT INTO SELECT statement to pull data from source table and load into target table
Use WHERE clause to filter data if required
Use INDEXES on columns used in WHERE clause to improve performance
Use BULK COLLECT to fetch data in batches to improve performance
Use PARALLEL hint to execute the query in parallel if the table is large
Use NOLOGGING option to improve performance by reducing redo log ...read more
Q8. What is autonomous transaction?
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
Plsql Developer Jobs
Q9. 13.How we can eliminate duplicates without using distinct command
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.
Q10. 1) What is Instance, how to recover instance . 2) Oracle logical/physical file system and processors . 3) SQL / PL/SQL Queries . 4) Unix Commands . 5) ITIL related questions . Ex- What is SLA, how many types of...
read moreThe 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
Q11. 3.what is joins and its types what is the use and what is natural join with example
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;
Q12. What is Global temp table ? Where did you use it in your project?
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.
Q13. 2.how to recover the data(Tables) in oracle or how to export the tables in file
To recover data in Oracle, you can use the flashback feature or export tables using the Data Pump utility.
To recover data using flashback, you can use the FLASHBACK TABLE statement to restore a table to a previous state.
To export tables, you can use the Data Pump utility with the EXPDP command to export tables to a file.
You can also use the SQL Developer tool to export tables as SQL insert statements or CSV files.
Q14. Difference between soft parsing and hard parsing of sql
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
Q15. Write a procedure to create dummy tables of whole schema.
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
Q16. Difference between implicit and explicit cursor
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
Q17. 10.What is indexing what is the role of indexing in database
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
Q18. 9.What is view and its type what is complex view with example
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
Q19. What are the definitions of case and decode, what are the differences between them, and can you explain the logic for each?
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
Q20. What is the definition of a package, and is it possible to use a package body without a package specification?
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
Q21. Difference between having and group by?
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;
Q22. 8.Write a command of copy the structure only not data of the table
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;
Q23. Truncate vs delete difference?
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.
Q24. 1.What is Trigger,procedure,cursor differentiate with example
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
Q25. Did you perform performance tuning, and if so, what steps did you take?
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.
Q26. Is it possible to combine two tables with differing data and columns without utilizing joins?
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.
Q27. Is it possible to use a package body without a package specification?
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.
Q28. What are the differences between Rank and Dense Rank in SQL?
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.
Q29. What are the primary key and foreign key in database design, and can you provide examples of each?
Primary key uniquely identifies each record in a table, while foreign key establishes a link between two tables.
Primary key ensures uniqueness and cannot have null values
Foreign key establishes a relationship between tables based on the primary key of another table
Example of primary key: EmployeeID in an Employee table
Example of foreign key: DepartmentID in an Employee table linking to DepartmentID in a Department table
Q30. What is a SQL query that can be used to find duplicate values in a database?
Use a SQL query with GROUP BY and HAVING clause to find duplicate values in a database.
Use GROUP BY clause to group the values that are duplicated.
Use HAVING clause to filter out the groups that have more than one occurrence.
Example: SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name HAVING COUNT(*) > 1;
Q31. What is the difference between char, varchar, and varchar2 data types?
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.
Q32. What is the process for writing a SQL query that includes a subquery?
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
Q33. What is SQL optimization. Normalization. Pragma usage
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
Q34. 12.Write a query to display employee records having same salary
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.
Q35. What are indexes ? What Indexes?are drawbacks of
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.
Q36. Explain about Triggers ?
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
Q37. What are the definitions of the Substr and Instr functions?
Substr function extracts a substring from a string, while Instr function returns the position of a substring within a string.
Substr function syntax: SUBSTR(string, start_position, length)
Example: SUBSTR('Hello World', 7, 5) will return 'World'
Instr function syntax: INSTR(string, substring)
Example: INSTR('Hello World', 'World') will return 7
Q38. What is the difference between procedures and functions?
Procedures are used to perform an action, while functions return a value.
Procedures do not return a value, while functions do.
Functions can be called from SQL queries, while procedures cannot.
Functions must return a value, while procedures do not necessarily have to.
Q39. Can you describe two complex JIRA issues you have worked on?
Resolved a critical bug causing data loss and implemented a new feature for better user experience.
Identified root cause of data loss bug by analyzing database queries and logs
Collaborated with cross-functional teams to prioritize and implement a fix
Designed and implemented a new feature based on user feedback to enhance usability
Q40. What are joins in SQL, and can you provide examples of their usage?
Joins in SQL are used to combine rows from two or more tables based on a related column between them.
Joins are used to retrieve data from multiple tables based on a related column.
Common types of joins include INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.
Example: SELECT * FROM table1 INNER JOIN table2 ON table1.column = table2.column;
Q41. What are the different types of cursors in database management systems?
Types of cursors in database management systems include implicit, explicit, and parameterized cursors.
Implicit cursors are automatically created by the database when a SQL statement is executed.
Explicit cursors are defined by the programmer and give more control over the result set.
Parameterized cursors allow for dynamic SQL statements with parameters.
Examples: SELECT statement using implicit cursor, DECLARE, OPEN, FETCH, CLOSE for explicit cursor, using parameters in a curso...read more
Q42. What is a package, and how do you utilize it in your project?
A package is a collection of related procedures, functions, variables, and other PL/SQL constructs.
Packages help organize and encapsulate code for easier maintenance and reuse.
They can contain both public and private elements.
Packages can be used to group related functionality together, improving code modularity.
Example: CREATE PACKAGE my_package AS ... END my_package;
Q43. What is a trigger in database management, and what are its different types?
A trigger in database management is a special type of stored procedure that is automatically executed when certain events occur in a database.
Triggers can be used to enforce business rules, maintain referential integrity, and automate repetitive tasks.
There are two main types of triggers: row-level triggers and statement-level triggers.
Row-level triggers are fired for each row affected by a triggering statement, while statement-level triggers are fired once for each triggerin...read more
Q44. What is the query to print the third highest salary from the given table?
Use a subquery to find the third highest salary in a table.
Use the RANK() function to assign a rank to each salary in descending order.
Filter the results to only include rows with a rank of 3.
Consider handling ties in salaries appropriately.
Q45. What steps did you take to perform root cause analysis in your project?
I conducted thorough analysis by reviewing code, logs, and discussing with team members.
Reviewed code to identify potential issues
Analyzed logs for error messages and patterns
Discussed with team members to gather insights and perspectives
Used debugging tools to trace the root cause
Q46. Cursors definition and types explained
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
Q47. Difference between Procedure and function
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
Q48. 4.Differentiate Foreign key,primary key and unique key
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.
Q49. What is a cursor. And its types
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
Q50. What is cursor? What is trigger & trigger performance
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
Interview Questions of Similar Designations
Top Interview Questions for Plsql Developer Related Skills
Interview experiences of popular companies
Calculate your in-hand salary
Confused about how your in-hand salary is calculated? Enter your annual salary (CTC) and get your in-hand salary
Reviews
Interviews
Salaries
Users/Month