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

QUESTION 56 Examine this code: CREATE OR REPLACE PACKAGE metric_converter IS c_height CONSTRAINT NUMBER := 2.54; c_weight CONSTRAINT NUMBER := .454; FUNCTION calc_height (p_height_in_inches NUMBER) RETURN NUMBER; FUNCTION calc_weight (p_weight_in_pounds NUMBER) RETURN NUMBER; END; / CREATE OR REPLACE PACKAGE BODY metric_converter IS FUNCTION calc_height (p_height_in_inches NUMBER) RETURN NUMBER IS BEGIN RETURN p_height_in_inches * c_height; END calc_height; FUNCTION calc_weight (p_weight_in_pounds NUMBER) RETURN NUMBER IS BEGIN RETURN p_weight_in_pounds * c_weight END calc_weight END metric_converter; / CREATE OR REPLACE FUNCTION calc_height (p_height_in_inches NUMBER) RETURN NUMBER IS BEGIN RETURN p_height_in_inches * metric_converter.c_height; END calc_height; / Which statement is true? 中間 (A) If you remove the package specification, then the package body and the stand alone stored function CALC_HEIGHT are removed. (B) If you remove the package body, then the package specification and the stand alone stored function CALC_HEIGHT are removed. (C) If you remove the package specification, then the package body is removed. (D) If you remove the package body, then the package specification is removed. (E) If you remove the stand alone stored function CALC_HEIGHT, then the METRIC_CONVERTER package body and the package specification are removed. (AB). The stand alone function CALC_HEIGHT cannot be created because its name is used in a packaged function.