複選題
QUESTION 105 Examine this code: CREATE OR REPLACE PRODECURE add_dept (p_dept_name VARCHAR2 DEFAULT 'placeholder', p_location VARCHAR2 DEFAULT 'Boston') IS BEGIN INSERT INTO departments VALUES (dept_id_seq.NEXTVAL, p_dept_name, p_location); END add_dept; / Which three are valid calls to the add_dep procedure? (Choose three)
(A) add_dept;
(B) add_dept('Accounting');
(C) add_dept(, 'New York');
(D) add_dept(p_location=>'New York');.

QUESTION 101 You want to create procedures, functions and packages Which privilege do you need? (A) EXECUTE CODE object privilege (B) CREATE ANY CODE object privilege (C) CREATE PACKAGE system privilege (D) CREATE PROCEDURE system privilege (E) CREATE FUNCTION, CREATE PROCEDURE, CREATE PACKAGE system privileges

QUESTION 102 Examine this function: CREATE OR REPLACE FUNCTION CALC_PLAYER_AVG (V_ID in PLAYER_BAT_STAT.PLAYER_ID%TYPE) RETURN NUMBER IS V_AVG NUMBER; BEGIN SELECT HITS / AT_BATS INTO V_AVG FROM PLAYER_BAT_STAT WHERE PLAYER_ID = V_ID; RETURN (V_AVG); END; Which statement will successfully invoke this function in SQL *Plus? long (A) SELECT CALC_PLAYER_AVG(PLAYER_ID) FROM PLAYER_BAT_STAT; (B) EXECUTE CALC_PLAYER_AVG(31); (C) CALC_PLAYER('RUTH'); (D) CALC_PLAYER_AVG(31); (E) START CALC_PLAYER_AVG(31)

QUESTION 103 Which three are true statements about dependent objects? (Choose three) S&L C 1. Invalid objects cannot be described. 2. An object with status of invalid cannot be a referenced object. 3. The Oracle server automatically records dependencies among objects. 4. All schema objects have a status that is recorded in the data dictionary. 5. You can view whether an object is valid or invalid in the USER_STATUS data dictionary view. 6. You can view whether an object is valid or invalid in the USER_OBJECTS data dictionary view. (A)124(B)235(C)136(D)245

QUESTION 104 You have created a stored procedure DELETE_TEMP_TABLE that uses dynamic SQL to remove a table in your schema. You have granted the EXECUTE privilege to user A on this procedure. When user A executes the DELETE_TEMP_TABLE procedure, under whose privileges are the operations performed by default? (A) SYS privileges (B) Your privileges (C) Public privileges (D) User A's privileges (E) User A cannot execute your procedure that has dynamic SQL.

複選題QUESTION 106 Which two statements about packages are true? (Choose two) L (A) Packages can be nested. (B) You can pass parameters to packages. (C) A package is loaded into memory each time it is invoked. (D) The contents of packages can be shared by many applications. (E) You can achieve information hiding by making package constructs private.

複選題QUESTION 107 Which two programming constructs can be grouped within a package? (Choose two) C~~ (A) Cursor (B) Constant (C) Trigger (D) Sequence (E) View

複選題QUESTION 108 Which two statements describe the state of a package variable after executing the package in which it is declared? (Choose two) Short and Long (A) It persists across transactions within a session. (B) It persists from session to session for the same user. (C) It does not persist across transaction within a session. (D) It persists from user to user when the package is invoked. (E) It does not persist from session to session for the same user.

QUESTION 109 Which code can you use to ensure that the salary is not increased by more than 10% at a time nor is it ever decreased? (A) ALTER TABLE emp ADD CONSTRAINT ck_sal CHECK (sal BETWEEN sal AND sal*1.1); (B) CREATE OR REPLACE TRIGGER check_sal BEFORE UPDATE OF sal ON emp FOR EACH ROW WHEN (new.sal < old.sal OR new.sal > old.sal * 1.1) BEGIN RAISE_APPLICATION_ERROR ( - 20508, 'Do not decrease salary not increase by more than 10%'); END; (C) CREATE OR REPLACE TRIGGER check_sal BEFORE UPDATE OF sal ON emp WHEN (new.sal < old.sal OR new.sal > old.sal * 1.1) BEGIN RAISE_APPLICATION_ERROR ( - 20508, 'Do not decrease salary not increase by more than 10%'); END; (D) CREATE OR REPLACE TRIGGER check_sal AFTER UPDATE OR sal ON emp WHEN (new.sal < old.sal OR -new.sal > old.sal * 1.1) BEGIN RAISE_APPLICATION_ERROR ( - 20508, 'Do not decrease salary not increase by more than 10%'); END;

QUESTION 110 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 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.

QUESTION 111 Which statement is valid when removing procedures? short 單行 (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.