PLSQl Interview Questions Part 2

  1. What are the various types of parameter modes in a procedure ?
  1. 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.
  1. Can functions be overloaded ?
Answer: Yes.
  1. 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.
  1. What are mutating triggers ?
Answer: A trigger giving a SELECT on the table on which the trigger is written.
  1. What are constraining triggers ?
Answer: A trigger giving an Insert/Updat e on a table having referential integrity constraint on the triggering table.
  1. Describe Oracle database's physical and logical structure ?
    • Physical : Data files, Redo Log files, Control file.
    • Logical : Tables, Views, Tablespaces, etc.
  1. Can you increase the size of a tablespace ? How ?
Answer: Yes, by adding datafiles to it.
  1. Can you increase the size of datafiles ? How ?
Answer: No (for Oracle 7.0)
Yes (for Oracle 7.3 by using the Resize clause )
  1. What is the use of Control files ?
Answer: Contains pointers to locations of various data files, redo log files, etc.
  1. 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
  1. What are the advantages of clusters ?
Answer: Access time reduced for joins.
  1. What are the disadvantages of clusters ?
Answer: The time for Insert increases.
  1. 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.

Post a Comment