複選題
QUESTION 31 Which three are valid ways to minimize dependency failure? (Choose three) not s~
(A) Querying with the SELECT * notification.
(B) Declaring variables with the %TYPE attribute.
(C) Specifying schema names when referencing objects.
(D) Declaring records by using the %ROWTYPE attribute.
(E) Specifying package.procedure notation while executing procedures.

複選題QUESTION 27 Examine this procedure: CREATE OR REPLACE PROCEDURE INSERT_TEAM (V_ID in NUMBER, V_CITY in VARCHAR2 DEFAULT 'AUSTIN', V_NAME in VARCHAR2) IS BEGIN INSERT INTO TEAM (id, city, name) VALUES (v_id, v_city, v_name); COMMIT; END Which two statements will successfully invoke this procedure in SQL *Plus? (Choose two) (A) EXECUTE INSERT_TEAM; (B) EXECUTE INSERT_TEAM(3, V_NAME=>'LONGHORNS', V_CITY=>'AUSTIN'); (C) EXECUTE INSERT_TEAM(3, 'AUSTIN','LONGHORNS'); (D) EXECUTE INSERT_TEAM (V_ID := V_NAME := 'LONGHORNS', V_CITY := 'AUSTIN'); (E) EXECUTE INSERT_TEAM (3, 'LONGHORNS');

QUESTION 28 To be callable from a SQL expression, a user-defined function must do what? (A) Be stored only in the database. (B) Have both IN and OUT parameters. (C) Use the positional notation for parameters. (D) Return a BOOLEAN or VARCHAR2 data type.

複選題QUESTION 29 Which two describe a stored procedure? (Choose two) (A) A stored procedure is typically written in SQL. (B) A stored procedure is a named PL/SQL block that can accept parameters. (C) A stored procedure is a type of PL/SQL subprogram that performs an action. (D) A stored procedure has three parts: the specification, the body, and the exception handler part. (E) The executable section of a stored procedure contains statements that assigns values, control execution, and return values to the calling environment.

複選題QUESTION 30 Examine this code: CREATE OR REPLACE PROCEDURE add_dept ( p_name departments.department_name%TYPE DEFAULT 'unknown', p_loc departments.location_id%TYPE DEFAULT 1700) IS BEGIN INSERT INTO departments(department_id, department_name, loclation_id) VALUES(dept_seq.NEXTVAL,p_name, p_loc); END add_dept; / You created the add_dept procedure above, and you now invoke the procedure in SQL *Plus. Which four are valid invocations? (Choose four) (A) EXECUTE add_dept(p_loc=>2500) (B) EXECUTE add_dept('Education', 2500) (C) EXECUTE add_dept('2500', p_loc =>2500) (D) EXECUTE add_dept(p_name=>'Education', 2500) (E) EXECUTE add_dept(p_loc=>2500, p_name=>'Education')

複選題QUESTION 32 Which two does the INSTEAD OF clause in a trigger identify? (Choose two) view event (A) The view associated with the trigger. (B) The table associated with the trigger. (C) The event associated with the trigger. (D) The package associated with the trigger. (E) The statement level or for each row association to the trigger.

QUESTION 33 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; / What is the name of the private procedure in this package? (A) CALC_TAX (B) INSERT_EMP (C) UPDATE_SAL (D) DELETE_EMP (E) UPDATE_EMP F. MANAGE_EMPS

QUESTION 34 What can you do with the DBMS_LOB package? long (A) Use the DBMS_LOB.WRITE procedure to write data to a BFILE. (B) Use the DBMS_LOB.BFILENAME function to locate an external BFILE. (C) Use the DBMS_LOB.FILEEXISTS function to find the location of a BFILE. (D) Use the DBMS_LOB.FILECLOSE procedure to close the file being accessed.

QUESTION 35 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, V_SALARY NUMBER); END BB_PACK; / CREATE OR REPLACE PACKAGE BODY BB_PACK IS V_PLAYER_AVG NUMBER(4,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 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 / Which statement will successfully assign .333 to the V_PLAYER_AVG variable from a procedure outside the package? 333 long (A) V_PLAYER_AVG := .333; (B) BB_PACK.UPD_PLAYER_STAT.V_PLAYER_AVG := .333; (C) BB_PACK.V_PLAYER_AVG := .333; (D) This variable cannot be assigned a value from outside of the package.

QUESTION 36 Examine this code: CREATE OR REPLACE PACKAGE comm_package IS g_comm NUMBER := 10; PROCEDURE reset_comm(p_comm IN NUMBER); END comm_package; / User Jones executes the following code at 9:01am: EXECUTE comm_package.g_comm := 15 User Smith executes the following code at 9:05am: EXECUTE comm_paclage.g_comm := 20 Which statement is true? (A) g_comm has a value of 15 at 9:06am for Smith. (B) g_comm has a value of 15 at 9:06am for Jones. (C) g_comm has a value of 20 at 9:06am for both Jones and Smith. (D) g_comm has a value of 15 at 9:03 am for both Jones and Smith. (E) g_comm has a value of 10 at 9:06am for both Jones and Smith. (AB). g_comm has a value of 10 at 9:03am for both Jones and Smith

QUESTION 37 Examine this code: CREATE OR REPLACE FUNCTION gen_email_name (p_first_name VARCHAR2, p_last_name VARCHAR2, p_id NUMBER) RETURN VARCHAR2 IS v_email_name VARCHAR2(19=; BEGIN v_email_name := SUBSTR(p_first_name, 1, 1) || SUBSTR(p_last_name, 1, 7) || '@Oracle.com'; UPDATE employees SET email = v_email_name WHERE employee_id = p_id; RETURN v_email_name; END; Which statement removes the function? (A) DROP gen_email_name; (B) REMOVE gen_email_name; (C) DELETE gen_email_name; (D) TRUNCATE gen_email_name; (E) DROP FUNCTION gen_email_name; (AB).ALTER FUNCTION gen_email_name REMOVE;