Most Frequently asked Oracle database SQL questions and answers
Are you preparing for PL/SQL Interviews? If yes, then we bring you here a list of commonly asked important PL/SQL interview questions.
This article covers a list of all the most important and commonly asked PL/SQL interview questions and answers, which will help you to crack any interview.
Exceptions are inevitable... Handle them
Errors that occur during compilation can be fixed on the fly, but that does not resolve or guarantee bug free code. You might have run-time errors known as exceptions which are situation based. Handle exceptions properly in your code to minimize the chances of code break.
Strengthen your exception handling concepts by going through our QA below.
Most Popular PL/SQL Interview Questions and Answers
Q1 : What are Exceptions? How many types of Exceptions are there?
Ans: In PL/SQL, a warning or error condition is called an exception. Exceptions can be internally defined (by the run-time system) or user defined. Exceptions are conditions that cause the termination of a block.
There are two types of exceptions
Pre-Defined – Predefined by PL/SQL and are associated with specific error codes.
User-Defined – Declared by the users and are rose on deliberate request. (Breaking a condition etc.)
Exception handlers are used to handle the exceptions that are raised. They prevent exceptions from propagating out of the block and define actions to be performed when exception is raised.
Q2 : What is basic syntax of Oracle exception?
Ans:
DECLARE
<variable declaration>;
.
.
BEGIN
<execution block>;
.
.
EXCEPTION
WHEN NO_DATA_FOUND THEN
<exception handling>;
.
.
WHEN OTHERS THEN
<exception handling>;
END;
Q3 : How exception is different from error?
Ans: When an ERROR occurs, an EXCEPTION is raised. Error is a bug whereas exception is a warning or an error condition.
Exceptions are handled, but errors are not.
Q4 : List some Pre-defined Oracle exceptions?
Ans: Below is the list of some very commonly occurred Oracle pre-defined exceptions.
NO_DATA_FOUND: A SELECT INTO statement returns no rows.DUP_VAL_ON_INDEX: Your program attempts to store duplicate values in a database column that is constrained by a unique index.TOO_MANY_ROWS: A SELECT INTO statement returns more than one row.VALUE_ERROR: Raised if the conversion of a character string into a number fails. (In SQL statements, INVALID_NUMBER is raised.)INVALID_NUMBER: In a SQL statement, the conversion of a character string into a number fails because the string does not represent a valid number. (In procedural statements, VALUE_ERROR is raised.)CASE_NOT_FOUND: None of the choices in the WHEN clauses of a CASE statement is met, and there is no ELSE clause.CURSOR_ALREADY_OPEN: Your program attempts to open an already open cursor.INVALID_CURSOR: Your program attempts an illegal cursor operation such as closing an unopened cursor.ZERO_DIVIDE: Your program attempts to divide a number by zero.
Q5 : What is NO_DATA_FOUND exception?
Ans: A SELECT INTO statement returns no rows. SQL aggregate functions such as AVG and SUM always return a value or a null. So, a SELECT INTO statement that calls an aggregate function never raises NO_DATA_FOUND. The FETCH statement is expected to return no rows eventually, so when that happens, no exception is raised.
Q6 : What is the difference between VALUE_ERROR and INVALID_NUMBER exception?
Ans: ORA-6502: value error is a run-time error.
ORA-01722: invalid number is a compile error.
–Illustration for Value Error Exception
DECLARE
n NUMBER;
BEGIN
SELECT 'a' -–Implicit conversion
INTO n
FROM DUAL;
EXCEPTION
WHEN INVALID_NUMBER THEN
DBMS_OUTPUT.PUT_LINE('Invalid Number.');
WHEN VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('Value error.');
END;
/
Value error.
PL/SQL procedure successfully completed.
–Illustration for Invalid Number Exception
DECLARE
n NUMBER;
BEGIN
SELECT TO_NUMBER('a') -–Explicit conversion
INTO n
FROM DUAL;
EXCEPTION
WHEN INVALID_NUMBER THEN
DBMS_OUTPUT.PUT_LINE('Invalid Number.');
WHEN VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('Value error.');
END;
/
Invalid Number.
PL/SQL procedure successfully completed.
Q7 : What are the exceptions associated with Cursors?
Ans:
INVALID_CURSOR: Your program attempts an illegal cursor operation such as closing an unopened cursor.
CURSOR_ALREADY_OPEN: Your program attempts to open an already open cursor. A cursor must be closed before it can be reopened. A cursor FOR loop automatically opens the cursor to which it refers. So, your program cannot open FOR loop cursors.
ROWTYPE_MISMATCH: The host cursor variable and PL/SQL cursor variable involved in an assignment have incompatible return types. For example, when an open host cursor variable is passed to a stored subprogram, the return types of the actual and formal parameters must be compatible
Q8 : What is the difference between SQL%NOTFOUND and NO_DATA_FOUND?
Ans: NO_DATA_FOUND exception is fired only in SELECT statements. But SQL%NOTFOUND is fired in DELETE or UPDATE.
Q9 : What are the exceptions associated with BULK COLLECT?
Ans:
INVALID_NUMBER: In a SQL statement, the conversion of a character string into a number fails because the string does not represent a valid number. (In procedural statements, VALUE_ERROR is raised.) This exception is also raised when the LIMIT-clause expression in a bulk FETCH statement does not evaluate to a positive number.
Q10 : What is PRAGMA EXCEPTION_INIT? Explain its usage?
Ans: An error is raised implicitly whenever your PL/SQL program violates an Oracle rule or exceeds a system-dependent limit. Every Oracle error has a number, but exceptions must be handled by name. So, PL/SQL predefines some common Oracle errors as exceptions which are called predefined exceptions.
All other errors are undefined and Oracle provides the pragma EXCEPTION_INIT to associate an exception name with Oracle error codes.
It is a directive to the compiler asking to associate a user provided exception name to the oracle error. There by displaying a specific error message pertaining to the error occurred. We can intercept any pre-defined ORA- error and write a specific handler for it, instead of using the OTHERS handler.
Syntax:
PRAGMA EXCEPTION_INIT (exception_name, oracle_error_name)
Q11 : What is user-defined exception?
Ans: PL/SQL lets you define exceptions of your own. Unlike predefined exceptions, user-defined exceptions must be declared and must be raised explicitly by RAISE statements. Exceptions can be declared only in the declarative part of a PL/SQL block, subprogram, or package.
DECLARE
<variable declaration>;
your_userdefined_exc EXCEPTION;
BEGIN
<execution block>;
.
.
RAISE your_userdefined_exc;
.
.
EXCEPTION
WHEN NO_DATA_FOUND THEN
<exception handling>;
WHEN your_userdefined_exc THEN
<exception handling>;
.
.
WHEN OTHERS THEN
<exception handling>;
END;
Q12 : What is RAISE and RAISE_APPLICATION_ERROR?
Ans: RAISE statement is used to raise a user defined exception. RAISE can also raise predefined exceptions.
A RAISE_APPLICATION_ERROR is a procedure belonging to DBMS_STANDARD package. It allows to display a user defined error message from a stored subprogram.
Q13 : What does SQLCODE return for the following-
- When Procedure/Function is executed successfully
- When used with any user defined exception
- No data found
- For any oracle error or predefined exception
Ans: SQLCODE always returns the Oracle error code (-ve error) associated with the exception.
When Procedure/Function is executed successfully – 0
When used with any user defined exception – +1
No data found – +100
For any oracle error or predefined exception – Error number
Q14 : What are the different exceptions associated with Collection?
Ans:
COLLECTION_IS_NULL : Attempts to apply collection methods other than EXISTS to an uninitialized (atomically null) nested table or varray, or the program attempts to assign values to the elements of an uninitialized nested table or varray.
NO_DATA_FOUND : Attempts to access data for a non-existing or deleted subscript in a collection.
SUBSCRIPT_BEYOND_COUNT : References a nested table or varray element using an index number larger than the number of elements in the collection.
SUBSCRIPT_OUTSIDE_LIMIT : References a nested table or varray element using an index number (-1 for example) that is outside the legal range.
VALUE_ERROR
Q15 : What is SQLCODE and where it is used?
Ans: The function SQLCODE returns the number code of the most recent exception. For internal exceptions, SQLCODE returns the number of the associated Oracle error. The number that SQLCODE returns is negative unless the Oracle error is no data found, in which case SQLCODE returns +100.
Uses of SQLCODE:
SQLCODEis only useful in an exception handler. Outside a handler,SQLCODEalways returns 0.SQLCODEis especially useful in theOTHERSexception handler, because it lets you identify which internal exception was raised.- You can assign the
SQLCODEvalue to a local variable within a PL/SQL block. However you can not useSQLCODEdirectly in a SQL statement.
Q16 : What is SQLERRM and where it is used?
Ans: The function SQLERRM returns the error message associated with its error-number argument. If the argument is omitted, it returns the error message associated with the current value of SQLCODE. SQLERRM with no argument is useful only in an exception handler. Outside a handler, SQLERRM with no argument always returns the normal, successful completion message. For internal exceptions, SQLERRM returns the message associated with the Oracle error that occurred. The message begins with the Oracle error code.
Uses of SQLERRM:
- SQLERRM is especially useful in the OTHERS exception handler, where it lets you identify which internal exception was raised.
- The error number passed to SQLERRM should be negative. Passing a zero to SQLERRM always returns the ORA-0000: normal, successful completion message. Passing a positive number to SQLERRM always returns the User-Defined Exception message unless you pass +100, in which case SQLERRM returns the ORA-01403: no data found message.
- You cannot use SQLERRM directly in a SQL statement. Assign the value of SQLERRM to a local variable first.
Q17 : What is FORMAT_ERROR_BACKTRACE and where it is used?
Ans:FORMAT_ERROR_BACKTRACE procedure displays the call stack at the point where an exception was raised, even if the procedure is called from an exception handler in an outer scope.
To find the exact position where the exception has occurred is always a tough job for the programmers to identify. To rectify this issue, a procedure FORMAT_ERROR_BACKTRACE in the DBMS_UTILITY package has been introduced in the Oracle version 10g. This procedure back traces the exception by propagating through the nested programs to bring in the exact route of the exception propagation.
In the below example, the predefined exception NO_DATA_FOUND has been explicitly raised to capture the error code using both SQLCODE and the back trace procedures.
BEGIN
raise no_data_found;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Backtrace => '||dbms_utility.format_error_backtrace);
dbms_output.put_line('SQLCODE => '||SQLCODE);
END;
Q18 : What is FORMAT_ERROR_STACK and where it is used?
Ans: FORMAT_ERROR_STACK function formats the current error stack. This can be used in exception handlers to look at the full error stack. This returns the error stack, up to 2000 bytes.
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE is available to get an exception’s stack trace, i.e. files and lines that lead up to the exception. When combined with DBMS_UTILITY.FORMAT_ERROR_STACK, which contains the exception error code and message, developers are able quickly identify defects.