Expleo Group Plsql Developer Interview Questions and Answers

Create a function based on shared logic

• Identify common logic that can be reused in multiple functions

• Create a separate function to encapsulate the shared logic

• Call the shared function from other functions where the logic is needed

Factorial of a number is the product of all positive integers up to that number.

• To calculate factorial of a number, multiply all positive integers up to that number.

• Factorial of 0 is 1.

• Example: Factorial of 5 is 5*4*3*2*1 = 120.

Modelling of schema involves designing the structure of a database to organize and represent data.

• Identify entities and their relationships

• Define attributes for each entity

• Establish primary and foreign keys

• Normalize the schema to reduce redundancy

• Consider performance and scalability

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

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.

A temporary variable used to store data during program execution.

• Temp variables are used to hold data temporarily during program execution.

• They are typically used in loops or conditional statements.

• Once the program execution is complete, the temp variable is no longer needed.

• Example: int temp = 0; for(int i=0; i<10; i++) { temp += i; }

• In this example, the temp variable is used to store the sum of the numbers 0-9.