How many types of cursors are available in PL/SQL?
There are two types of cursors in PL/SQL. Implicit cursor, and explicit cursor
PL/SQL Interview Questions
What is cursor and why it is required?
A cursor is a temporary work area created in a system memory when an SQL statement is executed. A cursor contains information on a select statement and the row of data accessed by it. This temporary work area stores the data retrieved from the database and manipulate this data. A cursor can hold more than …
What is consistency?
Consistency simply means that each user sees the consistent view of the data. Consider an example: there are two users A and B. A transfers money to B’s account. Here the changes are updated in A’s account (debit) but until it will be updated to B’s account (credit), till then other users can’t see the …
What is mutating table error?
Mutating table error is occurred when a trigger tries to update a row that it is currently using. It is fixed by using views or temporary tables.
Explain the SAVEPOINT statement.
With SAVEPOINT, only part of transaction can be undone.
Explain the Rollback statement?
The Rollback statement is issued when the transaction ends. Following conditions are true for a Rollback statement: The work done in a transition is undone as if it was never issued. All locks acquired by transaction are released.
Explain the Commit statement.
Following conditions are true for the Commit statement: Other users can see the data changes made by the transaction. The locks acquired by the transaction are released. The work done by the transaction becomes permanent.
What is the difference between syntax error and runtime error?
A syntax error can be easily detected by a PL/SQL compiler. For example: incorrect spelling etc. while, a runtime error is handled with the help of exception-handling section in a PL/SQL block. For example: SELECT INTO statement, which does not return any rows.
What are the cursor attributes used in PL/SQL?
%ISOPEN: it checks whether the cursor is open or not. %ROWCOUNT: returns the number of rows affected by DML operations: INSERT,DELETE,UPDATE,SELECT. %FOUND: it checks whether cursor has fetched any row. If yes – TRUE. %NOTFOUND: it checks whether cursor has fetched any row. If no – TRUE.
What are the advantages of stored procedure?
Modularity, extensibility, reusability, Maintainability and one time compilation.