11. What are the rules to be applied to NULLs whilst doing comparisons?
1) NULL is never TRUE or FALSE
2) NULL cannot be equal or unequal to other values
3) If a value in an expression is NULL, then the expression itself evaluates to NULL except for concatenation operator (||)
12. How is a process of PL/SQL compiled?
Compilation process includes syntax check, bind and p-code generation processes.
Syntax checking checks the PL/SQL codes for compilation errors. When all errors are corrected, a storage address is assigned to the variables that hold data. It is called Binding. P-code is a list of instructions for the PL/SQL engine. P-code is stored in the database for named blocks and is used the next time it is executed.
13. Differentiate between Syntax and runtime errors.
A syntax error can be easily detected by a PL/SQL compiler. For eg, incorrect spelling.
A runtime error is handled with the help of exception-handling section in an PL/SQL block. For eg, SELECT INTO statement, which does not return any rows.
14. Explain Commit, Rollback and Savepoint.
For a COMMIT statement, the following is true:
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.
A ROLLBACK statement gets issued when the transaction ends, and the following is true.
The work done in a transition is undone as if it was never issued.
All locks acquired by transaction are released.
It undoes all the work done by the user in a transaction. With SAVEPOINT, only part of transaction can be undone.
15. Define Implicit and Explicit Cursors.
A cursor is implicit by default. The user cannot control or process the information in this cursor.
If a query returns multiple rows of data, the program defines an explicit cursor. This allows the application to process each row sequentially as the cursor returns it.
16. Explain mutating table error.
It occurs when a trigger tries to update a row that it is currently using. It is fixed by using views or temporary tables, so database selects one and updates the other.
17. When is a declare statement required?
DECLARE statement is used by PL/SQL anonymous blocks such as with stand alone, non-stored procedures. If it is used, it must come first in a stand alone file.
18. How many triggers can be applied to a table?
A maximum of 12 triggers can be applied to one table.
19. What is the importance of SQLCODE and SQLERRM?
SQLCODE returns the value of the number of error for the last encountered error whereas SQLERRM returns the message for the last error.
20. If a cursor is open, how can we find in a PL/SQL Block?
the %ISOPEN cursor status variable can be used.
