From emp where empno vid end deleteemp procedure

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: ES(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); www.ExamMagic.com 1Z0-147 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; / How many public procedures are in the MANAGE_EMPS package? A. One B. Two C. Three D. Four E. Five Answer: C Explanation: There are three procedure that are declared in the Package Specification and these Procedures are public and can be called from inside and outside of the package. Incorrect Answers A, B, D & E. There are three procedures and one function declared in the package specification which are public. There is also one procedure that is only declared in the package body and it is private. WWW.ExamMagic.COM - 73: Which command must you issue to allow users to access the UPD_TEAM_STAT www.ExamMagic.com 1Z0-147 trigger on the TEAM table? A. GRANT SELECT, INSERT, UPDATE, DELETE ON TEAM TO PUBLIC; B. GRANT SELECT,INSERT,UPDATE,DELETE ON UPD_TEAM_STAT TO PUBLIC; C. GRANT EXECUTE ON TEAM TO PUBLIC D. GRANT SELECT, EXECUTE ON TEAM, UPD_TEAM_STAT TO PUBLIC; Answer: A WWW.ExamMagic.COM - 74: Examine this code: CREATE OR REPLACE PROCEDURE set_bonus (p_cutoff IN VARCHAR2 DEFAULT 'WEEKLY' p_employee_id IN employees_employee_id%TYPE p_salary IN employees_salary%TYPE, p_bonus_percent IN OUT NUMBER DEFAULT 1.5, p_margin OUT NUMBER DEFAULT 2, p_bonus_value OUT NUMBER) IS BEGIN UPDATE emp_bonus SET bonus_amount =(p_salary * p_bonus_percent)/p_margin WHERE employee_id = p_employee_id; END set_bonus; / You execute the CREATE PROCEDURE statement above and notice that it fails. What are two reasons why it fails? (Choose two) A. The syntax of the UPDATE statement is incorrect. B. You cannot update a table using a stored procedure. C. The format parameter p_bonus_value is declared but is not used anywhere. D. The formal parameter p_cutoff cannot have a DEFAULT clause. E. The declaration of the format parameter p_margin cannot have a DEFAULT clause. F. The declaration of the format parameter p_bonus_percent cannot have a DEFAULT clause. Answer: E, F Explanation: You can't assign a default value for the OUT and IN OUT parameters. Assigning a default value to an OUT or IN OUT parameter causes the following compilation error: PLS-00230: OUT and IN OUT formal parameters may not have default expressions www.ExamMagic.com 1Z0-147 Incorrect Answers A. There are no syntax errors on the Procedure. B. Updates in a Stored Procedure are allowed and are quite common. C. This parameter has a default value and the fact that it is not being used in the Procedure will not cause an error. WWW.ExamMagic.COM - 75: Which three statements are true regarding database triggers? (Choose three) A. A database trigger is a PL/SQL block, C, or Java procedure associated with a table, view, schema, or the database. B. A database trigger needs to be executed explicitly whenever a particular event takes place. C. A database trigger executes implicitly whenever a particular event takes place. D. A database trigger fires whenever a data event (such as DML) or system event (such as logon, shutdown) occurs on a schema or database. E. With a schema, triggers fire for each event for all users; with a database, triggers fire for each event for that specific user. Answer: A, C, D Explanation: The trigger body is a PL/SQL block in which you can issue both SQL and PL/SQL statements from the trigger body. You can also call a stored procedure or a Java procedure from the trigger body. You can also invoke a procedure developed using object-oriented languages, such as C. C Triggers are invoked implicitly whenever a particular event occurs in the database. D Database Triggers occur when a Data Event DM, DDL occurs or when system events such as shutting down or starting up the database or a logon or logoff from the database. Incorrect Answers B. Database Triggers are invoked implicitly whenever a particular event occurs in the database. E. Triggers that are created at the database level fire for all the users, and triggers that are created at the schema level fire only when the triggering event involves that schema. WWW.ExamMagic.COM - 76: You create a DML trigger. For the timing information, which are valid with a DML trigger? www.ExamMagic.com 1Z0-147 A. DURING B. IN PLACE OF C. ON SHUTDOWN D. BEFORE E. ON STATEMENT EXECUTION Answer: D Explanation: Trigger Timing determines whether the trigger will fire BEFORE or AFTER the DML statement on the table. The BEFORE and AFTER timings are not valid for views. You can only create an INSTEAD OF trigger on a view. Incorrect Answers A, B, C & E are not valid Oracle Events WWW.ExamMagic.COM - 77: Whic...
View Full Document

Ask a homework question - tutors are online