PL/SQL Interview Questions

When invoking this procedure, you encounter the error:
ORA-000: Unique constraint(SCOTT.THEATER_NAME_UK) violated.
How should you modify the function to handle this error?
1. An user defined exception must be declared and associated with the error code and handled in the EXCEPTION section.
2. Handle the error in EXCEPTION section by referencing the error code directly.
3. Handle the error in the EXCEPTION section by referencing the UNIQUE_ERROR predefined exception.
4. Check for success by checking the value of SQL%FOUND immediately after the UPDATE statement.

Read the following code:
40. CREATE OR REPLACE PROCEDURE calculate_budget IS
41. v_budget studio.yearly_budget%TYPE;
42. BEGIN
43. v_budget := get_budget(11);
44. IF v_budget < 30000
45. THEN
46. set_budget(11,30000000);
47. END IF;
48. END;

You are about to add an argument to CALCULATE_BUDGET. What effect will this have?
1. The GET_BUDGET function will be marked invalid and must be recompiled before the next execution.
2. The SET_BUDGET function will be marked invalid and must be recompiled before the next execution.
3. Only the CALCULATE_BUDGET procedure needs to be recompiled.
4. All three procedures are marked invalid and must be recompiled.

Which procedure can be used to create a customized error message?
1. RAISE_ERROR
2. SQLERRM
3. RAISE_APPLICATION_ERROR
4. RAISE_SERVER_ERROR

The CHECK_THEATER trigger of the THEATER table has been disabled. Which command can you issue to enable this trigger?
1. ALTER TRIGGER check_theater ENABLE;
2. ENABLE TRIGGER check_theater;
3. ALTER TABLE check_theater ENABLE check_theater;
4. ENABLE check_theater;

Examine this database trigger
52. CREATE OR REPLACE TRIGGER prevent_gross_modification
53. {additional trigger information}
54. BEGIN
55. IF TO_CHAR(sysdate, DY) = MON
56. THEN
57. RAISE_APPLICATION_ERROR(-20000,Gross receipts cannot be deleted on Monday);
58. END IF;
59. END;

This trigger must fire before each DELETE of the GROSS_RECEIPT table. It should fire only once for the entire DELETE statement. What additional information must you add?
1. BEFORE DELETE ON gross_receipt
2. AFTER DELETE ON gross_receipt
3. BEFORE (gross_receipt DELETE)
4. FOR EACH ROW DELETED FROM gross_receipt

More ......