Virtusa Consulting Services Plsql Developer Interview Questions and Answers for Experienced

Types of cursors include implicit, explicit, and parameterized cursors.

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

• Parameterized cursors allow for dynamic SQL statements to be executed with different parameters.

Execution plan is a roadmap created by the database optimizer to determine the most efficient way to execute a query.

• Execution plan shows the steps the database will take to execute a query.

• Query optimization involves finding the most efficient way to execute a query.

• Optimization techniques include index usage, join methods, and access paths.

• Understanding execution plans helps in tuning queries for better performance.

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

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.

• PL/SQL can be used to create stored procedures, functions, and triggers i...read more

SQL is a structured query language used to communicate with databases. SQL extensions add additional functionality to the language.

• SQL is a standard language used to interact with databases

• SQL extensions like PL/SQL add procedural programming capabilities to SQL

• PL/SQL is an extension of SQL used in Oracle databases