Examine this package:
CREATE OR REPLACE PACKAGE manage_emps
IS
tax_rate CONSTANT NUMBER(5,2) := .28;
v_id NUMBER;
PROCEDURE insert_emp (p_deptno NUMBER, p_sal NUMBER);
PROCEDURE delete_emp;
PROCEDURE update_emp;
FUNCTION calc_tax (p_sal NUMBER)
RETURN NUMBER;
END manage_emps;
/
CREATE OR REPLACE PACKAGE BODY manage_emps
IS
PROCEDURE update_sal
(p_raise_amt NUMBER)
IS
BEGIN UPDATE emp SET sal = (sal * p_raise_emt) + sal WHERE empno = v_id; END; PROCEDURE insert_emp (p_deptno NUMBER, p_sal NUMBER) IS BEGIN INSERT INTO emp(empno, deptno, sal) VALYES(v_id, p_depntno, p_sal); END insert_emp; PROCEDURE delete_emp IS BEGIN DELETE FROM emp WHERE empno = v_id; END delete_emp; PROCEDURE update_emp IS v_sal NUMBER(10, 2); v_raise NUMBER(10, 2); BEGIN SELECT sal INTO v_sal FROM emp WHERE empno = v_id; IF v_sal < 500 THEN v_raise := .05; ELSIP v_sal < 1000 THEN v_raise := .07; ELSE v_raise := .04; END IF; update_sal(v_raise); END update_emp; FUNCTION calc_tax (p_sal NUMBER) RETURN NUMBER IS BEGIN RETURN p_sal * tax_rate;
END calc_tax;
END manage_emps;
/
How many public procedures are in the MANAGE_EMPS package?

• A. Two
• B. Four
• C. Five
• D. Three
• E. One

Examine the trigger:
CREATE OR REPLACE TRIGGER Emp_count
AFTER DELETE ON Emp_tab
FOR EACH ROW
DELCARE
n INTEGER;
BEGIN
SELECT COUNT(*)
INTO n
FROM Emp_tab;
DBMS_OUTPUT.PUT_LINE(' There are now ' || a ||
' employees,');
END;
This trigger results in an error after this SQL statement is entered:
DELETE FROM Emp_tab WHERE Empno = 7499;
How do you correct the error?

• A. Change the trigger type to a BEFORE DELETE.
• B. Remove the DBMS_OUTPUT statement because it is not allowed in a trigger.
• C. Take out the COUNT function because it is not allowed in a trigger.
• D. Change the trigger to a statement-level trigger by removing FOR EACH ROW.

Examine this code:
CREATE OR REPLACE PROCEDURE set_bonus
(p_cutoff IN VARCHAR2 DEFAULT 'WEEKLY'
p_employee_id IN employees_employee_id%TYPE
p_salary IN employees_salary%TYPE,
p_bonus_percent IN OUT NUMBER DEFAULT 1.5,
p_margin OUT NUMBER DEFAULT 2,
p_bonus_value OUT NUMBER)
IS
BEGIN UPDATE emp_bonus SET bonus_amount =(p_salary * p_bonus_percent)/p_margin WHERE employee_id = p_employee_id; END set_bonus; /
You execute the CREATE PROCEDURE statement above and notice that it fails. What are two reasons why it fails? (Choose two)

• A. The syntax of the UPDATE statement is incorrect.
• B. You cannot update a table using a stored procedure.
• C. The declaration of the format parameter p_margin cannot have a DEFAULT clause.
• D. The formal parameter p_cutoff cannot have a DEFAULT clause.
• E. The declaration of the format parameter p_bonus_percent cannot have a DEFAULT clause.
• F. The format parameter p_bonus_value is declared but is not used anywhere.

There is a CUSTOMER table in a schema that has a public synonym CUSTOMER and you are granted all object privileges on it. You have a procedure PROCESS_CUSTOMER that processes customer information that is in the public synonym CUSTOMER table. You have just created a new table called CUSTOMER within your schema. Which statement is true?

• A. If the structure of your CUSTOMER table is the same as the public synonym CUSTOMER table then the procedure PROCESS_CUSTOMER is invalidated and gives compilation errors.
• B. If the structure of your CUSTOMER table is the same as the public synonym CUSTOMER table then the procedure PROCESS_CUSTOMER successfully recompiles when invoked and accesses your CUSTOMER table.
• C. If the structure of your CUSTOMER table is entirely different from the public synonym CUSTOMER table then the procedure PROCESS_CUSTOMER successfully recompiles and accesses your CUSTOMER table.
• D. Creating the table has no effect and procedure PROCESS_CUSTOMER still accesses data from public synonym CUSTOMER table.