Exammagiccom 9 examine this code create or replace

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: ent level trigger WWW.ExamMagic.COM - 9: Examine this code: CREATE OR REPLACE PACKAGE bonus IS g_max_bonus NUMBER := .99; FUNCTION calc_bonus (p_emp_id NUMBER) RETURN NUMBER; FUNCTION calc_salary (p_emp_id NUMBER) RETURN NUMBER; END; / CREATE OR REPLACE PACKAGE BODY bonus IS v_salary employees.salary%TYPE; v_bonus employees.commission_pct%TYPE; FUNCTION calc_bonus (p_emp_id NUMBER) RETURN NUMBER IS www.ExamMagic.com 1Z0-147 BEGIN SELECT salary, commission_pct INTO v_salary, v_bonus FROM employees WHERE employee_id = p_emp_id; RETURN v_bonus * v_salary; END calc_bonus FUNCTION calc_salary (p_emp_id NUMBER) RETURN NUMBER IS BEGIN SELECT salary, commission_pct INTO v_salary, v_bonus FROM employees WHERE employees RETURN v_bonus * v_salary + v_salary; END cacl_salary; END bonus; / Which statement is true? A. You can call the BONUS.CALC_SALARY packaged function from an INSERT command against the EMPLOYEES table. B. You can call the BONUS.CALC_SALARY packaged function from a SELECT command against the EMPLOYEES table. C. You can call the BONUS.CALC_SALARY packaged function form a DELETE command against the EMPLOYEES table. D. You can call the BONUS.CALC_SALARY packaged function from an UPDATE command against the EMPLOYEES table. Answer: B For the Oracle server to execute a SQL statement that calls a stored function, it must know the purity level of a stored functions, that is, whether the functions are free of side effects. Side effects are changes to database tables or public packaged variables (those declared in a package specification). Side effects could delay the execution of a query, yield order-dependent (therefore indeterminate) results, or require that the package state variables be maintained across user sessions. Various side effects are not allowed when a function is called from a SQL query or DML statement. Therefore, the following restrictions apply to stored functions called from SQL expressions: • A function called from a query or DML statement may not end the current transaction, create or roll back to a savepoint, or alter the system or session • A function called from a query statement or from a parallelized DML statement may not execute a DML statement or otherwise modify the database • A function called from a DML statement may not read or modify the particular table being modified by that DML statement www.ExamMagic.com 1Z0-147 WWW.ExamMagi c.COM - 10: Which statement is valid when removing procedures? A. Use a drop procedure statement to drop a standalone procedure. B. Use a drop procedure statement to drop a procedure that is part of a package. Then recompile the package specification. C. Use a drop procedure statement to drop a procedure that is part of a package. Then recompile the package body. D. For faster removal and re-creation, do not use a drop procedure statement. Instead, recompile the procedure using the alter procedure statement with the REUSE SETTINGS clause. Answer: A The DROP DROCEDURE statement is used to drop a stand alone procedure Incorrect Answers: B: You can't drop a procedure that's inside a package, you have to drop the package, and in this case the whole procedures, functions,... that are inside the packages will be droped. C: Same as B. D: REUSE SETTINGS is used to to prevent Oracle from dropping and reacquiring compiler switch settings.With this clause, Oracle preserves the existing settings and uses them for the recompilation. WWW.ExamMagic.COM - 11: Examine this package: CREATE OR REPLACE PACKAGE BB_PACK IS V_MAX_TEAM_SALARY NUMBER(12,2); PROCEDURE ADD_PLAYER(V_ID IN NUMBER, V_LAST_NAME VARCHAR2, NUMBER); END BB_PACK; / CREATE OR REPLACE PACKAGE BODY BB_PACK IS PROCEDURE UPD_PLAYER_STAT (V_ID IN NUMBER, V_AB IN NUMBER DEFAULT 4, V_HITS IN NUMBER) IS BEGIN UPDATE PLAYER_BAT_STAT www.ExamMagic.com 1Z0-147 SET AT_BATS = AT_BATS + V_AB, HITS = HITS + V_HITS WHERE PLAYER_ID = V_ID; COMMIT; END UPD_PLAYER_STAT; PROCEDURE ADD_PLAYER (V_ID IN NUMBER, V_LAST_NAME VARCHAR2, V_SALARY NUMBER) IS BEGIN INSERT INTO PLAYER(ID,LAST_NAME,SALARY) VALUES (V_ID, V_LAST_NAME, V_SALARY); UPD_PLAYER_STAT(V_ID,0,0); END ADD_PLAYER; END BB_PACK; You make a change to the body of the BB_PACK package. The BB_PACK body is recompiled. What happens if the stand alone procedure VALIDATE_PLAYER_STAT references this package? A. VALIDATE_PLAYER_STAT cannot recompile and must be recreated. B. VALIDATE_PLAYER_STAT is not invalidated. C. VALDIATE_PLAYER_STAT is invalidated. D. VALIDATE_PLAYER_STAT and BB_PACK are invalidated. Answer: B You can greatly simplify dependency management with packages when referencing a package procedure or function from a stand-alone procedure or function. • If the package body changes and the package specification does not change, the stand-alone procedure referencing a package construct remains valid. • If the package specification changes, the outside procedure referencing a package construct is invalidated, as is the package body. WWW.ExamMagic.COM - 12: You need to create a trigger on the EMP table that monitors every row that is changed and places this infor...
View Full Document

Ask a homework question - tutors are online