You create a procedure to handle the processing of bank current accounts which rolls back
payment transactions if the overdraft limit is exceeded.
The procedure should return an “error” condition to the caller in a manner consistent with other
Oracle server errors.
Which construct should be used to handle this requirement?
A.
The SQLERRM function
B.
The PRAGMA EXCEPTION_INIT function
C.
The RAISE_APPLICATION_ERROR procedure
D.
A user-defined exception used with a raise statement
Explanation:
http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/exceptioninit_pragma.htm#L
NPLS01315
D
Error condition, as i unterstand, is text (message), threfore only Raise_application_error is applicable
raise application error
is point C!
D
d
c
the question exactly is
should return an “error” condition to the caller in a manner consistent with other
Oracle server errors.
raise_application_error
Answer is D
—
Raising User-Defined Exception with RAISE Statement
In Example 11–9, the procedure declares an exception named past_due, raises it
explicitly with the RAISE statement, and handles it with an exception handler.
Example 11–9 Declaring, Raising, and Handling User-Defined Exception
CREATE PROCEDURE account_status (
due_date DATE,
today DATE
) AUTHID DEFINER
IS
past_due EXCEPTION; — declare exception
BEGIN
IF due_date < today THEN
RAISE past_due; — explicitly raise exception
END IF;
EXCEPTION
WHEN past_due THEN — handle exception
DBMS_OUTPUT.PUT_LINE ('Account past due.');
END;
/
BEGIN
account_status ('1-JUL-10', '9-JUL-10');
END;
/
Result:
Account past due.
C – RAISE_APPLICATION_ERROR
“The procedure should return an error condition to the caller in a manner consistent with other Oracle server errors.”
Options are bit confusing. But I will go with D.
c
C
answer-c
plsql 11g part 1,les_8.ppt pg-24
“C”
RAISE_APPLICATION_ERROR
raise_application_error procedure to allow you to raise custom error numbers within your applications. You can generate errors and their associated text starting with -20000 and proceeding through -20999 (a grand total of 1,000 error numbers that you can use)
he pragma EXCEPTION_INIT tells the compiler to associate an exception name with an Oracle error number. That lets you refer to any internal exception by name and to write a specific handler for it. When you see an error stack, or sequence of error messages, the one on top is the one that you can trap and handle.
Raise_application_error procedure to allow you to raise CUSTOM error numbers within your applications. T
The pragma EXCEPTION_INIT associates an exception name with an Oracle error number. You can intercept any ORA- error and write a specific handler for it instead of using the OTHERS handler
“a manner consistent with other
Oracle server errors.”
i think that the error when it raise , it should like other oracle errors
i.e
sqlcode-sqlerrm
so i think is C
C
As per Oracle Documentation: “The RAISE_APPLICATION_ERROR procedure can be used in either the executable section
or the exception section of a PL/SQL program, or both. The returned error is consistent with
how the Oracle Server produces a predefined, non-predefined, or user-defined error. The
error number and message are displayed to the user.”
So correct answer is C.
Maybe ABCD is correct?
This is the example from Matthew Morris. “Study Guide for Oracle Database 11g: Program with PL/SQL (Exam 1Z0-144)”.
CREATE OR REPLACE PROCEDURE submit_timesheet (p_ts_date DATE)
IS
BEGIN
IF TO_CHAR(p_ts_date, ‘DY’) IN (‘SAT’, ‘SUN’) THEN
RAISE_APPLICATION_ERROR(-20020,
‘Cannot submit timesheet for weekend dates.’); <== answer C
END IF;
END;
DECLARE
x_weekend_date EXCEPTION; <== answer D
PRAGMA EXCEPTION_INIT (x_weekend_date, -20020); <== answer B
BEGIN
submit_timesheet ('17-JUN-12');
EXCEPTION
WHEN x_weekend_date THEN
DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQLERRM(-20020))); <== answer A
END;
ORA-20020: Cannot submit timesheet for weekend dates.
C
C
Good write-up. I absolutely like this website. Thanks!
http://wwwcenturionwealth.com/
This is the best search system in the planet
http://google.com
Answer is: C
C – Correct: As per Oracle Documentation: “The RAISE_APPLICATION_ERROR procedure can be used in either the executable section
or the exception section of a PL/SQL program, or both. The returned error is consistent with
how the Oracle Server produces a predefined, non-predefined, or user-defined error. The
error number and message are displayed to the user.”
The AUTONOMOUS_TRANSACTION pragma changes the way a subprogram works within a transaction. A subprogram marked with this pragma can do SQL operations and commit or roll back those operations, without committing or rolling back the data in the main transaction
CREATE OR REPLACE PROCEDURE TEST_EXCEPTION(emp_id NUMBER) AS
SAL EMPLOYEES.SALARY%TYPE;
BEGIN
UPDATE EMPLOYEES
SET SALARY = SALARY – 5000
WHERE EMPLOYEE_ID = EMP_ID;
SELECT SALARY INTO SAL FROM EMPLOYEES WHERE EMPLOYEE_ID = EMP_ID;
IF SAL < 0 THEN
RAISE_APPLICATION_ERROR(-20301, 'SALARY IS OVERDRAFT');
ELSE
DBMS_OUTPUT.PUT_LINE('UPDATED'||EMP_ID);
END IF;
END TEST_EXCEPTION;
/
EXECUTE TEST_EXCEPTION(198);
ANS IS : C