QUESTION 49 You have a row level BEFORE UPDATE trigger on the EMP table. This trigger contains a SELECT statement on the EMP table to ensure that the new salary value falls within the minimum and maximum salary for a given job title. What happens when you try to update a salary value in the EMP table?
(A) The trigger fires successfully.
(B) The trigger fails because it needs to be a row level AFTER UPDATE trigger.
(C) The trigger fails because a SELECT statement on the table being updated is not allowed.
(D) The trigger fails because you cannot use the minimum and maximum functions in a BEFORE UPDATE trigger.

QUESTION 45 Examine this code: CREATE OR REPLACE PROCEDURE insert_dept (p_location_id NUMBER) IS v_dept_id NUMBER(4); BEGIN INSERT INTO departments VALUES (5, 'Education', 150, p_location_id); SELECT department_id INTO v_dept_id FROM employees WHERE employee_id=99999; END insert_dept; / CREATE OR REPLACE PROCEDURE insert_location ( p_location_id NUMBER, p_city VARCHAR2) IS BEGIN INSERT INTO locations(location_id, city) VALUES (p_location_id, p_city); insert_dept(p_location_id); END insert_location; / You just created the departments, the locations, and the employees table. You did not insert any rows. Next you created both procedures. You new invoke the insert_location procedure using the following command: EXECUTE insert_location (19, 'San Francisco') What is the result in thisEXECUTE command? and long (A) The locations, departments, and employees tables are empty. (B) The departments table has one row. The locations and the employees tables are empty. (C) The location table has one row. The departments and the employees tables are empty. (D) The locations table and the departments table both have one row. The employees table is empty.

QUESTION 46 The creation of which database objects will cause a DDL trigger to fire? (Choose all that apply) 1. Index 2. Cluster 3. Package 4. Function 5. Synonyms 6. Dimensions 7. Database links (A)12345(B)23456(C)12457(D)234567

複選題QUESTION 47 Which two program declarations are correct for a stored program unit? (Choose two) AC (A) CREATE OR REPLACE FUNCTION tax_amt (p_id NUMBER) RETURN NUMBER (B) CREATE OR REPLACE PROCEDURE tax_amt (p_id NUMBER) RETURN NUMBER (C) CREATE OR REPLACE PROCEDURE tax_amt (p_id NUMBER, p_amount OUT NUMBER) (D) CREATE OR REPLACE FUNCTION tax_amt (p_id NUMBER) RETURN NUMBER(10,2) (E) CREATE OR REPLACE PROCEDURE tax_amt (p_id NUMBER, p_amount OUT NUMBER(10, 2))

QUESTION 48 You need to implement a virtual private database (vpd). In order to have the vpd functionality, a trigger is required to fire when every user initiates a session in the database. What type of trigger needs to be created? (A) DML trigger (B) System event trigger (C) INSTEAD OF trigger (D) Application trigger

QUESTION 50 Examine this code: CREATE OR REPLACE STORED FUNCTION get_sal (p_raise_amt NUMBER, p_employee_id employees.employee_id%TYPE) RETURN NUMBER IS v_salaryNUMBER; v_raise NUMBER(8,2); BEGIN SELECT salary INTO v_salary FROM employees WHERE employee_id = p_employee_id; v_raise := p_raise_amt * v_salary; RETURN v_raise; END; Which statement is true? short (A) This statement creates a stored procedure named get_sal. (B) This statement returns a raise amount based on an employee id. (C) This statement creates a stored function named get_sal with a status of invalid. (D) This statement creates a stored function named get_sal. (E) This statement fails.

QUESTION 51 You need to disable all triggers on the EMPLOYEES table. Which command accomplishes this? (A) None of these commands; you cannot disable multiple triggers on a table in one command. (B) ALTER TRIGGERS ON TABLE employees DISABLE; (C) ALTER employees DISABLE ALL TRIGGERS; (D) ALTER TABLE employees DISABLE ALL TRIGGERS;

QUESTION 52 An internal LOB is _____. NOT A (A) A table. (B) A column that is a primary key. (C) Stored in the database. (D) A file stored outside of the database, with an internal pointer to it from a database column.

複選題QUESTION 53 Examine this code: CREATE OR REPLACE FUNCTION calc_sal(p_salary NUMBER) RETURN NUMBER IS v_raise NUMBER(4,2) DEFAULT 1.08; BEGIN RETURN v_raise * p_salary; END calc_sal; / Which statement accurately call the stored function CALC_SAL? (Choose two) 3行 (A) UPDATE employees (calc_sal(salary)) SET salary = salary * calc_sal(salary); (B) INSERT calc_sal(salary) INTO employees WHERE department_id = 60; (C) DELETE FROM employees(calc_sal(salary)) WHERE calc_sal(salary) > 1000; (D) SELECT salary, calc_sal(salary) FROM employees WHERE department_id = 60; (E) SELECT last_name, salary, calc_sal(salary) FROM employees ORDER BY calc_sal(salary);

QUESTION 54 This statement fails when executed: CREATE OR REPLACE TRIGGER CALC_TEAM_AVG AFTER INSERT ON PLAYER BEGIN INSERT INTO PLAYER_BATSTAT (PLAYER_ID, SEASON_YEAR,AT_BATS,HITS) VALUES (:NEW.ID, 1997, 0,0); END; To which type must you convert the trigger to correct the error? short (A) Row (B) Statement (C) ORACLE FORM trigger (D) Before

QUESTION 55 Examine this code: CREATE OR REPLACE PROCEDURE audit_emp (p_id IN emp_empno%TYPE) IS v_id NUMBER; PROCEDURE log_exec IS BEGIN INSERT INTO log_table (user_id, log_delete) VALUES (USER, SYSDATE); END log_exec; v_name VARCHAR2(20); BEGIN DELETE FROM emp WHERE empno = p_id; log_exec; SELECT ename, empno INTO v_name, v_id FROM emp WHERE empno = p_id; END audit_emp; Why does this code cause an error when compiled? (A) An insert statement is not allowed in a subprogram declaration. (B) Procedure LOG_EXEC should be declared before any identifiers. (C) Variable v_name should be declared before declaring the LOG_EXEC procedure. (D) The LOG_EXEC procedure should be invoked as EXECUTE log_exec with the AUDIT_EMP procedure.