OptiSol Business Solutions Plsql Developer Interview Questions and Answers

Developed a PL/SQL project for managing inventory and sales data in a retail store.

• Designed database tables to store product information, sales transactions, and customer details.

• Implemented PL/SQL procedures and functions for adding, updating, and querying data.

• Created triggers to enforce business rules and maintain data integrity.

• Developed reports using PL/SQL queries to analyze sales performance and inventory levels

I work on performance optimization by analyzing query execution plans, indexing, and code refactoring.

• Analyzing query execution plans to identify bottlenecks

• Creating appropriate indexes to improve query performance

• Refactoring code to optimize resource usage

• Using tools like Explain Plan and SQL Tuning Advisor

Packages and types are two important components in PL/SQL used for organizing and defining reusable code and data structures.

• Packages are containers for related procedures, functions, variables, and other PL/SQL constructs.

• Types are user-defined data structures that can be used to define new data types in PL/SQL.

• Packages can contain types as well as other PL/SQL constructs, providing a way to organize and encapsulate r...read more

To delete duplicate records in PL/SQL, use a combination of SELECT DISTINCT and DELETE statements.

• Identify duplicate records using SELECT DISTINCT with a COUNT(*) function.

• Use a DELETE statement with a subquery to remove duplicate records.

• Consider creating a temporary table to store unique records before deleting duplicates.

Collation is the set of rules determining how data is sorted and compared in a database.

• Collation defines the order in which characters are sorted and compared in a database

• It includes rules for comparing characters with diacritics, case sensitivity, and special characters

• Different collations can affect sorting order and comparison results

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...read more

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 mut...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 ...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 compar...read more

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 updat...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.

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 val...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...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;

Procedure is used to perform an action, while function returns a value.

• Procedure does not return a value, while function does

• Functions can be used in SQL queries, procedures cannot

• Functions can be called from SQL statements, procedures cannot

In performance tuning, init refers to the initialization parameter file used by Oracle Database to configure various settings.

• init is a text-based configuration file that contains parameters to optimize the performance of Oracle Database.

• It is used to set parameters such as memory allocation, parallel processing, and resource utilization.

• By tuning the init file, developers can improve the overall performance of the dat...read more

Named and unnamed exceptions in PL/SQL with examples

• Named exceptions are user-defined exceptions with specific names like 'custom_exception'

• Unnamed exceptions are predefined exceptions like 'NO_DATA_FOUND' or 'TOO_MANY_ROWS'

• Named exceptions can be raised using RAISE statement with the exception name

• Unnamed exceptions are raised automatically by the system in case of errors

• Example of named exception: DECLARE custom_exce...read more