10:29
plsql Interview Questions and Answers
1. What is an exception?
a An exception is an identifier, which defines an error condition within the Procedural Language/Structured Query Language (PL/SQL) code. When an error occurs, it is trapped and the program control unconditionally branches to the exception section of the PL/SQL block.
2. How is an exception raised and handled?
When a runtime error occurs, an exception is raised. The exception is handled when this error is successfully handled by the PL/SQL code and is not allowed to propagate to the outward environment. Compilation error cannot be handled within the program; only runtime errors can be handled within the PL/SQL code.
An exception can be raised:
By a runtime Oracle error
By the RAISE statement
By calling the RAISE_APPLICATION_ERROR procedure
After an exception is raised, the control is passed to the exception section, where the code is written about how to handle the error that has occurred. The control cannot pass back to the executable section of the same block after the exception is handled.
3. When is an exception propagated to the outside environment?
An exception is raised within the program and if there is no corresponding exception handler, the PL/SQL block terminates unsuccessfully and the exception is propagated to the calling environment.
4. Give the block structure of an exception section within the PL/SQL code.
The structure of an exception section within the PL/SQL code is
given as follows:
DECLARE
PL/SQL code .....;
BEGIN
PL/SQL code .....;
EXCEPTION
WHEN exception1 then
PL/SQL code .....;
WHEN exception2 then
PL/SQL code .....;
WHEN OTHERS THEN
PL/SQL code .....;
END;
5. What are the different types of exceptions?
The three types of exceptions that can be handled at runtime are as follows:
a Predefined Oracle error —Refers to an undeclared error, which is raised by the Oracle server implicitly Non-predefined Oracle error—Refers to an error declared in the declarative section of the code that is raised by the Oracle server implicitly User-defined error —Refers to an error declared in the declarative section of the code that is raised explicitly.
6. Describe the OTHERS exception handler.
The exception section handles only those errors that are specified; however, other errors that are not specified cannot be handled. To handle these errors explicitly, the OTHERS exception handler is used that is always defined as the last exception handler.
7. Can the PL/SQL block process more than one exception at a time?
No, the PL/SQL block cannot handle more than one exception. It can process only one exception handler before terminating.
8. Is it possible to have more than one OTHERS clause?
No, it is not possible to have more than one OTHERS clause in the exception section.
9. Give a few pre-defined Oracle errors.
Pre-defined Oracle errors are as follows:
? NO_DATA_FOUND —Refers to an error when a SELECT statement does not return any result set
? DUP_VAL_ON_INDEX —Refers to an error when a program tries to store duplicate values in an indexed column
? TOO_MANY_ROWS —Refers to an error when a SELECT INTO statement returns more than one row
? VALUE_ERROR —Refers to an error when an arithmetic, conversion, truncation, or size-constraints error occurs
? ROWTYPE_MISMATCH —Refers to an error when the actual and formal parameters do not have the same datatypes
10. What is the advantage of OTHERS exception handler?
The OTHERS exception handler ensures that no exception goes unhandled and the program terminates successfully.
12. Describe the SQLCODE and SQLERRM functions.
The SQLCODE function returns the Oracle error number for internal exceptions. This error number can be passed to the SQLERRM function to get the message associated with the error number. The SQLCODE function returns the value +1; whereas, the SQLERRM function returns the User-Defined Exception message for the user-defined exceptions.
15. What is RAISE.APPLICATIONJERROR?
The RAISE_APPLICATION_ERROR is a built-in procedure and part of the DBMS_STANDARD package. It is used to raise an exception and assign an error number and custom message to the user-defined errors in the stored programs. It is also used to report errors to the calling application and helps to avoid unhandled exceptions. Following is the syntax of the RAISEJ\PPLICATION_ERROR procedure:
RAISE_APPLICATION_ERROR (error_number, message) In the preceding syntax, error_number is a negative integer in the range of 20999 to 20000, and message is a character string up to 2048 bytes long.
16. What happens to the program control when the RAISE_APPLICATION_ERROR procedure is encountered?
When the RAISE_APPLICATION_ERROR procedure is encountered, it ends the program unit and returns the error number and message to the application. The error number and message can be trapped like any Oracle error.
17. What happens after a sub-block handles an exception?
When a sub-block handles an exception, the control passes to the next statement in the executable section of the enclosing block immediately after the END statement of the sub-block.
18.Can RAISE.APPLICATION.ERROR be called in the executable section of the program unit?
Yes, the RAISE_APPLICATION_ERROR procedure can be called in the executable section as well as the exception section of the PL/SQL code.
19. What happens if an exception is not handled in a sub-block?
If an exception is not handled in a sub-block, the exception propagates to the enclosing blocks until the associated handler is found. If none of the blocks has a handier for the exception, an unhandled exception error is passed back to the host environment as follows:
ORA-06510: PL/SQL: unhandled user-defined exception
20. Can processing be resumed from the point exception was raised after the exception is handled?
After the exception is handled, processing cannot be resumed within the executable section of the current block from where the exception was raised. The current block where the exception handier is declared will be terminated. The control will pass to the statement in the enclosing executable block. If there is no enclosing block, control will pass back to the calling host environment.
21. How can a transaction be retried after an exception?
a The following method can be used to retry a transaction after an exception is raised:
The transaction should be written within a sub-block a The sub-block can be placed inside a loop that repeats the transaction Mark a savepoint before starting the transaction. If the transaction succeeds, then commit and exit from the loop. If the transaction fails, the control is transferred to the exception handler where the transaction is rolled back to the savepoint to undo the changes, and then try to fix the problem.
24. What causes the INVALID_CURSOR exception?
This exception is raised when the program attempts to perform an illegal operation, such as closing an unopened cursor.
26. Where will the control be transferred if an exception is raised within an exception section of the block?
When an exception is raised within an exception section of a block, the control is transferred to the exception section of the enclosing block.
27. When is the CURSOR_ALREADY_OPEN exception raised?
This exception is raised when the program tries to open an already opened cursor. The cursor should be closed before it can be opened again.
29. When is the ROWJTYPE.MISMATCH exception raised?
The ROW__TYPE_MISMATCH exception is raised when a host cursor variable and a PL/SQL cursor variable involved in an assignment have incompatible return types.
30. What is the advantage of having a separate exception section within a PL/SQL code?
The advantages of having a separate exception section within a PL/SQL code are as follows:
It is easier to understand the program logic as it is easily visible and readable
Any error in any statement will be detected and handled easily
32. What happen when there is no handler for a raised exception?
If an exception is raised and there is no handler in the current block, the exception would propagate to the enclosing block. If no handler is found in the enclosing block, the exception will be propagated to the calling environment as an unhandled exception.
33. What is the difference between user-defined exceptions and Oracle-defined exceptions?
User-defined exceptions are explicitly raised; whereas, Oracle-defined exceptions are raised implicitly.
34. What is the difference between RAISE and RAISE_APPLICATION_ERROR?
RAISE is used to call pre-defined exceptions declared in the declarative section of the PL/SQL block.
RAISE_APPLICATION.„ERROR is used to call user-defined ORA - error messages from stored subprograms.
35. What is the meaning of PRAGMA keyword?
The PRAGMA keyword signifies that the statement is a compiler directive, which is not processed when the PL/SQL is executed. It is a pseudo-instruction that tells the compiler to interpret all the occurrences of exception name within the block with the associated Oracle server number.
a An exception is an identifier, which defines an error condition within the Procedural Language/Structured Query Language (PL/SQL) code. When an error occurs, it is trapped and the program control unconditionally branches to the exception section of the PL/SQL block.
2. How is an exception raised and handled?
When a runtime error occurs, an exception is raised. The exception is handled when this error is successfully handled by the PL/SQL code and is not allowed to propagate to the outward environment. Compilation error cannot be handled within the program; only runtime errors can be handled within the PL/SQL code.
An exception can be raised:
By a runtime Oracle error
By the RAISE statement
By calling the RAISE_APPLICATION_ERROR procedure
After an exception is raised, the control is passed to the exception section, where the code is written about how to handle the error that has occurred. The control cannot pass back to the executable section of the same block after the exception is handled.
3. When is an exception propagated to the outside environment?
An exception is raised within the program and if there is no corresponding exception handler, the PL/SQL block terminates unsuccessfully and the exception is propagated to the calling environment.
4. Give the block structure of an exception section within the PL/SQL code.
The structure of an exception section within the PL/SQL code is
given as follows:
DECLARE
PL/SQL code .....;
BEGIN
PL/SQL code .....;
EXCEPTION
WHEN exception1 then
PL/SQL code .....;
WHEN exception2 then
PL/SQL code .....;
WHEN OTHERS THEN
PL/SQL code .....;
END;
5. What are the different types of exceptions?
The three types of exceptions that can be handled at runtime are as follows:
a Predefined Oracle error —Refers to an undeclared error, which is raised by the Oracle server implicitly Non-predefined Oracle error—Refers to an error declared in the declarative section of the code that is raised by the Oracle server implicitly User-defined error —Refers to an error declared in the declarative section of the code that is raised explicitly.
6. Describe the OTHERS exception handler.
The exception section handles only those errors that are specified; however, other errors that are not specified cannot be handled. To handle these errors explicitly, the OTHERS exception handler is used that is always defined as the last exception handler.
7. Can the PL/SQL block process more than one exception at a time?
No, the PL/SQL block cannot handle more than one exception. It can process only one exception handler before terminating.
8. Is it possible to have more than one OTHERS clause?
No, it is not possible to have more than one OTHERS clause in the exception section.
9. Give a few pre-defined Oracle errors.
Pre-defined Oracle errors are as follows:
? NO_DATA_FOUND —Refers to an error when a SELECT statement does not return any result set
? DUP_VAL_ON_INDEX —Refers to an error when a program tries to store duplicate values in an indexed column
? TOO_MANY_ROWS —Refers to an error when a SELECT INTO statement returns more than one row
? VALUE_ERROR —Refers to an error when an arithmetic, conversion, truncation, or size-constraints error occurs
? ROWTYPE_MISMATCH —Refers to an error when the actual and formal parameters do not have the same datatypes
10. What is the advantage of OTHERS exception handler?
The OTHERS exception handler ensures that no exception goes unhandled and the program terminates successfully.
12. Describe the SQLCODE and SQLERRM functions.
The SQLCODE function returns the Oracle error number for internal exceptions. This error number can be passed to the SQLERRM function to get the message associated with the error number. The SQLCODE function returns the value +1; whereas, the SQLERRM function returns the User-Defined Exception message for the user-defined exceptions.
15. What is RAISE.APPLICATIONJERROR?
The RAISE_APPLICATION_ERROR is a built-in procedure and part of the DBMS_STANDARD package. It is used to raise an exception and assign an error number and custom message to the user-defined errors in the stored programs. It is also used to report errors to the calling application and helps to avoid unhandled exceptions. Following is the syntax of the RAISEJ\PPLICATION_ERROR procedure:
RAISE_APPLICATION_ERROR (error_number, message) In the preceding syntax, error_number is a negative integer in the range of 20999 to 20000, and message is a character string up to 2048 bytes long.
16. What happens to the program control when the RAISE_APPLICATION_ERROR procedure is encountered?
When the RAISE_APPLICATION_ERROR procedure is encountered, it ends the program unit and returns the error number and message to the application. The error number and message can be trapped like any Oracle error.
17. What happens after a sub-block handles an exception?
When a sub-block handles an exception, the control passes to the next statement in the executable section of the enclosing block immediately after the END statement of the sub-block.
18.Can RAISE.APPLICATION.ERROR be called in the executable section of the program unit?
Yes, the RAISE_APPLICATION_ERROR procedure can be called in the executable section as well as the exception section of the PL/SQL code.
19. What happens if an exception is not handled in a sub-block?
If an exception is not handled in a sub-block, the exception propagates to the enclosing blocks until the associated handler is found. If none of the blocks has a handier for the exception, an unhandled exception error is passed back to the host environment as follows:
ORA-06510: PL/SQL: unhandled user-defined exception
20. Can processing be resumed from the point exception was raised after the exception is handled?
After the exception is handled, processing cannot be resumed within the executable section of the current block from where the exception was raised. The current block where the exception handier is declared will be terminated. The control will pass to the statement in the enclosing executable block. If there is no enclosing block, control will pass back to the calling host environment.
21. How can a transaction be retried after an exception?
a The following method can be used to retry a transaction after an exception is raised:
The transaction should be written within a sub-block a The sub-block can be placed inside a loop that repeats the transaction Mark a savepoint before starting the transaction. If the transaction succeeds, then commit and exit from the loop. If the transaction fails, the control is transferred to the exception handler where the transaction is rolled back to the savepoint to undo the changes, and then try to fix the problem.
24. What causes the INVALID_CURSOR exception?
This exception is raised when the program attempts to perform an illegal operation, such as closing an unopened cursor.
26. Where will the control be transferred if an exception is raised within an exception section of the block?
When an exception is raised within an exception section of a block, the control is transferred to the exception section of the enclosing block.
27. When is the CURSOR_ALREADY_OPEN exception raised?
This exception is raised when the program tries to open an already opened cursor. The cursor should be closed before it can be opened again.
29. When is the ROWJTYPE.MISMATCH exception raised?
The ROW__TYPE_MISMATCH exception is raised when a host cursor variable and a PL/SQL cursor variable involved in an assignment have incompatible return types.
30. What is the advantage of having a separate exception section within a PL/SQL code?
The advantages of having a separate exception section within a PL/SQL code are as follows:
It is easier to understand the program logic as it is easily visible and readable
Any error in any statement will be detected and handled easily
32. What happen when there is no handler for a raised exception?
If an exception is raised and there is no handler in the current block, the exception would propagate to the enclosing block. If no handler is found in the enclosing block, the exception will be propagated to the calling environment as an unhandled exception.
33. What is the difference between user-defined exceptions and Oracle-defined exceptions?
User-defined exceptions are explicitly raised; whereas, Oracle-defined exceptions are raised implicitly.
34. What is the difference between RAISE and RAISE_APPLICATION_ERROR?
RAISE is used to call pre-defined exceptions declared in the declarative section of the PL/SQL block.
RAISE_APPLICATION.„ERROR is used to call user-defined ORA - error messages from stored subprograms.
35. What is the meaning of PRAGMA keyword?
The PRAGMA keyword signifies that the statement is a compiler directive, which is not processed when the PL/SQL is executed. It is a pseudo-instruction that tells the compiler to interpret all the occurrences of exception name within the block with the associated Oracle server number.
Posted in:
PL SQL Interview Questions And Answers
7 comments:
Very good and useful collection of question and answers thank you for sharing this article with us. Know more about PLSQL Online Training
no option to select
Nice questions and good descriptions about the SQL queries.
oracle sql interview questions for java developers
thank you so much, it was very easy learning.
Hi There,
Jeez oh man,while I applaud for your writing , it’s just so damn straight to the point #topic.
I have a requirement as below
I have a table with 3 columns
Col1 Col2 Col3 lowrange highrange
BRID A 1 1 3
BRID A 3 1 3
BRID C 1 1 5
BRID C 2 1 5
BRID C 3 1 5
BRID C 5 1 5
BRID B 1 1 7
BRID B 2 1 7
BRID B 3 1 7
BRID B 5 1 7
BRID B 7 1 7
We need to insert the missing number in col3 between lowrange and highrange.
I have managed to insert the missing numbers.
However, if we need to insert exactly between the missed place.
I would not be able to use order by as Col2 is not maintained in any order.
The output should be as below
Col1 Col2 Col3
BRID A 1
BRID A 2
BRID A 3
BRID C 1
BRID C 2
BRID C 3
BRID C 4
BRID C 5
BRID B 1
BRID B 2
BRID B 3
BRID B 4
BRID B 5
BRID B 6
BRID B 7
Kindly let me now any logic to achieve the same.
THANK YOU!! This saved my butt today, I’m immensely grateful.
Many Thanks,
Preethi
Hi There,
So bloody thorough! Ah! So happy and blissed out! I feel redeemed by reading out 35-top-plsql-error-handling-interview.html. Keep up the good work!
I got a list of the INDEXES from DBA Team which are non value added to the Database with the following conditions
Quote:
1:Low selectivity indexes.
2:The number of distinct key values is less than 10 and the number of rows in table in greater than 100000.
3:All these normal(Balances -Tree )indexes.
They are asking the App Team to review these and to drop those indexes.
But when I analyzed those indexes some of the columns are using in the where conditions of the so many queries.
After applying the HINT of those indexes also the cost of query was decreasing.
Here my confusion was,if we really drop those indexes, will it increase the performance?
or
Do we need to convert those as BITMAP as they have low cardinality ?
Please help me to understand the concept behind this .
Thank you very much and will look for more postings from you.
Thanks and Regards,
Preethi.
SQL> with x as
2 (select distinct col1,col2,substr(range,3,3)occurance from brida)
3 select col1,col2,row_number()over(partition by col1,col2 order by 1)col3 from x a, (select rownum r from dual connect by level<=
4 (select max(occurance) from x))
5 where r<=occurance
6 order by occurance,3
7 /
COL1 CO COL3
---------- -- ----------
brida A 1
brida A 2
brida A 3
brida C 1
brida C 2
brida C 3
brida C 4
brida C 5
brida B 1
brida B 2
brida B 3
brida B 4
brida B 5
brida B 6
brida B 7
Post a Comment