Infosys Plsql Developer Interview Questions and Answers for Experienced

Editing data in complex views can affect underlying tables, but it depends on the view's structure and rules.

• Complex views often involve joins, aggregations, or groupings, making them non-updatable.

• If a view is updatable, changes made to it will reflect in the underlying tables.

• Example: A simple view on a single table can be updated directly.

• Example: A view that aggregates data (e.g., SUM, COUNT) cannot be updated dire...read more

Performance tuning involves optimizing database queries and configurations for improved efficiency and speed.

• Analyze execution plans to identify slow queries. For example, use EXPLAIN PLAN to see how Oracle executes a query.

• Use indexing strategically. For instance, create indexes on columns frequently used in WHERE clauses to speed up data retrieval.

• Optimize SQL queries by avoiding SELECT *, using WHERE clauses effecti...read more

Regular expressions (regex) are sequences of characters that define search patterns for strings, used for pattern matching and manipulation.

• Regex is used for validating input formats, e.g., email addresses: ^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$

• It can extract specific patterns from text, such as phone numbers: \d{3}-\d{3}-\d{4}

• Regex supports various metacharacters like '.', '*', and '+', which have special me...read more

SQL Loader is a tool for loading data from external files into Oracle databases efficiently.

• Use the command: sqlldr userid=username/password control=control_file.ctl

• Control file specifies the format of the data file and how to load it.

• Example control file: LOAD DATA INFILE 'data.csv' INTO TABLE my_table FIELDS TERMINATED BY ','

• SQL Loader supports various data formats like CSV, fixed-width, etc.

• You can use options like ...read more

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

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.

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