QUESTION 43 Examine the trigger: CREATE OR REPLACE TRIGGER Emp_count AFTER DELETE ON Emp_tab FOR EACH ROW DELCARE n INTEGER; BEGIN SELECT COUNT(*) INTO n FROM Emp_tab; DBMS_OUTPUT.PUT_LINE(' There are now ' || a || ' employees,'); END; This trigger results in an error after this SQL statement is entered: DELETE FROM Emp_tab WHERE Empno = 7499; How do you correct the error? long
(A) Change the trigger type to a BEFORE DELET
(E)
(B) Take out the COUNT function because it is not allowed in a trigger.
(C) Remove the DBMS_OUTPUT statement because it is not allowed in a trigger.
(D) Change the trigger to a statement-level trigger by removing FOR EACH ROW.

QUESTION 39 Examine this code: CREATE OR REPLACE PROCEDURE audit_action (p_who VARCHAR2) AS BEGIN INSERT INTO audit(schema_user) VALUES(p_who); END audit_action; / CREATE OR REPLACE TRIGGER watch_it AFTER LOGON ON DATABASE CALL audit_action(ora_login_user) / What does this trigger do? (A) The trigger records an audit trail when a user makes changes to the database. (B) The trigger marks the user as logged on to the database before an audit statement is issued. (C) The trigger invoked the procedure audit_action each time a user logs on to his/her schema and adds the username to the audit table. (D) The trigger invokes the procedure audit_action each time a user logs on to the database and adds the username to the audit table.

QUESTION 40 Which view displays indirect dependencies, indenting each dependency? (A) DEPTREE (B) IDEPTREE (C) INDENT_TREE (D) I_DEPT_TREE

QUESTION 41 The OLD and NEW qualifiers can be used in which type of trigger? short (A) Row level DML trigger (B) Row level system trigger (C) Statement level DML trigger (D) Row level application trigger (E) Statement level system trigger (AB). Statement level application trigger

QUESTION 42 Which statement is true? long (A) Stored functions can be called from the SELECT and WHERE clauses only. (B) Stored functions do not permit calculations that involve database links in a distributed environment. (C) Stored functions cannot manipulate new types of data, such as longitude and latitude. (D) Stored functions can increase the efficiency of queries by performing functions in the query rather than in the application.

QUESTION 44 What is true about stored procedures?short no 大寫 (A) A stored procedure uses the DELCLARE keyword in the procedure specification to declare formal parameters. (B) A stored procedure is named PL/SQL block with at least one parameter declaration in the procedure specification. (C) A stored procedure must have at least one executable statement in the procedure body. (D) A stored procedure uses the DECLARE keyword in the procedure body to declare formal parameters.

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