1. Package Specification DEPT_PACK was created by the following code: CREATE OR REPLACE PACKAGE dept_pack IS PROCEDURE ins_dept(p_deptno IN NUMBER); FUNCTION get_dept(p_deptno IN NUMBER) RETURN VARCHAR2; END dept_pack; Which of the following are correct syntax for invoking the package subprograms? (Choose two.) Mark for Review (1) Points (Choose all correct answers) BEGIN dept_pack.ins_dept(20); END; (*)
BEGIN dept_pack.get_dept(20); END; DECLARE v_deptname VARCHAR2(20); BEGIN v_deptname := get_dept(50); END; CREATE PROCEDURE dept_proc IS v_deptname VARCHAR2(20); BEGIN v_deptname := dept_pack.get_dept(40); END; (*)
BEGIN dept_pack(30); END;
Incorrect. Refer to Section 10 Lesson 1. 2. In which component of a package is the full definition of a publ public ic proced procedure ure writte written? n? Mark Mark for for Revi Review ew (1) Points Body (*)
Specification Both the body and the specification Neither the body nor the specification
Incorrect. Refer to Section 10 Lesson 1. 3.
Which of the following can be included in a package?
Mark for Review (1) Points procedures variables PL/SQL types Exceptions All of the above (*)
Correct 4. Package EMP_PACK contains two procedures, DEL_EMP and S HOW_EMP. You want to write an anonymous block which invokes these procedures but you have forgotten which parameters they use. Which of the following will give you you this this info inform rmat atio ion? n? Mark Mark for for Revi Review ew (1) Points DESCRIBE del_emp DESCRIBE show_emp DESCRIBE emp_pack(del_emp, show_emp) DESCRIBE emp_pack (*)
DESCRIBE emp_pack.del_emp DESCRIBE emp_pack.show_emp
None of the above
Correct 5. A number variable declared in a package is initialized to 0 unl unles ess s ass assig igne ned d ano anoth ther er valu value. e. True True or Fals False? e? Mark Mark for for Rev Revie iew w (1) Points True False (*)
Correct 6. Which of the following are good reasons to group a set of proc proced edur ures es and and fun funct ctio ions ns into into a pac packa kage ge? ? Mark Mark for for Rev Revie iew w (1) Points Application developers do not need to know the details of the package bo dy code. Related subprograms and variables can be grouped together for easier man agement and maintenance. If the detailed code is changed, applications which invoke the package d o not need to be recompiled. All of the above. (*)
Incorrect. Refer to Section 10 Lesson 1. 7. To be able to invoke a package subprogram from outside the package, it must be declared in the package: Mark for Review (1) Points Body Specification Body and the specification (*)
None of the above
Incorrect. Refer to Section 10 Lesson 1. 8. The two parts of a package are stored as separate objec ts in in th the da database. Tr True or or Fa False? Mark fo for Re Review (1) Points True (*) False
Correct 1. What will be displayed when a user executes the followin g statement? SELECT object_name FROM user_objects WHERE object_type LIKE 'PACK%'; Mark for Review (1) Points The names of all package specifications in the user's schema The names of all package specifications and package bodies in the user's schema (*) The parameters which must be used when invoking all packaged subprograms in the user's schema The detailed code of all packages in the user's schema The names of all packages which can be invoked by the user
Incorrect. Refer to Section 10 Lesson 2. 2. SCOTT's schema contains a package EMP_PKG, which contain s a public procedure EMP_SAL, which accepts a NUMBER parameter. If the invoker h as execute rights on the package, which of the following will invoke the functio n success successful fully? ly? Mark Mark for Revie Review w (1) Points emp_pkg.emp_sal(101);
scott.emp_pkg.emp_sal(101): (*) emp_sal(101); None of the above. All of the above.
Incorrect. Refer to Section 10 Lesson 2. 3. A public component declared in the package specificatio n can be referenced by a private component defined in the package body. True or Fals False? e? Mark Mark for for Rev Revie iew w (1) Points True (*) False
Correct 4. We want to remove both the specification and the body o f package CO_PACK from the database. Which of the following commands will do thi s? Mark for Review (1) Points DROP BOTH co_pack; DROP PACKAGE BODY co_pack; DROP PACKAGE co_pack; (*) DROP PACKAGE SPECIFICATION co_pack; None of the above
Incorrect. Refer to Section 10 Lesson 2. 5. A local variable defined inside a package procedure is v isible to to th the ca calling en environment. Tr True or or Fa False? Mark fo for Re Review (1) Points
True False (*)
Correct 6. Examine the following package specification: CREATE OR REPLACE PACKAGE mypack IS percent_tax NUMBER := 20; PROCEDURE proc1; END mypack; The package body of mypack also includes a function called func1. Which of the f ollowing statements are true? (Choose three.) Mark for Review (1) Points (Choose all correct answers) proc1 is a public procedure and func1 is a private function. (*)
The package will not compile because you cannot declare variables in the specification, only procedures and functions. . The variable can be modified by: BEGIN mypack.percent_tax := 10; END; (*)
The function can be invoked from outside the package. The procedure can be invoked by: BEGIN mypack.proc1; END; (*)
Incorrect. Refer to Section 10 Lesson 2.
7. Which one of the following queries would you use to see the detailed detailed code code of a package package called called EMP_PKG? EMP_PKG? Mark for Review Review (1) Points SELECT text FROM user_source WHERE name = 'EMP_PKG' AND type = 'PACKAGE' ORDER BY line; SELECT source FROM user_packages WHERE name = 'EMP_PKG' AND type = 'PACK AGE BODY' ORDER BY line; SELECT text FROM all_source WHERE name = 'EMP_PKG' AND type = 'PACKAGE' ORDER BY line; SELECT text FROM user_source WHERE name = 'EMP_PKG' AND type = 'PACKAGE BODY' ORDER BY line; (*)
Incorrect. Refer to Section 10 Lesson 2. 8. Your schema contains a package called EMP_PKG. You want to remove the package body but not the specification. The correct syntax to do this is is: DR DROP BO BODY em emp_pkg; Tr True or or Fa False? Mark fo for Re Review (1) Points True False (*)
Correct 9. A local variable declared within a procedure in a packa ge can be referenced by any other component of that package. True or False? Mark for Review (1) Points True False (*)
Correct 10. When one component of a package is called, all the pack age's component components s are loaded into into memory. memory. True or False? Mark for Review Review
(1) Points True (*) False
Correct 1. The package name must be included when calling a package function from a SELECT statement executed outside the package. True or False? Mark for Review (1) Points True (*) False
Incorrect. Refer to Section 10 Lesson 3. 2. Examine the following package code: CREATE OR REPLACE PACKAGE over_pack IS PROCEDURE do_work1 (p1 IN VARCHAR2, p2 IN NUMBER); PROCEDURE do_work2 (p1 IN VARCHAR2, p2 IN NUMBER); PROCEDURE do_work1 (param1 IN CHAR, param2 IN NUMBER); FUNCTION do_work2 (param1 IN VARCHAR2, param2 IN NUMBER) RETURN DATE; END over_pack; Which of the following calls will be successful? (Choose three.) Mark for Review (1) Points (Choose all correct answers) over_pack.do_work1('Smith',20); v_date := over_pack.do_work2('Smith',20); (*) over_pack.do_work2('Smith',20); (*) over_pack.do_work1(p1=>'Smith',p2=>20); (*) over_pack.do_work1(param1=>'Smith');
Incorrect. Refer to Section 10 Lesson 3.
3. A package initialization block is executed automaticall y every time a user invokes any procedure or function in the package. True or Fa lse ? Mark for Review (1) Points True False (*)
Incorrect. Refer to Section 10 Lesson 3. 4. How would you invoke the constant km_to_mile from the gl obal_consts bodiless package at VARIABLE A? SELECT trail_name, distance_in_km * VARIABLE A FROM trails WHERE park_name = 'YOSEMITE'; Mark for Review (1) Points km_to_mile.global_consts km_to_mile (global_consts) global_consts.km_to_mile (*) global_consts (km_to_mile)
Incorrect. Refer to Section 10 Lesson 3. 5. The following package is valid. True or False? CREATE OR REPLACE PACKAGE exceptions_pkg IS e_cons_violation EXCEPTION; PRAGMA EXCEPTION_INIT (e_cons_violation, -2292); e_value_too_large EXCEPTION; PRAGMA EXCEPTION_INIT (e_value_too_large, -1438); END exceptions_pkg; Mark for Review (1) Points True (*) False
Correct 6. The following example package specification is valid to create a data type ed_type that can be used in other subprograms. True or False? CREATE OR REPLACE PACKAGE emp_dept_pkg IS TYPE ed_type IS RECORD (f_name employees.first_name%TYPE, l_name employees.last_name%TYPE, d_name departments.department_name%TYPE); PROCEDURE sel_emp_dept (p_emp_id IN employees.employee_id%TYPE, p_emp_dept_rec OUT ed_type); END emp_dept_pkg; Mark for Review (1) Points True (*) False
Correct
izat izatio ion n bloc block? k? (1) Points
7. Which of the following best describes a package initial Mark Mark for for Rev Revie iew w
It is a named procedure in a package which must be invoked by a user bef ore any other part of the package can be invoked. It is an anonymous block in the package specification. It is an anonymous block at the end of a package body which executes aut omatically the first time each user session invokes a subprogram in the package. (*) It is a private function within the package body. Because it is an anonymous block, it cannot be invoked and therefore wil l never execute. It is treated as a set of comments.
Incorrect. Refer to Section 10 Lesson 3.
8. Package FORWARD_PACK contains two procedures: PROC1 is public while PROC2 is private (not declared in the package specification). These procedures have no parameters. Which of the following package bodies will NOT c ompile successfully? (Choose two.) Mar Mark for Review (1) Points (Choose all correct answers) CREATE OR REPLACE PACKAGE BODY forward_pack IS PROCEDURE proc1 IS BEGIN proc2; END; PROCEDURE proc2 IS BEGIN DBMS_OUTPUT.PUT_LINE('Any message'); END; END forward_pack; (*)
CREATE OR REPLACE PACKAGE BODY forward_pack IS PROCEDURE proc2 IS BEGIN DBMS_OUTPUT.PUT_LINE('Any message'); END; PROCEDURE proc1 IS BEGIN proc2; END; END forward_pack; CREATE OR REPLACE PACKAGE BODY forward_pack IS PROCEDURE proc2; PROCEDURE proc1 IS BEGIN proc2; END; PROCEDURE proc2 IS BEGIN DBMS_OUTPUT.PUT_LINE('Any message'); END; END forward_pack; CREATE OR REPLACE PACKAGE BODY forward_pack IS PROCEDURE proc1; PROCEDURE proc1 IS BEGIN proc2; END; PROCEDURE proc2 IS proc1; END; END forward_pack; (*)
CREATE OR REPLACE PACKAGE BODY forward_pack IS PROCEDURE proc2; PROCEDURE proc1 IS BEGIN proc2; END; PROCEDURE proc2 IS BEGIN proc1; END; END forward_pack;
Incorrect. Refer to Section 10 Lesson 3. 9.
A bodiless package age contains what?
Mark for Review
(1) Points Procedures only Functions only Public variables only (*) Private variables only
Incorrect. Refer to Section 10 Lesson 3. 10. If a subprogram is public (declared in the package spec ification), its detailed code can be written anywhere in the package body withou t the need need to use use forward forward decla declarat ration ions. s. True True or False? False? Mark Mark for Revie Review w (1) Points True (*) False
Correct 11. INDEX BY is missing from this package declaration. Selec t the most efficient declaration. CREATE OR REPLACE PACKAGE emp_pkg IS TYPE emp_tab IS TABLE OF employees%ROWTYPE; PROCEDURE get_employees(p_emp_table OUT emp_tab);
END emp_pkg; Mark for Review (1) Points INDEX BY INTEGER INDEX BY BINARY INDEX BY BINARY_INTEGER (*) INDEX ALL
Incorrect. Refer to Section 10 Lesson 3. 12. Which one of the following is NOT a restriction on a pa ckag ckage e fun funct ctio ion n cal calle led d fro from m a SQL SQL stat statem emen ent? t? Mark Mark for for Rev Revie iew w (1) Points The function can include a COMMIT. The function can be overloaded. (*) The function can include a ROLLBACK. The function can return a BOOLEAN.
Incorrect. Refer to Section 10 Lesson 3. 13. When using a package function in DML statements, which rule rules s mus must t you you foll follow ow? ? (Ch (Choo oose se thre three) e) Mark Mark for for Revi Review ew (1) Points (Choose all correct answers) Must not end the current transaction (*) Can read or modify the table being changed by that DML statement Changes to a package variable could have an impact on another stored fun ction (*)
Cannot execute a DML statement or modify the database (*)
Incorrect. Refer to Section 10 Lesson 3. 14. ckage? 1 FUNCTION 2 FUNCTION 3 FUNCTION 4 FUNCTION
get_emp get_emp get_emp get_emp
Which two of these functions could not be in the same pa (p1 (p1 (p1 (p1
DATE) RETURN VARCHAR2; DATE, p2 NUMBER) RETURN VARCHAR2; DATE, p2 NUMBER) RETURN NUMBER; NUMBER, p2 DATE) RETURN VARCHAR2;
Mark for Review (1) Points 1 and 2 1 and 4 2 and 4 2 and 3 (*) 3 and 4
Incorrect. Refer to Section 10 Lesson 3. 1. In the following example, which statement best fits in L ine 1? (Choose 1) DECLARE v_more_rows_exist BOOLEAN := TRUE; BEGIN -- Line 1 LOOP v_more_rows_exist := curs_pkg.fetch_n_rows(3); DBMS_OUTPUT.PUT_LINE('-------'); EXIT WHEN NOT v_more_rows_exist; END LOOP; curs_pkg.close_curs; END; Mark for Review (1) Points curs_pkg.emp_curs%ISOPEN; curs_pkg.close_curs; curs_pkg.open_curs; (*)
EXIT WHEN curs_pkg.emp_curs%NOTFOUND;
Incorrect. Refer to Section 11 Lesson 1. 2. A package's state is initialized when the package is fi rst loaded. True or False? Mark for Review (1) Points True (*) False
Correct 3. A cursor's state is defined only by whether it is open or closed closed and, if open, open, how how many many rows rows it holds. True or False? False? Mark for Review Review (1) Points True False (*)
Incorrect. Refer to Section 11 Lesson 1. 4. Users A and B call the same procedure in a package to in itialize a global variable my_pkg.g_var. What will be the value of my_pkg.g_var for User A at Point A? User A: my_pkg.g_var is 10 User B: my_pkg.g_var is 10 User A: my_pkg.g_var is 50 User B: my_pkg.g_var is 25 Point A Mark for Review (1) Points 10 50 (*) 25
Correct 1. Which DBMS_OUTPUT package subprogram places text into th e buffer at Line 1? IF v_bool1 AND NOT v_bool2 AND v_number < 25 THEN --Line 1 ELSE ... END IF; DBMS_OUTPUT.NEW_LINE; Mark for Review (1) Points DBMS_OUTPUT.PUT('IF branch was executed'); (*) DBMS_OUTPUT.PUT_LINE('IF branch was executed'); DBMS_OUTPUT.GET_LINE('IF branch was executed'); DBMS_OUTPUT.NEW_LINE('IF branch was executed');
Incorrect. Refer to Section 11 Lesson 2. 2. The DBMS_OUTPUT package is useful for which of the foll owing owing activi activitie ties? s? (Choos (Choose e two) two) Mark Mark for for Revi Review ew (1) Points (Choose all correct answers) Interact with a user during execution of a function or procedure. Display results to the developer during testing for debugging purposes. (*) Trace the code execution path for a function or procedure. (*) Write operating system text files to the user's screen.
Correct 3. The UTL_FILE package contains several exceptions exclus ively ively used used in this this package package. . Which Which are they? they? (Choo (Choose se 3) Mark Mark for Revie Review w (1) Points
(Choose all correct answers) INVALID_PATH (*) NO_DATA_FOUND WRITE_ERROR (*) INVALID_OPERATION (*) ZERO_DIVIDE
Incorrect. Refer to Section 11 Lesson 2. 4. The UTL_FILE package can be used to read and write bina ry file files s suc such h as as JPE JPEGs Gs as well well as text text files files. . Tru True e or or Fal False se? ? Mark Mark for for Rev Revie iew w (1) Points True False (*)
Correct 5. package? (Choose 2) (1) Points
Which general exceptions may be handled by the UTL_FILE Mark for Review (Choose all correct answers)
TOO_MANY_ROWS VALUE_ERROR (*) ZERO_DIVIDE NO_DATA_FOUND (*)
Correct
6. The DBMS_OUTPUT gives programmers an easy-to-use interf ace to see for instance the current value of a loop counter or if a program make s it to a particular branch of an IF statem tement. (True or False?) Mark for Review (1) Points True (*) False
Correct 7. Mark for Review (1) Points (Choose all correct answers) It is used to append to a file until processing is complete. (*) It is used to read and write text files stored outside the database. (*) It is used to find out how much free space is left on an operating syste m disk. It is used to manipulate large object data type items in columns.
Incorrect. Refer to Section 11 Lesson 2. 1. What will happen when the following procedure is invoke d? CREATE OR REPLACE PROCEDURE do_some_work IS CURSOR c_curs IS SELECT object_name FROM user_objects WHERE object_type = 'FUNCTION'; BEGIN FOR v_curs_rec IN c_curs LOOP EXECUTE IMMEDIATE 'ALTER FUNCTION ' v_curs_rec.object_name ' COMPIL E'; EXIT WHEN c_curs%ROWCOUNT > 2; END LOOP; END; Mark for Review (1) Points All functions in the user's schema will be recompiled. The first two functions in the user's schema will be recompiled.
The first three functions in the user's schema will be recompiled. (*) The procedure will not compile successfully because you cannot ALTER fun ctions using Dynamic SQL. The procedure will not compile successfully because the syntax of the AL TER FUNCTION statement is incorrect.
Correct 2. When SQL statements are included within a procedure, th e statement statements s are parsed when the procedu procedure re is compiled. compiled. True True or or False? False? Mark for Review (1) Points True (*) False
Correct 3.
Name two reasons for using Dynamic SQL.
Mark for
Review (1) Points (Choose all correct answers) Avoid errrors at compile time of DML statements. Create a SQL statement with varying column data, or different conditions . (*) Enables data-definition statements to be written and executed from PL/SQ L. (*) Enables system control statements to be written and executed from PL/SQL .
Incorrect. Refer to Section 12 Lesson 1. 4.
Examine the following procedure, which drops a table wh
ose name is passed as an IN parameter: CREATE OR REPLACE PROCEDURE drop_tab (p_table_name IN VARCHAR2) IS v_sql_statement VARCHAR2(100); BEGIN ... END; Which of the following will work correctly when coded in the procedure's executa ble section? (Choose two.) Mark for Review (1) Points (Choose all correct answers) EXECUTE IMMEDIATE 'DROP TABLE p_table_name'; EXECUTE IMMEDIATE 'DROP TABLE ' p_table_name; (*)
v_sql_statement := 'DROP TABLE ' p_table_name; EXECUTE IMMEDIATE v_sql_statement; (*)
v_sql_statement := 'DROP TABLE ' p_table_name; EXECUTE IMMEDIATE 'v_sql_statement'; v_sql_statement := 'DROP TABLE '; EXECUTE IMMEDIATE v_sql_statement p_table_name;
Correct 5. DIAT DIATE. E. True True or Fals False? e? (1) Points
The DBMS_SQL package is easier to use than EXECUTE IMME Mark Mark for for Rev Revie iew w
True False (*)
Correct 6. For which of the following is it necessary to use Dynam ic SQL? SQL? (Choose (Choose three.) three.) Mark for Review Review
(1) Points (Choose all correct answers) ALTER (*) GRANT (*) SAVEPOINT UPDATE DROP (*)
Incorrect. Refer to Section 12 Lesson 1. 7. r ee . ) Mark for Review (1) Points
What happens when a SQL statement is parsed? (Choose th
(Choose all correct answers) The user's required privileges are checked. (*) The syntax of the statement is checked. (*) The statement is executed. The results of the statement are returned to the user. Oracle queries the Data Dictionary to make sure that the tables referenc ed in the SQL statement exist. (*)
Incorrect. Refer to Section 12 Lesson 1. 8. tabl table. e. True True or Fals False? e? (1) Points True False (*)
Only one call to DBMS_SQL is needed in order to drop a Mark Mark for for Rev Revie iew w
Incorrect. Refer to Section 12 Lesson 1. 9. A programmer wants to code a procedure which will creat e a table with a single column. The datatype of the column will be chosen by the user who invokes the procedure. The programmer writes the following code: CREATE OR REPLACE PROCEDURE create_tab (p_col_datatype IN VARCHAR2) IS BEGIN CREATE TABLE newtab (only_col p_col_datatype); END; Why will this procedure not compile successfully? Mark for Review (1) Points Because you cannot create a table inside a procedure Because the invoking user may not have CREATE TABLE privilege Because when the procedure is compiled, Oracle cannot check if the param eter value passed into the procedure is a valid column datatype (*) Because table NEWTAB may already exist None of the above; the procedure will compile successfully
Incorrect. Refer to Section 12 Lesson 1. 1. In the following example, where do you place the phrase BULK COLLECT? ... BEGIN SELECT -- Position A salary -- Position B INTO v_saltab -- Position C FROM employees WHERE department_id = 20 ORDER BY salary -- Position D ; ... Mark for Review (1) Points Position A Position B (*)
Position C Position D
Correct 2. ? Mark for Review (1) Points
What is the main purpose for using the RETURNING clause
Improve performance by returning a single value. Improve performance by minimizing the number of statements. Improve performance by making one call to the SQL engine. (*) Return more readily any exceptions that are raised by the statement.
Incorrect. Refer to Section 12 Lesson 2. 3. The following statement is a valid example of using the RETURNING clause. True or False? DECLARE TYPE EmpRec IS RECORD (last_name employees.last_name%TYPE, salary employees.sa lary%TYPE); emp_info EmpRec; emp_id NUMBER := 100; BEGIN UPDATE employees SET salary = salary * 1.1 WHERE employee_id = emp_id RETURNING last_name, salary INTO emp_info; dbms_output.put_line('Just gave a raise to ' emp_info.last_name ', who n ow makes ' emp_info.salary); END; Mark for Review (1) Points True (*) False
Incorrect. Refer to Section 12 Lesson 2. 4.
What are benefits of using the NOCOPY hint? (Choose two
) Mark for Review (1) Points (Choose all correct answers) Safer because it uses passing by value. Efficient since it uses less memory. (*) Uses a larger block of server memory for faster access. Faster because a single copy of the data is used. (*)
Correct 5.
In the following example, where do you place the phrase
BULK COLLECT? DECLARE TYPE NameList IS TABLE OF emp.ename%TYPE; names NameList; CURSOR c1 IS SELECT ename -- Position A FROM emp WHERE job = 'CLERK'; BEGIN OPEN c1; FETCH c1 -- Position B INTO -- Position C names; ... CLOSE c1; END; Mark for Review (1) Points Position A Position B (*) Position C
Correct 6. A function-based index may be made using your own funct ions, but only if the function is created using the DETERMINISTIC clause. True o r F a ls e ? Mark for Review (1) Points
True (*) False
Correct
or False? (1) Points
7. FORALL can only be used with the INSERT statement. True Mark for Review
True False (*)
Correct 8.
In the following example, where do you place the phrase
DETERMINISTIC? CREATE OR REPLACE FUNCTION total_sal (p_dept_id IN -- Position A employees.department_id%TYPE) RETURN NUMBER -- Position B IS v_total_sal NUMBER; BEGIN SELECT SUM(salary) INTO v_total_sal FROM employees WHERE department_id = p_dept_in; RETURN v_total_sal -- Position C; END total_sal; Mark for Review (1) Points Position A Position B (*) Position C
Correct 9. What is wrong with this code example? CREATE OR REPLACE PROCEDURE insert_emps IS TYPE t_emp IS TABLE OF employees%ROWTYPE INDEX BY BINARY_INTEGER; v_emptab t_emp; BEGIN
FORALL i IN v_emptab.FIRST..v_emptab.LAST INSERT INTO employees VALUES v_emptab(i); END LOOP; END insert_emps; Mark for Review (1) Points The phrase should be FOR ALL. v_emptab is incorrectly typed. FORALL does not require END LOOP. (*) Nothing is wrong; it will compile successfully.
Incorrect. Refer to Section 12 Lesson 2. 10.
The following procedure compiles successfully. True or F
alse? CREATE OR REPLACE PACKAGE emp_pkg IS TYPE t_emp IS TABLE OF employees%ROWTYPE INDEX BY BINARY_INTEGER; PROCEDURE emp_proc (p_small_arg IN NUMBER, p_big_arg NOCOPY OUT t_emp); ... END emp_pkg; Mark for Review (1) Points True False (*)
Incorrect. Refer to Section 12 Lesson 2. 1. A database trigger is a PL/SQL stored subprogram which is explicitly invoked just like a procedure or a function. True or False? Mark for Review (1) Points True False (*)
Correct
2. Which of the following are NOT allowed within a databas e trigger? trigger? (Choose (Choose two) two) Mark for Review Review (1) Points (Choose all correct answers) COMMIT (*) A call to a packaged procedure INSERT A Boolean variable SAVEPOINT (*)
Incorrect. Refer to Section 13 Lesson 1. 3. A business rule states that an employee's salary must b e between 4000 and 30000. We could enforce this rule using a check constraint, b ut it is bett better er to use use a data databa base se trig trigge ger. r. True True or Fals False? e? Mark Mark for for Revi Review ew (1) Points True False (*)
Incorrect. Refer to Section 13 Lesson 1. 4. Which of the following are good guidelines to follow wh en creating triggers? (Choose two) Mar Mark for Review (1) Points (Choose all correct answers) Be aware of recursive and cascading effects (*) Where possible, use triggers to enforce NOT NULL constraints Avoid lengthy trigger logic by creating a procedure and invoking it from within the trigger (*)
Use triggers to replace functionality which is already built into the da tabase Always create more triggers than you need, because it is better to be sa fe
Incorrect. Refer to Section 13 Lesson 1. 5. r? Mark for Review (1) Points
Which of the following best describes a database trigge
It allows users to log on to the database It executes automatically whenever a particular event occurs within the database (*) It prevents unique constraints from being violated It executes automatically whenever a user clicks on a button with their mouse It allows foreign key constraints to be violated
Correct 6. While editing a document in Microsoft Word, you go to t he FILE menu and SAVE your work. To do this, Microsoft Word has executed an appl ication ication trigger. trigger. True True or False? Mark for for Review Review (1) Points True (*) False
Correct 7. You can use a database trigger to prevent invalid trans acti action ons s from from bei being ng com commi mitt tted ed. . True True or or Fals False? e? Mark Mark for for Rev Revie iew w (1) Points
True (*) False
Correct
t r i g ge r ? (1) Points
8. Which of the following could NOT be done by a database Mark for Review
Enforcing a complex business rule Enforcing a complex database security check Recalculating the total salary bill for a department whenever an employe e's salary is changed Ensuring that a student never arrives late for a class (*) Keeping a log of how many rows have been inserted into a table
Incorrect. Refer to Section 13 Lesson 1. 9. Which of the following events could NOT automatically f ire a database database trigger? trigger? Mark for Review Review (1) Points A user logging on to the database A SQL INSERT statement You click your mouse on a button to choose the correct answer to this qu estion (*) A DML operation on a view The Database Administrator shuts down the database
Incorrect. Refer to Section 13 Lesson 1.
10. A user's schema contains procedure MYPROC, function MYF UNC, trigger MYTRIGG and package MYPACK which contains a public procedure PACKPR OC. These subprograms have no parameters, and the function returns a NUMBER. Whi ch of the following calls to these objects (from an anonymous block) are incorre ct? (Choose two) Mark for Review (1) Points (Choose all correct answers) mypack.packproc; mytrigg; (*) myproc; v_number := myfunc; IF NOT myfunc THEN ... (*)
Incorrect. Refer to Section 13 Lesson 1. 1. An AFTER UPDATE trigger can specify more than one colum n. True or False? Mark for Review (1) Points True (*) False
Incorrect. Refer to Section 13 Lesson 2. 2. We want to prevent employees from being deleted on Sund ays. To do this, we create the following trigger: CREATE OR REPLACE TRIGGER stop_del_emps ....... DELETE ON employees -- Line A BEGIN IF TO_CHAR(SYSDATE','DY') = 'SUN' THEN RAISE_APPLICATION_ERROR(-20101,'Invalid delete'); END IF; END; Should this be a BEFORE or AFTER trigger, and why? Mark for Review (1) Points It should be a BEFORE trigger because if an AFTER trigger were created, the employee would already have been deleted by the time the trigger checks the
date. (*) It should be a BEFORE trigger because you cannot use RAISE_APPLICATION_E RROR with AFTER triggers. It should be an AFTER trigger because the Oracle Server cannot fire the trigger until it knows that the employee has been deleted. It does not matter, either a BEFORE or an AFTER trigger could be created .
Correct 3. Which of the following are possible keywords for the ti ming ming com compo pone nent nt of of a tri trigg gger er? ? (Cho (Choos ose e thre three. e.) ) Mark Mark for for Rev Revie iew w (1) Points (Choose all correct answers) BEFORE (*) INSTEAD WHENEVER INSTEAD OF (*) AFTER (*)
Incorrect. Refer to Section 13 Lesson 2. 4. What is wrong with the following code? CREATE OR REPLACE TRIGGER mytrigg AFTER DELETE ON departments BEGIN INSERT INTO audit_table (who, when) VALUES (USER, SYSDATE); COMMIT; END; Mark for Review (1) Points A DML trigger cannot itself contain a DML statement such as INSERT INTO audit_table
You cannot use COMMIT inside a trigger. (*) The last line of code should be END mytrigg; The second line should be: AFTER DELETE OF DEPARTMENTS Nothing is wrong, the trigger will execute successfully
Incorrect. Refer to Section 13 Lesson 2. 5. There are five employees in department 50. A statement trigger is created by: CREATE OR REPLACE TRIGGER emp_upd_trigg AFTER DELETE ON EMPLOYEES BEGIN ... A user now executes: DELETE FROM employees WHERE department_id = 50; How many times will the trigger fire, and when? Mark for Review (1) Points Once, before the DELETE is executed Five times, after each employee row is deleted Once, after the DELETE is executed (*) Six times, once after each row and once at the end of the statement The trigger will not fire at all
Correct 6. Which of the following is the correct syntax for creati ng a DML trigger associated with the EMPLOYEES table? The trigger must fire when ever an employee's JOB_ID is updated, but not if a different column is updated. Mark for Review (1) Points CREATE TRIGGER job_upd_trigg AFTER UPDATE ON employees(job_id)
BEGIN ... CREATE TRIGGER job_upd_trigg WHENEVER UPDATE OF job_id IN employees BEGIN ... CREATE TRIGGER job_upd_trigg AFTER UPDATE ON employees.job_id BEGIN ... CREATE TRIGGER job_upd_trigg AFTER UPDATE OF job_id ON employees BEGIN ... (*)
Incorrect. Refer to Section 13 Lesson 2. 7. A BEFORE statement trigger inserts a row into a logging table every time a user updates the salary column of the employees table. The u ser now tries to update the salaries of three employees with a single UPDATE sta tement, but the update fails because it violates a check constraint. How many ro ws will will be inse insert rted ed into into the the log loggi ging ng tabl table? e? Mark Mark for for Rev Revie iew w (1) Points None, the transactions are rolled back because the update failed. (*) One Three Four None of the above
Incorrect. Refer to Section 13 Lesson 2. 8. We want to create a log record automatically every time any DML operation is executed on either or both of the EMPLOYEES and DEPARTMENT S tables. What is the smallest number of triggers that must be create to do this ? Mark for Review (1) Points One
Two (*) Three Six Eight
Correct 1. A row trigger has been created which is fired by UPDATE ON employees. A user now executes a single SQL statement which updates four row s of of the the EMPL EMPLOY OYEE EES S tab table le. . How How many many time times s will will the the row row trig trigge ger r fir fire? e? Mark Mark for for Review (1) Points Once Twice Four times (*) Five times Eight times
Incorrect. Refer to Section 13 Lesson 3. 2. The OLD and NEW qualifiers can be used with statement t riggers riggers as well as row row triggers. triggers. True True or False? Mark for Review Review (1) Points True False (*)
Incorrect. Refer to Section 13 Lesson 3. 3. Which of the following statements about INSTEAD OF trig gers are NOT true? (Choose two.) Mark for Review (1) Points
(Choose all correct answers) They can be created on a table. (*) They can be created on a simple view. They can be created on a complex view. They can be statement triggers. (*) They can be row triggers.
Incorrect. Refer to Section 13 Lesson 3. 4. You decide to create the following trigger: CREATE OR REPLACE TRIGGER empl_trigg BEFORE UPDATE ON employees BEGIN -- Line A RAISE_APPLICATION_ERROR('Cannot update salary'); ELSE INSERT INTO log_table values (USER, SYSDATE); END IF; END; You want the trigger to prevent updates to the SALARY column, but allow updates to all other columns. What should you code at Line A? Mark for Review (1) Points IF UPDATING SALARY THEN IF UPDATING('SALARY') THEN (*) IF UPDATE('SALARY') THEN IF UPDATING(SALARY) THEN IF UPDATE(SALARY) THEN
Incorrect. Refer to Section 13 Lesson 3. 5.
What is wrong with this compound trigger example?
CREATE OR REPLACE TRIGGER compound_trigger FOR UPDATE OF salary COMPOUND TRIGGER threshold CONSTANT SIMPLE_INTEGER := 200; BEFORE EACH ROW IS BEGIN -- some action END BEFORE EACH ROW; AFTER EACH ROW IS BEGIN -- some action END AFTER EACH ROW; AFTER STATEMENT IS BEGIN -- some action END AFTER STATEMENT; END compound_trigger; Mark for Review (1) Points Missing BEFORE timing statement. Missing the EXCEPTION section. Missing name of table on which the trigger fires. (*) Missing the INSTEAD OF timing section. Missing the BEFORE and INSTEAD OF timing sections.
Incorrect. Refer to Section 13 Lesson 3. 6. cates in a trigger? (1) Points
Which of the following best describes conditional predi Mark for Review
They are special variables which must be DECLAREd within the trigger. They allow the trigger code to see what data values are being inserted i nto a row. They are automatically declared boolean variables which allow the trigge r body to detect which DML operation is being executed. (*) They are special cursor attributes, like %ROWCOUNT and %NOTFOUND
Correct 7. Whenever an employee's JOB_ID is updated, we want to in sert a row into a logging table to record the employee_id and the new value of J OB_ID. We create a row trigger whose body includes the following code: BEGIN INSERT INTO logging_table (emp_id, job_id) VALUES -- Point A END; At point A, which of the following will insert the correct data into the logging table? (Choose two.) Mark for Review (1) Points (Choose all correct answers) (:OLD.employee_id, :OLD.job_id); (:OLD.employee_id, :NEW.job_id); (*) (:NEW.employee_id, :OLD.job_id); (:NEW.employee_id, :NEW.job_id); (*) (NEW.employee_id, NEW.job_id);
Incorrect. Refer to Section 13 Lesson 3. 8. Examine the following code. To create a row trigger, wh at code should be included at Line A? CREATE OR REPLACE TRIGGER del_emp_trigg BEFORE DELETE ON employees ---- Line A BEGIN ... Mark for Review (1) Points FOR EVERY ROW FOR EACH ROW (*) FOR EVERY ROW
FOR ALL ROWS Nothing is needed because DML triggers are row triggers by default.
Correct 9. The following view and trigger have been created: CREATE VIEW dept_view AS SELECT * FROM departments; CREATE OR REPLACE TRIGGER dept_view_trigg INSTEAD OF UPDATE ON dept_view BEGIN DBMS_OUTPUT.PUT_LINE('Sample Message'); END; Departments 50 and 80 exist but department 81 does not. A user now executes the following statement: UPDATE dept_view SET department_name = 'Sales' WHERE department_id IN (50,80,81); What happens? Mark for Review (1) Points Two rows are updated and "Sample Message" is displayed once. No rows are updated and "Sample Message" is displayed once. No rows are updated and "Sample Message" is displayed twice. (*) No rows are updated and "Sample Message" is displayed three times. None of the above.
Incorrect. Refer to Section 13 Lesson 3. 10.
What are the timing events for a compound trigger?
Mark for Review (1) Points Before the triggering statement; After the triggering statement; Instead of the triggering statement. Before the triggering statement; Before each row; After each row; After
the triggering statement. (*) Before the triggering statement; After the triggering statement; After e ach row. Before the triggering statement; Before each row; After the triggering s tatement.
Correct 1. You can create a trigger which prevents DDL statements on an individual table, while still allowing DDL on other tables in the same sch ema. True or False? Mark for Review (1) Points True False (*)
Correct 2. What is wrong with the following code? CREATE OR REPLACE TRIGGER call_trigg AFTER UPDATE OR DELETE ON employees BEGIN CALL del_emp_proc END; Mark for Review (1) Points When CALL is used, the END; statement should be omitted. (*) The CALL statement should end with a semicolon (;) You cannot use a CALL statement in a DML trigger. When using CALL, only one DML statement can be tested, so UPDATE OR DELE TE is wrong.
Correct 3. The database administrator creates a trigger that autom atically disconnects user HACKER whenever HACKER connects to the database. What type of trigger is this? Mark for Review
(1) Points A DDL trigger A Database Event trigger (*) A DML trigger A statement trigger An INSTEAD OF trigger
Incorrect. Refer to Section 13 Lesson 4. 4. What is wrong with the following code? CREATE OR REPLACE TRIGGER emp_dml_trigg BEFORE UPDATE OF salary ON employees FOR EACH ROW DECLARE v_max_sal employees.salary%TYPE; BEGIN SELECT max(sal) INTO v_max_sal FROM employees; END; Mark for Review (1) Points You cannot use a DECLARE statement in a trigger. The trigger body is reading the same table (employees) that the triggeri ng event is updating. (*) You must use RAISE_APPLICATION_ERROR in a BEFORE trigger. You can never use SELECT inside a DML trigger. Nothing is wrong, the trigger will execute correctly.
Incorrect. Refer to Section 13 Lesson 4. 5. The database administrator wants to write a log record every time any user's session raises an ORA-00942 exception. The DBA decides to create the following trigger: CREATE OR REPLACE TRIGGER log_942_trigg AFTER SERVERERROR ON DATABASE
BEGIN -- Line A INSERT INTO log_table VALUES ( ...); END; What should the DBA code at Line A? Mark for Review (1) Points IF (SERVERERROR(942)) THEN IF (IS_SERVERERROR(942)) THEN (*) IF (SERVERERROR = 942) THEN IF (IS_SERVERERROR = 942) THEN IF (IS_SERVERERROR(ORA-00942)) THEN
Incorrect. Refer to Section 13 Lesson 4. 6. Which of the following could NOT cause a DDL or Databas e Event trigger to fire? Mark for Review (1) Points A table is dropped. A user connects to the database. The DBA starts up the database. A user deletes rows from the EMPLOYEES table. (*) A specific exception is raised in a user's session.
Incorrect. Refer to Section 13 Lesson 4. 7. User HARJIT wants to prevent any objects which he owns from being dropped. Harjit decides to execute the following code: CREATE OR REPLACE TRIGGER stop_drop ---- Line A BEGIN RAISE_APPLICATION_ERROR(-20201,'Attempted drop'); END;
What should Harjit code at Line A? Mark for Review (1) Points BEFORE DROP ON HARJIT BEFORE DROP ON TABLE BEFORE DROP ON SCHEMA (*) BEFORE DROP ON OWNER BEFORE DROP ON USER_OBJECTS
Incorrect. Refer to Section 13 Lesson 4. 8. You have been granted CREATE TRIGGER privilege. You can now create create an AFTE AFTER R LOGOF LOGOFF F ON SCHEMA SCHEMA trigger trigger. . True True or or False False? ? Mark Mark for for Revie Review w (1) Points True False (*)
Incorrect. Refer to Section 13 Lesson 4. 9. Mutating table errors can be caused by DML triggers, bu t not by database event triggers. True or Fa False? Mark for Review (1) Points True (*) False
Correct
igger body? (1) Points
10. What is the benefit of using the CALL statement in a tr Mark for Review
It allow both DDL events and database events to be handled by a single t rigger. It prevents data being read from a mutating table. It allows the database administrator to monitor who is currently connect ed to the database. It allows the trigger body code to be placed in a separate procedure. (* )
Incorrect. Refer to Section 13 Lesson 4. . User KULJIT creates two triggers named EMP1_TRIGG and E MP2_TRIGG, which are both DML triggers referencing her EMPLOYEES table. Kuljit n ow wants to remove both of these triggers from the database. What command(s) sho uld Kuljit use to do this? Mark for Review (1) Points DROP ALL TRIGGERS ON employees; DROP TRIGGERS ON employees; DROP TRIGGER emp1_trigg; DROP TRIGGER emp2_trigg; (*)
DROP TRIGGER emp1_trigg AND emp2_trigg;
Incorrect. Refer to Section 13 Lesson 5. 2. You have created several DML triggers which reference y our DEPARTMENTS table. Now you want to disable all of them using a single SQL st atemen atement. t. Whic Which h comma command nd shou should ld you you use? use? Mark for Review Review (1) Points ALTER TRIGGER DISABLE ALL ON departments; ALTER TABLE departments DISABLE ALL TRIGGERS; (*) ALTER TABLE departments DISABLE TRIGGERS; DROP ALL TRIGGERS ON departments;
Correct 3. Which dictionary view would you query to see the detail ed body code of triggers in your schema? Mark for Review (1) Points USER_SOURCE USER_TRIGGER USER_TRIGGERS (*) USER_OBJECTS None of the above, you cannot view the code of the trigger body after th e trigger has been created.
Correct 4. A user creates the following trigger: CREATE OR REPLACE TRIGGER emp_trigg AFTER DELETE ON employees BEGIN ... END; The user now tries to drop the EMPLOYEES table. What happens? Mark for Review (1) Points The table is dropped but the trigger is not dropped. An error message is displayed because you cannot drop a table that is re ferenced by a trigger. The table is dropped and the trigger is disabled. Both the table and the trigger are dropped. (*)
Incorrect. Refer to Section 13 Lesson 5.
5. enab enable led d or or dis disab able led? d? (1) Points
Which command would you use to see if your triggers are Mark Mark for for Rev Revie iew w
SELECT trigger_name, status FROM USER_TRIGGERS; (*)
SELECT object_name, status FROM USER_OBJECTS WHERE object_type = 'TRIGGER'; SELECT trigger_name, trigger_type FROM USER_TRIGGERS; DESCRIBE TRIGGER
Incorrect. Refer to Section 13 Lesson 5. 6. By default, any user can create a DML trigger on a tabl e in in hi his/her sc schema. Tr True or or Fa False? Mar Mark fo for Re Review (1) Points True False (*)
Correct
