Function is a subprogram written to perform certain computations and return a single value.
What are the modes for passing parameters to Oracle?
There are three modes for passing parameters to subprograms
IN - An In-parameter lets you pass values to the subprogram being called. In the subprogram it acts like a constant and cannot be assigned a value.
OUT – An out-parameter lets you return values to the caller of the subprogram. It acts like an initialized variable its value cannot be assigned to another variable or to itself.
INOUT – An in-out parameter lets you pass initial values to the subprogram being called and returns updated values to the caller.
What is the difference between Truncate and Delete Statement?
Truncate – Data truncated by using truncate statement is lost permanently and cannot be retrieved even by rollback. Truncate command does not use rollback segment during its execution, hence it is fast.
Delete – Data deleted by using the delete statement can be retrieved back by Rollback. Delete statement does not free up the table object allocated space.
What are Exceptions? How many types of Exceptions are there?
Exceptions are conditions that cause the termination of a block. There are two types of exceptions
Pre-Defined – Predefined by PL/SQL and are associated with specific error codes.
User-Defined – Declared by the users and are rose on deliberate request. (Breaking a condition etc.)
Exception handlers are used to handle the exceptions that are raised. They prevent exceptions from propagating out of the block and define actions to be performed when exception is raised.
What is a Pragma Exception_Init? Explain its usage?
A)Pragma Exception_Init is used to handle undefined exceptions. It issues a directive to the compiler asking it to associate an exception to the oracle error. There by displaying a specific error message pertaining to the error occurred.
Pragma Exception_Init (exception_name, oracle_error_name).