D due to the lack of exception handling the inserts

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: rt into the locations table and the insert into the departments table are rolled back. D. Due to the lack of exception handling the inserts into the locations & departments are rollback back. WWW.ExamMagic.COM - 56: The creation of which database objects will cause a DDL trigger to fire? (Choose all that apply) A. Index B. Cluster C. Package D. Function E. Synonyms F. Dimensions G. Database links Answer: A,B, C,D,E Explanation: www.ExamMagic.com 1Z0-147 DDL triggers fire for clusters, functions, indexes, packages, procedures, roles, sequences, synonyms, tables, tablespaces, triggers, types, views, or users. WWW.ExamMagic.COM - 57: Which two program declarations are correct for a stored program unit? (Choose two) 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)) Answer: A,C Explanation: A. This is the correct syntax for creating a Function. The syntax for creating a function is similar to that of creating a procedure with the addition of a RETURN statement. The following is the syntax for CREATE FUNCTION: CREATE [OR REPLACE] FUNCTION [(parameter [mode1] datatype1, parameter2 [mode2] datatype2 ...)] RETURN datatype IS | AS PL/SQL BLOCK; C. This is the correct syntax for creating a Procedure.The syntax for creating a procedure is: CREATE [OR REPLACE] PROCEDURE [parameter1 [mode1] datatype1, parameter2 [mode2] datatype2, . . .)] IS | AS ... BEGIN www.ExamMagic.com 1Z0-147 ... EXCEPTION END ; Incorrect Answers B. This is incorrect syntax for a Procedure. Functions have a RETURN Clause, Procedures do not. D. When you define the data type, the length of the data type is not allowed in the parameter list. If you specify the length of a formal parameter, Oracle issues an error at compilation time. E. The length of the data type is not allowed in the parameter list for functions or procedures. WWW.ExamMagic.COM - 58: 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 Answer: B Explanation: System Event Triggers can be defined to fire at either at the schema level or database level. You can create a trigger that is fired when a user connects to the database. The triggering event in this case is LOGON. This trigger can be created either at the database level or at the schema level. If the trigger is created at the database level, the trigger is fired for all the users that connect to the database. If the trigger is created at the schema level, the trigger is fired when the user that created the trigger connects to the database. Incorrect Answers A. DML Triggers fire when a DML Operation is perform on a Table BEFORE OR AFTER (Trigger Timing) the DML Event (INSERT, UPDATE or DELETE) on the TABLE. C. INSTEAD OF TRIGGERS will fire on a view. D. Application trigger - Fires when a particular event occurs in the application. www.ExamMagic.com 1Z0-147 Application triggers are developed using Oracle client-side tools, such as Oracle Forms Developer. WWW.ExamMagic.COM - 59: 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. Answer: C Explanation: This will result in a mutating table. A mutating table is a table against which a data manipulation statement has been issued and the corresponding trigger on the DML statement is reading from the same table, at the same time. To work around this you would need to need to create a statement level trigger with the SELECT statement and place the values into the package variables. Then the ROW Level Trigger could check the values in the package variables. Incorrect Answers A. This trigger would result in a mutating table and would generate an error. B. ROW level Triggers result in a mutating table, STATEMENT level triggers do not. D. You may use a MINIMUM and MAXIMUM Functions but not in a ROW level Trigger that queries the same table in which a DML operation is being performed. WWW.ExamMagic.COM - 60: Examine this code: CREATE OR REPLACE STORED FUNCTION get_sal (p_raise_amt NUMBER, p_employee_id employees.employee_id%TYPE) RETURN NUMBER IS www.ExamMagic.com 1Z0-147 v_salaryNUMBER; v_raise NUMBER(8,2); BEGIN SELECT salary INTO v_salary FROM employees WHERE employee_id = p_employee_id;...
View Full Document

This note was uploaded on 01/05/2011 for the course COMS 131 taught by Professor Dad during the Spring '10 term at University of Mumbai.

Ask a homework question - tutors are online