Oracle DBA Interview Question and Answers

21. What do you understand by PL/SQL collections?
  PL/SQL collections are the composite types. These are group of elements, which can be manipulated individually, and are similar to arrays in C. There are three types of collections in PL/SQL given as follows:
  • Index by tables or associative arrays – Contains elements of same datatype indexed by an integer. The number of elements in associative arrays is not limited.
  • Nested tables – Refer to the tables that can be used in SQL statements and bulk insert operation.
  • Variable arrays – Refer to the tables that can be used in SQL statements and stored in database tables, such as nested tables. However, they can hold a limited number of elements.
 
Your Name Your Email-ID
Your Answer
22. What is conditional compilation?
 
  • Conditional compilation allows you to compile code conditionally based on compiler directives.
  • It is helpful when you need to have different code based on certain conditions.
 
Your Name Your Email-ID
Your Answer
23. Explain the use of table functions?
 
  • Table function is PL/SQL code that performs functions on PL/SQL table to return a PL/SQL table.
  • Oracle 9i has added a feature that allows these functions to be used as a regular table. The only requirement to use table functions is to use a keyword TABLE.
 
Your Name Your Email-ID
Your Answer
24. Explain the difference between function, procedure and package?
 
  • A PL/SQL function or procedures is used to carry out a single task.
  • Although there is no restriction, a procedure is generally used to carry out an operation while a function is used to apply a function on one or many columns to return a value.
  • A package is a collection of procedures or functions. Even though there is no restriction on grouping different though there is no restriction on grouping different procedures or functions, generally they are grouped based on the modular approach used in application.
 
Your Name Your Email-ID
Your Answer
12345678910 Page 7 of 10