Friday, April 30, 2010
Q: What are the various types of Exceptions?
A: User defined and Predefined Exceptions.
Q: Can we define exceptions twice in same block?
Q: What is the difference between a procedure and a function?
A: Functions return a single variable by value whereas procedures do not return any variable by value. Rather they return multiple variables by passing variables by reference through their OUT parameter.
Q: Can you have two functions with the same name in a PL/SQL block?
Q: Can you have two stored functions with the same name?
Q: Can you call a stored function in the constraint of a table?
Q: What are the various types of parameter modes in a procedure?
A: IN, OUT AND INOUT.
Q: What is Over Loading and what are its restrictions?
A: OverLoading means an object performing different functions depending upon the no. of parameters or the data type of the parameters passed to it.
Q: Can functions be overloaded?
Q: Can 2 functions have same name & input parameters but differ only by return datatype?
Q: What are the constructs of a procedure, function or a package?
A: The constructs of a procedure, function or a package are:
· variables and constants
Q: Why Create or Replace and not Drop and recreate procedures?
A: So that Grants are not dropped.
Q: Can you pass parameters in packages? How?
A: Yes. You can pass parameters to procedures or functions in a package.
Q: What are the parts of a database trigger?
A: The parts of a trigger are:
· A triggering event or statement
· A trigger restriction
· A trigger action
Q: What are the various types of database triggers?
A: There are 12 types of triggers, they are combination of:
· Insert, Delete and Update Triggers.
· Before and After Triggers.
· Row and Statement Triggers.
Q: What is the advantage of a stored procedure over a database trigger?
A: We have control over the firing of a stored procedure but we have no control over the firing of a trigger.
Q: What is the maximum no. of statements that can be specified in a trigger statement?
Q: Can views be specified in a trigger statement?
Q: What are the values of :new and :old in Insert/Delete/Update Triggers?
A: INSERT : new = new value, old = NULL
DELETE : new = NULL, old = old value
UPDATE : new = new value, old = old value
Q: What are cascading triggers? What is the maximum no of cascading triggers at a time?
A: When a statement in a trigger body causes another trigger to be fired, the triggers are said to be cascading. Max = 32.