Oracle Interview Questions Part 3

Q21: Differentiate between function and procedure.

A21: A function is used to return a single value whereas a procedure doesn’t return any value. It returns multiple variables. This is achieved by passing variables by reference through OUT parameter.

Q22: Can there be more than one function with similar name in a PL/SQL block?

A22: Yes.

Q23: Can there be more than one stored functions with similar name?

A23: Yes.

Q24: Can a stored function be called in the constraint of a table?

A24: No.

Q25: Give the different types of parameter modes in a procedure.

A25: INOUT, OUT and IN.

Q26: Explain over loading.

A26: Over loading happens when an object is performing various functions based on the number or data types of the parameters passed through it.

Q27: Can functions be over loaded?

A27: Yes. Functions can be overloaded.

Q28: If two functions having same name and input parameters return different data type?

A28: No

Q29: Give the constructs of a package, function or a procedure.

A29: The constructs for a package, function or a procedure are:

a)      Exceptions,

b)      Cursors

c)      Variables and constants.

Q30: Why do you create or replace procedures rather that drop and recreate.

A30: In order to prevent Grants from getting dropped we create and replace procedures rather than drop and recreate.