PL/SQL Interview Questions and Answers Part 3

15. Explain how functions and procedures are called in a PL SQL block.

Answer:

Functions are called as a part of an expression.

Example:

amount:=calculate_amt()

Procedures are called as a statement.

Example:

calculate_amt();

16. What is the purpose of SQLCODE and SQLERRM?

Answer:

Both SQLCODE and SQLERRM are Oracle's built-in error reporting functions in PL/SQL.

SQLCODE returns the number of the last encountered error.

SQLERRM returns the message for the last error.

17. What is Sequence?

Answer:

A Sequence is an object to generate a number sequence. It can be used to create an autonumber field to generate primary key values. The problem is that the sequence number is lost if the transaction is rolled back.


The above code creates a sequence student_seq which can be used to provide student id number when rows are added to the student table.

18. What is Raise_application_error?

Answer:

Raise_application_error is a procedure of the package DBMS_STANDARD that allows issuing a user_defined error messages from the database trigger or stored sub-program.

19. Does PL/SQL support CREATE command?

Answer:

No. PL/SQL doesn't support the data definition commands like CREATE.

20. Write some predefined exceptions in PL/SQL?

Answer:

DUP_VAL_ON_INDEX
NO_DATA_FOUND
TOO_MANY_ROWS
CURSOR_ALREADY_OPEN
ZERO_DIVIDE
PROGRAM_ERROR
TIMEOUT _ON_RESOURCE
STORAGE_ERROR
INVALID_NUMBER
INVALID_CURSOR
LOGON_DENIED
VALUE_ERROR
etc.