This is incorrect way for calling wwwexammagiccom 5

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: er. This is incorrect way for calling. WWW.ExamMagic.COM - 5: Which two statements about packages are true? (Choose two) A. Packages can be nested. B. You can pass parameters to packages. C. A package is loaded into memory each time it is invoked. D. The contents of packages can be shared by many applications. E. You can achieve information hiding by making package constructs private. Answer: D,E Actually theses are some of the advantages of the package, sharing the package among applications and hide the logic of the procedures and function that are inside the package by declaring them in the package header and write the code of these procedures and functions inside the package body. Incorrect Answers: A: Packages can not be nested B: Parameters can't be passed to a package; parameters can be passed to procedures and functions only. C: By the first time you call a procedure, function, or reference a global variable within the package, the whole package will be loaded into the memory and stay there, so when ever you need to reference any of the package's constructs again you will find it in the memory. WWW.ExamMagic.COM - 6: Which two programming constructs can be grouped within a package? (Choose two) www.ExamMagic.com 1Z0-147 A. Cursor B. Constant C. Trigger D. Sequence E. View Answer: A,B Explanation: The constructs that can be grouped within a package include: Procedures and Functions Cursors, Variables and Constants Composite data types, such as TABLE or RECORD Exceptions Comments PRAGMAs Incorrect Answers C: Triggers are objects that we create are created on the tables. D: Sequences can't be grouped inside the packages, but we can reference then inside the package. E: Views are created and they are database objects, and they can't be grouped inside the packages. WWW.ExamMagic.COM - 7: Which two statements describe the state of a package variable after executing the package in which it is declared? (Choose two) A. It persists across transactions within a session. B. It persists from session to session for the same user. C. It does not persist across transaction within a session. D. It persists from user to user when the package is invoked. E. It does not persist from session to session for the same user. Answer: A,E You can keep track of the state of a package variable or cursor, which persists throughout the user session, from the time the user first references the variable or cursor to the time the user disconnects. 1. Initialize the variable within its declaration or within an automatic, one-time-only procedure. 2. Change the value of the variable by means of package procedures. 3. The value of the variable is released when the user disconnects. Incorrect Answers www.ExamMagic.com 1Z0-147 B: Each session will have its own value for the variables C: It persists across the transactions and through the user session. D: Each user has his own values and results, because each user has his own users. WWW.ExamMagic.COM - 8: Which code can you use to ensure that the salary is not increased by more than 10% at a time nor is it ever decreased? A. ALTER TABLE emp ADD CONSTRAINT ck_sal CHECK (sal BETWEEN sal AND sal*1.1); B. CREATE OR REPLACE TRIGGER check_sal BEFORE UPDATE OF sal ON emp FOR EACH ROW WHEN (new.sal < old.sal OR new.sal > old.sal * 1.1) BEGIN RAISE_APPLICATION_ERROR ( - 20508, 'Do not decrease salary not increase by more than 10%'); END; C. CREATE OR REPLACE TRIGGER check_sal BEFORE UPDATE OF sal ON emp WHEN (new.sal < old.sal OR new.sal > old.sal * 1.1) BEGIN RAISE_APPLICATION_ERROR ( - 20508, 'Do not decrease salary not increase by more than 10%'); END; D. CREATE OR REPLACE TRIGGER check_sal AFTER UPDATE OR sal ON emp WHEN (new.sal < old.sal OR -new.sal > old.sal * 1.1) BEGIN RAISE_APPLICATION_ERROR ( - 20508, 'Do not decrease salary not increase by more than 10%'); END; Answer: B Row triggers are the correct chose for solving the problem. A row trigger fires each time the table is affected by the triggering event. If the triggering event affects no rows, a row trigger is not executed. Row triggers are useful if the trigger action depends on data of rows that are affected or on data provided by the triggering event itself. You can create a BEFORE row trigger in order to prevent the triggering operation from succeeding if a certain condition is www.ExamMagic.com 1Z0-147 violated. Within a ROW trigger, reference the value of a column before and after the data change by prefixing it with the OLD and NEW qualifier. Incorrect Answers: A: Check constaint can't do this job lets take a look: SQL> ALTER TABLE emp ADD 2 CONSTRAINT ck_sal CHECK (sal BETWEEN sal AND sal*1.1) 3/ Table altered. SQL> select ename, sal 2 from emp 3 where ename = 'Bill'; ENAME SAL ---------- ---------Bill 5000 Now let's issue an update statement SQL> update emp 2 set sal = 10 3 where ename = 'Bill'; 1 row updated. As you can see the check constraint can't compare the old value with the new value. D,C: You can use NEW and OLD qualifier with row level triggers, If in the CREATE TRIGGER statement you didn't say FOR EACH ROW then the trigger will be statem...
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