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 98 Examine this package: CREATE OR REPLACE PACKAGE BB_PACK IS V_MAX_TEM_SALARY NUMBER(12,2); PROCEDURE ADD_PLAYER (V_ID IN NUMBER, V_LAST_NAME VACHAR 2,V_SALARY NUMBER); END BB_PACk; / CREATE OR REPLACE PACKAGE BODY BB_PACK IS V_PLAYER_AVG NUMBER84,3); PROCEDURE UPD_PLAYER_STAT (V_ID IN NUMBER, V_AB IN NUMBER DEFAULT 4, V_HITS IN NUMBER) IS BEGIN UPDATE PLAYER_BAT_STAT SET AT_BATS = AT_BATS + V_AB, HITS = HITS + V_HITS WHERE PLAYER_ID=V_ID; COMMIT; VALIDATE_PLAYER_STAT(V_ID); END UPD_PLAYER_STAT; PROCEDURE ADD_PLAYER (V_ID IN NUMBER, V_LAST_NAME VARCHAR2, V_SALARY NUMBERI) 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; If you add an IF statement to the ADD_PLAYER procedure which additional step must you perform? r A Recompile the ADD PLAYER procedure Recompile both the BB PACK specification and body S~ (A) Recompile the ADD_PLAYER procedure (B) Recompile both the BB_PACK specification and body (C) Recompile the BB_PACK specification (D) Recompile the BB_PACK body

QUESTION 99 Which statement is true about removing packages? (A) You must remove the package body first (B) Removing a package specification removes the body too (C) Removing the package body removes the specification too (D) You must remove both the package body and the specification separately (E) Removing a package specification removes all stand alone stored functions named in the specification

複選題QUESTION 100 Which two statements are true about LOBs? (Choose two.) CE (A) BFILES are stored in the database (B) All LOBs have read and write access (C) NCLOB represents a multi-byte character object (D) The Oracle9i server performs implicit conversions between BLOBs and NUMBER data types (E) The Oracle9i server performs implicit conversions between CLOBs and VARCHAR2 data types

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