30+ TCS Plsql Developer Interview Questions and Answers

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

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

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

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

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

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

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

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

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;

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.

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.

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.

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

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.

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.

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.

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

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;

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

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

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.

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

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

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

A dynamic cursor in database management allows for the execution of different SQL queries at runtime.

• Dynamic cursors are used when the SQL query to be executed is not known until runtime.

• They allow for flexibility in querying the database based on user input or other conditions.

• Dynamic cursors can be used to handle varying result sets or conditions in a more efficient manner.

• Example: Using a dynamic cursor to search for different products based on user-selected criteria.

Explanation of triggers and their types in PL/SQL

• Triggers are database objects that are automatically executed in response to certain events

• Types of triggers include DML, DDL, and system triggers

• Syntax for creating a trigger: CREATE [OR REPLACE] TRIGGER trigger_name {BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON table_name [FOR EACH ROW] [WHEN condition] BEGIN ... END;

Yes, it is possible to update data in a view using INSTEAD OF triggers.

• Views are virtual tables that display data from one or more tables.

• By using INSTEAD OF triggers, you can update data in a view by specifying the logic to handle the update operation.

• The trigger intercepts the update operation on the view and executes the specified logic to update the underlying tables.

• For example, you can create an INSTEAD OF trigger on a view to update data in multiple tables based on cer...read more

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.

Group by and having clause are used together to filter groups based on specified conditions.

• Group by clause is used to group rows that have the same values into summary rows.

• Having clause is used to filter groups based on specified conditions.

• Example: SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 5000;

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.

I have a strong ability to learn new software quickly.

• I am a self-motivated learner and enjoy exploring new technologies.

• I am comfortable with online tutorials, documentation, and experimenting with new software.

• I have experience learning new software such as Oracle SQL Developer, Toad, and PL/SQL Developer.

• I am able to adapt to new software quickly and efficiently.

Table size can be obtained by querying the data dictionary views in SQL.

• Use the query 'SELECT table_name, round((num_rows * avg_row_len)/1024/1024,2) as size_mb FROM all_tables WHERE table_name = 'YOUR_TABLE_NAME';'

• Alternatively, you can use the query 'SELECT segment_name, bytes/1024/1024 as size_mb FROM user_segments WHERE segment_name = 'YOUR_TABLE_NAME';'

TCS is a global IT services company known for its innovative solutions and commitment to employee growth.

• TCS has a strong reputation in the IT industry for delivering high-quality services.

• TCS offers opportunities for professional growth and development through training programs and career advancement.

• TCS has a diverse and inclusive work culture that values teamwork and collaboration.

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

Delete removes specific rows from a table, drop removes the entire table, truncate removes all rows from a table.

• Delete is a DML command used to remove specific rows from a table based on a condition.

• Drop is a DDL command used to remove an entire table along with its structure and data.

• Truncate is a DDL command used to remove all rows from a table but keeps the table structure intact.

• Delete can be rolled back, drop cannot be rolled back, truncate cannot be rolled back.

• Example...read more