Oracle Apps( EBS) Interview Questions and Answers Part 6
Oracle Apps( EBS) Interview Questions and Answers Part 6:--
Top 8 Questions asked ME in Oracle apps Technical Interview:---
1. How you will identify error records in bulk collect?
2. If error is there in one begin end block then will the program terminate the loop or it will skip to another line of code?
3. Sequence of report triggers in oracle?
4. alert usage?
5. How are user defined exceptions defined and called?
1. How you will identify error records in bulk collect?
Anwses: The error should be caught in the exception section, and you should then loop over the entries in SQL%BULK_EXCEPTIONS to determine which errors have taken place. After that, you can either record error information to a log table or attempt to recover the DML statement.
Let's review some key PL/SQL terms related to transactions, errors, and exceptions before digging into the FORALL's error-related features.
Each and every SQL statement is atomic, or all-or-nothing. Put another way, if your update statement finds 100 rows that require editing, and as it is changing the
If there is a mistake in the hundredth row, all the changes made in the previous 100 rows are undone. What does this mean, Forall? The first time the SQL engine encounters an issue while processing the DML statement that was sent to it by FORALL, it defaults to stopping and sending the error back to the PL/SQL engine.
Any claims that the FORALL successfully completes are still pending commitment or rollback, in addition to not processing any further.
Example:--
SET SERVEROUTPUT ON ;-- semicolon at the end of serveroutput on, does not affect the code in plsql
SET SERVEROUTPUT ON--or
BEGIN
dbms_output.put_line ('Tech Insights: Unraveling the World of IT');
END;
/
If, however, you want to keep on going, even if there is an SQL error for a particular set of bind variable values, you need to take advantage of the
SAVE EXCEPTIONS clause.
Add the SAVE EXCEPTIONS clause to the FORALL header if you want the PL/SQL engine to run as many DML statements as it can, even if problems are raised during the process.
The PL/SQL engine will then store that data in a pseudo-collection called if the SQL engine raises an error.
SQL%BULK_EXCEPTIONS, and carry on with the statement execution. Upon attempting every statement, PL/SQL raises an ORA-24381 error.
SQL%BULK_EXCEPTIONS is a collection of records, each of which has two fields:
ERROR_INDEX and ERROR_CODE.
ERROR_INDEX field contains a sequentially generated integer, incremented with each statement execution in the SQL engine.
The result that SQLCODE returned when the error happened was ERROR_CODE. Please take note that the error message is absent from this collection.
Final code with error capturing details:--
DECLARE
TYPE bulkcollect_t IS TABLE OF VARCHAR2(10 CHAR);
l_string bulkcollect_t := bulkcollect_t ('A','AAA','AA');
l_error_count NUMBER;
BEGIN
FORALL indx IN 1 .. l_string.COUNT SAVE EXCEPTIONS
INSERT INTO bulkcollect_test (c) VALUES (l_string (indx));
DBMS_OUTPUT.PUT_LINE ('Rows inserted BEFORE EXCEPTION : ' || SQL%ROWCOUNT);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('Rows inserted : ' || SQL%ROWCOUNT);
l_error_count := SQL%BULK_EXCEPTIONS.count;-- SQL%BULK_EXCEPTIONS used to count the number of error records while processing into a table
DBMS_OUTPUT.PUT_LINE('Number of failures: ' || l_error_count);
FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE (
'Error '
|| indx
|| ' occurred on index '
|| SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX
|| ' attempting to update name to "'
|| l_string (
SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX)
|| '"');
DBMS_OUTPUT.PUT_LINE (
'Oracle error is : '
|| SQLERRM(-SQL%BULK_EXCEPTIONS(indx).ERROR_CODE));
END LOOP;
ROLLBACK;
END;
/
DROP TABLE bulkcollect_test
/
2. If error is there in one begin end block then will the program terminate or it will skip to another line of code?
Solution: It will terminate the program.
2.1. If error is there in one begin end block and exception handler is there then will the program terminate or it will skip to another line of code?
Solution: The program control will got erorr then will be move to exception section and handles the exception there.
if there is no exception in plsql block then Unhandled exception Halts(terminates) the execution of the host program.
When an exception is not handled correctly by the application code, it results in an unhandled exception. For instance, it is frequently the case that a file you attempt to open on disk appears to be missing.
The declaration, executable, and exception-handling portions make up a PL/SQL block. The executable section of a block must exist.
The parts on exception-handling and declaration, however, are optional.
When an exception is triggered, it propagates if PL/SQL is unable to locate a handler for it in the current block or subprogram. In other words, until a handler is discovered or there are no more blocks to search, the exception repeats itself in subsequent enclosing blocks.
What does an unhandled exception do?
Unhandled exception Halts(terminates) the execution of the host program.
3. Oracle Apps R12 sequence report triggers? Oracle Report Builder Trigger Sequence of Fire Solution: The following is the order in which Oracle reports builder triggers are triggered. Initially, prior to the parameter form trigger fire 2. After the fire is triggered by the parameter form 3. Third Prior to Report starting a fire 4. Fourth, ignite the fire between the pages 5. Fifth After Report Set Off Fire
4. Definition alert steps?
Solution: Two kinds of alerts exist: • Event-based alerts
• Recurrent Notifications
What uses are there for Alerts?
• Notifications can be sent.
• Log files can be attached to notifications.
• Stored procedures in PL/SQL can be called.
• You can obtain the outcomes by sending emails for approval.
• Print some content in real time
5. how to define and call user defined exceptions?
Solution:
An error that occurs when a program is being executed is called an exception. By using the EXCEPTION block, programmers may identify certain conditions in PL/SQL programs and enable the program to take the appropriate action against the error condition.
There are two types of exceptions −
• System-defined exceptions
• User-defined exceptions
Example 2:user defined exception
Raising Exceptions
When there is an internal database error, the database server automatically raises exceptions; however, the programmer can also directly raise
exceptions by using the command RAISE. The easy syntax to raise an exception is as follows.
Top 8 Questions asked ME in Oracle apps Technical Interview:---
1. How you will identify error records in bulk collect?
2. If error is there in one begin end block then will the program terminate the loop or it will skip to another line of code?
3. Sequence of report triggers in oracle?
4. alert usage?
5. How are user defined exceptions defined and called?
1. How you will identify error records in bulk collect?
Anwses: The error should be caught in the exception section, and you should then loop over the entries in SQL%BULK_EXCEPTIONS to determine which errors have taken place. After that, you can either record error information to a log table or attempt to recover the DML statement.
Let's review some key PL/SQL terms related to transactions, errors, and exceptions before digging into the FORALL's error-related features.
Each and every SQL statement is atomic, or all-or-nothing. Put another way, if your update statement finds 100 rows that require editing, and as it is changing the
If there is a mistake in the hundredth row, all the changes made in the previous 100 rows are undone. What does this mean, Forall? The first time the SQL engine encounters an issue while processing the DML statement that was sent to it by FORALL, it defaults to stopping and sending the error back to the PL/SQL engine.
Any claims that the FORALL successfully completes are still pending commitment or rollback, in addition to not processing any further.
Example:--
SET SERVEROUTPUT ON ;-- semicolon at the end of serveroutput on, does not affect the code in plsql
SET SERVEROUTPUT ON--or
BEGIN
dbms_output.put_line ('Tech Insights: Unraveling the World of IT');
END;
/
If, however, you want to keep on going, even if there is an SQL error for a particular set of bind variable values, you need to take advantage of the
SAVE EXCEPTIONS clause.
Add the SAVE EXCEPTIONS clause to the FORALL header if you want the PL/SQL engine to run as many DML statements as it can, even if problems are raised during the process.
The PL/SQL engine will then store that data in a pseudo-collection called if the SQL engine raises an error.
SQL%BULK_EXCEPTIONS, and carry on with the statement execution. Upon attempting every statement, PL/SQL raises an ORA-24381 error.
SQL%BULK_EXCEPTIONS is a collection of records, each of which has two fields:
ERROR_INDEX and ERROR_CODE.
ERROR_INDEX field contains a sequentially generated integer, incremented with each statement execution in the SQL engine.
The result that SQLCODE returned when the error happened was ERROR_CODE. Please take note that the error message is absent from this collection.
Final code with error capturing details:--
DECLARE
TYPE bulkcollect_t IS TABLE OF VARCHAR2(10 CHAR);
l_string bulkcollect_t := bulkcollect_t ('A','AAA','AA');
l_error_count NUMBER;
BEGIN
FORALL indx IN 1 .. l_string.COUNT SAVE EXCEPTIONS
INSERT INTO bulkcollect_test (c) VALUES (l_string (indx));
DBMS_OUTPUT.PUT_LINE ('Rows inserted BEFORE EXCEPTION : ' || SQL%ROWCOUNT);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('Rows inserted : ' || SQL%ROWCOUNT);
l_error_count := SQL%BULK_EXCEPTIONS.count;-- SQL%BULK_EXCEPTIONS used to count the number of error records while processing into a table
DBMS_OUTPUT.PUT_LINE('Number of failures: ' || l_error_count);
FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE (
'Error '
|| indx
|| ' occurred on index '
|| SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX
|| ' attempting to update name to "'
|| l_string (
SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX)
|| '"');
DBMS_OUTPUT.PUT_LINE (
'Oracle error is : '
|| SQLERRM(-SQL%BULK_EXCEPTIONS(indx).ERROR_CODE));
END LOOP;
ROLLBACK;
END;
/
DROP TABLE bulkcollect_test
/
2. If error is there in one begin end block then will the program terminate or it will skip to another line of code?
Solution: It will terminate the program.
2.1. If error is there in one begin end block and exception handler is there then will the program terminate or it will skip to another line of code?
Solution: The program control will got erorr then will be move to exception section and handles the exception there.
if there is no exception in plsql block then Unhandled exception Halts(terminates) the execution of the host program.
When an exception is not handled correctly by the application code, it results in an unhandled exception. For instance, it is frequently the case that a file you attempt to open on disk appears to be missing.
The declaration, executable, and exception-handling portions make up a PL/SQL block. The executable section of a block must exist.
The parts on exception-handling and declaration, however, are optional.
When an exception is triggered, it propagates if PL/SQL is unable to locate a handler for it in the current block or subprogram. In other words, until a handler is discovered or there are no more blocks to search, the exception repeats itself in subsequent enclosing blocks.
What does an unhandled exception do?
Unhandled exception Halts(terminates) the execution of the host program.
3. Oracle Apps R12 sequence report triggers? Oracle Report Builder Trigger Sequence of Fire Solution: The following is the order in which Oracle reports builder triggers are triggered. Initially, prior to the parameter form trigger fire 2. After the fire is triggered by the parameter form 3. Third Prior to Report starting a fire 4. Fourth, ignite the fire between the pages 5. Fifth After Report Set Off Fire
4. Definition alert steps?
Solution: Two kinds of alerts exist: • Event-based alerts
• Recurrent Notifications
What uses are there for Alerts?
• Notifications can be sent.
• Log files can be attached to notifications.
• Stored procedures in PL/SQL can be called.
• You can obtain the outcomes by sending emails for approval.
• Print some content in real time
5. how to define and call user defined exceptions?
Solution:
An error that occurs when a program is being executed is called an exception. By using the EXCEPTION block, programmers may identify certain conditions in PL/SQL programs and enable the program to take the appropriate action against the error condition.
There are two types of exceptions −
• System-defined exceptions
• User-defined exceptions
Example 2:user defined exception
Raising Exceptions
When there is an internal database error, the database server automatically raises exceptions; however, the programmer can also directly raise
exceptions by using the command RAISE. The easy syntax to raise an exception is as follows.
No comments:
We encourage respectful comments.
Did this article help you? Let us know in the comments!
What questions do you have about this topic?
We'd love to hear your suggestions for future content.
Please Share this article with your friends and see what they think!