Virtusa Consulting Services Oracle SQL and PLSQL Developer Interview Questions, Process, and Tips

Reference cursor in procedure is used to return result sets from a stored procedure.

• Declare a cursor variable in the procedure using TYPE keyword

• Open the cursor variable using OPEN keyword

• Fetch data from the cursor using FETCH keyword

• Close the cursor using CLOSE keyword after fetching all data

Lead and lag functions are used to access data from a subsequent or previous row in a result set.

• Lead function is used to access data from the next row in the result set.

• Lag function is used to access data from the previous row in the result set.

• Both functions can be used to compare values between rows or calculate differences.

Yes, you can update in a function in Oracle SQL and PLSQL.

• You can use the UPDATE statement within a PLSQL function to update data in a table.

• Make sure to commit the changes using COMMIT statement.

• Example: CREATE OR REPLACE FUNCTION update_employee_salary(emp_id IN NUMBER, new_salary IN NUMBER) RETURN NUMBER IS BEGIN UPDATE employees SET salary = new_salary WHERE employee_id = emp_id; COMMIT; RETURN 1; END;

No, you cannot commit in a trigger as it can cause mutating table errors.

• Triggers cannot contain COMMIT or ROLLBACK statements.

• Committing in a trigger can lead to mutating table errors.

• Instead, use triggers for data validation, not for committing transactions.

Autonomous transaction is a separate transaction that is independent of the main transaction.

• Autonomous transactions are used to perform certain tasks independently of the main transaction.

• They are useful for logging, auditing, or error handling purposes.

• Autonomous transactions are started with the PRAGMA AUTONOMOUS_TRANSACTION statement.

• Changes made in an autonomous transaction are not visible to the main transaction ...read more