PLSQl Interview Questions Part 2

21. What are the various types of parameter modes in a procedure ?

Answer: IN, OUT AND INOUT.

22. What is Over Loading and what are its restrictions ?

Answer: OverLoading means an object performing different functions depending upon the no.of parameters or the data type of the parameters passed to it.

23. Can functions be overloaded ?

Answer: Yes.

24. Can 2 functions have same name & input parameters but differ only by return datatype

Answer: No.

What are the constructs of a procedure, function or a package ?

Answer: The constructs of a procedure, function or a package are :

·       variables and constants

·       cursors

·       exceptions

·       Why Create or Replace and not Drop and recreate procedures ?

Answer: So that Grants are not dropped.

·       Can you pass parameters in packages ? How ?

Answer: Yes.You can pass parameters to procedures or functions in a package.

·       What are the parts of a database trigger ?

Answer: The parts of a trigger are:

·       A triggering event or statement

·       A trigger restriction

·       A trigger action

·       What are the various types of database triggers ?

Answer: There are 12 types of triggers, they are combination of :

·       Insert, Delete and Update Triggers.

·       Before and After Triggers.

·       Row and Statement Triggers.

·       What is the advantage of a stored procedure over a database trigger ?

Answer: We have control over the firing of a stored procedure but we have no control over the firing of a trigger.

·       What is the maximum no.of statements that can be specified in a trigger statement ?

Answer: One.

·       Can views be specified in a trigger statement ?

Answer: No

·       What are the values of :new and :old in Insert/Delete/Update Triggers ?

Answer: INSERT : new = new value, old = NULL
DELETE : new = NULL, old = old value
UPDATE : new = new value, old = old value 

·       What are cascading triggers? What is the maximum no of cascading triggers at a time?

Answer: When a statement in a trigger body causes another trigger to be fired, the triggers are said to be cascading.Max = 32.

41. What are mutating triggers ?

Answer: A trigger giving a SELECT on the table on which the trigger is written.

42. What are constraining triggers ?

Answer: A trigger giving an Insert/Updat e on a table having referential integrity constraint on the triggering table.

41. Describe Oracle database's physical and logical structure ?

Answer:

• Physical : Data files, Redo Log files, Control file.
• Logical : Tables, Views, Tablespaces, etc.
41. Can you increase the size of a tablespace ? How ?

Answer: Yes, by adding datafiles to it.

43. Can you increase the size of datafiles ? How ?

Answer: No (for Oracle 7.0)
Yes (for Oracle 7.3 by using the Resize clause )

44. What is the use of Control files ?

Answer: Contains pointers to locations of various data files, redo log files, etc.

45. What is the use of Data Dictionary ?

Answer: It Used by Oracle to store information about various physical and logical Oracle structures e.g.Tables, Tablespaces, datafiles, etc

46. What are the advantages of clusters ?

Answer: Access time reduced for joins.

47. What are the disadvantages of clusters ?

Answer: The time for Insert increases.

48. Can Long/Long RAW be clustered ?

Answer: No.

 
Can null keys be entered in cluster index, normal index ?

Answer: Yes.

Can Check constraint be used for self referential integrity ? How ?

Answer: Yes.In the CHECK condition for a column of a table, we can reference some other column of the same table and thus enforce self referential integrity.