Section 1 (Answer all questions in this section) Quiz: Introduction to PL/SQL 1. PL/SQL stands for: (1) Points
Mark for Review
Processing Language for SQL. Procedural Language extension for SQL. (*) Primary Language for SQL. Proprietary Language for SQL. Correct 2. Which of the following statements is true? (1) Points
Mark for Review
PL/SQL is an Oracle proprietary, procedural, 3GL programming language. (*) PL/SQL is an Oracle proprietary, procedural, 4GL programming language. PL/SQL is an Oracle proprietary, nonprocedural, 3GL programming language. PL/SQL is an ANSI-compliant, procedural programming language. Correct 3. Which of the following statements is true? (1) Points
Mark for Review
You can embed PL/SQL statements within SQL code. You can embed SQL statements within PL/SQL code. (*) You can embed procedural constructs within SQL code. None. Correct 4. Which of the following statements about SQL is true? (1) Points
Mark for Review
SQL is an Oracle proprietary, nonprocedural, 4GL programming language. SQL is an Oracle proprietary, procedural, 3GL programming language. SQL is an ANSI-compliant, nonprocedural, 4GL programming language. (*) SQL is an ANSI-compliant, procedural, 4GL programming language.
Correct 5. In which three ways does PL/SQL extend the SQL programming language? k for Review (1) Points
Mar
(Choose all correct answers) By adding procedural constructs. (*) By adding compound constructs. By adding iterative control. (*) By adding conditional control. (*) Correct 6. Nonprocedural languages languages allow the programmer to produce a result when a se se ries of steps are followed. True or False? Mark for Review (1) Points True False (*) Correct
Quiz: Benefits to PL/SQL 1. Which of the following can be done using PL/SQL? (1) Points
Mark for Review
Create complex applications. Retrieve and modify data in Oracle database tables. Manage database tasks such as security. Create custom reports. All of the above (*) Incorrect. Refer to Section 1 Lesson 2. 2. You can create a Web site application written written entirely in PL/SQL. True or False? Mark for Review (1) Points True (*)
False Incorrect. Refer to Section 1 Lesson 2. 3. PL/SQL differs from from C and Java Java in which of the following ways? (Choose two two .) Mark for Review (1) Points (Choose all correct answers) It requires an Oracle database or tool. (*) It does not support object-oriented programming. It is the most efficient language to use with an Oracle database. (*) It is the most complex programming language to learn. It is not portable to other operating systems. Incorrect. Refer to Section 1 Lesson 2. 4. Procedural constructs constructs give you better control of your SQL statements and t heir execution. True or False? Mark for Review (1) Points True (*) False Incorrect. Refer to Section 1 Lesson 2. 5. Which of the following can be be compiled as a standalone program program outside the the database? Mark for Review (1) Points A program developed in PL/SQL A program developed in Java A program developed in C All the above Programs developed in Java or C, but not in PL/SQL. (*) Correct 6. When multiple SQL statements are combined into PL/SQL blocks, performance performance improves. True or False? Mark for Review (1) Points
True (*) False Correct Quiz: Creating PL/SQL Blocks 1. What are the characteristics of an anonymous block? (Choose two.) Review (1) Points
Mark for
(Choose all correct answers) Unnamed (*) Stored in the database Compiled each time the application is executed (*) Can be declared as procedures or as functions Correct 2. What are the characteristics of a PL/SQL stored subprogram? (Choose two.) Mark for Review (1) Points (Choose all correct answers) Named (*) Not stored in the database Can be invoked at any time (*) Do not exist after they are executed Correct 3. In a PL/SQL block, which of the following should not be followed by a semi colon? Mark for Review (1) Points DECLARE (*) END All SQL statements All PL/SQL statements Correct
4. Which statements are mandatory in a PL/SQL block? (Choose two.) Review (1) Points
Mark for
(Choose all correct answers) DECLARE BEGIN (*) EXCEPTION END; (*) Correct 5. How can you display results to check that a PL/SQL block is working correc tly? Mark for Review (1) Points You don't need to do anything, the results will display automatically. Use an Exception section Use DBMS_OUTPUT.PUT_LINE (*) Write a C or Java program to display the results Correct 6. What is wrong with this PL/SQL anonymous block? BEGIN DBMS_OUTPUT.PUT_LINE('Hello'); DBMS_OUTPUT.PUT_LINE(' and Goodbye'); Mark for Review (1) Points The Declaration section is missing The Exception section is missing There is nothing wrong with the block, it will work fine. The END; statement is missing (*) Correct 7. Which sections of a PL/SQL block are optional? (1) Points Declaration and Executable
Mark for Review
Declaration and Exception (*) Exception only Executable only Correct 8. Which of the following is NOT a PL/SQL programming environment? Review (1) Points
Mark for
Oracle jDeveloper SQL*Plus gSQL*Plus (*) SQL Workshop in Application Express Correct 9. Which of the following is a PL/SQL programming environment? iew (1) Points
Mark for Rev
Oracle Cdeveloper Java*Plus PL/SQL Express SQL*Workshop in Application Express (*) Correct 10. This PL/SQL anonymous block will execute successfully. True or False? DECLARE v_date DATE := SYSDATE; DBMS_OUTPUT.PUT_LINE(v_date); END; Mark for Review (1) Points True False (*) Correct 11. Which lines of code will correctly display the message "Hello World" ? (C hoose two.) Mark for Review
(1) Points (Choose all correct answers) DBMS_OUTPUT('Hello World'); DBMS_OUTPUT.PUT_LINE('Hello World'); (*) DBMS_OUTPUT.PUT_LINE('Hello' || 'World'); DBMS_OUTPUT.PUT_LINE('Hello' || ' ' || 'World'); (*) Correct
Section 2 (Answer all questions in this section) Quiz: Using Variables PL/SQL 1. A function called FORMAT_TODAYS_DATE accepts no parameters and returns today 's date in the format: Month DD, YYYY The following anonymous block invokes the function: DECLARE v_today DATE; BEGIN -- invoke the function here Which of the following statements correctly assigns the date variable v_today to the value returned by the format_todays_date function? Mark for Review (1) Points format_todays_date := v_today('Month DD, YYYY'); v_today := format_todays_date ('Month DD, YYYY'); v_today := format_todays_date(v_today); v_today := TO_DATE(format_todays_date, 'Month DD, YYYY'); (*) Incorrect. Refer to Section 2 Lesson 1. 2. Variables may be reused. True or False? (1) Points
Mark for Review
True (*) False Correct 3. Evaluate the following declaration. Determine whether or not it is legal. DECLARE test NUMBER(5); Mark for Review (1) Points
legal (*) illegal Incorrect. Refer to Section 2 Lesson 1. 4. Constants must be initialized. True or False? (1) Points
Mark for Review
True (*) False Correct 5. Examine the following variable declarations: DECLARE v_number NUMBER := 10; v_result NUMBER; Which of the following correctly assigns the value 50 to V_RESULT? view (1) Points
Mark for Re
v_result := v_number * 5; v_result := 100 / 2; v_result := ROUND(49.77); All of the above. (*) Incorrect. Refer to Section 2 Lesson 1. 6. Which of the following are required when declaring a variable? (Choose two .) Mark for Review (1) Points (Choose all correct answers) Identifier name (*) CONSTANT Data type (*) NOT NULL Incorrect. Refer to Section 2 Lesson 1. 7. Evaluate the following declaration. Determine whether or not it is legal. DECLARE name,dept VARCHAR2(14); Mark for Review
(1) Points legal illegal (*) Correct 8. After they are declared, variables can be used only once in an application . True or False? Mark for Review (1) Points True False (*) Incorrect. Refer to Section 2 Lesson 1. Quiz: Recognizing PL/SQL Lexical Units 1. The name of a variable is an example of an identifier. True or False? for Review (1) Points
Mark
True (*) False Incorrect. Refer to Section 2 Lesson 2. 2.
What characters must enclose non-numeric literal values?
Mark for Review
(1) Points Double quotes: " " Parentheses: () Single quotes: ' ' (*) Incorrect. Refer to Section 2 Lesson 2. 3. Which of the following are lexical units? (Choose two.) (1) Points (Choose all correct answers) Data types PL/SQL blocks Identifiers (*)
Mark for Review
Literals (*) Incorrect. Refer to Section 2 Lesson 2. 4. Which of the following symbols can be used to enclose a comment in PL/SQL? Mark for Review (1) Points ? ? */ / * :: :: /* */ (*) Correct 5. Which of the following is a valid naming convention for an identifier? (Ch oose two.) Mark for Review (1) Points (Choose all correct answers) Can include letters or numbers (*) Cannot be a reserved word (*) Can be over 30 characters Can start with a number or special character Correct 6. What is a lexical unit? (1) Points
Mark for Review
A data type for a column A building block of a PL/SQL block (*) A type of variable Correct Quiz: Recognizing Data Types
1. A datatype may specify a valid range of values. True or False? Review
Mark for
(1) Points True (*) False Incorrect. Refer to Section 2 Lesson 3. 2. Which of the folowing are scalar data types? (Choose three.) view (1) Points
Mark for Re
(Choose all correct answers) Array Character (*) Table Date (*) Boolean (*) Incorrect. Refer to Section 2 Lesson 3. 3. A datatype specifies and restricts the possible data values that can be as signed to a variable. True or False? Mark for Review (1) Points True (*) False Incorrect. Refer to Section 2 Lesson 3. 4. Which of the following is a composite data type? (1) Points
Mark for Review
CLOB VARCHAR2 RECORD (*) DATE Incorrect. Refer to Section 2 Lesson 3. 5. What are the data types of the variables in the following declaration? DECLARE fname VARCHAR2(20);
fname VARCHAR2(15) DEFAULT 'fernandez'; BEGIN ... Mark for Review (1) Points Scalar (*) Composite LOB Correct 6. A Scalar data type holds a ____ value. (1) Points
Mark for Review
Multi Large Single (*) Correct 7. Which of the following are PL/SQL data types? (Choose three.) eview (1) Points
Mark for R
(Choose all correct answers) Large Objects (LOB) (*) Lexical Scalar (*) Delimiter Composite (*) Incorrect. Refer to Section 2 Lesson 3. Quiz: Using Scalar Data Types 1. If you use the %TYPE attribute, you can avoid hard-coding the column name. T rue or False? Mark for Review (1) Points True False (*)
Correct 2. Which of the following is NOT a character data type? (1) Points
Mark for Review
VARCHAR2 BOOLEAN (*) CHAR LONG Correct 3. When declared using %TYPE, a variable will inherit ____ from the column on which it is based. Mark for Review (1) Points The name of the column The value of the column The data type and size of the column (*) Correct 4. Code is easier to read if you declare one identifier per line. True or Fal se? Mark for Review (1) Points True (*) False Correct 5. Which of the following is NOT a good guideline for declaring variables? Mark for Review (1) Points Declare one identifier per line Use column names as identifiers (*) Use NOT NULL when the variable must have a value Incorrect. Refer to Section 2 Lesson 4. 6.
Which of the following variable declarations does NOT use a number data ty
pe? Mark for Review (1) Points v_count PLS_INTEGER := 0; v_median_age NUMBER(6,2); v_students LONG; (*) v_count BINARY_INTEGER; Incorrect. Refer to Section 2 Lesson 4. Quiz: Writing Pl/SQL Executable Statements 1. The DECODE and MAX functions can be used in PL/SQL statements. True or Fal se? Mark for Review (1) Points True False (*) Incorrect. Refer to Section 2 Lesson 5. 2. Explicit conversions can be slower than implicit conversions. True or Fals e? Mark for Review (1) Points True False (*) Correct 3. Which of the following is correct? (1) Points
Mark for Review
v_family_name = SMITH; V_FAMILY_NAME = SMITH; v_family_name := SMITH; v_family_name := 'SMITH'; (*) Incorrect. Refer to Section 2 Lesson 5. 4. Which of the following are valid PL/SQL operators? (Choose three.) for Review (1) Points
Mark
(Choose all correct answers) Concatenation (*) Exception Exponential (*) Arithmetic (*) Incorrect. Refer to Section 2 Lesson 5. 5. The LENGTH and ROUND functions can be used in PL/SQL statements. True or F alse? Mark for Review (1) Points True (*) False Incorrect. Refer to Section 2 Lesson 5. 6. Examine the following block. What should be coded at Line A? DECLARE v_char VARCHAR2(8) := '24/09/07'; v_date DATE; BEGIN v_date := ....... Line A END; Mark for Review (1) Points v_date := FROM_CHAR(v_char,'dd/mm/yy'); v_date := TO_DATE(v_char,'dd/mm/yy'); (*) v_date := v_char; Correct 7. The TO_CHAR function is used for explicit data type conversions. True or F alse? Mark for Review (1) Points True (*) False Incorrect. Refer to Section 2 Lesson 5.
8. PL/SQL can implicitly convert a CHAR to a NUMBER, provided the CHAR contai ns a numeric value, for example '123'. True or False? Mark for Review (1) Points True (*) False Incorrect. Refer to Section 2 Lesson 5. 9. Which explicit function is used to convert a character into a number? rk for Review (1) Points
Ma
TO_DATE TO_NUMBER (*) TO_CHAR Incorrect. Refer to Section 2 Lesson 5. 10. Which of the following statements about implicit conversions is NOT true? Mark for Review (1) Points Code containing implicit conversions typically runs faster than code contain ing explicit conversions. (*) Code containing implicit conversions may not work in the future if Oracle ch anges the conversion rules. Code containing implicit conversions is harder to read and understand. Incorrect. Refer to Section 2 Lesson 5. 11. PL/SQL statements must be written on a single line. (1) Points True False (*) Correct 12. Examine the following code: DECLARE x VARCHAR2(20); BEGIN x:= 5 + 4 * 5 ; DBMS_OUTPUT.PUT_LINE(x); END;
Mark for Review
What value of x will be displayed? Mark for Review (1) Points 45 29 25 (*) 14 Incorrect. Refer to Section 2 Lesson 5. 13. When PL/SQL converts data automatically from one data type to another, it is called _______ conversion. Mark for Review (1) Points Explicit Implicit (*) TO_CHAR Correct 14. Using implicit conversions is good programming practice. w (1) Points
Mark for Revie
True False (*) Incorrect. Refer to Section 2 Lesson 5. 15. Which of the following data type conversions can be done implicitly? (Cho ose two.) Mark for Review (1) Points (Choose all correct answers) DATE to NUMBER NUMBER to VARCHAR2 (*) NUMBER to PLS_INTEGER (*) Incorrect. Refer to Section 2 Lesson 5. 16.
What will happen when the following code is executed?
DECLARE v_new_date DATE; BEGIN v_new_date := 'Today'; DBMS_OUTPUT.PUT_LINE(v_new_date); END; Mark for Review (1) Points The block will execute and display today's date. The block will execute and display the word "Today". The block will fail because the character value "Today" cannot be implicitly converted to a date. (*) Incorrect. Refer to Section 2 Lesson 5. Quiz: Nested Blocks and Variable Scope 1. What happens when an exception occurs in the executable section of a PL/SQL block? Mark for Review (1) Points Oracle keeps trying to re-execute the statement which caused the exception. The remaining statements in the executable section are not executed. Instead , Oracle looks for an EXCEPTION section in the block. (*) The remaining statements in the executable section of the block are executed . The exception is always propagated to the calling environment. Incorrect. Refer to Section 2 Lesson 6. 2. Examine the following nested blocks. Line B causes an exception. What will be displayed when this code is executed? DECLARE var_1 NUMBER; BEGIN var_1 := 4; DECLARE var_2 NUMBER; BEGIN var_2 := 'Unhappy'; -- Line B var_1 := 8; END; var_1 := 12; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(var_1); END; Mark for Review
(1) Points Unhappy 12 8 4 (*) Incorrect. Refer to Section 2 Lesson 6. 3. Examine the following code. What is the scope of variable v_myvar? DECLARE v_myvar NUMBER; BEGIN v_myvar := 6; DECLARE v_hervar NUMBER; BEGIN v_hervar := 4; END; END; Mark for Review (1) Points Only the outer block Both the inner and the outer block (*) Only the inner block Neither block Incorrect. Refer to Section 2 Lesson 6. 4. For the anonymous block below, what is the correct reference to the father 's date of birth in the inner block? <> DECLARE v_father_name VARCHAR2(20):='Patrick'; v_date_of_birth DATE:='20-Apr-1972'; BEGIN DECLARE v_child_name VARCHAR2(20):='Mike'; v_date_of_birth DATE:='12-Dec-2002'; ... Mark for Review (1) Points v_date_of_birth.outer <>v_date_of_birth <>
outer.v_date_of_birth (*) Incorrect. Refer to Section 2 Lesson 6. 5. Examine the following code. Line A causes an exception. What will be displ ayed when the block is executed? DECLARE x NUMBER := 10; y NUMBER; BEGIN x := 15; y := 'Happy'; -- Line A x := 20; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(x); END; Mark for Review (1) Points 10 20 15 (*) Nothing is displayed Incorrect. Refer to Section 2 Lesson 6. 6. What values will be displayed when the following code is executed? DECLARE v_mynum NUMBER; BEGIN v_mynum := 7; DECLARE v_mynum NUMBER; BEGIN DBMS_OUTPUT.PUT_LINE(v_mynum); v_mynum := 3; END; DBMS_OUTPUT.PUT_LINE(v_mynum); END; Mark for Review (1) Points 3,3 3,7 Null, 7 (*) Null, 3 Incorrect. Refer to Section 2 Lesson 6.
7. A variable declared in an outer block is available in both the outer block s and in any inner blocks. True or False? Mark for Review (1) Points True False (*) Incorrect. Refer to Section 2 Lesson 6. 8. Examine the following code. At Line A, we want to assign a value of 25 to the outer block's variable (V1). What must we do? DECLARE v_myvar NUMBER; -- This is V1 BEGIN DECLARE v_myvar NUMBER := 8; BEGIN -- Line A END; END; Mark for Review (1) Points At Line A, code: v_myvar := 25; Label both blocks and at line A, code: v_myvar := 25; It cannot be done because the outer block's v_myvar is out of scope at Line A. Label the outer block and (at Line A) dot-prefix v_myvar with the block labe l. (*) It cannot be done because the outer block's v_myvar is in scope but not visi ble at Line A.
Incorrect. Refer to Section 2 Lesson 6. 9. An inner block is nested within an outer block. An exception occurs within the inner block, but the inner block does not have an EXCEPTION section. What h appens? Mark for Review (1) Points The exception is propagated to the outer block and the remaining executable statements in the outer block are skipped. (*)
The exception is propagated to the outer block and the remaining executable statements in the outer block are executed. Oracle automatically tries to re-execute the inner block. The outer block is bypassed and the exception is always propagated to the ca lling environment. Incorrect. Refer to Section 2 Lesson 6. 10. What is wrong with this code? DECLARE v_a NUMBER; BEGIN v_a := 27; <> BEGIN v_a := 15; END; Mark for Review (1) Points The outer block has no label. Variable v_a is out of scope within the inner block and therefore cannot be referenced. The inner block has no END; statement. (*) Nothing is wrong, the code will execute successfully. Incorrect. Refer to Section 2 Lesson 6. Quiz: Good Programming Practices 1. Which of the following makes PL/SQL code easier to read and maintain? for Review (1) Points Place multiple statements on the same line. Type everything in lowercase. Use suitable comments in the code. (*) Correct 2. Examine the following code: DECLARE v_first_name varchar2 (30); v_salary number (10); BEGIN
Mark
SELECT first_name, salary INTO v_first_name, v_salary FROM employees WHERE last_name = 'King'; END; Which programming guideline would improve this code? Mark for Review (1) Points Use a suitable naming convention for variables. Indent the code to make it more readable. (*) Use upper and lower case consistently. Incorrect. Refer to Section 2 Lesson 7. 3. Which of the following are examples of good programming practice? (Choose three.) Mark for Review (1) Points (Choose all correct answers) Document code with comments. (*) Use implicit data type conversions. Develop naming conventions for identifiers and other objects. (*) Indent code so that it can be read more easily. (*) Use table column names as the names of variables. Incorrect. Refer to Section 2 Lesson 7. 4. Comments change how a PL/SQL program executes, so an unsuitable comment ca n cause the program to fail. True or False? Mark for Review (1) Points True False (*) Incorrect. Refer to Section 2 Lesson 7. 5. Which of the following are examples of good programming practice? (Choose two.) Mark for Review (1) Points (Choose all correct answers) Use the %TYPE attribute to declare a variable according to another previous
ly declared variable or database column. (*) Declare one or more identifiers per line for improved performance. For clarity, use column names as identifiers. Use meaningful names for identifiers. (*) Incorrect. Refer to Section 2 Lesson 7. 6. What symbol is used to comment a series of lines? (1) Points
Mark for Review
/ / before and after the comment /* */ before and after the comment (*) * * before and after the comment Correct Section 3 (Answer all questions in this section) Quiz: Review of SQL DML 1. Look at this SQL statement: MERGE INTO old_trans ot USING new_trans nt ON (ot.trans_id = nt.trans_id) .... ; OLD_TRANS is the source table and NEW_TRANS is the target table. True or false? Mark for Review (1) Points True False (*) Correct 2. What would be the result of the following statement: DELETE employees; ark for Review (1) Points Nothing, no data will be changed. All rows in the employees table will be deleted. (*) The statement will fail because it contains a syntax error. The row with EMPOYEE_ID=100 will be deleted.
M
Correct 3. When inserting a row into a table, the VALUES clause must include a value for every column of the table. True or False? Mark for Review (1) Points True False (*) Correct 4. To modify an existing row in a table, you can use the ________ statement. Mark for Review (1) Points MODIFY INSERT ALTER UPDATE (*) Incorrect. Refer to Section 3 Lesson 1. 5. What is wrong with the following statement? DELETE from employees WHERE salary > (SELECT MAX(salary) FROM employees); Mark for Review (1) Points You cannot code a subquery inside a DELETE statement. You cannot use inequality operators such as "<" and ">" inside a DELETE stat ement. Nothing is wrong, the statement will execute correctly. (*) Correct 6. Is it possible to insert more than one row at a time using an INSERT state ment with a VALUES clause? Mark for Review (1) Points No, you can only create one row at a time when using the VALUES clause. (*) Yes, you can list as many rows as you want, just remember to separate the ro ws with commas.
No, there is no such thing as INSERT ... VALUES. Incorrect. Refer to Section 3 Lesson 1. 7. What is wrong with the following statement? MERGE INTO emps e USING new_emps ne ON (e.employee_id = ne.employee_id) WHEN MATCHED THEN UPDATE SET ne.salary = e.salary WHEN NOT MATCHED THEN INSERT VALUES (ne.employee_id, ne.first_name, ne.last_name, .... ne.sala ry, ....); Mark for Review (1) Points The UPDATE clause must include the target table name: UPDATE emps SET .... The INSERT clause must include a column list as well as a list of column val ues. The SET clause is trying to update the source table from the target table. ( *) Nothing is wrong, the statement will execute correctly. Incorrect. Refer to Section 3 Lesson 1. 8. You want to modify existing rows in a table. Which of the following are NO T needed in your SQL statement? (Choose two). Mark for Review (1) Points (Choose all correct answers) A MODIFY clause. (*) An UPDATE clause. The name of the table. The name of the column(s) you want to modify. A new value for the column you want to modify (this can be an expression or a subquery). A WHERE clause. (*) Incorrect. Refer to Section 3 Lesson 1. Quiz: Retrieving Data in PL/SQL 1.
It is good programming practice to create identifiers having the same name a
s column names. True or False? (1) Points
Mark for Review
True False (*) Correct 2. Look at this PL/SQL block: DECLARE v_count NUMBER; BEGIN SELECT COUNT(*) INTO v_count FROM employees WHERE salary > 50000; END; No employees earn more than $50000. Which of the following statements are true? (Choose two). Mark for Review (1) Points (Choose all correct answers) The SELECT will return value 0 into V_COUNT. (*) The SELECT will fail because it does NOT return exactly one row. The block will fail because variable V_SALARY was not declared. The SELECT returns exactly one row. (*) The block will fail because no results are displayed to the user. Incorrect. Refer to Section 3 Lesson 2. 3. Which of the following is NOT a valid guideline for retrieving data in PL/ SQL? Mark for Review (1) Points Terminate the SQL statement with a semicolon (;) Do NOT use a WHERE clause in SELECT statements. (*) Where possible, declare variables using the %TYPE attribute. Specify the same number of variables in the INTO clause as database columns in the SELECT clause. Correct 4. Which SQL statements can be used directly in a PL/SQL block? (Choose two.) Mark for Review
(1) Points (Choose all correct answers) GRANT EXECUTE ON ... SELECT * INTO ... (*) REVOKE SELECT ON ... UPDATE employees SET... (*) ALTER TABLE employees ... Incorrect. Refer to Section 3 Lesson 2. 5. Which one of these SQL statements can be directly included in a PL/SQL exe cutable block? Mark for Review (1) Points IF... THEN...; INSERT INTO...; (*) SELECT * FROM DUAL; SHOW USER; Incorrect. Refer to Section 3 Lesson 2. 6. When used in a PL/SQL block, which SQL statement must return exactly one r ow? Mark for Review (1) Points INSERT UPDATE SELECT (*) MERGE DELETE Incorrect. Refer to Section 3 Lesson 2. 7. Does PL/SQL allow you to have a variable with the same name as a database column? Mark for Review (1) Points No Yes (*)
Incorrect. Refer to Section 3 Lesson 2. 8. What will happen when the following block is executed? DECLARE v_last employees.last_name%TYPE; v_first employees.first_name%TYPE; v_salary employees.salary%TYPE; BEGIN SELECT first_name, last_name INTO v_first, v_last, v_salary FROM employees WHERE employee_id=100; END; Mark for Review (1) Points The block will fail because the SELECT statement returns more than one row. The block will fail because the SELECT is trying to read two columns into th ree PL/SQL variables. (*) The block will fail because V_LAST was declared before V_FIRST. The block will execute successfully, and the V_SALARY variable will be set t o NULL. Incorrect. Refer to Section 3 Lesson 2. Quiz: Manipulating Data in PL/SQL 1. Which implicit cursor attribute identifies the number of rows updated in the following statement?
DBMS_OUTPUT.PUT_LINE (__________ || ' rows updated.');
Mark for Review (1) Points SQL%COUNT SQL%NUMBER SQL%ROWCOUNT (*) SQLROW%COUNT Correct 2. A PL/SQL block contains the following DML statement: UPDATE wf_countries SET population = population * 1.1 WHERE country_id = 229; Which kind of cursor is used for this statement?
Mark for Review (1) Points An implicit cursor named "WF_COUNTRIES". An implicit cursor named "SQL". (*) An explicit cursor named "SQL". An explicit cursor which must be declared and named by the PL/SQL programmer . Incorrect Refer to Section 3 Lesson 3. 3. You can use implicit cursor attributes such as SQL%ROWCOUNT directly insid e a DML statement. For example: INSERT INTO log_table VALUES (SYSDATE, USER, SQL%ROWCOUNT); True or False? Mark for Review (1) Points True False (*) Correct 4. Employee_id 999 does not exist. What will happen when the following code i s executed? DECLARE employee_id employees.employee_id%TYPE := 999; BEGIN UPDATE employees SET salary = salary * 1.1 WHERE employee_id = employee_id; END; Mark for Review (1) Points No rows are updated but the block completes successfully. Every employee row is updated. (*) An exception is raised because you cannot give a variable the same name as a table column. An exception is raised because the UPDATE statement did not modify any rows.
Incorrect Refer to Section 3 Lesson 3.
5. Which of the following use an implicit cursor? (1) Points
Mark for Review
DML statements only. SELECT statements only. DML statements and SELECT statements which return a single row. (*) COMMIT and ROLLBACK statements only. Correct 6. Which of the following SQL DML commands can be used inside a PL/SQL block? Mark for Review (1) Points INSERT and UPDATE only. UPDATE and DELETE only. INSERT, UPDATE and DELETE only. INSERT, UPDATE, DELETE and MERGE. (*) Incorrect Refer to Section 3 Lesson 3. 7. There are three employees in department 90. What will be displayed when th e following code is executed? DECLARE v_open CHAR(3) := 'NO'; BEGIN UPDATE emplo yees SET job_id = 'ST_CLERK' WHERE department_id = 90; IF SQL%FOUND THEN v_open := 'YES'; END IF; DBMS_OUTPUT.PUT_LINE(v_open || ' ' || SQL%ROWCOUNT); END; Ma rk for Review (1) Points NO 3 YES 1 YES 3 (*) Nothing will be displayed. The block will fail because you cannot use implic it cursor attributes directly in a call to DBMS_OUTPUT.PUT_LINE. Incorrect Refer to Section 3 Lesson
3
Quiz: Using Transaction Controlled Statements 1. How many INSERTs can you have in one transaction? (1) Points One
Mark for Review
As many as you want until you do a COMMIT or ROLLBACK. (*) As many as you can execute before the database does an AUTOSAVE. As many as you want until a different DML statement (UPDATE, DELETE or MERGE ) is executed. Correct 2.
In a PL/SQL block, where can you code a COMMIT statement?
Mark for Revie
w (1) Points In any section of the block: Declaration, Executable, or Exception. Only the Executable section. In the Executable and/or the Exception sections. (*) Nowhere; the COMMIT statement must be outside the block. Incorrect. Refer to Section 3 Lesson 4. 3. How many transactions are in the following block? BEGIN INSERT INTO countries (country_id, country_name) VALUES ('XA', 'Xanadu'); INSERT INTO countries (country_id, country_name) VALUES ('NV', 'Neverland'); UPDATE countries SET country_name='Deutchland' WHERE country_id='DE'; UPDATE countries SET region_id=1 WHERE country_name LIKE '%stan'; END; How many transactions are shown above? Mark for Review (1) Points Four; each DML is a separate transaction Two; both the INSERTs are one transaction and both the UPDATEs are a second transaction. It depends on how many rows are updated - there will be a separate transacti on for each row. One (*) Incorrect. Refer to Section 3 Lesson 4. 4. Examine the following code: BEGIN INSERT INTO animals
VALUES ('aa','aardvarks'); SAVEPOINT sp_1; INSERT INTO animals VALUES ('bb','big birds'); SAVEPOINT sp_2; ROLLBACK TO sp_1; INSERT INTO animals VALUES ('cc','cool cats'); COMMIT; END; Which row(s) will be in the ANIMALS table after this block is executed? Mark for Review (1) Points cool cats big birds and cool cats aardvaarks and cool cats (*) aardvaarks, big birds and cool cats Correct Section 4 (Answer all questions in this section)
Quiz: Conditional Control: If Statements 1. What will be displayed when this block is executed? DECLARE v_bool1 BOOLEAN := TRUE; v_bool2 BOOLEAN; v_char VARCHAR(4) := 'up'; BEGIN IF (v_bool1 AND v_bool2) THEN v_char:='down'; ELSE v_char:='left'; END IF; DBMS_OUTPUT.PUT_LINE(v_char); END; Mark for Review (1) Points up down left (*) null
Incorrect. Refer to Section 4 Lesson 1. 2. Which of the following statements are true about any of the PL/SQL conditi onal control structures such as IF ... , CASE ... and loops? Mark for Review (1) Points They allow the programmer to use logical tests to determine which statements are executed and which are not. They allow a set of statements to be executed repeatedly (i.e. more than onc e). They determine a course of action based on conditions. All of the above. (*) Incorrect. Refer to Section 4 Lesson 1. 3. A basic loop is a type of control structure used to change the logical flo w of statements in a PL/SQL block. True or False? Mark for Review (1) Points True (*) False Correct 4. Look at the following (badly written) code: age := 5; IF age<30 THEN mature := 'adult'; ELSIF age<22 THEN mature := 'teenager'; ELSIF age<13 THEN mature := 'child'; END IF; DBMS_OUTPUT.PUT_LINE(mature); What will be displayed when this code is executed? Mark for Review (1) Points child teenager adult (*) adultteenagerchild Correct 5. What will be displayed when this block is executed? DECLARE v_bool1 BOOLEAN := NULL; v_bool2 BOOLEAN := NULL;
v_char VARCHAR(10) := 'Start'; BEGIN IF (v_bool1 = v_bool2) THEN v_char:='Equal'; ELSE v_char:='Not equal'; END IF; DBMS_OUTPUT.PUT_LINE(v_char); END; Mark for Review (1) Points Equal Not equal (*) Start Nothing will be displayed. The block will fail because you cannot compare tw o null values. Correct 6. Which one of the following is correct syntax for an IF statement? or Review (1) Points IF condition THEN DO statement1; statement2; END IF; IF condition THEN statement1; statement2; END IF; (*) IF condition THEN statement1; statement2; ENDIF; IF condition THEN statement1; AND statement2; END IF;
Correct 7. What will be displayed when this block is executed? DECLARE
Mark f
v_birthdate DATE; BEGIN IF v_birthdate < '01-JAN-2000' THEN DBMS_OUTPUT.PUT_LINE(' Born in the 20th century '); ELSE DBMS_OUTPUT.PUT_LINE(' Born in the 21st century '); END IF; END; Mark for Review (1) Points Born in the 20th century Born in the 21st century (*) Exception raised because no date given Incorrect. Refer to Section 4 Lesson 1. 8. You want to repeat a set of statements 100 times, incrementing a counter e ach time. What kind of PL/SQL control structure would you use? Mark for Review (1) Points IF...THEN...ELSE IF...THEN...ELSIF...ELSE CASE...WHEN...THEN A loop. (*) Incorrect. Refer to Section 4 Lesson 1. 9. Name three types of control structures in PL/SQL. (Choose three) r Review (1) Points (Choose all correct answers) LOOP statements (*) SELECT statements EXCEPTIONS IF statements (*) CASE statements (*) Incorrect. Refer to Section 4 Lesson 1.
Mark fo
10. We want to execute one of three statements depending on whether the value in V_VAR is 10, 20 or some other value. What should be coded at Line A? IF v_var = 10 THEN statement1; -- Line A statement2; ELSE statement3; END IF; Mark for Review (1) Points ELSE IF v_var = 20 THEN ELSIF v_var = 20 ELSIF v_var = 20 THEN (*) IF v_var = 20 THEN Incorrect. Refer to Section 4 Lesson 1. 11. What is wrong with the following trivial IF statement: IF (v_job='President') THEN v_salary := 10000; Mark for Review (1) Points IF and THEN must be on the same line: IF (v_job='President') THEN ... The condition should be coded: IF (v_job := 'President') END IF; is missing (*) ELSE is missing Correct
Quiz: Conditional Control: Case Statements 1. Look at the following code: DECLARE x BOOLEAN := FALSE; y BOOLEAN := FALSE; z BOOLEAN ; BEGIN z := (x OR NOT y); -- Line A .... END; What is the value of Z at Line A? Mark for Review (1) Points
True (*) False NULL An error will occur because you cannot combine two Boolean variables using " NOT". Incorrect. Refer to Section 4 Lesson 2. 2. Examine the following code: DECLARE v_a BOOLEAN; v_b BOOLEAN := FALSE; v_c BOOLEAN ; BEGIN v_c := (v_a AND v_b); -- Line A .... END; What is the value of v_c at Line A? Mark for Review (1) Points True False (*) NULL Undefined Incorrect. Refer to Section 4 Lesson 2. 3. Examine the following code: DECLARE v_score NUMBER(3); v_grade CHAR(1); BEGIN CASE v_score -- Line A .... The CASE statement must convert a numeric score to a letter grade: 90 -> A, 80 > B, 70 -> C and so on. What should be coded at Line A? Mark for Review (1) Points WHEN 90 THEN v_grade := 'A'
WHEN 90 THEN v_grade := 'A'; (*) WHEN 90 THEN 'A' WHEN 90 THEN 'A'; Correct 4. How must you end a CASE statement? (1) Points
Mark for Review
END; END CASE; (*) END IF; ENDCASE; Correct 5. How must you end a CASE expression? (1) Points
Mark for Review
END; (*) ENDIF; END CASE; ENDCASE; Incorrect. Refer to Section 4 Lesson 2. 6. What will be displayed when the following block is executed? DECLARE v_age1 NUMBER(3); v_age2 NUMBER(3); v_message VARCHAR2(20); BEGIN CASE WHEN v_age1 = v_age2 THEN v_message := 'Equal'; WHEN v_age1 <> v_age2 THEN v_message := 'Unequal'; ELSE v_message := 'Undefined'; END CASE; DBMS_OUTPUT.PUT_LINE(v_message); END; Mark for Review (1) Points Equal Undefined (*)
Unequal Nothing will be displayed because V_MESSAGE is set to NULL. Incorrect. Refer to Section 4 Lesson 2. 7. What will be displayed when the following block is executed? DECLARE v_age NUMBER(3); v_gender VARCHAR2(6) := 'Female'; v_status VARCHAR2(20); BEGIN CASE WHEN v_age >= 18 AND v_gender = 'Male' THEN v_status := 'Adult Male'; WHEN v_age >= 18 AND v_gender = 'Female' THEN v_status := 'Adult Female'; WHEN v_age < 18 AND v_gender = 'Male' THEN v_status := 'Junior Male';
WHEN v_age < 18 AND v_gender = 'Female' THEN v_status := 'Junior Female'; < br /> ELSE v_status := 'Other Value'; END CASE; DBMS_OUTPUT.PUT_LINE(v_status); END; Mark for Review (1) Points Adult Male Junior Female Other Value (*) Nothing will be displayed because V_STATUS is set to NULL. Correct 8. Examine the following code: DECLARE v_score NUMBER(3); v_grade CHAR(1); BEGIN v_grade := CASE v_score -- Line A .... The CASE expression must convert a numeric score to a letter grade: 90 -> A, 80 -> B, 70 -> C and so on. What should be coded at Line A? Mark for Review (1) Points WHEN 90 THEN grade := 'A' WHEN 90 THEN v_grade := 'A'; WHEN 90 THEN 'A' (*)
WHEN 90 THEN 'A'; Correct Quiz: Iterative Control: Basic Loops 1. What will be displayed when this block is executed? DECLARE v_count NUMBER := 10; v_result NUMBER; BEGIN LOOP v_count := v_count - 1; EXIT WHEN v_count < 5; v_result := v_count * 2; END LOOP; DBMS_OUTPUT.PUT_LINE(v_result); END; Mark for Review (1) Points 8 10 (*) 12 NULL Incorrect. Refer to Section 4 Lesson 3. 2. Examine the following code: DECLARE v_count NUMBER := 0; v_string VARCHAR2(20); BEGIN LOOP v_string := v_string || 'x'; IF LENGTH(v_string) > 10 THEN EXIT; END IF; v_count := v_count + 1; END LOOP; DBMS_OUTPUT.PUT_LINE(v_count); END; What will be displayed when this block is executed? Mark for Review (1) Points 9 10 (*)
11 xxxxxxxxxxx Correct 3. What are the three kinds of loops in PL/SQL? (1) Points
Mark for Review
ascending, descending, unordered infinite, finite, recursive IF, CASE, LOOP FOR, WHILE, basic (*) Correct 4. Which kind of loop is this? i := 10; LOOP i := i + 1; EXIT WHEN i > 30; END LOOP; Mark for Review (1) Points A FOR loop. A WHILE loop. A basic loop. (*) An infinite loop. A nested loop. Incorrect. Refer to Section 4 Lesson 3. 5. For which one of these tasks should you use a PL/SQL loop? ew (1) Points
Mark for Revi
Updating the salary of one employee. Executing the same set of statements repeatedly until a condition becomes tr ue. (*) Deciding whether a value is within a range of numbers. Making a decision based on whether a condition is true or not.
Correct 6. Look at this code: DECLARE v_bool BOOLEAN := TRUE; v_date DATE; BEGIN LOOP EXIT WHEN v_bool; SELECT SYSDATE INTO v_date FROM dual; END LOOP; END; How many times will the SELECT statement execute? Mark for Review (1) Points Once. Twice. Never (the SELECT will not execute at all) (*) An infinite number of times because the EXIT condition will never be true Correct 7. You want to calculate and display the multiplication table for "sevens": 7 x1=7, 7x2=14, 7x3=21 and so on. Which kind of PL/SQL construct is best for this? Mark for Review (1) Points A loop (*) A CASE statement IF ... END IF; A Boolean variable. Incorrect. Refer to Section 4 Lesson 3. 8. How many EXIT statements can be coded inside a basic loop? ew (1) Points None. One only. Two. As many as you need, there is no limit. (*)
Mark for Revi
Incorrect. Refer to Section 4 Lesson 3.
Section 5 Quiz: Iterative Control: While and For Loops 1. You want a loop that counts backwards from 10 through 1. How do you code tha t? Mark for Review (1) Points FOR i IN 10 .. 1 LOOP FOR i IN 1 .. 10 BY -1 LOOP FOR i IN REVERSE 1 .. 10 LOOP (*) FOR i IN REVERSE 10 .. 1 LOOP Incorrect. Refer to Section 4 Lesson 4. 2. In a WHILE loop, the controlling condition is checked at the start of each iteration. True or False? Mark for Review (1) Points True (*) False Incorrect. Refer to Section 4 Lesson 4. 3. You should use a WHILE loop when the number of iterations of the loop is k nown in advance. True or False? Mark for Review (1) Points True False (*) Incorrect. Refer to Section 4 Lesson 4. 4. In a FOR loop, an explicitly declared counter is automatically incremented by 1 for each iteration of the loop. True or False? Mark for Review (1) Points True False (*) Incorrect. Refer to Section 4 Lesson 4.
5. Look at this code fragment: FOR i IN 1 .. 3 LOOP i := 4; DBMS_OUTPUT.PUT_LINE('The counter is: ' || i); END LOOP; How many lines of output will be displayed? Mark for Review (1) Points One Three Four The block will fail because you cannot change the value of i inside the loop . (*) Incorrect. Refer to Section 4 Lesson 4. 6. Look at the following code fragment: i := 2; WHILE i < 3 LOOP
i := 4; DBMS_OUTPUT.PUT_LINE('The counter is: ' || i); END LOOP; How many lines of output will be displayed? Mark for Review (1) Points No lines One line (*) Two lines The block will fail because you cannot use DBMS_OUTPUT.PUT_LINE inside a loo p. Incorrect. Refer to Section 4 Lesson 4. 7. Look at the following block: DECLARE v_date DATE := SYSDATE; BEGIN WHILE v_date < LAST_DAY(v_date) LOOP v_date := v_date + 1; END LOOP; DBMS_OUTPUT.PUT_LINE(v_date); END; If today's date is 17th April 2007, what will be displayed when this block execu tes?
Mark for Review (1) Points 01-MAY-07 31-DEC-07 4/30/2007 (*) 4/17/2007 Incorrect. Refer to Section 4 Lesson 4. 8. Which statement best describes when a FOR loop should be used? Review (1) Points
Mark for
When an EXIT WHEN statement must be coded. When an implicitly declared counter must increase by 1 in each iteration of the loop. (*) When we want to exit from the loop when a Boolean variable becomes FALSE. When the statements inside the loop must execute at least once. Correct Quiz: Iterative Control: Nested Loops 1. What type of loop statement would you write for Point A? BEGIN FOR v_outerloop IN 1..3 LOOP -- Point A DBMS_OUTPUT.PUT_LINE('Outer loop is:'||v_outerloop|| ' and inner loop is: '||v_innerloop); END LOOP; END LOOP; END; Mark for Review (1) Points WHILE v_innerloop <=5 LOOP FOR v_innerloop 1..5 LOOP (*) LOOP WHILE v_outerloop
2. What will be displayed when the following block is executed? DECLARE x NUMBER(6) := 0 ; BEGIN FOR i IN 1..10 LOOP FOR j IN 1..5 LOOP x := x+1 ; END LOOP; END LOOP; DBMS_OUTPUT.PUT_LINE(x); END; Mark for Review (1) Points 5 10 15 50 (*) Incorrect. Refer to Section 4 Lesson 5. 3. When the following code is executed, how many lines of output will be disp layed? BEGIN FOR i IN 1..5 LOOP FOR j IN 1..8 LOOP DBMS_OUTPUT.PUT_LINE(i || ',' || j); END LOOP; DBMS_OUTPUT.PUT_LINE(i); END LOOP; END; Mark for Review (1) Points 80 45 (*) 14 41 Incorrect. Refer to Section 4 Lesson 5. 4. Which one of these statements about using nested loops is true? Review (1) Points
Mark for
All the loops must be labelled The outer loop must be labelled, but the inner loop need not be labelled
The outer loop must be labelled if you want to exit the outer loop from with in the inner loop (*) Both loops can have the same label Incorrect. Refer to Section 4 Lesson 5. 5. What statement allows you to exit the outer loop at Point A in the followi ng block? DECLARE v_outer_done CHAR(3) := 'NO'; v_inner_done CHAR(3) := 'NO'; BEGIN LOOP -- outer loop ... LOOP -- inner loop ... ... -- Point A EXIT WHEN v_inner_done = 'YES'; ... END LOOP; ... EXIT WHEN v_outer_done = 'YES'; ... END LOOP; END; Mark for Review (1) Points EXIT AT v_outer_done = 'YES'; EXIT WHEN v_outer_done = 'YES'; (*) WHEN v_outer_done = YES EXIT; EXIT <>; Correct 6. Look at the following code: DECLARE v_blue NUMBER(3) := 0; v_red NUMBER(3) := 0; BEGIN << blue >> LOOP v_blue := v_blue + 1; EXIT WHEN v_blue > 10; << red >> LOOP v_red := v_red + 1; EXIT WHEN v_red > 10; -- Line A END LOOP red; END LOOP blue; END; What should you code at Line A to exit from the outer loop?
Mark for Review (1) Points EXIT; EXIT red; EXIT <>; EXIT blue; (*) Correct Quiz: Introductionto Explicit Cursors
1. You have declared a cursor EMP_CURSOR to select many rows from the EMPLOYEES table. The following five statements will be in the executable section: A FETCH emp_cursor INTO v_empno,v_last_name; B OPEN emp_cursor; C END LOOP; D CLOSE emp_cursor; E LOOP In which order should you code these statements? Mark for Review (1) Points B, E, A, C, D (*) E, B, A, C, D B, E, A, D, C B, A, E, D, C Correct 2. Examine the following code: DECLARE CURSOR dept_curs IS SELECT department_name FROM departments; v_dept_name departments.department_name%TYPE; BEGIN OPEN dept_curs; LOOP FETCH dept_curs INTO v_dept_name; DBMS_OUTPUT.PUT_LINE(v_dept_name); EXIT WHEN dept_curs%NOTFOUND; END LOOP; CLOSE dept_curs; END; There are 10 rows in the DEPARTMENTS table. What will happen when this code is e xecuted?
Mark for Review (1) Points 10 rows will be displayed. 10 rows will be displayed, followed by a row of NULL values. The last row will be displayed twice. (*) A NO_DATA_FOUND exception will be raised. The loop will execute for ever; the same 10 rows will be displayed over and over again. Correct 3. When must you declare and use an explicit cursor? (1) Points
Mark for Review
You need to UPDATE more than one row in a table. You want to use a MERGE statement. You need to SELECT more than one row from a table. (*) You want to be able to ROLLBACK a transaction if needed. Correct 4. You execute the following code: DECLARE CURSOR emp_curs IS SELECT last_name FROM employees; v_last_name employees.last_name%TYPE; BEGIN OPEN emp_curs; LOOP -- Point A FETCH emp_curs INTO v_last_name; EXIT WHEN emp_curs%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_last_name); END LOOP; CLOSE emp_curs; END; At Point A (after you have OPENed the cursor) another user updates an employee's last_name from 'Smith' to 'Jones' and immediately COMMITs. When your block FETCHes this row, which value will be fetched and displayed? Mark for Review (1) Points 1 Smith (*)
Jones Smith and Jones (the row will be fetched twice) An INVALID_CURSOR exception will be raised when you try to FETCH the row. Incorrect. Refer to Section 5 Lesson 1. 5. What is wrong with the following code? DECLARE CURSOR dept_curs IS SELECT department_name FROM departments; v_dept_name departments.department_name%TYPE; BEGIN OPEN dept_curs; LOOP FETCH dept_curs INTO v_dept_name; EXIT WHEN dept_curs%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_dept_name); CLOSE dept_curs; END LOOP; END; Mark for Review (1) Points Nothing is wrong, all the rows will be FETCHed and displayed. The OPEN statement should be inside the loop. The EXIT WHEN ... statement should be coded outside the loop. The CLOSE statement should be coded after END LOOP; (*) The loop should be a WHILE loop, not a basic loop. Correct 6. You cannot OPEN or CLOSE an implicit cursor. Why not? (1) Points
Mark for Review
Because an implicit cursor is always called SQL. Because an implicit cursor is OPENed and CLOSEd automatically by Oracle. (*)
Correct 7. Which one of the following statements is NOT true? (1) Points
Mark for Review
You can use ORDER BY when declaring an explicit cursor. You can not use an INTO clause when declaring an explicit cursor.
An explicit cursor can select from only one table. No joins are allowed. (*) An explicit cursor must be DECLAREd before it can be OPENed. Incorrect. Refer to Section 5 Lesson 1. 8. One (and only one) employee has LAST_NAME = 'Grant'. You need to code: SELECT ... FROM employees WHERE last_name = 'Grant'; Which type of cursor should you use, and why? Mark for Review (1) Points An implicit cursor, because there is only one 'Grant'. An implicit cursor, because SELECT is a SQL statement and implicit cursors a re always called "SQL". An explicit cursor, because there could be more than one 'Grant' in the futu re. (*) An explicit cursor, because you can use an implicit cursor only for DML stat ements. Correct 9. Which one of the following explicit cursor declarations is NOT valid? rk for Review (1) Points
Ma
CURSOR country_curs IS SELECT country_name, region_name FROM wf_countries c, wf_world_regions r WHERE c.region_id = r.region_id; CURSOR country_curs IS SELECT country_name INTO v_country_name FROM wf_count ries; (*) CURSOR country_curs IS SELECT country_name FROM wf_countries ORDER BY popula tion DESC; CURSOR country_curs IS SELECT country_name FROM wf_countries WHERE region_id IN (SELECT region_id FROM wf_world_regions WHERE LOWER(region_name) LIKE '%asia %'); Correct 10. What is wrong with the following code? DECLARE CURSOR emp_curs IS SELECT last_name, salary FROM employees; v_last_name employees.last_name%TYPE; v_salary employees.salary%TYPE; BEGIN FETCH emp_curs INTO v_last_name, v_salary; OPEN emp_curs;
FETCH emp_curs INTO v_last_name, v_salary; CLOSE emp_curs; END; Mark for Review (1) Points When FETCHing more than one row, you MUST use a loop. The cursor declaration does not include a WHERE condition. The cursor declaration does not include an INTO clause. The first row is FETCHed before the cursor is OPENed. (*) Incorrect. Refer to Section 5 Lesson 1. 11. You must make sure you have the same number of variables in your INTO sta tement as you have in your SELECT list. True or False? Mark for Review (1) Points True (*) False Correct 12. Which statement correctly places the employee id and last name into the s tated variables? DECLARE CURSOR emp_cursor IS SELECT employee_id, last_name FROM employees WHERE department_id = 30; v_empno employees.employee_id%TYPE; v_lname employees.last_name%TYPE; BEGIN OPEN emp_cursor; -- Point A ... Mark for Review (1) Points GET emp_cursor INTO v_empno, v_lname; FETCH emp_cursor INTO v_empno, v_lname; (*) GET emp_cursor.employee_id, emp_cursor.last_name INTO v_empno, v_lname; FETCH emp_cursor.employee_id, emp_cursor.last_name INTO v_empno, v_lname; Correct 13.
There are 8 countries in REGION_ID 13 (Central America). What will happen
when the following code is executed? DECLARE CURSOR country_curs IS SELECT country_name FROM wf_countries WHERE region_id = 13; v_country_name wf_countries.country_name%TYPE; BEGIN OPEN country_curs; WHILE country_curs%FOUND LOOP FETCH country_curs INTO v_country_name; DBMS_OUTPUT.PUT_LINE(v_country_name); END LOOP; CLOSE country_curs; END; Mark for Review (1) Points Eight rows will be fetched and displayed successfully. The last seven rows will be fetched and displayed. The block will execute, but no rows will be displayed. (*) The block will fail because you can not use a WHILE loop with an explicit cu rsor. None of the above. Incorrect. Refer to Section 5 Lesson 1. 14. Which of the following best describes the difference between implicit and explicit cursors? Mark for Review (1) Points Implicit cursors are used for SELECT statements, while explicit cursors are used for DML statements. Implicit cursor are named by the PL/SQL programmer, while explicit cursors a re always named SQL. Implicit cursors are defined automatically by Oracle, while explicit cursors must be declared by the PL/SQL programmer. (*) Implicit cursors store rows on disk, while explicit cursors store rows in me mory. Correct
Quiz: Using Explicit Cursor Attributes 1.
You have declared the following cursor:
CURSOR country_curs IS SELECT * FROM wf_countries ORDER BY country_name; There are over 200 rows in the WF_COUNTRIES table, but you want to fetch and dis play only the first 25 rows. How would you exit from the FETCH loop? Mark for Review (1) Points EXIT WHEN country_curs%FOUND(25); EXIT WHEN country_curs%ROWCOUNT > 25; (*) EXIT WHEN ROWCOUNT > 25; WHEN country_curs > 25 THEN EXIT; END IF; Correct 2. Examine the following code: DECLARE CURSOR country_curs IS SELECT country_id, country_name FROM wf_countries ORDER BY country_name; v_country country_curs%ROWTYPE; BEGIN OPEN country_curs; LOOP FETCH country_curs INTO v_country; EXIT WHEN country_curs%NOTFOUND; ------- Line A END LOOP; CLOSE country_curs; END; You want to display the id and name of each FETCHed country. What would you code at Line A? Mark for Review (1) Points DBMS_OUTPUT.PUT_LINE(country_id || ' ' || country_name); DBMS_OUTPUT.PUT_LINE(v_country(country_id) || ' ' || v_country(country_name) ); DBMS_OUTPUT.PUT_LINE(country_curs.country_id || ' ' || country_curs.country_ name); DBMS_OUTPUT.PUT_LINE(v_country.country_id || ' ' || v_country.country_name); (*) Incorrect. Refer to Section 5 Lesson 2.
3. Look at the following code: DECLARE CURSOR emp_cursor IS SELECT employee_id, last_name, salary FROM employees; v_empcurs emp_cursor%ROWTYPE; What is the data type of V_EMPCURS? Mark for Review (1) Points Scalar Record (*) Cursor Row Correct 4. Look at these declarations: DECLARE CURSOR dept_loc_cursor IS SELECT department_id, department_name, location_name FROM departments d, locations l WHERE d.location_id = l.location_id; v_dept_loc dept_loc_cursor%ROWTYPE; How many fields does V_DEPT_LOC contain? Mark for Review (1) Points Two, because the cursor joins two tables Four Three (*) None Incorrect. Refer to Section 5 Lesson 2. 5. Which of the following explicit cursor attributes evaluates to TRUE if the most recent FETCH returns a row? Mark for Review (1) Points %ISOPEN %NOTFOUND %FOUND (*)
%ROWCOUNT Incorrect. Refer to Section 5 Lesson 2. 6. You can reference explicit cursor attributes directly in a SQL statement. True or False? Mark for Review (1) Points True False (*) Incorrect. Refer to Section 5 Lesson 2. 7. How must you reference one field which is part of a PL/SQL record? for Review (1) Points
Mark
field_name.record_name record_name.field_name (*) record_name(field_name) field_name OF record_name It cannot be done. Incorrect. Refer to Section 5 Lesson 2.
Quiz: Cursor FOR Loops 1. Which of the following is a benefit of using a cursor FOR loop? eview (1) Points
Mark for R
The exception handling is done automatically. . The OPEN, CLOSE, FETCH and EXIT from the loop are done automatically. (*) You can OPEN the same cursor twice at the same time. Because there is less code, the loop executes faster. %ROWCOUNT increments automatically each time a row is FETCHed. Incorrect. Refer to Section 5 Lesson 3. 2. A cursor FOR loop using a subquery can extensively shorten code length whe n compared to an explicit cursor declaration. True or False? Mark for Review
(1) Points True (*) False Correct 3. Which one of the following is a valid cursor FOR loop with a subquery? ark for Review (1) Points
M
FOR emp_rec IN (SELECT last_name || first_name FROM employees) LOOP ... FOR emp_rec IN (SELECT UPPER(last_name) FROM employees) LOOP ... FOR emp_rec IN SELECT last_name, salary*12 "ANNSAL" FROM employees LOOP ... FOR emp_rec IN (SELECT last_name, salary*12 "ANNSAL" FROM employees) LOOP .. . (*) None of the above. Incorrect. Refer to Section 5 Lesson 3. 4. What is the DISadvantage of using a cursor FOR loop with a subquery? k for Review (1) Points You cannot reference cursor attributes such as %NOTFOUND. (*) The execution speed is slower. You cannot declare the cursor in the declaration section. You cannot use the cursor to join two or more tables. There are no disadvantages. Incorrect. Refer to Section 5 Lesson 3. 5. What is wrong with the following piece of code? BEGIN FOR emp_record IN emp_cursor LOOP DBMS_OUTPUT.PUT_LINE(emp_record.last_name); END LOOP; IF emp_record.last_name = 'Patel' THEN ... Mark for Review (1) Points EMP_RECORD has not been explicitly declared. The cursor has not been OPENed.
Mar
You cannot reference EMP_RECORD outside the loop. (*) It should read: DBMS_OUTPUT.PUT_LINE(emp_cursor.last_name); Nothing is wrong, the code will execute correctly. Correct 6. Look at the following code: DECLARE CURSOR emp_cursor IS SELECT * FROM employees; BEGIN FOR emp_record IN emp_cursor LOOP DBMS_OUTPUT.PUT_LINE( --Point A -- ); END LOOP; END; To display the salary of an employee, what code should you write at Point A? Mark for Review (1) Points emp_record.salary (*) emp_cursor.salary employees.salary emp_record.employees.salary TO_CHAR(salary) Incorrect. Refer to Section 5 Lesson 3. 7. You have declared a cursor as follows: CURSOR loc_curs IS SELECT * FROM locations; How should you code a FOR loop to use this cursor? Mark for Review (1) Points FOR loc_rec IN 1 .. loc_curs%ROWCOUNT LOOP ... WHILE loc_rec IN loc_curs LOOP ... FOR loc_curs IN loc_rec LOOP ... IF loc_rec IN loc_curs LOOP ... FOR loc_rec IN loc_curs LOOP ... (*) Incorrect. Refer to Section 5 Lesson 3.
Quiz: Cursors for Parameters 1. What is wrong with the following cursor declaration? SELECT * FROM departments WHERE location_id = p_loc_id; Mark for Review (1) Points You cannot reference a cursor parameter in a WHERE clause. The parameter should be coded as: (p_loc_id NUMBER) (*) The parameter should be coded as: (p_loc_id IN NUMBER) Nothing is wrong, the cursor declaration is correct. Correct 2. The following cursor has been declared: CURSOR emp_curs (p_dept_id employees.department_id%TYPE, p_job_id employees.job_id%TYPE) IS SELECT * FROM employees WHERE department_id = p_dept_id AND job_id = p_job_id; Which of the following will correctly open the cursor? Mark for Review (1) Points OPEN emp_curs(20); FOR emp_rec IN emp_curs(20) LOOP ... OPEN emp_curs('IT_PROG', 20); FOR emp_rec IN emp_curs(20,'IT_PROG') LOOP ... (*) FOR emp_rec IN emp_curs(p_dept_id p_job_id) LOOP ... Incorrect. Refer to Section 5 Lesson 4. 3. You want to use explicit cursors to fetch and display all the countries in a specific region. There are 19 rows in the WF_WORLD_REGIONS table. You want to use a different region each time the cursor is opened. How many cursors should you declare? Mark for Review (1) Points 19 cursors, all in the same PL/SQL block. 19 cursors in 19 PL/SQL blocks (one in each block).
20 cursors, in case an extra row is inserted into WF_WORLD_REGIONS later. One cursor with a parameter in the WHERE clause. (*) None of the above. Incorrect. Refer to Section 5 Lesson 4. 4. Using parameters with a cursor, you can open and close the cursor several times in a block, returning a different active set each time. True or False? M ark for Review (1) Points True (*) False Correct 5. Look at the following code: DECLARE CURSOR emp_curs (p_dept_id employees.department_id%TYPE) IS SELECT * FROM employees WHERE department_id = p_dept_id; v_emp_rec emp_curs%ROWTYPE; v_deptid NUMBER(4) := 50; BEGIN OPEN emp_curs( -- Point A --); .... You want to open the cursor, passing value 50 to the parameter. Which of the fol lowing are correct at Point A? Mark for Review (1) Points 50 v_deptid 100 / 2 All of the above. (*) Incorrect. Refer to Section 5 Lesson 4. Quiz: Using Cursors for Update 1. What is the difference between the following two blocks of code? --Block A DECLARE CURSOR emp_cursor IS SELECT employee_id, last_name FROM employees
WHERE department_id = 80 FOR UPDATE OF salary; --Block B DECLARE CURSOR emp_cursor IS SELECT employee_id, last_name FROM employees WHERE department_id = 80 FOR UPDATE OF salary NOWAIT; Mark for Review (1) Points There is no difference; the programs behave exactly the same way. In Block A, the program waits indefinitely until the rows are available. In Block B, the program returns control immediately so that it can do other work. ( *) In Block A, the program waits indefinitely until the rows are available. In Block B, control is returned to your program after 5 seconds so that it can do o ther work. Correct 2. When can we use the WHERE CURRENT OF clause? (1) Points
Mark for Review
Only with an UPDATE, not with a DELETE. Only with a DELETE, not with an UPDATE. When the cursor is declared as SELECT ... FOR UPDATE ...; (*) When the cursor is based on a single table (not on a join). When the cursor has not been OPENed. Incorrect. Refer to Section 5 Lesson 5. 3. You declare a cursor as a join of two tables: CURSOR emp_dept_curs IS SELECT last_name, salary, department_name FROM employees e, departments d WHERE e.department_id = d.department_id -- Point A -- ; You want to lock fetched rows from EMPLOYEES, but NOT lock fetched rows from DEP ARTMENTS. Which of the following is correct at Point A? Mark for Review (1) Points
FOR UPDATE FOR UPDATE of salary (*) FOR UPDATE OF employees FOR UPDATE (last_name) Correct 4. If the rows you attempt to reserve using FOR UPDATE have already been lock ed by another session and you use the NOWAIT option, what is the outcome? Mark for Review (1) Points The block executes successfully with no errors. The server will wait until the locks have been released by the other user. An Oracle server error occurs. (*) Your rows will override the other users' lock and your block will execute su ccessfully. Incorrect. Refer to Section 5 Lesson 5. 5. You have declared the following cursor: CURSOR country_curs IS SELECT country_id, country_name FROM wf_countries FOR UPDATE WAIT 10; Another user updates a row in WF_COUNTRIES but does not COMMIT the update. What will happen when you OPEN country_curs; ? Mark for Review (1) Points A LOCKED_ROWS exception is raised immediately. The other user's transaction is automatically rolled back. Your session waits indefinitely until the other user COMMITs. Your session waits for 10 seconds, and then returns control to your block so that it can continue to execute. (*) Your block fails because you should have coded: FOR UPDATE WAIT (10); Incorrect. Refer to Section 5 Lesson 5. 6. You want to fetch rows from the EMPLOYEES table. You want to lock the fetc hed rows, to prevent other users from updating them. You declare the following c
ursor: CURSOR emp_curs IS SELECT employee_id, last_name, salary FROM employees -- Line A -- ; What should you code at Line A? What should you code at Line A? Mark for Review (1) Points FOR LOCK FOR UPDATE OF employees FOR UPDATE (*) FOR UPDATE (employees) Correct 7. You have declared a cursor as SELECT .... FOR UPDATE; You have OPENed the cursor and locked the FETCHed rows. When are these row locks released? Mark fo r Review (1) Points When an UPDATE ... WHERE CURRENT OF cursor_name; is executed. When you CLOSE the cursor. When your block finishes executing. When you explicitly COMMIT or ROLLBACK your transaction. (*) When another user tries to SELECT the rows. Correct Quiz: Using Multiple Cursors 1. Assume that table BIGDEPTS contains 100 rows, and table BIGEMPS contains 100 0 rows, with 10 employees in each department. Consider the following code: DECLARE CURSOR bigdept_cur IS SELECT * FROM bigdepts; CURSOR bigemp_cur IS SELECT * FROM bigemps; BEGIN FOR dept_rec IN bigdept_cur LOOP DBMS_OUTPUT.PUT_LINE (dept_rec.department_name); FOR emp_rec IN bigemp_cur LOOP IF emp_rec.department_id=dept_rec.department_id THEN DBMS_OUTPUT.PUT_LINE (emp_rec.last_name); END IF;
END LOOP; END LOOP; END; Why is this code inefficient? Mark for Review (1) Points It locks both tables unnecessarily. It is using two cursors when one cursor is enough. It is doing a Cartesian Product, joining every employee with every departmen t and displaying 1100 lines of output. It reads 1000 employee rows every time BIGEMP_CUR is OPENed, and then ignore s 990 of them. (*) It is using cursor FOR loops, which are less efficient than OPENing and CLOS Eing the cursors explicitly. Correct 2. Which of the following is NOT NOT allowed when using multiple cursors with par par ameters? Mark for Review (1) Points You cannot use cursor FOR loops. You cannot declare the cursors FOR UPDATE. You cannot declare a cursor based on a join. You cannot OPEN more than one cursor at the same time. None of the above, they are all allowed. (*) Correct 3. Assume your schema schema contains 25 tables. How many many explicit cursors cursors can you d eclare and use within a single PL/SQL block? Mark for Review (1) Points Only one. As many as you need - there is no limit. (*) A maximum of three. As many as you need, but only one of them can be open at any time. A maximum of 25 (one for each table in your schema). Incorrect. Refer to Section 5 Lesson 6.
4. Which of the following is a good reason to use two cursors cursors in a single single PL/ SQL block? Mark for Review (1) Points To allow one cursor to be opened twice at the same time. When two tables are related to each other (often by a foreign key) and we wa nt to produce a multilevel report using data from both tables. (*) To allow rows to be locked as they are FETCHed. To speed up the execution of the PL/SQL block. It is the only way to declare a cursor with a parameter. Incorrect. Refer to Section 5 Lesson 6. 5. You want to produce a report which displays each department and (immediate ly after each department) a list of employees who work in that department. You d eclare a DEPARTMENTS cursor as: CURSOR dept_curs IS SELECT * FROM departments ORDER BY department_id; How could you declare the EMPLOYEES cursor? (Choose two). Mark for Review (1) Points (Choose all correct answers) CURSOR emp_curs IS SELECT * FROM employees; CURSOR emp_curs (p_dept_id NUMBER) IS SELECT * FROM employees WHERE department_id = p_dept_id; (*) CURSOR emp_curs IS SELECT * FROM employees ORDER BY department_id; CURSOR emp_curs (p_dept_id departments.department_id%TYPE) IS SELECT * FROM employees WHERE department_id = p_dept_id; (*) CURSOR emp_curs IS SELECT * FROM employees WHERE department_id = departments.departmen departments.department_id; t_id;
Incorrect. Refer to Section 5 Lesson 6. 6. Examine the following code: DECLARE CURSOR region_cur IS SELECT * FROM wf_world_regions; v_region_rec region_cur%ROWTYPE; CURSOR country_cur (p_region_id NUMBER) IS SELECT * FROM wf_countries WHERE region_id = p_region_id; v_country_rec country_cur%ROWTYPE; BEGIN OPEN region_cur; LOOP FETCH region_cur INTO v_region_rec; EXIT WHEN region_cur%NOTFOUND; DBMS_OUTPUT.PUT_LINE (v_region_rec.region_name); -- Line A -LOOP FETCH country_cur INTO v_country_rec; EXIT WHEN country_cur%NOTFOUND; ...... What would you code at Line A? Mark for Review (1) Points OPEN country_cur (p_region_id); OPEN country_cur (wf_world_regions.reg (wf_world_regions.region_id); ion_id); OPEN country_cur (v_region_rec.region_id); (*) OPEN country_cur (region_cur.region_id (region_cur.region_id); ); OPEN country_cur; Incorrect. Refer to Section 5 Lesson 6. Section 6 Quiz: User-Defined Records 1. Which of the following will successfully successfully create a record type containing containing two fields, and a record variable of that type? Mark for Review (1) Points TYPE person_type IS RECORD (l_name VARCHAR2(20), gender CHAR(1)); person_rec TYPE person_type;
TYPE person_type IS RECORD (l_name VARCHAR2(20), gender CHAR(1)); person_rec person_type; (*) TYPE person_type IS (l_name VARCHAR2(20), gender CHAR(1)); person_rec person_type; TYPE person_type IS (l_name VARCHAR2(20), gender CHAR(1)); person_rec TYPE person_type;
Incorrect. Refer to Section 6 Lesson 1 2. Which of the following statements about user-defined PL/SQL records is NOT true? Mark for Review (1) Points It is not the same as a row in a database table It can be used as an OUT parameter in a package procedure It can be a component of another PL/SQL record It must contain one or more components, but all the components must have sca lar datatypes (*) It can be defined as NOT NULL Incorrect. Refer to Section 6 Lesson 1 3. The following code declares a PL/SQL record with the same structure as a r ow of the departments table. True or False? DECLARE v_dept_rec departments%ROWTYPE; ... Mark for Review (1) Points True (*) False Correct
Quiz: Indexing Tables of Records 1. To declare an INDEX BY table, we must first declare a type and then declare a collection variable of that type. True or False? Mark for Review (1) Points True (*) False Incorrect. Refer to Section 6 Lesson 2. 2. Which of these PL/SQL data structures could store a complete copy of the e mployees table, i.e., 20 complete table rows? Mark for Review (1) Points A record An INDEX BY table of records (*) An INDEX BY table An explicit cursor based on SELECT * FROM employees; Incorrect. Refer to Section 6 Lesson 2. 3. What is the largest number of elements (i.e., records) that an INDEX BY ta ble of records can contain? Mark for Review (1) Points 100 4096 32767 Many millions of records because a BINARY_INTEGER or PLS_INTEGER can have a very large value (*) None of the above Incorrect. Refer to Section 6 Lesson 2. 4. Which of the following methods can be used to reference elements of an IND EX BY table? (Choose three.) Mark for Review (1) Points (Choose all correct answers) EXISTS (*) FIRST (*)
COUNT (*) PREVIOUS DROP Incorrect. Refer to Section 6 Lesson 2. 5. Which of these PL/SQL data structures can NOT store a collection? or Review (1) Points
Mark f
A PL/SQL record (*) An INDEX BY table of records An INDEX BY table indexed by PLS_INTEGER An INDEX BY table indexed by BINARY_INTEGER Incorrect. Refer to Section 6 Lesson 2. 6. Which of the following successfully declares an INDEX BY table of records which could be used to store copies of complete rows from the departments table? Mark for Review (1) Points DECLARE TYPE t_depttab IS TABLE OF departments%TYPE INDEX BY BINARY_INTEGER; DECLARE TYPE t_depttab IS TABLE OF departments%ROWTYPE INDEX BY BINARY_INTEGER; (*) DECLARE TYPE t_depttab IS INDEX BY TABLE OF departments%ROWTYPE INDEX BY BINARY_INTEGER; DECLARE TYPE t_depttab IS TABLE OF departments%ROWTYPE INDEX BY NUMBER;
Incorrect. Refer to Section 6 Lesson 2. Section 7 Quiz: Handling Exceptions
1. Which of these exceptions can be handled by an EXCEPTION section in a PL/SQL block? Mark for Review (1) Points An attempt is made to divide by zero A SELECT statement returns no rows Any other kind of exception that can occur within the block All of the above (*) None of the above Incorrect. Refer to Section 7 Lesson 1. 2. Examine the following code. Why does this exception handler not follow goo d practice guidelines? (Choose two.) DECLARE v_dept_name departments.department_name%TYPE; BEGIN SELECT department_name INTO v_dept_name FROM departments WHERE department_id = 75; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('A select returned more than one row'); END; Mark for Review (1) Points (Choose all correct answers) You should not use DBMS_OUTPUT.PUT_LINE in an exception handler. department_id 75 does not exist in the departments table. The exception handler should test for the named exception NO_DATA_FOUND. (* ) The exception handler should COMMIT the transaction. The exception section should include a WHEN TOO_MANY_ROWS exception handler . (*) Incorrect. Refer to Section 7 Lesson 1. 3. Which of the following are NOT good practice guidelines for exception hand ling? (Choose two.) Mark for Review (1) Points (Choose all correct answers) Test your code with different combinations of data to see what potential er rors can happen.
Use an exception handler whenever there is any possibility of an error occu rring. Include a WHEN OTHERS handler as the first handler in the exception section . (*) Allow exceptions to propagate back to the calling environment. (*) Handle specific named exceptions where possible, instead of relying on WHEN OTHERS. Incorrect. Refer to Section 7 Lesson 1. 4. Which of the following is NOT an advantage of including an exception handl er in a PL/SQL block? Mark for Review (1) Points Prevents errors from occurring (*) Code is more readable because error-handling routines can be written in the same block in which the error occurred Prevents errors from being propagated back to the calling environment Avoids costly and time-consuming correction of mistakes Incorrect. Refer to Section 7 Lesson 1. 5.
Which of the following best describes a PL/SQL exception?
Mark for Revie
w (1) Points A user enters an invalid password while trying to log on to the database. An error occurs during the execution of the block, which disrupts the normal operation of the program. (*) A compile-time error occurs because the PL/SQL code references a non-existen t table. The programmer forgets to declare a cursor while writing the PL/SQL code. Correct 6. Only one exception can be raised automatically during one execution of a P L/SQL block. True or False? Mark for Review (1) Points True (*) False Incorrect. Refer to Section 7 Lesson 1.
7. Which of the following EXCEPTION sections are constructed correctly? (Choo se three.) Mark for Review (1) Points (Choose all correct answers) EXCEPTION WHEN NO_DATA_FOUND THEN statement_1; WHEN OTHERS THEN statement_2; END; (*) EXCEPTION WHEN TOO_MANY_ROWS THEN statement_1; END; (*) EXCEPTION WHEN NO_DATA_FOUND THEN statement_1; WHEN NO_DATA_FOUND THEN statement_2; WHEN OTHERS THEN statement_3; END; EXCEPTION WHEN OTHERS THEN statement_1; END; (*) EXCEPTION WHEN OTHERS THEN statement_1; WHEN NO_DATA_FOUND THEN statement_2; END;
Incorrect. Refer to Section 7 Lesson 1. 8. The following EXCEPTION section is constructed correctly. True or False? EXCEPTION WHEN ZERO_DIVIDE OR TOO_MANY_ROWS OR NO_DATA_FOUND THEN statement_1; statement_2; WHEN OTHERS THEN statement_3; END; Mark for Review (1) Points True (*) False
Correct
Quiz: Trapping Oracle Server Exceptions 1. Which type(s) of exception MUST be explicitly raised by the PL/SQL programme r? Mark for Review (1) Points User-defined exceptions. (*) Predefined Oracle server errors such as TOO_MANY_ROWS. Non-predefined Oracle server errors such as ORA-01203. All of the above. Incorrect. Refer to Section 7 Lesson 2. 2. What is the correct syntax to associate an exception named EXCEPNAME with the non-predefined Oracle Server error ORA-02292? Mark for Review (1) Points PRAGMA EXCEPTION_INIT (newname, -2292) (*) RAISE_APPLICATION_ERROR (-2292, excepname); SQLCODE (-2292, excepname); WHEN (-2292, excepname) THEN ? Incorrect. Refer to Section 7 Lesson 2. 3. Which one of the following events would implicitly raise an exception? ark for Review (1) Points
M
The PL/SQL programmer mis-spells the word BEGIN as BEGAN. A database constraint is violated. (*) A SELECT statement returns exactly one row. An UPDATE statement modifies no rows. Incorrect. Refer to Section 7 Lesson 2. 4. Examine the following code. At Line A, you want to raise an exception if t he employee's manager_id is null. What kind of exception is this? DECLARE v_mgr_id employees.manager_id%TYPE;
BEGIN SELECT manager_id INTO v_mgr_id FROM employees WHERE employee_id = 100; IF v_mgr_id IS NULL THEN -- Line A END IF; ... Mark for Review (1) Points A predefined Oracle Server exception A constraint violation A non-predefined Oracle server exception A user-defined exception (*) A NO_DATA_FOUND exception Incorrect. Refer to Section 7 Lesson 2. 5. An ORA-1400 exception is raised if an attempt is made to insert a null val ue into a NOT NULL column. DEPARTMENT_ID is the primary key of the DEPARTMENTS t able. What will happen when the following code is executed? DECLARE e_not_null EXCEPTION; BEGIN PRAGMA EXCEPTION_INIT(e_not_null, -1400); INSERT INTO departments (department_id, department_name) VALUES(null, 'Marketing'); EXCEPTION WHEN e_not_null THEN DBMS_OUTPUT.PUT_LINE('Cannot be null'); END; Mark for Review (1) Points The exception will be raised and "Cannot be null" will be displayed. The code will not execute because the syntax of PRAGMA EXCEPTION_INIT is wro ng. The code will not execute because PRAGMA EXCEPTION_INIT must be coded in the DECLARE section. (*) The code will not execute because the syntax of the INSERT statement is wron g. Correct 6. A PL/SQL block executes and an Oracle Server exception is raised. Which of the following contains the text message associated with the exception? Mark f or Review (1) Points
SQLCODE SQLERRM (*) SQL%MESSAGE SQL_MESSAGE_TEXT Incorrect. Refer to Section 7 Lesson 2. 7. Which of the following is NOT a predefined Oracle Server error? Review (1) Points
Mark for
NO_DATA_FOUND TOO_MANY_ROWS e_sal_too_high EXCEPTION; (*) ZERO_DIVIDE DUP_VAL_ON_INDEX Correct 8. There are no employees whose salary is less than 2000. Which exception han dlers would successfully trap the exception which will be raised when the follow ing code is executed? (Choose two.) DECLARE v_mynum NUMBER := 10; v_count NUMBER; BEGIN SELECT COUNT(*) INTO v_count FROM employees WHERE salary < 2000; v_mynum := v_mynum / v_count; EXCEPTION ... END; Mark for Review (1) Points (Choose all correct answers) NO_DATA_FOUND ZERO_DIVIDE (*) SQL%ROWCOUNT = 0 OTHERS (*) OTHER
Correct 9. Which of the following best describes a predefined Oracle Server error? Mark for Review (1) Points Has a standard Oracle error number but must be declared and named by the PL/ SQL programmer Has a standard Oracle error number and a standard name which can be referenc ed in the EXCEPTION section (*) Is associated with an Oracle error number using PRAGMA EXCEPTION_INIT Is not raised automatically but must be declared and raised explicitly by th e PL/SQL programmer Incorrect. Refer to Section 7 Lesson 2. 10. Which kind of error can NOT be handled by PL/SQL? (1) Points
Mark for Review
Syntax errors (*) Predefined Oracle Server errors Non-predefined Oracle Server errors User-defined errors Incorrect. Refer to Section 7 Lesson 2. 11. Examine the following code. The UPDATE statement will raise an ORA-02291 exception. BEGIN UPDATE employees SET department_id = 45; EXCEPTION WHEN OTHERS THEN INSERT INTO error_log_table VALUES (SQLCODE); END; What will happen when this code is executed? Mark for Review (1) Points The code will execute and insert error number 02291 into error_log_table. The code will fail because SQLCODE has not been declared. The code will fail because we access error message numbers by using SQLERRNU M, not SQLCODE. The code will fail because we cannot use functions like SQLCODE directly in a SQL statement. (*)
Incorrect. Refer to Section 7 Lesson 2. 12. How would you trap Oracle Server exception ORA-01403: no data found? rk for Review (1) Points WHEN NO DATA FOUND THEN ... WHEN ORA-01403 THEN ... WHEN NO_DATA_FOUND THEN ... (*) WHEN SQL%ROWCOUNT=0 THEN ... Incorrect. Refer to Section 7 Lesson 2.
Quiz: Trapping User-defined Exceptions 1. What is the datatype of a user-defined exception? (1) Points
Mark for Review
BOOLEAN VARCHAR2 EXCEPTION (*) NUMBER None of the above Incorrect. Refer to Section 7 Lesson 3. 2. How are user-defined exceptions raised ? (1) Points
Mark for Review
By PRAGMA EXCEPTION_INIT By DECLARE e_my_excep EXCEPTION; By RAISE exception_name; (*) None of the above. They are raised automatically by the Oracle server. Incorrect. Refer to Section 7 Lesson 3. 3. The following line of code is correct. True or False? RAISE_APPLICATION_ERROR(-21001,'My error message'); Mark for Review (1) Points
Ma
True False (*) Correct 4. The following three steps must be performed to use a user-defined exceptio exceptio n: - Raise the exception - Handle the exception - Declare the exception In what sequence must these steps be performed? Mark for Review (1) Points Raise, Handle, Declare Handle, Raise, Declare Declare, Raise, Handle (*) The steps can be performed in any order. Incorrect. Refer to Section 7 Lesson 3. 5. What will be displayed when the following code is executed? DECLARE e_myexcep EXCEPTION; BEGIN DBMS_OUTPUT.PUT_LINE('Message DBMS_OUTPUT.PUT_LINE(' Message 1'); RAISE e_myexcep; DBMS_OUTPUT.PUT_LINE('Message DBMS_OUTPUT.PUT_LINE(' Message 2'); EXCEPTION WHEN e_myexcep THEN DBMS_OUTPUT.PUT_LINE('Message DBMS_OUTPUT.PUT_LINE('M essage 3'); RAISE e_myexcep; DBMS_OUTPUT.PUT_LINE('Message DBMS_OUTPUT.PUT_LINE('M essage 4'); END; Mark for Review (1) Points Message 1 Message 3 Message 1 Message 3 Message 4 Message 1 Message 2 Message 3 Message 4 The code will not execute because it contains at least one syntax error.
The code will execute but will return an unhandled exception to the calling environment. (*)
Correct 6. You want to display your own error message to to the user. What is the correc correc t syntax to do this? Mark for Review (1) Points RAISE_APPLICATION_ERROR(20001, RAISE_APPLICATION_ERRO R(20001, 'My own message'); RAISE_APPLICATION_ERROR('My RAISE_APPLICATION_ERRO R('My own message', -20001); RAISE application_error; RAISE_APPLICATION_ERROR RAISE_APPLICATION_ERRO R (-20001, 'My own message'); (*) Incorrect. Refer to Section 7 Lesson 3. 7. What is a user-defined exception? (1) Points
Mark for Review
A predefined Oracle server exception such as NO_DATA_FOUND. An exception which has a predefined Oracle error number but no predefined na me. An exception handler which the user (the programmer) includes in the EXCEPTI ON section. An exception which is not raised automatically by the Oracle server, but mus t be declared and raised explicitly by the PL/SQL programmer. (*) Incorrect. Refer to Section 7 Lesson 3. 8. What is wrong with with the following code? BEGIN UPDATE employees SET salary = 20000 WHERE job_id = 'CLERK'; IF SQL%ROWCOUNT = 0 THEN RAISE NO_DATA_FOUND; -- Line A END IF; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('No DBMS_OUTPUT.PUT_LINE('N o employee was updated'); END; Mark for Review (1) Points You cannot use SQL%ROWCOUNT in conditional control statements such as IF or CASE.
NO_DATA_FOUND has not been DECLAREd Line A should be: HANDLE NO_DATA_FOUND You cannot explicitly raise predefined Oracle Server errors such as NO_DATA_ FOUND. Nothing is wrong, the code will execute correctly. (*) Incorrect. Refer to Section 7 Lesson 3.
Quiz: Recognizing the Scope of Exceptions
1. What will happen when the following following code is executed? executed? DECLARE e_excep1 EXCEPTION; e_excep2 EXCEPTION; BEGIN RAISE e_excep1; EXCEPTION WHEN e_excep1 THEN BEGIN RAISE e_excep2; END; END; Mark for Review (1) Points It will fail to compile because you cannot have a subblock inside an excepti on section. It will fail to compile because e_excep1 is out of scope in the subblock. It will fail to compile because you cannot declare more than one exception i n the same block. It will compile successfully and return an unhandled e_excep2 to the calling environment. (*) Incorrect. Refer to Section 7 Lesson 4. 2. What will happen when when the following code is executed? DECLARE e_outer_excep EXCEPTION; BEGIN DECLARE e_inner_excep EXCEPTION; BEGIN RAISE e_outer_excep; END; EXCEPTION WHEN e_outer_excep THEN DBMS_OUTPUT.PUT_LINE('Outer DBMS_OUTPUT.PUT_LINE('O uter raised');
WHEN e_inner_excep THEN DBMS_OUTPUT.PUT_LINE('Inner raised'); END; Mark for Review (1) Points The code will execute successfully and 'Outer Raised' will be displayed. The code will propagate the e_outer_excep back to the calling environment (A pplication Express). The code will fail to compile because e_inner_excep cannot be referenced in the outer block. (*) The code will fail to compile because e_inner_excep was declared but never R AISEd. Incorrect. Refer to Section 7 Lesson 4. 3. Predefined Oracle Server exceptions such as NO_DATA_FOUND can be raised au tomatically in inner blocks and handled in outer blocks. True or False? Mark f or Review (1) Points True (*) False Incorrect. Refer to Section 7 Lesson 4. 4. Non-predefined Oracle Server errors (associated with Oracle error numbers by PRAGMA EXCEPTION_INIT) can be declared and raised in inner blocks and handled in outer blocks. True or False? Mark for Review (1) Points True False (*) Correct 5. There are three employees in department 90. What will be displayed when th is code is executed? DECLARE v_last_name employees.last_name%TYPE; BEGIN DBMS_OUTPUT.PUT_LINE('Message 1'); BEGIN SELECT last_name INTO v_last_name FROM employees WHERE department_id = 90; DBMS_OUTPUT.PUT_LINE('Message 2'); END; DBMS_OUTPUT.PUT_LINE('Message 3'); EXCEPTION
WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Message 4'); END; Mark for Review (1) Points Message 1 Message 3 Message 4 Message 1 Message 4 (*) Message 1 An unhandled exception will be propagated back to the calling environment. None of the above
Correct 6. What will be displayed when the following code is executed? <> DECLARE v_myvar NUMBER; BEGIN v_myvar := 25; DECLARE v_myvar NUMBER := 100; BEGIN outer.v_myvar := 30; v_myvar := v_myvar / 0; outer.v_myvar := 35; END; v_myvar := 40; EXCEPTION WHEN ZERO_DIVIDE THEN DBMS_OUTPUT.PUT_LINE(v_myvar); END; Mark for Review (1) Points 25 30 (*) 35 40
100 Incorrect. Refer to Section 7 Lesson 4.
`
Section 8 Quiz: Creating Procedures 1. Which of the following keywords MUST be included in every PL/SQL procedure d efinition? (Choose two.) Mark for Review (1) Points (Choose all correct answers) BEGIN (*) REPLACE EXCEPTION DECLARE END (*) Incorrect. Refer to Section 8 Lesson 1. 2. Which of the following are characteristics of anonymous PL/SQL blocks but not PL/SQL subprograms? (Choose three.) Mark for Review (1) Points (Choose all correct answers) Can take parameters Are stored in the database Can begin with the keyword DECLARE (*) Are unnamed (*) Are compiled every time they are executed (*) Incorrect. Refer to Section 8 Lesson 1. 3. Which of the following are benefits of using PL/SQL subprograms rather tha n anonymous blocks? (Choose three.) Mark for Review (1) Points (Choose all correct answers) Better data security (*)
Code reuse (*) Stored externally Easier code maintenance (*) Do not need to define exceptions Incorrect. Refer to Section 8 Lesson 1. 4. Why will the following procedure fail? CREATE OR REPLACE PROCEDURE mainproc ... IS PROCEDURE subproc (...) IS BEGIN ... BEGIN ... subproc (...); ... END; Mark for Review (1) Points Procedure main proc must use the keyword AS not IS Procedure mainproc does not need the keyword BEGIN Procedure subproc does not need the keyword BEGIN Procedure subproc does not have an END; statement (*) Incorrect. Refer to Section 8 Lesson 1. 5. The following are the steps involved in creating, and later modifying and re-creating, a PL/SQL procedure in Application Express. Which step is missing? Type the procedure code in the SQL Commands window Click on the "Save" button and save the procedure code Retrieve the saved code from "Saved SQL" in SQL Commands Modify the code in the SQL Commands window Execute the code to re-create the procedure Mark for Review (1) Points Enter parameters and data type Exe ute the procedure from USRE_SOURCE data dictionary view Execute the code to create the procedure (*) Invoke the procedure from an anonymous block
Incorrect. Refer to Section 8 Lesson 1. 6. A programmer wants wants to create a PL/SQL procedure procedure named MY_PROC. MY_PROC. What will h appen when the following code is executed? CREATE OR REPLACE PROCEDURE my_proc IS v_empid employees.empid%TYPE; BEGIN SELECT employee_id INTO v_empid FROM employees WHERE region_id = 999; DBMS_OUTPUT.PUT_LINE('The DBMS_OUTPUT.PUT_LINE(' The salary is: ' || v_salary); Mark for Review (1) Points The statement will raise a NO_DATA_FOUND exception because region_id 999 doe s not exist. The statement will fail because the last line of code should be END my_proc; (*) The statement will fail because you cannot declare variables such as v_empid inside a procedure. Correct 7. Procedures are generally used to perform what? (1) Points
Mark for Review
A SELECT statement An action (*) A return of values All of the above None of the above Incorrect. Refer to Section 8 Lesson 1. 8. Subprograms and anonymous blocks can be called by other applications. applications. True or False? Mark for Review (1) Points True False (*) Correct 9. PL/SQL subprograms, subprograms, unlike anonymous blocks, blocks, are compiled each time they a re executed. True or False? Mark for Review (1) Points
True False (*) Correct 10. A stored procedure procedure add_dept may be invoked by the following command in Ap plication Express. True or False? BEGIN add_dept; END; Mark for Review (1) Points True (*) False Incorrect. Refer to Section 8 Lesson 1. 11. A nested subprogram subprogram can be called from the main procedure or or from the cal cal ling environment. True or False? Mark for Review (1) Points True False (*) Correct 12.
A stored PL/SQL procedure procedure can be be invoked from which of the following?
A PL/SQL anonymous block A calling application A SELECT statement Another PL/SQL procedure Mark for Review (1) Points A only A and B A and C A, B and D (*) B and C Incorrect. Refer to Section 8 Lesson 1.
13. When modifying procedure code, the procedure code statement must be re-ex ecuted to validate validate and store it it in the database. database. True True or False? Mark for Revie w (1) Points True False (*) Correct
Quiz: Using Parameters in Procedures
Quiz: Using Parameters in Procedures
1. What is the purpose of using parameters with stored procedures? eview (1) Points
Mark for R
They prevent the procedure from modifying data in the database. They allow values to be passed between the calling environment and the proce dure. (*) They count the number of exceptions raised by the procedure. They speed up the execution of the procedure. Incorrect. Refer to Section 8 Lesson 2. 2. Which of the following best describes describes the difference difference between a parameter a nd an argument? Mark for Review (1) Points They are both names of variables. A parameter is passed into the procedure, while an argument is passed out of the procedure A parameter is the name of a variable, while an argument is the datatype of that variable A parameter is the name of a variable that is passed into or out of a proced ure, while an argument is the value of that variable (*) There is no difference, parameters and arguments are the same thing Incorrect. Refer to Section 8 Lesson 2.
3. Which of the following can be used as an argument for a procedure paramete r? Mark for Review (1) Points The name of a variable. A literal value. An expression. All of the above. (*) None of the above. Incorrect. Refer to Section 8 Lesson 2. 4. What is the correct syntax to create procedure MYPROC that accepts two num ber parameters X and Y? Mark for Review (1) Points CREATE PROCEDURE myproc (x NUMBER, y NUMBER) IS ... (*) CREATE PROCEDURE (x NUMBER, y NUMBER) myproc IS ... CREATE PROCEDURE myproc IS (x NUMBER, y NUMBER) ... CREATE PROCEDURE IS myproc (x NUMBER, y NUMBER) ? Incorrect. Refer to Section 8 Lesson 2. 5. A procedure has been created as: CREATE PROCEDURE myproc (p_left NUMBER, p_right NUMBER) IS BEGIN .... You want to call the procedure from an anonymous block. Which of the following c alls is valid? Mark for Review (1) Points myproc(p_left, p_right); myproc(v_left, v_right); myproc(v_left, 30); All of the above. (*) Incorrect. Refer to Section 8 Lesson 2. Quiz: Passing Parameters
1. A procedure is invoked by this command: myproc('Smith',100,5000); What is the method of passing parameters used here? Mark for Review (1) Points Positional (*) Named A combination of positional and named. None of the above Incorrect. Refer to Section 8 Lesson 3. 2. Procedure NUMPROC has been created as: CREATE PROCEDURE numproc (x NUMBER, y NUMBER := 100, z NUMBER) IS BEGIN .... You want to call the procedure, passing arguments of 10 for X and 20 for Z. Whic h one of the following calls is correct? Mark for Review (1) Points numproc(10,,20); numproc(x=10,z=20); numproc(10,z=>20); (*) numproc(x=>10,20); Incorrect. Refer to Section 8 Lesson 3. 3. Which kind of parameters cannot have a DEFAULT value? (1) Points OUT (*) IN CONSTANT R(ead) W(rite) Incorrect. Refer to Section 8 Lesson 3.
Mark for Review
4. Three IN parameters for procedure ADD_EMPLOYEE are defined as: (p_name VARCHAR2 , p_salary NUMBER := 1000, p_hired DATE DEFAULT SYSDATE) The procedure is invoked by: add_employee('Jones'); What is the value of P_SALARY when the procedure starts to execute? Mark for Review (1) Points NULL 1000 (*) The procedure will not compile because P_SALARY should have been coded as DE FAULT 1000 The call will fail because P_SALARY is a required parameter Incorrect. Refer to Section 8 Lesson 3. 5. The following procedure has been created: CREATE OR REPLACE PROCEDURE myproc (p_p1 NUMBER, p_p2 VARCHAR2) IS BEGIN ... Which one of the following calls to the procedure will NOT work? Mark for Review (1) Points myproc(80, 'Smith'); myproc(p_p1 => 80, 'Smith'); (*) myproc(80, p_p2 => 'Smith'); myproc(p_p1 => 80, p_p2 => 'Smith'); Correct 6. Which of the following statements about IN OUT parameters is true? (Choose two.) Mark for Review (1) Points (Choose all correct answers) The data type for the parameter must be VARCHAR2. The parameter value passed into the subprogram is always returned unchanged to the calling environment. The parameter value can be returned as the original unchanged value. (*)
The parameter value can be returned as a new value that is set within the p rocedure. (*) Incorrect. Refer to Section 8 Lesson 3. 7. A procedure is invoked by this command: myproc('Smith',salary=>5000); What is the method of passing parameters used here? Mark for Review (1) Points Positional Named A combination of positional and named (*) None of the above Correct 8. When creating a procedure, where in the code must the parameters be listed ? Mark for Review (1) Points After the procedure name. (*) After the keyword IS or AS. Before the procedure name. After the keyword PROCEDURE. Incorrect. Refer to Section 8 Lesson 3. 9. If you don't specify a mode for a parameter, what is the default mode? ark for Review (1) Points OUT IN (*) COPY DEFAULT R(ead) Incorrect. Refer to Section 8 Lesson 3.
M
10. What are the three parameter modes for procedures? (1) Points
Mark for Review
IN, OUT, IN OUT (*) R(ead), W(rite), A(ppend) CONSTANT, VARIABLE, DEFAULT COPY, NOCOPY, REF Incorrect. Refer to Section 8 Lesson 3. 11. What will happen when the following procedure is called as format_phone ( 8005551234)? CREATE OR REPLACE PROCEDURE format_phone (p_phone_no IN OUT VARCHAR2) IS BEGIN p_phone_no := SUBSTR(p_phone_no,1,3) || '.' || SUBSTR(p_phone_no,4,3) || '.' || SUBSTR(p_phone_no,7); END format_phone; Mark for Review (1) Points The phone number 800.555.1234 is printed to the screen. The phone number (800) 555-1234 is printed to the screen. The phone number 800.555.1234 is placed into the p_phone_no variable. (*) The procedure does not execute because the input variable is not properly de clared. Incorrect. Refer to Section 8 Lesson 3. Section 9 Quiz: Creating Functions
1. A stored function: (1) Points
Mark for Review
must have at least one IN parameter. cannot be called in a SQL statement. must return one and only one value. (*) is called as a standalone executable statement.
Incorrect. Refer to Section 9 Lesson 1. 2.
To create a function successfully, the following steps should be performed
: Re-execute the code until it compiles correctly Write the code containing the CREATE or REPLACE FUNCTION followed by the functio n code Test the function from a SQL statement or an anonymous block If the function fails to compile, correct the errors Load the code into Application Express Execute the code in Application Express What is the correct order to perform these steps? Mark for Review (1) Points B,E,F,D,A,C (*) D,B,E,F,A,C B,C,E,F,D,A A,B,E,F,D,C Correct 3. A function may execute more than one RETURN statement found in the body of that function. True or False? Mark for Review (1) Points True False (*) Incorrect. Refer to Section 9 Lesson 1. 4.
CREATE FUNCTION get_sal (p_id employees.employee_id%TYPE)) RETURN number IS v_sal employees.salary%TYPE := 0; BEGIN SELECT salary INTO v_sal FROM employees WHERE employee_id = p_id; RETURN v_sal; END get_sal;
Which variable is passed to the function and which variable is returned from the function? Mark for Review (1) Points GET_SAL is passed and V_SAL is returned. SALARY is passed and P_ID is returned. EMPLOYEE_ID is passed and SALARY is returned. P_ID is passed and V_SAL is returned. (*) Incorrect. Refer to Section 9 Lesson 1. 5. You have created a function called GET_COUNTRY_NAME which accepts a countr y_id as an IN parameter and returns the name of the country. Which one of the fo llowing calls to the function will NOT work? Mark for Review (1) Points v_name := get_country_name(100); DBMS_OUTPUT.PUT_LINE(get_country_name(100)); SELECT get_country_name(100) FROM dual; BEGIN get_country_name(100, v_name); END; (*) Incorrect. Refer to Section 9 Lesson 1. 6. When using Invoker's rights, the invoker needs privileges on the database objects referenced within the subprogram, as well as GRANT privilege on the proc edure. True or False? Mark for Review (1) Points True False (*) Correct 7. Which of the following is found in a function and not a procedure? for Review (1) Points An exception section. IN parameters. Local variables in the IS/AS section. Return statement in the header. (*)
Mark
Incorrect. Refer to Section 9 Lesson 1. 8. What will happen when the following subprogram is compiled? CREATE OR REPLACE FUNCTION get_job (p_job jobs.job_id%TYPE) RETURN jobs.job_title%TYPE IS; v_title jobs.job_title%TYPE; BEGIN SELECT job_title INTO v_title FROM jobs WHERE job_id = p_jobid; RETURN v_title; END get_job; Mark for Review (1) Points The subprogram will work without errors. The subprogram will fail because of an error in the SELECT statement. (*) The subprogram will fail because the RETURN is declared with %TYPE. The program will compile successfully. Incorrect. Refer to Section 9 Lesson 1. 9. What is wrong with the following code? CREATE FUNCTION annual_comp (sal em ployees.salary%TYPE, comm_pct IN employees.commission%TYPE) RETURN NUMBER(5,2) I S BEGIN RETURN (sal*12) + NVL(comm_pct,0)*12*sal; END annual_comp; Mark for Re view (1) Points The sal parameter should specify the IN keyword. The RETURN NUMBER has a scale and precision. (*) There should be parentheses (brackets) around NVL(comm_pct,0)*12*sal The END; statement should not include the function name. Incorrect. Refer to Section 9 Lesson 1. 10. Procedure p1 has a single OUT parameter of type DATE. Function f1 returns a DATE. What is the difference between p1 and f1? Mark for Review (1) Points p1 can be invoked from an anonymous block but f1 cannot f1 can be used within a SQL statement but p1 cannot (*) p1 can have as many IN parameters as needed but f1 cannot have more than two IN parameters There is no difference because they both return a single value of the same d
atatype Correct 11. Based on the following function definition: Create function annual_comp ( sal employees.salary%type, comm_pct In employees.commission%type) ... Which one of the following is an incorrect call for annual_comp? Mark for Review (1) Points Execute dbms_output.put_line(annual_comp (1000,.2)) Select employee_id, annual_comp(salary, commission_pct) from employees; Declare Ann_comp number (6,2); Begin ... Ann_comp := annual_comp(1000,.2); . .. End; Select employee_id, annual_comp(salary) from employees; (*) Incorrect. Refer to Section 9 Lesson 1. 12. The following function has been created: CREATE OR REPLACE FUNCTION find_sal (p_emp_id IN employees.employee_id%TYPE) RETURN NUMBER IS ... We want to invoke this function from the following anonymous block: DECLARE v_mynum NUMBER(6,2); v_mydate DATE; BEGIN ... Line A END; Which of the following would you include at Line A? Mark for Review (1) Points find_sal(100,v_mynum); v_mynum := find_sal(100); (*) v_mydate := find_sal(100); find_sal(v_mynum,100); Incorrect. Refer to Section 9 Lesson 1. 13. Function GET_JOB accepts an employee id as input and returns that employe e's job id. Which of the following calls to the function will NOT work? Mark f or Review (1) Points DBMS_OUTPUT.PUT_LINE(get_job(100));
IF get_job(100) = 'IT_PROG' THEN ... get_job(100,v_job_id); (*) v_job_id := get_job(100); Incorrect. Refer to Section 9 Lesson 1. 14. A PL/SQL function can have IN OUT parameters. True or False? eview (1) Points
Mark for R
True False (*) Incorrect. Refer to Section 9 Lesson 1. 15. Function MYFUNC1 has been created, but has failed to compile because it c ontains syntax errors. We now try to create procedure MYPROC1 which invokes this function. Which of the following statements is true? Mark for Review (1) Points MYPROC1 will compile correctly, but will fail when it is executed. MYPROC1 will compile and execute succesfully. MYPROC1 will fail to compile because the function is invalid. (*) MYPROC1 will compile and execute successfully, except that the call to MYFUN C1 will be treated as a comment and ignored. Correct Quiz: Using Functions in SQL Statements 1. The following function has been created: CREATE OR REPLACE FUNCTION upd_dept (p_dept_id IN departments.department_id%TYPE) RETURN NUMBER IS BEGIN UPDATE departments SET department_name = 'Accounting' WHERE department_id = p_dept_id; RETURN p_dept_id; END; Which of the following will execute successfully? Mark for Review (1) Points DELETE FROM departments WHERE department_id = upd_dept(department_id);
SELECT upd_dept(department_id) FROM employees; DELETE FROM employees WHERE department_id = upd_dept(80); (*) SELECT upd_dept(80) FROM dual;
Incorrect. Refer to Section 9 Lesson 2. 2. Which of the following is NOT a legal location for a function call in a SQ L statement? Mark for Review (1) Points FROM clause of a SELECT statement (*) WHERE clause in a DELETE statement SET clause of an UPDATE statement VALUES clause of an INSERT statement Incorrect. Refer to Section 9 Lesson 2. 3. Function DOUBLE_SAL has been created as follows: CREATE OR REPLACE FUNCTION double_sal (p_salary IN employees.salary%TYPE) RETURN NUMBER IS BEGIN RETURN(p_salary * 2); END; Which of the following calls to DOUBLE_SAL will NOT work? Mark for Review (1) Points SELECT * FROM employees WHERE double_sal(salary) > 20000; SELECT * FROM employees ORDER BY double_sal(salary) DESC; UPDATE employees SET salary = double_sal(salary); SELECT last_name, double_sal(salary) FROM employees; None of the above; they will all work (*) Incorrect. Refer to Section 9 Lesson 2.
4. User-defined functions can extend the power of SQL statements where Oracle does not provide ready-made functions such as UPPER and LOWER. True or False? Mark for Review (1) Points True (*) False Correct 5. Which of the following is NOT a benefit of user-defined functions? for Review (1) Points
Mark
They can add business rules to the database and can be reused many times. They can be used in a WHERE clause to filter data. They can do the same job as built-in system functions such as UPPER and ROUN D. (*) They can often be used inside SQL statements. Incorrect. Refer to Section 9 Lesson 2. 6. You want to create a function which can be used in a SQL statement. Which one of the following can be coded within your function? Mark for Review (1) Points RETURN BOOLEAN One or more IN parameters (*) An OUT parameter COMMIT; Incorrect. Refer to Section 9 Lesson 2. Quiz: Review of Data Dictionary 1.
Which of the following statements about the "super-view" DICTIONARY is true? Mark for Review (1) Points It lists all the dictionary views. It can be thought of as a "catalog of the master catalog". We can use it like a Web search engine to remind ourselves of the names of d ictionary views.
All of the above. (*) None of the above. Incorrect. Refer to Section 9 Lesson 3. 2. User MARY executes the SQL statement: SELECT COUNT(*) FROM USER_VIEWS; A value of 15 is returned. Which of the following statements is true? Mark for Review (1) Points There are 15 views in Mary's schema. (*) Mary has created views on 15 of her tables. There are 15 views in the database. Other users have granted Mary SELECT privilege on 15 of their views. Incorrect. Refer to Section 9 Lesson 3. 3. Which of the following will display how many objects of each type are in a user's schema? Mark for Review (1) Points SELECT COUNT(*) FROM user_objects; SELECT object_type, COUNT(*) FROM user_objects GROUP BY object_type; (*) SELECT object_type, COUNT(*) FROM all_objects GROUP BY object_type; DESCRIBE user_objects GROUP BY object_type;
Incorrect. Refer to Section 9 Lesson 3. 4. You have forgotten the name of the Dictionary view USER_TABLES. Which of t he following statements is the best and quickest way to remind yourself? Mark for Review (1) Points
SELECT * FROM dictionary WHERE table_name LIKE USER%; Read the online Oracle documentation at http://technet.oracle.com. SELECT * FROM dict WHERE table_name LIKE 'USER%TAB%'; (*) SELECT * FROM dictionary WHERE table_name = 'USER_TABLES'; Phone the database administrator. Correct 5. A user executes the following statement: CREATE INDEX fn_index ON employees(first_name); What output will the following statement now display: SELECT index_name FROM user_indexes WHERE index_name LIKE 'fn%'; Mark for Review (1) Points fn_index FN_INDEX fn_index FN_INDEX No output will be displayed (*) Correct 6. User BOB is not a database administrator. BOB wants to see the names of al l the tables in his schema, as well as all the tables in other users' schemas wh ich he has privileges to use. Which Data Dictionary view would BOB query to do t his? Mark for Review (1) Points USER_TABLES ALL_TABLES (*) DBA_TABLES USER_TAB_COLUMNS
None of the above. Incorrect. Refer to Section 9 Lesson 3. 7. Which of the following is NOT a benefit of the Data Dictionary? Review (1) Points
Mark for
It allows us to remind ourselves of the names of our tables, in case we have fogotten them. It allows us to check which system privileges have been granted to us. It will speed up the execution of SELECT statements in which the WHERE claus e column is not indexed. (*) It allows the PL/SQL compiler to check for object existence; for example, wh en creating a procedure which references a table, the PL/SQL compiler can check that the table exists. Incorrect. Refer to Section 9 Lesson 3. 8. Which of the following best describes the Data Dictionary? ew (1) Points
Mark for Revi
It is a set of tables which can be updated by any user who has the necessary privileges. It is an automatically managed master catalog of all the objects stored in t he database. (*) It contains a backup copy of all the data in the database. It contains a list of all database tables which are not in any schema. Incorrect. Refer to Section 9 Lesson 3.
Quiz: Managing Procedures and Functions
1. The database administrator has granted the DROP ANY PROCEDURE privilege to u ser KIM. This allows Kim to remove other users' procedures and functions from th e database. How would Kim now drop function GET_EMP, which is owned by user MEHM ET? Mark for Review (1) Points DROP FUNCTION get_emp FROM mehmet DROP FUNCTION mehmet.get_emp (*)
DROP PROCEDURE mehmet.get_emp DROP PROGRAM mehmet.get_emp None of the above Incorrect. Refer to Section 9 Lesson 4. 2. You need to remove procedure BADPROC from your schema. What is the correct syntax to do this? Mark for Review (1) Points DELETE PROCEDURE badproc; DROP PROGRAM badproc; ALTER PROCEDURE badproc DISABLE; DROP PROCEDURE badproc; (*) Incorrect. Refer to Section 9 Lesson 4. 3. proc_a has been created as follows: CREATE OR REPLACE PROCEDURE proc_a IS v_last_name employees.last_name%TYPE; BEGIN SELECT last_name INTO v_last_name FROM employees WHERE employee_id = 999; /* This SELECT will raise an exception because employee_id 999 does not exis t */ DBMS_OUTPUT.PUT_LINE('This SELECT failed'); END; proc_b is now created as follows: CREATE OR REPLACE PROCEDURE proc_b IS BEGIN proc_a; DBMS_OUTPUT.PUT_LINE('proc_a was invoked'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('An exception occurred'); END; What will be displayed when proc_b is executed? Mark for Review (1) Points An exception occurred (*) This SELECT failed proc_a was invoked An exception occurred
This SELECT failed This SELECT failed proc_a was invoked Nothing will be displayed
Incorrect. Refer to Section 9 Lesson 4. 4. Procedure ins_emp accepts an employee_id as an IN parameter and attempts t o insert a row with that employee_id into the EMPLOYEES table. Ins_emp does not contain an exception section. A second procedure is created as follows: CREATE OR REPLACE PROCEDURE call_ins_emp IS BEGIN ins_emp(99); -- this employee does not exist ins_emp(100); -- this employee already exists ins_emp(999); -- this employee does not exist EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('An exception occurred'); END; When call_ins_emp is executed, which rows will be inserted into the EMPLOYEES ta ble? Mark for Review (1) Points 99 only (*) 99 and 999 All three rows will be inserted 999 only No rows will be inserted Incorrect. Refer to Section 9 Lesson 4. 5. Which view would you query to see the detailed code of a procedure? for Review (1) Points user_source (*) user_procedures user_objects user_dependencies user_errors
Mark
Incorrect. Refer to Section 9 Lesson 4. 6. Which dictionary view will list all the PL/SQL subprograms in your schema? Mark for Review (1) Points user_source user_procedures user_objects (*) user_dependencies user_subprograms Correct
Quiz: Review of Object Privileges
1. User SVETLANA creates a view called EMP_VIEW that is based on a SELECT from her EMPLOYEES table. Svetlana now wants user PHIL to be able to query the view. What is the smallest set of object privileges that Svetlana must grant to Phil? Mark for Review (1) Points SELECT on EMP_VIEW and SELECT on EMPLOYEES SELECT and EXECUTE on EMP_VIEW SELECT on EMP_VIEW (*) SELECT on EMP_VIEW and REFERENCES on EMPLOYEES Incorrect. Refer to Section 9 Lesson 5. 2. USERB creates a function called SEL_PROC (using Definer's Rights) which in cludes the statement: SELECT ... FROM usera.employees ...; USERC needs to execute UserB's procedure. What privileges are needed for this to work correctly? (Choose two.) Mark for Review (1) Points (Choose all correct answers)
UserB needs SELECT on userA.employees (*) UserC needs SELECT on userA.employees UserC needs EXECUTE on userB.sel_proc (*) UserA needs EXECUTE on userB.sel_proc UserC needs EXECUTE on Userb Incorrect. Refer to Section 9 Lesson 5. 3. User TOM needs to grant both SELECT and INSERT privileges on both his EMPL OYEES and DEPARTMENTS tables to both DICK and HARRY. What is the smallest number of GRANT statements needed to do this? Mark for Review (1) Points 1 2 (*) 3 4 8 Incorrect. Refer to Section 9 Lesson 5. 4. User COLLEEN owns an EMPLOYEES table and wants to allow user AYSE to creat e indexes on the table. Which object privilege must Colleen grant to Ayse? Mar k for Review (1) Points SELECT on EMPLOYEES INDEX on EMPLOYEES (*) ALTER on EMPLOYEES CREATE on EMPLOYEES None of the above Incorrect. Refer to Section 9 Lesson 5. 5. User DIANE owns a DEPARTMENTS table. User JOEL needs to update the locatio n_id column of Diane's table, but no other columns. Which SQL statement should D iane execute to allow this? Mark for Review (1) Points GRANT UPDATE ON departments TO joel; GRANT UPDATE ON departments(location_id) TO joel;
GRANT UPDATE ON departments.location_id TO joel; GRANT UPDATE(location_id) ON departments TO joel; (*) GRANT UPDATE ON location_id OF departments TO joel; Incorrect. Refer to Section 9 Lesson 5. 6. User FRED creates a procedure called DEL_DEPT using Definer's Rights, whic h deletes a row from Fred's DEPARTMENTS table. What privilege(s) will user BOB n eed to be able to execute Fred's procedure? Mark for Review (1) Points EXECUTE on DEL_DEPT (*) EXECUTE on DEL_DEPT and DELETE on DEPARTMENTS EXECUTE on DEL_DEPT and DELETE on FRED.DEPARTMENTS DELETE on FRED.DEPARTMENTS Correct
Quiz: Using Invokers Rights and Autonomous Transactions
1. Users SYS (the DBA), TOM, DICK and HARRY each have an EMPLOYEES table in the ir schemas. SYS creates a procedure DICK.SEL_EMP using Invoker's Rights which co ntains the following code: SELECT ... FROM EMPLOYEES ... ; HARRY now executes the procedure. Which employees table will be queried? Mark for Review (1) Points SYS.EMPLOYEES DICK.EMPLOYEES HARRY.EMPLOYEES (*) None of the above Incorrect. Refer to Section 9 Lesson 6. 2. What will happen when the following subprogram is compiled? PROCEDURE at_proc IS PRAGMA AUTONOMOUS_TRANSACTION;
dept_id NUMBER := 90; BEGIN UPDATE ... INSERT ... END at_proc; Mark for Review (1) Points The subprogram will fail because it is missing AUTHID CURRENT_USER before IS . The autonomous transaction subprogram will fail because it must include COMM IT or ROLLBACK. (*) The compilation will fail because a semicolon after AUTONOMOUS_TRANSACTION i s not needed. The program will compile successfully. Correct 3. User SALLY's schema contains a NEWEMP table. Sally uses Invoker's rights t o create procedure GET_NEWEMP which includes the line: SELECT ... FROM NEWEMP ... ; Sally also grants EXECUTE privilege on the procedure to CURLY, but no other priv ileges. What will happen when Curly executes the procedure? Mark for Review (1) Points The procedure will execute successfully. The procedure will fail because Curly does not have SELECT privilege on NEWE MP. The procedure will fail because there is no NEWEMP table in Curly's schema. (*) The procedure will fail because Curly does not have the EXECUTE ANY PROCEDUR E system privilege. Correct 4. When using Invoker's rights, the invoker needs privileges on the database objects referenced within the subprogram, as well as GRANT privilege on the proc edure. True or False? Mark for Review (1) Points True False (*) Correct
5. Procedure GET_EMPS includes a SELECT ... FROM EMPLOYEES. The procedure was created using Invoker's Rights. Which of the following statements are true? (Ch oose three.) Mark for Review (1) Points (Choose all correct answers) The user who executes the procedure needs EXECUTE privilege on the procedur e. (*) The creator of the procedure needs SELECT privilege on EMPLOYEES. (*) The user who executes the procedure does not need any privileges. The user who executes the procedure needs SELECT privilege on EMPLOYEES. (* ) Incorrect. Refer to Section 9 Lesson 6. 6. An autonomous transaction subprogram may be in a the same package as the c alling subprogram or may be in a separate subprogram. True or False? Mark for Review (1) Points True False (*) Correct 7. Which of the following is the correct syntax to create a procedure using I nvoker's Rights? Mark for Review (1) Points CREATE PROCEDURE myproc IS AUTHID CURRENT_USER BEGIN ... CREATE PROCEDURE myproc AUTHID CURRENT_USER IS BEGIN ... (*) CREATE PROCEDURE AUTHID CURRENT_USER myproc IS BEGIN ... CREATE PROCEDURE myproc IS BEGIN AUTHID CURRENT_USER ...
Incorrect. Refer to Section 9 Lesson 6. Section 10 Quiz: Creating Packages 1. Package EMP_PACK contains two procedures, DEL_EMP and SHOW_EMP. You want to write an anonymous block which invokes these procedures but you have forgotten w hich parameters they use. Which of the following will give you this information? Mark for Review (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 2. To be able to invoke a package subprogram from outside the package, it mus t 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. 3. Which of the following are good reasons to group a set of procedures and f unctions into a package? Mark for Review (1) Points Application developers do not need to know the details of the package body c ode. Related subprograms and variables can be grouped together for easier managem ent and maintenance.
If the detailed code is changed, aplications which invoke the package do not need to be recompiled. All of the above. (*) Correct 4. In which component of a package is the full definition of a public procedu re written? Mark for Review (1) Points Body (*) Specification Both the body and the specification Neither the body nor the specification Incorrect. Refer to Section 10 Lesson 1. 5. The two parts of a package are stored as separate objects in the database. True or False? Mark for Review (1) Points True (*) False Incorrect. Refer to Section 10 Lesson 1. 6. 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. 7. Which of the following can be included in a package? (1) Points
Mark for Review
procedures variables PL/SQL types Exceptions All of the above (*) Incorrect. Refer to Section 10 Lesson 1. 8. A number variable declared in a package is initialized to 0 unless assigne d another value. True or False? Mark for Review (1) Points True False (*) Incorrect. Refer to Section 10 Lesson 1. Quiz: Managing Package Concepts 1. A local variable declared within a procedure in a package can be referenced by any other component of that package. True or False? Mark for Review (1) Points
True False (*) Incorrect. Refer to Section 10 Lesson 2. 2. A local variable defined inside a package procedure is visible to the call ing environment. True or False? Mark for Review (1) Points True False (*) Correct 3. Your schema contains a package called EMP_PKG. You want to remove the pack age body but not the specification. The correct syntax to do this is: DROP BODY emp_pkg; True or False? Mark for Review (1) Points True False (*) Incorrect. Refer to Section 10 Lesson 2. 4. SCOTT's schema contains a package EMP_PKG which contains a public procedur e EMP_SAL which accepts a NUMBER parameter. Which of the following will invoke t he function successfully? Mark for Review (0) Points emp_pkg.emp_sal(101); scott.emp_pkg.emp_sal(101): (*) emp_sal(101); None of the above. All of the above. Correct 5. What will be displayed when a user executes the following 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 sch ema (*) 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. 6. When one component of a package is called, all the package's components ar e loaded into memory. True or False? Mark for Review (1) Points True (*) False Incorrect. Refer to Section 10 Lesson 2. 7. A public component declared in the package specification can be referenced by a private component defined in the package body. True or False? Mark for R eview (1) Points True (*) False Incorrect. Refer to Section 10 Lesson 2. 8. Which one of the following queries would you use to see the detailed code of a package called EMP_PKG? Mark for 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 = 'PACKAGE 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. 9. 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 sp ecification, 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. 10.
We want to remove both the specification and the body of package CO_PACK
from the database. Which of the following commands will do this? ew (1) Points
Mark for Revi
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.
Quiz: Advance Package Concepts 1.
Which two of these functions could not be in the same package?
FUNCTION get_emp (p1 FUNCTION get_emp (p1 FUNCTION get_emp (p1 FUNCTION get_emp (p1 Mark for Review (1) Points
DATE) RETURN VARCHAR2; DATE, p2 NUMBER) RETURN VARCHAR2; DATE, p2 NUMBER) RETURN NUMBER; NUMBER, p2 DATE) RETURN VARCHAR2;
1 and 2 1 and 4 2 and 4 2 and 3 (*) 3 and 4 Incorrect. Refer to Section 10 Lesson 3. 2. When using a package function in DML statements, which rules must you foll ow? (Choose three) Mark for Review (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 functi on (*) Cannot execute a DML statement or modify the database (*)
Incorrect. Refer to Section 10 Lesson 3. 3. The package name must be included when calling a package function from a S ELECT statement executed outside the package. True or False? Mark for Review (1) Points True (*) False Correct 4. INDEX BY is missing from this package declaration. What is the most effici ent 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. 5. A package initialization block is executed automatically every time a user invokes any procedure or function in the package. True or False? Mark for Rev iew (1) Points True False (*) Correct 6. Which one of the following is NOT a restriction on a package function call ed from a SQL statement? Mark for Review (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. Correct 7. Which of the following best describes a package initialization block? rk for Review (1) Points
Ma
It is a named procedure in a package which must be invoked by a user before 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 automat ically 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 will ne ver execute. It is treated as a set of comments. Incorrect. Refer to Section 10 Lesson 3. 8. How would you invoke the constant km_to_mile from the global_consts bodile ss 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. 9. If a subprogram is public (declared in the package specification), its det ailed code can be written anywhere in the package body without the need to use f orward declarations. True or False? Mark for Review (1) Points True (*) False Incorrect. Refer to Section 10 Lesson 3.
10. 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 11. 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'); Correct 12. Package FORWARD_PACK contains two procedures: PROC1 is public while PROC2 is private (not declared in the package specification). These procedures have n o parameters. Which of the following package bodies will NOT compile successfull y? (Choose two.) 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;
Correct 13. A bodiless package contains what? (1) Points
Mark for Review
Procedures only Functions only Public variables only (*) Private variables only Incorrect. Refer to Section 10 Lesson 3. 14. The following example package specification is valid to create a data typ e 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 Section 11 Quiz: Persistant State of Package Variables 1. In the following example, which statement best fits in Line 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 first 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 and, if o pen, how many rows it holds. True or False? Mark for 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 initialize a global variable my_pkg.g_var. What will be the value of my_pkg.g_var for User A at Poin t 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 Incorrect. Refer to Section 11 Lesson 1.
Quiz: Using Oracle Supplied Packages 1. The UTL_FILE package contains several exceptions exclusively used in this pa ckage. Which are they? (Choose 3) Mark for Review (1) Points (Choose all correct answers) INVALID_PATH (*) NO_DATA_FOUND WRITE_ERROR (*) INVALID_OPERATION (*) ZERO_DIVIDE Incorrect. Refer to Section 11 Lesson 2. 2. The UTL_FILE package can be used to read and write binary files such as JP EGs as well as text files. True or False? Mark for Review (1) Points True False (*) Incorrect. Refer to Section 11 Lesson 2. 3.
Which general exceptions may be handled by the UTL_FILE package? (Choose 2 ) Mark for Review (1) Points (Choose all correct answers) TOO_MANY_ROWS VALUE_ERROR (*) ZERO_DIVIDE NO_DATA_FOUND (*) Incorrect. Refer to Section 11 Lesson 2. 4. The DBMS_OUTPUT gives programmers an easy-to-use interface to see for inst ance the current value of a loop counter or if a program makes it to a particula r branch of an IF statement. (True or False?) Mark for Review (1) Points True (*)
False Incorrect. Refer to Section 11 Lesson 2. 5. Using the FOPEN function, you can do which actions with the UTL_FILE packa ge? (Choose 2) 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 system d isk. It is used to manipulate large object data type items in columns. Incorrect. Refer to Section 11 Lesson 2. 6. Which DBMS_OUTPUT package subprogram places text into the buffer at Line 1 ? (Choose one) 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. 7. The DBMS_OUTPUT package is useful for which of the following activities? ( Choose two) Mark for Review (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
Section 12 Quiz: Using Dynamic SQL 1. Name two reasons for using Dynamic SQL. (1) Points
Mark for Review
(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/SQL. (*) Enables system control statements to be written and executed from PL/SQL. Correct 2. What happens when a SQL statement is parsed? (Choose three.) view (1) Points
Mark for Re
(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 referenced in the SQL statement exist. (*) Incorrect. Refer to Section 12 Lesson 1. 3. For which of the following is it necessary to use Dynamic SQL? (Choose thr ee.) Mark for Review (1) Points (Choose all correct answers)
ALTER (*) GRANT (*) SAVEPOINT UPDATE DROP (*) Incorrect. Refer to Section 12 Lesson 1. 4. Examine the following procedure, which drops a table whose 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. When SQL statements are included within a procedure, the statements are pa rsed when the procedure is compiled. True or False? Mark for Review (1) Points True (*)
False Correct 6. The DBMS_SQL package is easier to use than EXECUTE IMMEDIATE. True or Fals e? Mark for Review (1) Points True False (*) Incorrect. Refer to Section 12 Lesson 1. 7. What will happen when the following procedure is invoked? 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 functio ns using Dynamic SQL. The procedure will not compile successfully because the syntax of the ALTER FUNCTION statement is incorrect. Incorrect. Refer to Section 12 Lesson 1. 8. A programmer wants to code a procedure which will create a table with a si ngle column. The datatype of the column will be chosen by the user who invokes t he 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 parameter 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. 9. Only one call to DBMS_SQL is needed in order to drop a table. True or Fals e? Mark for Review (1) Points True False (*) Incorrect. Refer to Section 12 Lesson 1.
Quiz: Improving PL/SQL Performance 1. 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 2. 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. Correct 3.
What is the main purpose for using the RETURNING clause?
Mark for Review
(1) Points 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. 4. 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 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. FORALL can only be used with the INSERT statement. True or False? or Review (1) Points True False (*) Correct 7. The following procedure compiles successfully. True or False? 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 (*)
Mark f
Incorrect. Refer to Section 12 Lesson 2. 8.
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. (*) Incorrect. Refer to Section 12 Lesson 2. 9. A function-based index may be made using your own functions, but only if t he function is created using the DETERMINISTIC clause. True or False? Mark for Review (1) Points True (*) False Correct 10. 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.s alary%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 now makes ' || emp_info.salary); END; Mark for Review (1) Points True (*) False Section 13 Quiz: Introduction to Triggers
1. Which of the following could NOT be done by a database trigger? eview (1) Points
Mark for R
Enforcing a complex business rule Enforcing a complex database security check Recalculating the total salary bill for a department whenever an employee'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. 2. A business rule states that an employee's salary must be between 4000 and 30000. We could enforce this rule using a check constraint, but it is better to use a database trigger. True or False? Mark for Review (1) Points True False (*) Correct 3. While editing a document in Microsoft Word, you go to the FILE menu and SA VE your work. To do this, Microsoft Word has executed an application trigger. Tr ue or False? Mark for Review (1) Points True (*) False Correct 4. A user's schema contains procedure MYPROC, function MYFUNC, trigger MYTRIG G and package MYPACK which contains a public procedure PACKPROC. These subprogra ms have no parameters, and the function returns a NUMBER. Which of the following calls to these objects (from an anonymous block) are incorrect? (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. 5. You can use a database trigger to prevent invalid transactions from being committed. True or False? Mark for Review (1) Points True (*) False Correct 6. Which of the following are NOT allowed within a database trigger? (Choose two) Mark for 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. 7.
Which of the following best describes a database trigger?
Mark for Revie
w (1) Points It allows users to log on to the database It executes automatically whenever a particular event occurs within the data base (*) It prevents unique constraints from being violated It executes automatically whenever a user clicks on a button with their mous e It allows foreign key constraints to be violated Incorrect. Refer to Section 13 Lesson 1.
8. Which of the following events could NOT automatically fire a database trig ger? Mark for 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 questi on (*) A DML operation on a view The Database Administrator shuts down the database Incorrect. Refer to Section 13 Lesson 1. 9. A database trigger is a PL/SQL stored subprogram which is explicitly invok ed just like a procedure or a function. True or False? Mark for Review (1) Points True False (*) Correct 10. Which of the following are good guidelines to follow when creating trigge rs? (Choose two) 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 wi thin the trigger (*) Use triggers to replace functionality which is already built into the datab ase Always create more triggers than you need, because it is better to be safe Incorrect. Refer to Section 13 Lesson 1. Quiz: Creating DML Triggers - Part 1 1. We want to prevent employees from being deleted on Sundays. To do this, we c reate 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_ERROR with AFTER triggers. It should be an AFTER trigger because the Oracle Server cannot fire the trig ger until it knows that the employee has been deleted. It does not matter, either a BEFORE or an AFTER trigger could be created. Incorrect. Refer to Section 13 Lesson 2. 2. A BEFORE statement trigger inserts a row into a logging table every time a user updates the salary column of the employees table. The user now tries to up date the salaries of three employees with a single UPDATE statement, but the upd ate fails because it violates a check constraint. How many rows will be inserted into the logging table? Mark for Review (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. 3. Which of the following is the correct syntax for creating a DML trigger as sociated with the EMPLOYEES table? The trigger must fire whenever 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. 4. Which of the following are possible keywords for the timing component of a trigger? (Choose three.) Mark for Review (1) Points (Choose all correct answers) BEFORE (*) INSTEAD WHENEVER INSTEAD OF (*) AFTER (*) Incorrect. Refer to Section 13 Lesson 2. 5. 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 audi t_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. 6. 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 Incorrect. Refer to Section 13 Lesson 2. 7. We want to create a log record automatically every time any DML operation is executed on either or both of the EMPLOYEES and DEPARTMENTS tables. What is t he smallest number of triggers that must be create to do this? Mark for Review (1) Points One Two (*) Three Six Eight Incorrect. Refer to Section 13 Lesson 2. 8. An AFTER UPDATE trigger can specify more than one column. True or False? Mark for Review (1) Points True (*) False
Incorrect. Refer to Section 13 Lesson 2. Quiz: Creating DML Triggers - Part ll 1. 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. 2. Which of the following best describes conditional predicates in a trigger? Mark for Review (1) Points They are special variables which must be DECLAREd within the trigger. They allow the trigger code to see what data values are being inserted into a row. They are automatically declared boolean variables which allow the trigger bo dy to detect which DML operation is being executed. (*) They are special cursor attributes, like %ROWCOUNT and %NOTFOUND Incorrect. Refer to Section 13 Lesson 3. 3. The OLD and NEW qualifiers can be used with statement triggers as well as row triggers. True or False? Mark for Review (1) Points
True False (*) 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. A row trigger has been created which is fired by UPDATE ON employees. A us er now executes a single SQL statement which updates four rows of the EMPLOYEES table. How many times will the row trigger fire? Mark for Review (1) Points Once Twice Four times (*) Five times Eight times Incorrect. Refer to Section 13 Lesson 3. 6. 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. 7. Which of the following statements about INSTEAD OF triggers 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. 8. Examine the following code. To create a row trigger, what code should be i ncluded 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. Incorrect. Refer to Section 13 Lesson 3. 9. Whenever an employee's JOB_ID is updated, we want to insert a row into a l ogging table to record the employee_id and the new value of JOB_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. 10. What are the timing events for a compound trigger? (1) Points
Mark for Review
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 each row.
Before the triggering statement; Before each row; After the triggering state ment
Quiz: Creating DDL and Database Events Triggers 1. You can create a trigger which prevents DDL statements on an individual tabl e, while still allowing DDL on other tables in the same schema. True or False? Mark for Review (1) Points True False (*) Correct 2. The database administrator creates a trigger that automatically disconnect s 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 Correct 3. 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 BEGIN and END; statements 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 DELETE i s wrong.
Correct 4. You have been granted CREATE TRIGGER privilege. You can now create an AFTE R LOGOFF ON SCHEMA trigger. True or False? Mark for Review (1) Points True False (*) Incorrect. Refer to Section 13 Lesson 4. 5. Mutating table errors can be caused by DML triggers, but not by database e vent triggers. True or False? Mark for Review (1) Points True (*) False Correct 6. What is the benefit of using the CALL statement in a trigger body? for Review (1) Points
Mark
It allow both DDL events and database events to be handled by a single trigg er. It prevents data being read from a mutating table. It allows the database administrator to monitor who is currently connected t o the database. It allows the trigger body code to be placed in a separate procedure. (*) Incorrect. Refer to Section 13 Lesson 4. 7. Which of the following could NOT cause a DDL or Database 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. 8. 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 followin g 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. 9. 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. 10. 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 triggering e vent 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.
Quiz: Managing Triggers
1. You have created several DML triggers which reference your DEPARTMENTS tab le. Now you want to disable all of them using a single SQL statement. Which comm and should you use? Mark for 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 2. Which dictionary view would you query to see the detailed body code of tri ggers 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 the tr
igger has been created. Incorrect. Refer to Section 13 Lesson 5. 3. 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 refere nced 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. 4. User KULJIT creates two triggers named EMP1_TRIGG and EMP2_TRIGG, which ar e both DML triggers referencing her EMPLOYEES table. Kuljit now wants to remove both of these triggers from the database. What command(s) should Kuljit use to d o 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. 5. Which command would you use to see if your triggers are enabled or disable d? Mark for Review (1) Points 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 table in his/her schema . True or False? Mark for Review (1) Points True False (*) Incorrect. Refer to Section 13 Lesson 5. Section 14 1. Which of the following will NOT help to minimize dependency failures? (Choos e two.) Mark for Review (1) Points (Choose all correct answers) SELECTing a list of column names instead of using SELECT * (*) Declaring records using the %ROWTYPE attribute Including a column list with INSERT statements Declaring scalar variables with NOT NULL if the corresponding table column has a NOT NULL constraint (*) Declaring scalar variables using the %TYPE attribute Incorrect. Refer to Section 14 Lesson 1. 2. PL/SQL procedure A invokes procedure B, which in turn invokes procedure C, which references table T. If table T is dropped, which of the following stateme nts is true? Mark for Review (1) Points C is invalid but A and B are still valid
A, B and C are all invalid (*) B and C are invalid but A is still valid A, B and C are all still valid None of the above Correct 3. Procedure get_depts has been marked invalid because one of the objects it references has been altered. Which of the following statements are true? (Choose two.) Mark for Review (1) Points (Choose all correct answers) The procedure will be recompiled automatically the next time it is invoked. The recompilation will always be successful. The procedure will be recompiled automatically the next time it is invoked. The recompilation may or may not be successful. (*) The procedure can be recompiled manually by: ALTER PROCEDURE get_depts COMPILE; (*) The procedure can be recompiled manually by: ALTER PROCEDURE get_depts RECOMPILE; The procedure does not need to be recompiled.
Incorrect. Refer to Section 14 Lesson 1. 4. The IDEPTREE view shows dependencies by indenting the lines of output inst ead of by using a NESTED_LEVEL column. True or False? Mark for Review (1) Points True (*) False Correct 5. User ALICE owns a procedure show_emps which references table employees. Wh ich of the following will generate information that shows this dependency? Mar k for Review
(1) Points BEGIN deptree_fill('TABLE','EMPLOYEES'); END; BEGIN deptree_fill('PROCEDURE','ALICE','SHOW_EMPS'); END; BEGIN deptree_fill('ALICE','TABLE','EMPLOYEES'); END; BEGIN deptree_fill('TABLE','ALICE','EMPLOYEES'); END; (*) BEGIN deptree_fill('ALICE','PROCEDURE','SHOW_EMPS'); END;
Incorrect. Refer to Section 14 Lesson 1. 6. Which data dictionary view shows information about references and dependen cies? Mark for Review (1) Points DEPTREE USER_DEPENDENCIES (*) USER_REFERENCES USER_LOCAL_DEPENDENCIES Incorrect. Refer to Section 14 Lesson 1. 7. Package emp_pack contains two public procedures: get_emps and upd_emps. A separate procedure emp_proc invokes emp.pack.get_emps. The upd_emps package body code is now altered, and the package body (but not the package specification) i s recreated. emp_proc will be marked invalid and needs to be recompiled. True or False? Mark for Review (1) Points True False (*) Correct 8.
A single PL/SQL subprogram such as a procedure can be both a referenced ob
ject and a dependent object. True or False? (1) Points
Mark for Review
True (*) False Incorrect. Refer to Section 14 Lesson 1. 9. A procedure show_emps contains the following declaration: CURSOR emp_curs IS SELECT last_name, salary FROM employees; What will happen to the procedure if a new column is added to the employees tabl e? Mark for Review (1) Points The procedure will still be valid and execute correctly because it does not reference the added column. The procedure will automatically be dropped and must be recreated. The procedure will be marked invalid and must be recompiled before it can be reexecuted. (*) Users' privileges to execute the procedure will automatically be revoked. Incorrect. Refer to Section 14 Lesson 1. 10. Which of the following statements will show whether procedure myproc is v alid or invalid? Mark for Review (1) Points SELECT status FROM USER_OBJECTS WHERE object_type = 'PROCEDURE' AND object_name = 'MYPROC'; (*) SELECT status FROM USER_PROCEDURES WHERE procedure_name = 'MYPROC'; SELECT valid FROM USER_OBJECTS WHERE object_type = 'PROCEDURE' AND object_name = 'MYPROC'; SELECT * FROM deptree;
Incorrect. Refer to Section 14 Lesson 1. 11. A procedure includes the following code: SELECT first_name, salary INTO v_first_name, v_salary
FROM employees WHERE employee_id = 100; Which of the following changes to the employees table will allow the procedure t o be recompiled successfully ? (Choose two.) Mark for Review (1) Points (Choose all correct answers) The table is dropped but a public table exists with the same name and struc ture. (*) The table is dropped. A new column is added to the table. (*) The table name is changed to newemps. The first_name column is dropped from the table. Incorrect. Refer to Section 14 Lesson 1. 12. A SELECT from DEPTREE produced the following output. NESTED_LEVEL TYPE NAME > 0 TABLE EMPLOYEES 1 VIEW EMP_VW 2 PROCEDURE ADD_EMP 1 PROCEDURE QUERY_EMP What dependencies does this show? (Choose three.) Mark for Review (1) Points (Choose all correct answers) QUERY_EMP is directly dependent on EMPLOYEES (*) ADD_EMP is directly dependent on EMPLOYEES ADD_EMP is directly depedent on EMP_VW (*) QUERY_EMP is directly dependent on ADD_EMP EMP_VW is directly dependent on EMPLOYEES (*) Incorrect. Refer to Section 14 Lesson 1. 13. Which of the following database objects are created when the utldtree.sql script is run? (Choose three.) Mark for Review (1) Points (Choose all correct answers) The utldtree table
The deptree_temptab table (*) The deptree and ideptree views (*) The deptree table The deptree_fill procedure (*) Incorrect. Refer to Section 14 Lesson 1. 14. View dept_view is based on a select from table departments. Procedure sho w_dept contains code which selects from dept_view. Which of the following statem ents are true? (Choose three.) Mark for Review (1) Points (Choose all correct answers) departments is indirectly dependent on show_dept show_dept is directly dependent on dept_view (*) dept_view is directly dependent on departments (*) show_dept is indirectly dependent on departments (*) emp_view is directly dependent on show_dept Incorrect. Refer to Section 14 Lesson 1. Quiz: Understanding Remote Dependencies 1. With remote dependencies, one master data dictionary that resides on one ser ver identifies the status of all schema objects. True or False? Mark for Revie w (1) Points True False (*) Correct 2. A remote dependency is when a dependent object resides on a database on a separate node. True or False? Mark for Review (1) Points True (*) False Correct
3. In Signature Mode, a procedure will not compile if the signatures of the r emote dependencies do not match. True or False? Mark for Review (1) Points True (*) False Incorrect. Refer to Section 14 Lesson 2. 4. In Signature Mode, a compiled procedure is still valid if its dependent pr ocedure has a parameter data type change from NUMBER to INTEGER. Mark for Revi ew (1) Points True (*) False Correct 5.
In this scenario, the following status is given for each procedure:
Procedure A is local, executed, and invalidated because the remote Procedure B t ime stamp does not match the local time stamp for Procedure B Procedure A is recompiled. In Timestamp Mode, now Procedure A will execute successfully. True or False? rk for Review (1) Points
Ma
True (*) False Incorrect. Refer to Section 14 Lesson 2. 6. Which statement for setting a database parameter is the default for remote dependency checking? Mark for Review (1) Points ALTER SESSION SET REMOTE_DEPENDENCIES_MODE = TIMESTAMP (*) ALTER SESSION SET REMOTE_DEPENDENCIES_MODE = SIGNATURE ALTER SESSION REMOTE_DEPENDENCIES_MODE = TIMESTAMP ALTER SESSION REMOTE_DEPENDENCIES_MODE = SIGNATURE Incorrect. Refer to Section 14 Lesson 2.
7.
In this scenario, the following status is given for each procedure:
Procedure A is local and has a time stamp of 10 AM Procedure B is remote and has a local time stamp of 5 AM and has a remote time s tamp of 4 AM In Timestamp Mode, Procedure A will execute successfully at 11 AM. True or False ? Mark for Review (1) Points True False (*) Correct 8. The Data Dictionary controls the remote dependency mode. True or False? Mark for Review (1) Points True False (*) Incorrect. Refer to Section 14 Lesson 2. Section 15 1. Which data dictionary view allows you to see the setting for PLSQL_OPTIMIZE_ LEVEL? Mark for Review (1) Points USER_PLSQL_OBJECTS USER_PLSQL_OPTIMIZE USER_PLSQL_OBJECT_SETTINGS (*) USER_OBJECT_SETTINGS USER_PLSQL_CODE_TYPE Correct 2. Which are NOT examples of benefits of using PLSQL_OPTIMIZE_LEVEL. (Choose two) Mark for Review (1) Points (Choose all correct answers) Control what PL/SQL does with useless code.
Combining compiled code from one subprogram into another subprogram. Separating compiled code so that separate units may be repeated as needed. (*) Backward compatible with previous versions of the Oracle database. Modify source code to optimize frequently-used elements at the top. (*) Incorrect. Refer to Section 15 Lesson 1. 3. What are the valid values for PLSQL_OPTIMIZE_LEVEL in the data dictionary? Mark for Review (1) Points 0,1,2,3 (*) 0,1,2,3,4 1,2,3 1,2,3,4 Incorrect. Refer to Section 15 Lesson 1. 4. PLSQL_CODE_TYPE determines the type of code for PL/SQL code and for SQL st atements, which is what speeds up the execution speed. True or False? Mark for Review (1) Points True False (*) Correct 5. When setting PLSQL_OPTIMIZE_LEVEL = 2, the compiled code will remove code and exceptions that can never be executed. True or False? Mark for Review (1) Points True (*) False Incorrect. Refer to Section 15 Lesson 1. 6. To set the PLSQL_CODE_TYPE to its fastest execution speed, which command d o you use? Mark for Review (1) Points ALTER SYSTEM SET PLSQL_CODE_TYPE=NATIVE;
ALTER SYSTEM SET PLSQL_CODE_TYPE=2; ALTER SESSION SET PLSQL_CODE_TYPE = NATIVE; (*) ALTER SESSION SET PLSQL_CODE_TYPE = INTERPRETED; ALTER SESSION SET PLSQL_CODE_TYPE = 2; Correct Quiz: Displaying Compiler Warning Messages 1. The informational warning level for PL/SQL compiled code identifies the code that may cause execution speed to be slow. True or False? Mark for Review (1) Points True False (*) Correct 2. The two statements below are equivalent. True or False? DBMS_WARNING.ADD_WARNING_SETTING_CAT ('INFORMATIONAL','ENABLE','SESSION'); and ALTER SESSION SET PLSQL_WARNINGS = 'ENABLE:INFORMATIONAL'; Mark for Review (1) Points True (*) False Correct 3. A warning in PL/SQL is the same as an error in PL/SQL, but can only be vie wed through the USER_ERRORS data dictionary view. True or False? Mark for Revi ew (1) Points True False (*) Incorrect. Refer to Section 15 Lesson 2. 4. Which PL/SQL warning message identifies code that can cause unexpected beh aviour or wrong results when executed? Mark for Review
(1) Points INFORMATIONAL PERFORMANCE ALL SEVERE (*) ERROR Incorrect. Refer to Section 15 Lesson 2. 5. Which pair of DBMS_WARNING commands would allow you to obtain the current settings and change and restore those settings in a PL/SQL subprogram? (Choose t wo) Mark for Review (1) Points (Choose all correct answers) DBMS_WARNING.SET_WARNING_SETTING_STRING (*) DBMS_WARNING.ADD_WARNING_SETTING_CAT DBMS_WARNING.GET_WARNING_SETTING_STRING (*) DBMS_WARNING.GET_WARNING_STRING Correct 6. An error in PL/SQL is when the compiler does not proceed successfully and an error message is displayed. True or False? Mark for Review (1) Points True (*) False Correct Quiz: Using Conditional Compilation 1. If the version and release of the Oracle database in use is 10.2, what state ment will allow syntax available in version 10.2 or later? Mark for Review (1) Points $IF DBMS_DB_VERSION.VER_LE_10_2 $THEN -- some messaage $ELSE -- some action $END
$IF DBMS_DB_VERSION.VER_LE_10_2 $THEN -- some messaage $ELSE -- some action $END; $IF DBMS_DB_VERSION.VER_LE_10_1 $THEN -- some messaage $ELSE -- some action $END (*) $IF DBMS_DB_VERSION.VER_LE_10_1 $THEN -- some messaage $ELSE -- some action $END;
Incorrect. Refer to Section 15 Lesson 3. 2. Identify the selection directives used in conditional compilation. for Review (1) Points $IF $THEN $ELSE $END $CCFLAG $$IF $$THEN $$ELSE $$ELSIF $$END $IF $THEN $ELSE $ELSIF $ENDIF $IF $THEN $ELSE $ELSIF $END (*) $$IF $$THEN $$ELSE $$END
Mark
$$DEBUG
Incorrect. Refer to Section 15 Lesson 3. 3. Inquiry directives are used to selectively include or exclude PL/SQL code based on values of pre-defined variables that are set using the PLSQL_CCFLAGS pa rameter. True or False? Mark for Review (1) Points True False (*) Correct 4. Conditional compilation allows you to include extra code to help with debu gging, which can be removed once errors are resolved. True or False? Mark for Review (1) Points True (*) False Correct 5. The value of DBMS_DB_VERSION.VER_LE_11 is TRUE when the version of the Ora cle database is version 11 or greater. True or False? Mark for Review (1) Points True False (*) Incorrect. Refer to Section 15 Lesson 3. 6. You can choose which code to include in a PL/SQL program based on conditio nal compilation directives. True or False? Mark for Review (1) Points True (*) False Correct
Quiz: Hiding your Source Code
1. One benefit of obfuscation is to protect intellectual property written in PL /SQL. True or False? Mark for Review (1) Points True (*) False Correct 2. For PL/SQL code larger than 32,767 characters, you must use the wrap utili ty. True or False? Mark for Review (1) Points True (*) False Incorrect. Refer to Section 15 Lesson 4. 3. To obfuscate the procedure my_proc, what statement should be at Line A? BEGIN -- Line A ('CREATE OR REPLACE PROCEDURE mycleverproc (p_param1 IN NUMBER, p_param2 OUT NUMBER) IS BEGIN ... /* some clever but private code here */ END mycleverproc;'); END; Mark for Review (1) Points DBMS_DML.CREATE_WRAP DBMS_DDL.CREATE_WRAP DBMS_DDL.CREATE_WRAPPED (*) DBMS_DDL.WRAPPED DBMS_DDL.WRAP_CODE Incorrect. Refer to Section 15 Lesson 4. 4. To create obfuscated code using the wrapper utility, determine the order i n which to execute the following steps. A Connect to the database and execute the wrapped text file as a script to comp ile the wrapped code into the Data Dictionary. B Log into the database server computer. C Create a text file containing your complete unwrapped source code. D Execute WRAP to create a second text file containing the wrapped code. Mark for Review (1) Points
