Section 4 Lesson 5: Iterative Control: Nested Loops

Iterative Control: Nested Loops

 1.  What statement allows you to exit the outer loop at Point A in the following 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;
(1) Points
     
    EXIT AT v_outer_done = 'YES';
    EXIT WHEN v_outer_done = 'YES'; (*)
    WHEN v_outer_done = YES EXIT;
    EXIT <<outer loop>>;
     
  2.  Which one of these statements about using nested loops is true? (1) Points
     
    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 within the inner loop (*)
    Both loops can have the same label
     
  3.  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;
(1) Points
     
    5
    10
    15
    50 (*)
     
  4.  When the following code is executed, how many lines of output will be displayed?
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;
(1) Points
     
    80
    45 (*)
    14
    41
     
  5.  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;
(1) Points
     
    WHILE v_innerloop <=5 LOOP
    FOR v_innerloop 1..5 LOOP (*)
    LOOP
    WHILE v_outerloop<v_innerloop LOOP
     
  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?
(1) Points
     
    EXIT;
    EXIT red;
    EXIT <<blue>>;
    EXIT blue; (*)

Section 4 Lesson 4: Iterative Control: While and For Loops

Iterative Control: While and For Loops
     
  1.  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?
(1) Points
     
    No lines
    One line (*)
    Two lines
    The block will fail because you cannot use DBMS_OUTPUT.PUT_LINE inside a loop.
     
  2.  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 executes?
(1) Points
     
    01-MAY-07
    31-DEC-07
    4/30/2007 (*)
    4/17/2007
     
  3.  Which statement best describes when a FOR loop should be used? (1) Points
     
    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.
     
  4.  You should use a WHILE loop when the number of iterations of the loop is known in advance. True or False? (1) Points
     
    True
    False (*)
     
  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? (1) Points
     
    One
    Three
     Four
     The block will fail because you cannot change the value of i inside the loop. (*)
     
  6.  In a FOR loop, an explicitly declared counter is automatically incremented by 1 for each iteration of the loop. True or False? (1) Points
     
    True
     False (*)
     
 7.  In a WHILE loop, the controlling condition is checked at the start of each iteration. True or False?  (1) Points
     
    True (*)
     False
     
  8.  You want a loop that counts backwards from 10 through 1. How do you code that? (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

Section 4 Lesson 3: Iterative Control: Basic Loops

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;
(1) Points
     
    8
    10 (*)
    12
    NULL
     
  2.  For which one of these tasks should you use a PL/SQL loop? (1) Points
     
    Updating the salary of one employee.
    Executing the same set of statements repeatedly until a condition becomes true. (*)
    Deciding whether a value is within a range of numbers.
    Making a decision based on whether a condition is true or not.
     
  3.  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?
(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
     
  4.  Which kind of loop is this?
i := 10;
LOOP
    i := i + 1;
    EXIT WHEN i > 30;
END LOOP;
(1) Points
     
    A FOR loop.
    A WHILE loop.
    A basic loop. (*)
    An infinite loop.
    A nested loop.
     
  5.  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?
(1) Points
     
    9
    10 (*)
    11
    xxxxxxxxxxx
     
  6.  You want to calculate and display the multiplication table for "sevens": 7x1=7, 7x2=14, 7x3=21 and so on. Which kind of PL/SQL construct is best for this?  (1) Points
     
    A loop (*)
    A CASE statement
    IF ... END IF;
    A Boolean variable.
     
  7.  What are the three kinds of loops in PL/SQL?  (1) Points
     
    ascending, descending, unordered
    infinite, finite, recursive
    IF, CASE, LOOP
    FOR, WHILE, basic (*)

     
  8.  How many EXIT statements can be coded inside a basic loop? (1) Points
     
    None.
    One only.
    Two.
     As many as you need, there is no limit. (*)

Section 4 Lesson 2: Conditional Control: Case Statements

Conditional Control: Case Statements

 Section 1
   
  1.  How must you end a CASE expression? (1) Points
     
    END; (*)
    ENDIF;
    END CASE;
    ENDCASE;
     
  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?
(1) Points
     
    True
    False (*)
    NULL
    Undefined
     
  3.  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;
(1) Points
     
    Equal
    Undefined (*)
    Unequal
    Nothing will be displayed because V_MESSAGE is set to NULL.
     
  4.  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?
(1) Points
     
    WHEN 90 THEN grade := 'A'
    WHEN 90 THEN v_grade := 'A';
    WHEN 90 THEN 'A' (*)
    WHEN 90 THEN 'A';
     
  5.  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';
     ELSE v_status := 'Other Value';
   END CASE;
   DBMS_OUTPUT.PUT_LINE(v_status);
END;
(1) Points
     
    Adult Male
    Junior Female
    Other Value (*)
    Nothing will be displayed because V_STATUS is set to NULL.
     
  6.  How must you end a CASE statement? (1) Points
     
    END;
    END CASE; (*)
    END IF;
    ENDCASE;
     
  7.  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?
(1) Points
     
    WHEN 90 THEN v_grade := 'A'
    WHEN 90 THEN v_grade := 'A'; (*)
    WHEN 90 THEN 'A'
    WHEN 90 THEN 'A';
     
  8.  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?
(1) Points
     
    True (*)
    False
    NULL
    An error will occur because you cannot combine two Boolean variables using "NOT".

Section 4 Lesson 1: Conditional Control: IF Statements

Conditional Control: IF Statements

 Section 1
 
  1.  Name three types of control structures in PL/SQL. (Choose three)  (1) Points
   (Choose all correct answers)
   
    LOOP statements (*)
    SELECT statements
    EXCEPTIONS
    IF statements (*)
    CASE statements (*)
   
  2.  You want to repeat a set of statements 100 times, incrementing a counter each time. What kind of PL/SQL control structure would you use? (1) Points
   
    IF...THEN...ELSE
    IF...THEN...ELSIF...ELSE
    CASE...WHEN...THEN
    A loop. (*)
   
  3.  A basic loop is a type of control structure used to change the logical flow of statements in a PL/SQL block. True or False? (1) Points
   
    True (*)
    False
   
  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?
(1) Points
   
    child
    teenager
    adult (*)
    adultteenagerchild
   
  5.  Which one of the following is correct syntax for an IF statement? (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;
   
  6.  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;
(1) Points
   
    ELSE IF v_var = 20 THEN
    ELSIF v_var = 20
    ELSIF v_var = 20 THEN (*)
    IF v_var = 20 THEN
   
  7.  What will be displayed when this block is executed?
DECLARE
   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;
(1) Points
   
    Born in the 20th century
    Born in the 21st century (*)
    Exception raised because no date given
   
  8.  Which of the following statements are true about any of the PL/SQL conditional control structures such as IF ... , CASE ... and loops? (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 once).

    They determine a course of action based on conditions.

    All of the above. (*)
   
  9.  What is wrong with the following trivial IF statement:
IF (v_job='President')
THEN v_salary := 10000;
(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

   
  10.  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;
(1) Points
   
    Equal
    Not equal (*)
    Start
    Nothing will be displayed. The block will fail because you cannot compare two null values.
   
  11.  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;
(1) Points
   
    up
    down
    left (*)
    null

Section 3 Lesson 2: Retrieving Data in PL/SQL

Retrieving Data in PL/SQL

 Section 1
   
  1.  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 $50,000. Which of the following statements are true? (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.

  2.  Which one of these SQL statements can be directly included in a PL/SQL executable block?  (1) Points
   
    IF... THEN...;
    INSERT INTO...; (*)
    SELECT * FROM DUAL;
    SHOW USER;

  3.  It is good programming practice to create identifiers having the same name as column names. True or False? (1) Points
   
    True
    False (*)
   
  4.  Which of the following is NOT a valid guideline for retrieving data in PL/SQL?  (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.
   
  5.  Which SQL statements can be used directly in a PL/SQL block? (Choose two.)  (1) Points
  (Choose all correct answers)
   
    GRANT EXECUTE ON ...
    SELECT * INTO ... (*)
    REVOKE SELECT ON ...
    UPDATE employees SET... (*)
    ALTER TABLE employees ...
   
  6.  Does PL/SQL allow you to have a variable with the same name as a database column? (1) Points
   
    No
    Yes (*)
   
  7.  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;
(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 three 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 to NULL.
   
  8.  When used in a PL/SQL block, which SQL statement must return exactly one row? (1) Points
   
    INSERT
    UPDATE
    SELECT (*)
    MERGE
    DELETE

Section 3 Lesson 1: Review of SQL DML

Review of SQL DML

Section 1
   
  1.  When inserting a row into a table, the VALUES clause must include a value for every column of the table. True or False?  (1) Points
   
    True
    False (*)
   
  2.  What would be the result of the following statement: DELETE employees;  (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.

  3.  Is it possible to insert more than one row at a time using an INSERT statement with a VALUES clause?  (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 rows with commas.
    No, there is no such thing as INSERT ... VALUES.
   
  4.  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.salary, ....);
(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 values.
    The SET clause is trying to update the source table from the target table. (*)
    Nothing is wrong, the statement will execute correctly.
   
  5.  You want to modify existing rows in a table. Which of the following are NOT needed in your SQL statement? (Choose two).  (1) Points
   
    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, (*)
   
  6.  What is wrong with the following statement?
DELETE from employees WHERE salary > (SELECT MAX(salary) FROM employees);
(1) Points
   
    You cannot code a subquery inside a DELETE statement.
    You cannot use inequality operators such as "<" and ">" inside a DELETE statement.
    Nothing is wrong, the statement will execute correctly. (*)
   
  7.  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?
(1) Points
   
    True
    False (*)
   
  8.  To modify an existing row in a table, you can use the ________ statement.  (1) Points
   
    MODIFY
    INSERT
    ALTER
    UPDATE (*)

Section 2 Lesson 7: Good Programming Practices

Good Programming Practices

Section 1
   
  1.  Comments change how a PL/SQL program executes, so an unsuitable comment can cause the program to fail. True or False?  (1) Points
   
    True
    False (*)
   
  2.  Examine the following code:
DECLARE
v_first_name varchar2 (30);
v_salary number (10);
BEGIN
SELECT first_name, salary
INTO v_first_name, v_salary
FROM employees
WHERE last_name = 'King';
END;

Which programming guideline would improve this code?
(1) Points
   
    Use a suitable naming convention for variables.
    Indent the code to make it more readable. (*)
    Use upper and lower case consistently.
   
  3.  What symbol is used to comment a series of lines?  (1) Points
   
    / / before and after the comment
    /* */ before and after the comment (*)
    * * before and after the comment
   
  4.  Which of the following are examples of good programming practice? (Choose two.) (1) Points
  (Choose all correct answers)
   
    Use the %TYPE attribute to declare a variable according to another previously 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. (*)

  5.  Which of the following are examples of good programming practice? (Choose three.)  (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.

  6.  Which of the following makes PL/SQL code easier to read and maintain?  (1) Points
   
    Place multiple statements on the same line.
    Type everything in lowercase.
    Use suitable comments in the code. (*)

Section 2 Lesson 6: Nested Blocks and Variable Scope

Nested Blocks and Variable Scope

 Section 1
   
  1.  A variable is global to an outer block and local to the inner block. True or False? (1) Points
    True
     False (*)
   
  2.  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;
(1) Points
   
    Only the outer block
    Both the inner and the outer block (*)
    Only the inner block
    Neither block

  3.  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;
(1) Points
   
    3,3
    3,7
    Null, 7 (*)
    Null, 3
   
  4.  Examine the following code. Line A causes an exception. What will be displayed 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;
(1) Points
   
    10
    20
    15 (*)
    Nothing is displayed
   
  5.  For the anonymous block below, what is the correct reference to the father's date of birth in the inner block?
<<outer>>
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';
...
(1) Points
   
    v_date_of_birth.outer
    <<outer>>v_date_of_birth
    <<outer.v_date_of_birth>>
    outer.v_date_of_birth (*)
   
  6.  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 happens?  (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 calling environment.
   
  7.  What is wrong with this code?
DECLARE
    v_a NUMBER;
BEGIN
    v_a := 27;
    <<inner_block>>
    BEGIN
       v_a := 15;
END;
(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.
   
  8.  What happens when an exception occurs in the executable section of a PL/SQL block? (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.
   
  9.  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;
(1) Points
   
    Unhappy

    12

    8

    4 (*)
   
  10.  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;
(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 label. (*)

    It cannot be done because the outer block's v_myvar is in scope but not visible at Line A.

Section 2 Lesson 5: Writing PL/SQL Executable Statements

Writing PL/SQL Executable Statements

 Section 1
   
  1.  Examine the following code: DECLARE x VARCHAR2(20); BEGIN x:= 5 + 4 * 5 ; DBMS_OUTPUT.PUT_LINE(x); END; What value of x will be displayed?  (1) Points
   
    45
    29
    25 (*)
    14
   
  2.  Which explicit function is used to convert a character into a number?  (1) Points
   
    TO_DATE
    TO_NUMBER (*)
    TO_CHAR
   
  3.  Using implicit conversions is good programming practice.  (1) Points
   
    True
    False (*)
   
  4.  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;
(1) Points
   
    v_date := FROM_CHAR(v_char,'dd/mm/yy');
    v_date := TO_DATE(v_char,'dd/mm/yy'); (*)
    v_date := v_char;

  5.  When PL/SQL converts data automatically from one data type to another, it is called _______ conversion.  (1) Points
   
    Explicit
    Implicit (*)
    TO_CHAR
   
  6.  The DECODE and MAX functions can be used in PL/SQL statements. True or False?  (1) Points
   
    True
    False (*)
   
  7.  Which of the following are valid PL/SQL operators? (Choose three.)  (1) Points (Choose all correct answers)
   
    Concatenation (*)
    Exception
    Exponential (*)
    Arithmetic (*)
   
  8.  Which of the following statements about implicit conversions is NOT true?  (1) Points
   
    Code containing implicit conversions typically runs faster than code containing explicit conversions. (*)
    Code containing implicit conversions may not work in the future if Oracle changes the conversion rules.
    Code containing implicit conversions is harder to read and understand.

  9.  Which of the following is correct?  (1) Points
   
    v_family_name = SMITH;
    V_FAMILY_NAME = SMITH;
    v_family_name := SMITH;
    v_family_name := 'SMITH'; (*)
   
  10.  The TO_CHAR function is used for explicit data type conversions. True or False?  (1) Points
   
    True (*)
    False
   
  11.  Explicit conversions can be slower than implicit conversions. True or False? (1) Points
   
    True
    False (*)

  12.  PL/SQL statements must be written on a single line. (1) Points
    True
    False (*)
   
  13.  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;
(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. (*)
   
  14.  The LENGTH and ROUND functions can be used in PL/SQL statements. True or False? (1) Points
   
    True (*)
    False
   
  15.  PL/SQL can implicitly convert a CHAR to a NUMBER, provided the CHAR contains a numeric value, for example '123'. True or False? (1) Points
   
    True (*)
    False
   
  16.  Which of the following data type conversions can be done implicitly? (Choose two.)  (1) Points
   (Choose all correct answers)
    DATE to NUMBER
    NUMBER to VARCHAR2 (*)
    NUMBER to PLS_INTEGER (*)

Section 2 Lesson 4: Using Scalar Data Types

Using Scalar Data Types

 Section 1
   
  1.  If you use the %TYPE attribute, you can avoid hard-coding the column name. True or False?  (1) Points
   
    True
    False (*)

  2.  Code is easier to read if you declare one identifier per line. True or False? (1) Points
   
    True (*)
    False
   
  3.  Which of the following is NOT a good guideline for declaring variables?  (1) Points
   
    Declare one identifier per line
    Use column names as identifiers (*)
    Use NOT NULL when the variable must have a value

  4.  Which of the following variable declarations does NOT use a number data type?  (1) Points
   
    v_count PLS_INTEGER := 0;
    v_median_age NUMBER(6,2);
    v_students LONG; (*)
    v_count BINARY_INTEGER;

  5.  When declared using %TYPE, a variable will inherit ____ from the column on which it is based.  (1) Points
   
    The name of the column
    The value of the column
    The data type and size of the column (*)
   
  6.  Which of the following is NOT a character data type?  (1) Points
   
    VARCHAR2
    BOOLEAN (*)
    CHAR
    LONG

Section 2 Lesson 3: Recognizing Data Types

Recognizing Data Types

 Section 1
 
  1.  Which of the folowing are scalar data types? (Choose three.)  (1) Points (Choose all correct answers)
   
    Array
    Character (*)
    Table
    Date (*)
    Boolean (*)

  2.  Which of the following is a composite data type? (1) Points
   
    CLOB
    VARCHAR2
    RECORD (*)
    DATE

 3.  What are the data types of the variables in the following declaration?
DECLARE
fname VARCHAR2(20);
fname VARCHAR2(15) DEFAULT 'fernandez';
BEGIN
...
(1) Points
   
    Scalar (*)
    Composite
    LOB
   
  4.  A datatype may specify a valid range of values. True or False?  (1) Points
   
    True (*)
    False
   
  5.  A Scalar data type holds a ____ value.  (1) Points
   
    Multi
    Large
    Single (*)
   
  6.  A datatype specifies and restricts the possible data values that can be assigned to a variable. True or False? (1) Points
   
    True (*)
    False

   
  7.  Which of the following are PL/SQL data types? (Choose three.)  (1) Points (Choose all correct answers)
   
    Large Objects (LOB) (*)
    Lexical
    Scalar (*)
    Delimiter
    Composite (*)

Section 2 Lesson 2: Recognizing PL/SQL Lexical Units

Recognizing PL/SQL Lexical Units

Section 1
   
  1.  What is a lexical unit?  (1) Points
   
    A data type for a column
    A building block of a PL/SQL block (*)
    A type of variable
   
  2.  What characters must enclose non-numeric literal values?  (1) Points
   
    Double quotes: " "
    Parentheses: ()
    Single quotes: ' ' (*)

  3.  The name of a variable is an example of an identifier. True or False?  (1) Points
   
    True (*)
    False
   
  4.  Which of the following symbols can be used to enclose a comment in PL/SQL?  (1) Points
   
    ? ?
    */ / *
    :: ::
    /* */ (*)
   
  5.  Which of the following are lexical units? (Choose two.) (1) Points (Choose all correct answers)
   
    Data types
    PL/SQL blocks
    Identifiers (*)
    Literals (*)
   
  6.  Which of the following is a valid naming convention for an identifier? (Choose two.)  (1) Points (Choose all correct answers)
   
    Can include letters or numbers (*)
    Cannot contain a reserved word (*)
    Can be over 30 characters
    Can start with a number or special character

Section 2 Lesson 1: Using Variables in PL/SQL

 Using Variables in PL/SQL

Section 1
 
  1.  Evaluate the following declaration. Determine whether or not it is legal.
DECLARE
    name,dept VARCHAR2(14);
(1) Points
   
    legal
    illegal (*)

  2.  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?  (1) Points
   
    v_result := v_number * 5;
    v_result := 100 / 2;
    v_result := ROUND(49.77);
    All of the above. (*)
   
  3.  After they are declared, variables can be used only once in an application. True or False?  (1) Points
   
    True
    False (*)
   
  4.  Constants must be initialized. True or False?  (1) Points
   
    True (*)
    False
   
  5.  Evaluate the following declaration. Determine whether or not it is legal. DECLARE
    test NUMBER(5);  (1) Points
   
    legal (*)
    illegal
   
  6.  Which of the following are required when declaring a variable? (Choose two.)  (1) Points (Choose all correct answers)
   
    Identifier name (*)
    CONSTANT
    Data type (*)
    NOT NULL
   
  7.  Variables may be reused. True or False?  (1) Points
    True (*)
    False
   
  8.  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?
(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'); (*)

Section 1 Lesson 3: Creating PL/SQL Blocks

 Section 1
 
  1.  What are the characteristics of an anonymous block? (Choose two.) (1) Points (Choose all correct answers)
   
    Unnamed (*)
    Stored in the database
    Compiled each time the application is executed (*)
    Can be declared as procedures or as functions
   
  2.  Which sections of a PL/SQL block are optional?  (1) Points
    Declaration and Executable
    Declaration and Exception (*)
    Exception only
    Executable only

  3.  Which statements are mandatory in a PL/SQL block? (Choose two.)  (1) Points (Choose all correct answers)
    DECLARE
    BEGIN (*)
    EXCEPTION
    END; (*)

 4.  Which lines of code will correctly display the message "Hello World" ? (Choose two.)  (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'); (*)
   
  5.  How can you display results to check that a PL/SQL block is working correctly?  (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
   
  6.  What are the characteristics of a PL/SQL stored subprogram? (Choose two.)  (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
   
  7.  This PL/SQL anonymous block will execute successfully. True or False?
DECLARE
    v_date DATE := SYSDATE;
    DBMS_OUTPUT.PUT_LINE(v_date);
END;
(1) Points
   
    True
    False (*)
   
  8.  Which of the following is NOT a PL/SQL programming environment?  (1) Points
    Oracle jDeveloper
    SQL*Plus
    gSQL*Plus (*)
    SQL Workshop in Application Express
 
  9.  What is wrong with this PL/SQL anonymous block?
BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello');
    DBMS_OUTPUT.PUT_LINE(' and Goodbye');
(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 (*)
   
  10.  In a PL/SQL block, which of the following should not be followed by a semicolon? (1) Points
   
    DECLARE (*)
    END
    All SQL statements
    All PL/SQL statements
   
  11.  Which of the following is a PL/SQL programming environment?  (1) Points
   
    Oracle Cdeveloper
    Java*Plus
    PL/SQL Express
    SQL*Workshop in Application Express (*)

Section 1 Lesson 2: Benefits of PL/SQL

Section 1
   
  1.  When multiple SQL statements are combined into PL/SQL blocks, performance improves. True or False? (1) Points
    True (*)
    False
   
  2.  Procedural constructs give you better control of your SQL statements and their execution. True or False?  (1) Points
   True (*)
   False
   
  3.  PL/SQL differs from C and Java in which of the following ways? (Choose two.)  (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.

 4.  Which of the following can be compiled as a standalone program outside the database?  (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. (*)
     
  5.  Which of the following can be done using PL/SQL?  (1) Points
    Create complex applications.
    Retrieve and modify data in Oracle database tables.
    Manage database tasks such as security.
    Create custom reports.
    All of the above (*)
   
  6.  You can create a Web site application written entirely in PL/SQL. True or False?  (1) Points
   True (*)
   False

Section 1 Lesson 1: Introduction to PL/SQL

Section 1

1. In which three ways does PL/SQL extend the SQL programming language? (1) Points
(Choose all correct answers)
By adding procedural constructs. (*)
By adding compound constructs.
By adding iterative control. (*)
By adding conditional control. (*)

2.  Which of the following statements is true? (1) Points
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.

3.  Which of the following statements is true? (1) Points
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.

4. PL/SQL stands for:  (1) Points
Processing Language for SQL.
Procedural Language extension for SQL. (*)
Primary Language for SQL.
Proprietary Language for SQL.

5.  Nonprocedural languages allow the programmer to produce a result when a series of steps are followed. True or False? (1) Points
True
False (*)

6.  Which of the following statements about SQL is true? (1) Points
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.

PLSQL Mid Term Exam Semester 1

1. Which of the following declarations is invalid?
v_count PLS_INTEGER:=0;
college_name VARCHAR2(20):='Harvard';
v_pages CONSTANT NUMBER; (*)
v_start_date DATE := sysdate+1;

2. Which of the following should NOT be used as the name ofa variable?
A table name.
A table column name. (*)
The database name.

3.
1. Null
2. False
3. True
4. 0
Which of the above can be assigned to a Boolean variable?

2 and 3
2, 3 and 4
1, 2 and 3 (*)
1, 2, 3 and 4

4. The following anonymous block of code is run:
BEGIN
INSERT INTO countries (id, name)
VALUES ('XA', 'Xanadu');
INSERT INTO countries (id, name)
VALUES ('NV','Neverland');
COMMIT;
COMMIT;
ROLLBACK;
END;
What happens when the block of code finishes?

You have nothing new; the last ROLLBACK undid the INSERTs.
You have the rows added twice; there are four new rows.
You have the two new rows added. (*)
You get an error; you cannot COMMIT twice in a row.

5. Which of the following best describes a databasetransaction?
All the DML statements in a single PL/SQL block
A related set of SQL DML statements which must be executed eithercompletely or not at all (*)
A single SQL statement that updates multiple rows of a table
A SELECT statement based on a join of two or more database tables

6. Which of the following are valid identifiers? (Choose two.) (Choose all correct answers)
Full Name
students_street_address (*)
v_code (*)
#hours
completion_%

7. Which of the following are valid identifiers? (Choosetwo.) (Choose all correct answers)
yesterday (*)
yesterday's date
number_of_students_in_the_class
v$testresult (*)
#students

8. Which of the following are PL/SQL lexical units? (Choosetwo.) (Choose all correct answers)
Identifiers (*)
Table Columns
Reserved Words (*)
Anonymous Blocks
SQL Workshop

9. What will be displayed when the following code isexecuted?
DECLARE
varA NUMBER := 12;
BEGIN
DECLARE
varB NUMBER := 8;
BEGIN
varA := varA + varB;
END;
DBMS_OUTPUT.PUT_LINE(varB);
END;

8
12
Nothing, the block will fail with an error (*)
20
VarB

10. In the following code, Line A causes an exception. Whatvalue will be displayed when the code is executed?
DECLARE
outer_var VARCHAR2(50) := 'My';
BEGIN
outer_var := outer_var || ' name';
DECLARE
inner_var NUMBER;
BEGIN
inner_var := 'Mehmet'; --Line A
outer_var := outer_var || ' is';
END;
outer_var := outer_var || ' Zeynep';
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(outer_var);
END;

My
My name (*)
My name is
My name is Zeynep

11. When nested blocks are used, which blocks can or must be labeled?
The inner block must be labeled, the outer block can be labeled.
Both blocks must be labeled
Nested blocks cannot be labeled
The outer block must be labeled if it is to be referred to in the inner block. (*)

12. What will be displayed when the following code isexecuted?
DECLARE
x VARCHAR2(6) := 'Chang';
BEGIN
DECLARE
x VARCHAR2(12) := 'Susan';
BEGIN
x := x || x;
END;
DBMS_OUTPUT.PUT_LINE(x);
END;

Susan
Chang (*)
ChangChang
SusanChang
The code will fail with an error

13. Examine the following code. At Line A, we want to assigna value of 22 to the outer block's variable v_myvar. What code should wewrite at Line A?
<<outer_block>>
DECLARE
v_myvar NUMBER;
BEGIN
<<inner_block>>
DECLARE
v_myvar NUMBER := 15;
BEGIN
--Line A
END;
END;

outer_block.v_myvar := 22; (*)
v_myvar := 22;
<<outer_block>>.v_myvar := 22;
v_myvar(outer_block) := 22;
We cannot reference the outer block's variable because both variables have the same name

14. Which one of these SQL statements can be directlyincluded in a PL/SQL executable block?
DELETE FROM employeesWHERE department_id=60;(*)
SELECT salary FROM employeesWHERE department_id=60;
CREATE TABLE new_emps (last_name VARCHAR2(10), first_name VARCHAR2(10));
DROP TABLE locations;

15. Which of the following is NOT a good guideline forretrieving data in PL/SQL?
Declare the receiving variables using %TYPE
The WHERE clause is optional in nearly all cases. (*)
Specify the same number of variables in the INTO clause as databasecolumns in the SELECT clause.
THE SELECT statement should fetch exactly one row.

16. A variable is declared as:
DECLARE
v_salary employees.salary%TYPE;
BEGIN
Which of the following is a correct use of the INTO clause?
SELECT salaryINTO v_salaryFROM employeesWHERE employee_id=100;(*)
SELECT v_salaryINTO salaryFROM employeesWHERE employee_id=100;
SELECT salaryFROM employeesINTO v_salary;
SELECT salaryFROM employeesWHERE employee_id=100INTO v_salary;

17. Which rows will be deleted from the EMPLOYEES table when the following code is executed?
DECLARE
salary employees.salary%TYPE := 12000;
BEGIN
DELETE FROM employeesWHERE salary > salary;
END;

All rows whose SALARY column value is greater than 12000.
All rows in the table.
No rows. (*)
All rows whose SALARY column value is equal to 12000.

18. Which one of these SQL statements can be directlyincluded in a PL/SQL executable block?
SELECT last_name FROM employeesWHERE employee_id=100;
DESCRIBE employees;
UPDATE employeesSET last_name='Smith';(*)
DROP TABLE employees;

19. Evaluate the following declaration. Determine whether ornot it is legal.
DECLARE
maxsalary NUMBER(7) = 5000;

Correct.
Not correct. (*)

20. Assignment statements can continue over several lines inPL/SQL. True or False?
True (*)
False

21. When a variable is defined using the CONSTANT keyword, the value ofthe variable cannot change. True or False?
True (*)
False

22. Variables can be used in the following ways in a PL/SQLblock. (Choose two.) (Choose all correct answers)
To store data values. (*)
To rename tables and columns.
To refer to a single data value several times. (*)
To comment code.

23. When a variable is defined using the NOT NULL keywords, the variable must contain a value. True or False?
True (*)
False

24. PL/SQL is an Oracle proprietary, procedural, 4GLprogramming language. True or False?
True
False (*)

25. A program which specifies a list of operations to be performed sequentially to achieve the desired result can be called:

declarative
nondeclarative
procedural (*)
low level

26. Which of the following statements about PL/SQL and SQL is true?
PL/SQL and SQL are both ANSI-compliant.
PL/SQL and SQL can be used with many types of databases, including Oracle.
PL/SQL and SQL are both Oracle proprietary programming languages.
PL/SQL allows basic program logic and control flow to be combined
with SQL statements. (*)

27. Which statements are optional in a PL/SQL block? (Choosetwo.) (Choose all correct answers)
DECLARE (*)
BEGIN
EXCEPTION (*)
END;

28. What is the purpose of using DBMS_OUTPUT.PUT_LINE in aPL/SQL block?
To perform conditional tests
To allow a set of statements to be executed repeatedly
To display results to check if our code is working correctly (*)
To store new rows in the database

29. Which of the following tools can NOT be used to developand test PL/SQL code?
Oracle Jdeveloper
Oracle Application Express
Oracle JSQL (*)
Oracle iSQL*Plus

30. Given below are the parts of a PL/SQL block:
1. END;
2. EXCEPTION
3. DECLARE
4. BEGIN
Arrange the parts in order.

2,1,4,3
3,4,2,1 (*)
3,2,4,1
4,3,2,1

31. Every PL/SQL anonymous block must start with the keyword DECLARE.
True or False?
True
False (*)

32. Which lines of code will correctly display the message"The cat sat on the mat"? (Choose two.)
(Choose all correct answers)
DBMS_OUTPUT.PUT_LINE('The cat sat on the mat'); (*)
DBMS_OUTPUT.PUT_LINE(The cat sat on the mat);
DBMS_OUTPUT.PUT_LINE('The cat' || 'sat on the mat');
DBMS_OUTPUT.PUT_LINE('The cat sat ' || 'on the mat'); (*)

33. Errors are handled in the Exception part of the PL/SQLblock. True or False?
True (*)
False

34. Which component of Oracle Application Express is used toenter and run SQL statements and PL/SQL blocks?
Application Builder
SQL Workshop (*)
Utilities
Object Browser
Correct Correct

35. ______ are meant to store large amounts of data.
Variables
Scalar data types
LOBs (*)

36. What is the data type of the variable V_DEPT_TABLE in the followingdeclaration?
DECLARE
TYPE dept_table_type IS TABLE OF departments%ROWTYPE INDEX BYPLS_INTEGER; v_dept_table dept_table_type; ...

Scalar
Composite (*)
LOB

37. Which of these are PL/SQL data types? (Choose three.) (Choose all correct answers)
Scalar (*)
Identifier
Delimiter
Composite (*)
LOB (*)

38. What is the output when the following program isexecuted?
set serveroutput on
DECLARE
a VARCHAR2(10) := '333';
b VARCHAR2(10) := '444';
c PLS_INTEGER;
d VARCHAR2(10);
BEGIN
c := TO_NUMBER(a) + TO_NUMBER(b);
d := a || b;
DBMS_OUTPUT.PUT_LINE(c);
DBMS_OUTPUT.PUT_LINE(d);
END;

Nothing. The code will result in an error.
c=777 and d=333444 (*=777 and d=333444 (*)
c=777 and d=777
c=333444 and d=777

39. The implicit data type conversion at Point A may notwork correctly. Why not?
DECLARE
v_mydate DATE;
BEGIN
V_MYDATE := '29-Feb-04'; --Point A
END;

There are only 28 days in February
Oracle cannot implicitly convert a character string to a date, even
if the string contains a valid date value
If the database language is not English, 'Feb' has no meaning. (*)
V_MYDATE has been entered in uppercase

40. If today's date is 14th June 2007, which statement willcorrectly convert today's date to the value: June 14, 2007 ?
TO_CHAR(sysdate)
TO_DATE(sysdate)
TO_DATE(sysdate,'Month DD, YYYY')
TO_CHAR(sysdate, 'Month DD, YYYY') (*)

Incorrect Incorrect. Refer to Section 2.
Previous Page 8 of 10 Next Summary

41. PL/SQL can convert a VARCHAR2 value containing alphabeticcharacters to a NUMBER value. True or False?
True
False (*)

42. What is wrong with this assignment statement?
myvar :=
'To be or not to be';
'That is the question';

An assignment statement must be a single line of code
Nothing is wrong, the statement is fine
An assignment statement must have a single semicolon at the end (*)
"myvar" is not a valid name for a variable
Character literals should not be enclosed in quotes

43. Examine the following code. What is the final value ofV_MYVAR ?
DECLARE
v_myvar NUMBER;
BEGIN
v_myvar := 1 + 2 * 3;
v_myvar := v_myvar * 2;
END;

81
49
14 (*)
18

44. Which of the following are valid assignment statements?
(Choose two.) (Choose all correct answers)
v_string = 'Hello';
v_string := Hello;
v_number := 17 + 34; (*)
v_string := 'Hello'; (*)
v_date := 28-DEC-06;

45. Examine the following code. What is the final value of V_MYBOOL ?
DECLARE
v_mynumber NUMBER;
v_mybool BOOLEAN ;
BEGIN
v_mynumber := 6;
v_mybool := (v_mynumber BETWEEN 10 AND 20);
v_mybool := NOT (v_mybool);
END;

True (*)
False

46. Examine the following code:
1 DECLARE
2 x NUMBER;
3 BEGIN
4 x:= '300';
5 END;
After line 4, what is the value of x?

'300'
300 (*)
NULL

47. TO_NUMBER, TO_CHAR, and TO_DATE are all examples of:

Implicit conversion functions
Explicit conversion functions (*)
Character functions
Operators

48. Assume there are 5 employees in Department 10. Whathappens when the following statement is executed?
UPDATE employeesSET salary=salary*1.1;

All employees get a 10% salary increase. (*)
No rows are modified because you did not specify "WHEREdepartment_id=10"
A TOO_MANY_ROWS exception is raised.
An error message is displayed because you must use the INTO clauseto hold the new salary.

49. You declare an implicit cursor in the DECLARE section of
a PL/SQL block. True or False?
True
False (*)

50. A PL/SQL block includes the following statement:
SELECT last_name INTO v_last_name
FROM employeesWHERE employee_id=100;
What is the value of SQL%ISOPEN immediately after the SELECT statement isexecuted?
True
False (*)
Null
Error. That attribute does not apply for implicit cursors.

PLSQL Mid Term Exam Semester 2

1. Examine the following code:
CREATE TRIGGER emp_triggAFTER UPDATE OF salary ON employeesFOR EACH ROW
DECLARE
v_count NUMBER;
BEGIN
--Line A
END;
Which of the following statements is NOT allowed at Line A?

SELECT count(*) INTO v_count FROMdepartments;
UPDATE employees SET job_id = 'IT_PROG' WHEREemployee_id = :OLD.employee_id;
SELECT count(*) INTO v_count FROM employees;(*)
DBMS_OUTPUT.PUT_LINE('A salary was updated');
None. All of the above are allowed.

2. Which of the following statements could cause a DDLtrigger to fire?
DROP TABLE employees;
ALTER TABLE departments ADD (budgetNUMBER(8,2));
CREATE TABLE newemp AS SELECT * FROMemployees;
TRUNCATE TABLE locations;
All of the above (*)

3. The database administrator wants to write a log recordevery time an Oracle Server error occurs in any user's session. The DBAcreates the following trigger:

CREATE TRIGGER log_errs_trigg--Line A
BEGIN
--Line A
BEGIN
INSERT INTO errlog_table VALUES (...);
END;
What should the DBA code at Line A ?

AFTER ERROR ON DATABASE
AFTER SERVER ERROR ON DATABASE
AFTER SERVERERROR ON SCHEMA
AFTER SERVERERROR ON DATABASE (*)
AFTER ORACLE ERROR ON SCHEMA

4. You want to prevent any objects in your schema frombeing altered or dropped. You decide to create the following trigger:
CREATE TRIGGER stop_ad_trigg--Line A
BEGIN
RAISE_APPLICATION_ERROR(-20203,'Invalid Operation');
END;
What should you code at Line A ?

AFTER ALTER OR DROP ON SCHEMA
INSTEAD OF ALTER OR DROP ON SCHEMA
BEFORE ALTER OR DROP ON SCHEMA (*)
BEFORE ALTER, DROP ON SCHEMA
AFTER ALTER, DROP ON SCHEMA

5. Which kinds of trigger can cause a mutating tableproblem? (Choose two.) (Choose all correct answers)
BEFORE UPDATE row triggers (*)
DDL triggers
AFTER DELETE row triggers (*)
Database Event triggers
INSTEAD OF triggers
Database Event triggers
INSTEAD OF triggers

6. Examine this code:
CREATE TRIGGER new_triggAFTER CREATE ON reserved_word
BEGIN ...
Which of the following can be used in place of reserved_word? (Choosetwo.) (Choose all correct answers)
TABLE
SCHEMA (*)
USER
DATABASE (*)
TABLE employees

7. Examine this code:
CREATE TRIGGER de_trigg--Line A
BEGIN ...
Which of the following are NOT valid at Line A ? (Choose two.) (Choose all correct answers)
AFTER LOGOFF ON SCHEMA (*)
AFTER LOGON ON SCHEMA
BEFORE LOGOFF ON SCHEMA
BEFORE DISCONNECT ON SCHEMA (*)
AFTER SERVERERROR ON SCHEMA

8. You need to disable all triggers that are associatedwith DML statements on the DEPARTMENTS table. Which of the following commands should you use?

ALTER TABLE departments DISABLE ALL TRIGGERS;(*)
ALTER TRIGGER DISABLE ALL ON departments;
ALTER TABLE departments DISABLE TRIGGERS;
DISABLE ALL TRIGGERS ON departments;
ALTER TABLE departments DROP ALL TRIGGERS;

9. After the following SQL statement is executed, all thetriggers on the DEPARTMENTS table will no longer fire, but will remain inthe database. True or False?
ALTER TABLE departments DISABLE ALL TRIGGERS;
True (*)
False

10. User AYSEGUL successfully creates the following trigger:
CREATE TRIGGER loc_triggBEFORE UPDATE ON aysegul.locationsBEGIN ....
AYSEGUL now tries to drop the LOCATIONS table. What happens?

An error message is displayed because youcannot drop a table that is associated with a trigger.
The table is dropped and the trigger isdisabled.
The trigger is dropped but the table is notdropped.
Both the table and the trigger are dropped.(*)
None of the above.

11. A SQL statement canpass through several stages. Which of the following is NOT one of thesestages?

BIND
FETCH
PARSE
RETURN(*)
EXECUTE

12. Examine the following code:
CREATE OR REPLACE PROCEDURE myproc ISCURSOR c_curs IS SELECT view_name FROM user_views;
BEGIN
FOR v_curs_rec IN c_curs LOOP
EXECUTE IMMEDIATE 'DROP VIEW ' || v_curs_rec.view_name;
END LOOP;
END;
What will happen when this procedure is invoked?
All views in the user's schema will be dropped. (*)
The procedure will not compile successfullybecause the syntax of EXECUTE IMMEDIATE is incorrect.
The procedure will raise an exception becauseDynamic SQL can drop tables but cannot drop views.
The procedure will raise an exception becauseone of the views is a complex view.

13. Which of the following SQL statements can be included ina PL/SQL block only by using Dynamic SQL? (Choose two.) (Choose all correct answers)

DELETE
SAVEPOINT
ALTER (*)
SELECT ..... FOR UPDATE NOWAIT
GRANT (*)
SAVEPOINT
ALTER (*)
SELECT ..... FOR UPDATE NOWAIT
GRANT (*)

14. MARY wants HENRY to be able to query her EMPLOYEEStable. Mary executes the following code:
DECLARE
v_grant_stmt VARCHAR2(50);
BEGIN
v_grant_stmt := 'GRANT SELECT ON employees TO henry';
DBMS_SQL.EXECUTE(v_grant_stmt);
END;

Mary has successfully granted the privilege to Henry. True or False?

True
False (*)

15. Package MULTIPACK declares the following global
variable:
g_myvar NUMBER;
User DICK executes the following:
multipack.g_myvar := 45;
User HAZEL now connects to the database. Both users immediately execute:
BEGIN
DBMS_OUTPUT.PUT_LINE(multipack.g_myvar);
END;
What values will Dick and Hazel see?

Dick: 45, Hazel: 45
Dick: 45, Hazel: 0
Dick: 45, Hazel: null (*)
Dick: 0, Hazel: 0
Both queries will fail because the syntax ofDBMS_OUTPUT.PUT_LINE is incorrect

16. Package CURSPACKdeclares a global cursor in the package specification. The packagecontains three public procedures: OPENPROC opens the cursor; FETCHPROCfetches 5 rows from the cursor's active set; CLOSEPROC closes the cursor.
What will happen when a user session executes the following commands in the order shown?
curspack.openproc; --line 1
curspack.fetchproc; --line 2
curspack.fetchproc; --line 3
curspack.openproc; --line 4
curspack.fetchproc; --line 5
curspack.closeproc; --line 6

The first 15 rows will be fetched.
The first 10 rows will be fetched, then thefirst 5 rows will be fetched again.
The first 5 rows will be fetched three times.
An error will occur at line 2.
An error will occur at line 4. (*)

17. Which of the following statements about a packageinitialization block is true?
It cannot contain any SQL statements.
It is an anonymous block at the end of apackage body. (*)
It is a procedure in a package that must beinvoked before the rest of the package can be used.
It is an anonymous block in the packagespecification.
It is executed automatically every time anyglobal variable in the package is referenced.

18. A public function in a package is invoked from within aSQL statement. The function's code can include a COMMIT statement. True or False?
True
False (*)

19. Package TAXPACK declares a global variable G_TAXRATENUMBER(2,2). The value of the tax rate is stored in table TAXTAB in the database. You want to read this value automatically into G_TAXRATE eachtime a user session makes its first call to TAXPACK. How would you dothis?
Declare the global variable as:
g_taxrate NUMBER(2,2) := SELECT tax_rate FROM taxtab;

Create a database trigger that includes thefollowing code:
SELECT tax_rate INTO taxpack.g_taxrate FROM taxtab;

Add a private function to the package body ofTAXPACK, and invoke the function from the user session.

Add a package initialization block to thepackage body of TAXPACK.(*)

20. Which two of these declarations cannot be in the same package specification?
PROCEDURE myproc (p1 NUMBER, p2 VARCHAR2);
PROCEDURE myproc (p1 VARCHAR2, p2 NUMBER);
PROCEDURE myproc (p1 NUMBER, p2 CHAR);
PROCEDURE myproc (p1 NUMBER);

1 and 2
1 and 3 (*)
2 and 3
3 and 4
1 and 4

21. Which of the followingare NOT stored inside the database? (Choose two.) (Choose all correct answers)
A PL/SQL package specification
A database trigger
An anonymous block (*)
An application trigger (*)
A sequence

22. You can use a trigger to prevent rows from being deletedfrom the EMPLOYEES table on Mondays. True or False?
True (*)
False

23. What type of database object would you create to writean auditing record automatically every time a user connects to the
database?
A procedure
A complex view
A trigger (*)
A function
A package

24. A business rule states that an employee's salary cannotbe greater than 99,999.99 or less than 0. The best way to enforce thisrule is by using:

A datatype of NUMBER(7,2) for the SALARYcolumn
A database trigger
A check constraint (*)
An application trigger
A view

25. The following objects have been created in a user'sschema:
-a function FUNC1
-A package PACK1 which contains a public procedure PACKPROC and aprivate function PACKFUNC
-a trigger TRIGG1.
The procedure and functions each accept a single IN parameter of typeNUMBER, and the functions return BOOLEANs. Which of the following callsto these objects (from an anonymous block) are correct? (Choose two.) (Choose all correct answers)

pack1.packproc(25); (*)
SELECT func1(100) FROM dual;
trigg1;
IF pack1.packfunc(40) THEN ...
IF func1(75) THEN ... (*)

26. You can code COMMIT and ROLLBACK statements in a trigger body. True or False?
True
False (*)

27. A trigger can be created in the database or within anapplication. True or False?
True (*)
False

28. An Oracle directory called FILESDIR has been created byexecuting:
CREATE OR REPLACE DIRECTORY filesdir AS 'C:\NEWFILES';
Which of the following will create a new text file calledC:\NEWFILES\EMP_REPORT.TXT ?

UTL_FILE.CREATE('FILESDIR','EMP_REPORT.TXT');
UTL_FILE.FOPEN('C:\NEWFILES\EMP_REPORT.TXT','w');
UTL_FILE.FOPEN('FILESDIR','EMP_REPORT.TXT','w'); (*)
UTL_FILE.OPEN('FILESDIR','EMP_REPORT.TXT','c');

29. Why is it better to use DBMS_OUTPUT only in anonymousblocks, not inside stored subprograms such as procedures?

Because DBMS_OUTPUT cannot be used inside procedures

Because anonymous blocks display messageswhile the block is executing, while procedures do not display anythinguntil their execution has finished

Because DBMS_OUTPUT should be used only fortesting and debugging PL/SQL code (*)

Because DBMS_OUTPUT can raise a NO_DATA_FOUND exception if used inside a packaged procedure

30. Which of the following best describes the purpose of theUTL_FILE package?

It is used to load binary files such asemployees' photos into the database. employees' photos into the database.

It is used to read and write text files
stored outside the database. (*)

It is used to find out how much free space isleft on an operating system disk.

It is used to query CHAR and VARCHAR2 columnsin tables.

31. What will be displayedwhen the following code is executed?

BEGIN
DBMS_OUTPUT.PUT('I do like');
DBMS_OUTPUT.PUT_LINE('to be');
DBMS_OUTPUT.PUT('beside the seaside');
END;

I do like to be
beside the seaside

I do like
to be
beside the seaside

I do like to be

I do liketo be(*)

I do like to be beside the seaside

32. Every subprogram which has been declared in a package
specification must also be included in the package body. Triue or False?
True (*)
False

33. The following package specification has been created:
CREATE OR REPLACE PACKAGE mypack ISFUNCTION myfunc(p_funcparam DATE) RETURN BOOLEAN;
PROCEDURE myproc(p_procparam IN NUMBER);
END mypack;
Which of the following will correctly invoke the package subprograms? (Choose two.) (Choose all correct answers)
mypack.myfunc('22-JAN-07');
mypack.myproc(35);(*)
IF NOT mypack.myfunc(SYSDATE) THEN DBMS_OUTPUT.PUT_LINE('Message'); END IF;(*)
myproc(40);
v_num := mypack.myproc(22);

34. Which one of the following can NOT be part of a Package?
Procedures
Explicit cursors
Triggers (*)
Functions
Global variables

35. What is wrong with the following syntax for creating apackage specification?

CREATE OR REPLACE PACKAGE mypack ISg_constant1 NUMBER(6) := 100;
FUNCTION func1 (p_param1 IN VARCHAR2);
FUNCTION func2;
g_constant1 NUMBER(6) := 100;
FUNCTION func1 (p_param1 IN VARCHAR2);
FUNCTION func2;
END mypack;

You cannot declare constants in the specification.
A package must contain at least oneprocedure.
The RETURN datatype of the functions must be specified.(*)
The first line should be: CREATE OR REPLACE PACKAGE SPECIFICATION mypack IS Nothing is wrong, this code contains noerrors.

36. What is wrong with the following code?

CREATE OR REPLACE TRIGGER loc_triggBEFORE DELETE ON locations
BEGIN
RAISE_APPLICATION_ERROR(-20201,'Invalid delete');
ROLLBACK;
END;

END loc_trigg;
a trigger.

BEFORE DELETE OF locations (*) and execute successfully.
The last line should be: You cannot use RAISE_APPLICATION_ERROR inside
The second line should be: You cannot use ROLLBACK inside a trigger.
Nothing is wrong, this trigger will compile

37. What is wrong with the following code?
CREATE OR REPLACE TRIGGER emp_dept_triggBEFORE UPDATE OR DELETE ON employees, departmentsBEGIN
...

One trigger can be associated with only onetable(*)
The second line should be: BEFORE (UPDATE,DELETE) ON employees, departments
DML triggers must be row triggers, so FOREACH ROW is missing
The second line should be: BEFORE UPDATE OR DELETE ON employees OR departments

38. A DML statement trigger fires only once for eachtriggering DML statement, while a row trigger fires once for each rowprocessed by the triggering statement. True or False?
True (*)
False

39. The following code will successfully create emp_trigg: True or False?
CREATE OR REPLACE TRIGGER emp_triggBEFORE DELETE OF salary ON employeesBEGIN
RAISE_APPLICATION_ERROR(-20202,'Deleting salary is not allowed');
END;

True
False (*)

40. In a package, public components are declared in thespecification but private components are not. True or False?
True (*)
False

41. Package NEWPACKcontains several procedures and functions, including private functionPRIVFUNC. From where can PRIVFUNC be invoked? (Choose two.) (Choose all correct answers)

From an anonymous block
From any procedure in NEWPACK (*)
From any private function in another package
From any function in NEWPACK (*)
From any public procedure in another package

42. Which of the following will display the detailed code of the subprograms in package DEPTPACK in your schema ?
SELECT text FROM USER_SOURCE
WHERE name = 'DEPTPACK'
AND type = 'PACKAGE'ORDER BY line;

SELECT text FROM USER_SOURCE
WHERE name = 'DEPTPACK'
AND type = 'PACKAGE BODY'ORDER BY line;(*)

SELECT text FROM USER_SOURCE
WHERE object_name = 'DEPTPACK'AND object_type = 'PACKAGE BODY'ORDER BY line;
AND object_type = 'PACKAGE BODY'ORDER BY line;

SELECT text FROM USER_SOURCE
WHERE name = 'DEPTPACK'
AND type = 'BODY'ORDER BY line;

43. Package OLDPACK is in your schema. What will happen whenthe following statement is executed?
DROP PACKAGE oldpack;

The body will be dropped but thespecification will be retained.
The specification will be dropped but thebody will be retained.
Both the specification and the body will bedropped. (*)
The statement will fail because you must dropthe body before you can drop the specification.

44. When a change is made to the detailed code of a publicprocedure in a package (but not to the procedure's name or parameters), both the specification and the body must be recompiled. True or False?

True
False (*)

45. Your schema contains four packages, each having aspecification and a body. You have also been granted privileges to accessthree packages (and their bodies) in other users' schemas. What will bedisplayed by the following query?
SELECT COUNT(*) FROM ALL_OBJECTSWHERE object_type LIKE 'PACK%'
AND owner <> USER;

14
7
3
6 (*)
0

46. We want to remove the specification (but not the body) of package BIGPACK from the database.
Which of the following commands will do this?

DROP PACKAGE bigpack;
DROP PACKAGE SPECIFICATION bigpack;
DROP PACKAGE bigpack SPECIFICATION;
DROP PACKAGE HEADER bigpack;
None of the above (*)

47. Examine the following code. To create a row trigger, what code should be included at Line A?
CREATE TRIGGER dept_triggAFTER UPDATE OR DELETE ON departments--Line A
BEGIN ...

AFTER EACH ROW
FOR EVERY ROW
FOR EACH ROW (*)
ON EACH ROW
ON EVERY ROW

48. With which kind of trigger can the :OLD and :NEWqualifiers be used?
DDL triggers
Database Event triggers
Statement triggers
Row triggers (*)
AFTER triggers

49. In the following code:
CREATE TRIGGER mytriggINSTEAD OF INSERT OR UPDATE ON my_object_nameFOR EACH ROW
BEGIN ...
my_object_name can be the name of a table. True or False?
True
False (*)

50. Examine the following trigger. It should raise anapplication error if a user tries to update an employee's last name. Itshould allow updates to all other columns of the EMPLOYEES table. Whatshould be coded at line A?
CREATE TRIGGER stop_ln_triggBEFORE UPDATE ON employeesBEGIN
--Line A
RAISE_APPLICATION_ERROR(-20201,'Updating last name not allowed');
END IF;
END;

IF UPDATING LAST_NAME THEN
IF UPDATING('LAST_NAME') THEN (*)
IF UPDATE('LAST_NAME') THEN
IF UPDATING THEN

PLSQL Mid Term Exam Semester 2

1. Examine the following code:
CREATE TRIGGER emp_triggAFTER UPDATE OF salary ON employeesFOR EACH ROW
DECLARE
v_count NUMBER;
BEGIN
--Line A
END;
Which of the following statements is NOT allowed at Line A?

SELECT count(*) INTO v_count FROMdepartments;
UPDATE employees SET job_id = 'IT_PROG' WHEREemployee_id = :OLD.employee_id;
SELECT count(*) INTO v_count FROM employees;(*)
DBMS_OUTPUT.PUT_LINE('A salary was updated');
None. All of the above are allowed.

2. Which of the following statements could cause a DDLtrigger to fire?
DROP TABLE employees;
ALTER TABLE departments ADD (budgetNUMBER(8,2));
CREATE TABLE newemp AS SELECT * FROMemployees;
TRUNCATE TABLE locations;
All of the above (*)

3. The database administrator wants to write a log recordevery time an Oracle Server error occurs in any user's session. The DBAcreates the following trigger:

CREATE TRIGGER log_errs_trigg--Line A
BEGIN
--Line A
BEGIN
INSERT INTO errlog_table VALUES (...);
END;
What should the DBA code at Line A ?

AFTER ERROR ON DATABASE
AFTER SERVER ERROR ON DATABASE
AFTER SERVERERROR ON SCHEMA
AFTER SERVERERROR ON DATABASE (*)
AFTER ORACLE ERROR ON SCHEMA

4. You want to prevent any objects in your schema frombeing altered or dropped. You decide to create the following trigger:
CREATE TRIGGER stop_ad_trigg--Line A
BEGIN
RAISE_APPLICATION_ERROR(-20203,'Invalid Operation');
END;
What should you code at Line A ?

AFTER ALTER OR DROP ON SCHEMA
INSTEAD OF ALTER OR DROP ON SCHEMA
BEFORE ALTER OR DROP ON SCHEMA (*)
BEFORE ALTER, DROP ON SCHEMA
AFTER ALTER, DROP ON SCHEMA

5. Which kinds of trigger can cause a mutating tableproblem? (Choose two.)
(Choose all correct answers)
BEFORE UPDATE row triggers (*)
DDL triggers
AFTER DELETE row triggers (*)
Database Event triggers
INSTEAD OF triggers
Database Event triggers
INSTEAD OF triggers

6. Examine this code:
CREATE TRIGGER new_triggAFTER CREATE ON reserved_word
BEGIN ...
Which of the following can be used in place of reserved_word? (Choosetwo.) (Choose all correct answers)
TABLE
SCHEMA (*)
USER
DATABASE (*)
TABLE employees

7. Examine this code:
CREATE TRIGGER de_trigg--Line A
BEGIN ...
Which of the following are NOT valid at Line A ? (Choose two.) (Choose all correct answers)
AFTER LOGOFF ON SCHEMA (*)
AFTER LOGON ON SCHEMA
BEFORE LOGOFF ON SCHEMA
BEFORE DISCONNECT ON SCHEMA (*)
AFTER SERVERERROR ON SCHEMA


8. You need to disable all triggers that are associatedwith DML statements on the DEPARTMENTS table. Which of the following commands should you use?

ALTER TABLE departments DISABLE ALL TRIGGERS; (*)
ALTER TRIGGER DISABLE ALL ON departments;
ALTER TABLE departments DISABLE TRIGGERS;
DISABLE ALL TRIGGERS ON departments;
ALTER TABLE departments DROP ALL TRIGGERS;

9. After the following SQL statement is executed, all thetriggers on the DEPARTMENTS table will no longer fire, but will remain inthe database. True or False?
ALTER TABLE departments DISABLE ALL TRIGGERS;

True (*)
False

10. User AYSEGUL successfully creates the following trigger:
CREATE TRIGGER loc_triggBEFORE UPDATE ON aysegul.locationsBEGIN ....
AYSEGUL now tries to drop the LOCATIONS table. What happens?

An error message is displayed because youcannot drop a table that is associated with a trigger.
The table is dropped and the trigger isdisabled.
The trigger is dropped but the table is notdropped.
Both the table and the trigger are dropped.(*)
None of the above.

11. A SQL statement canpass through several stages. Which of the following is NOT one of thesestages?
BIND
FETCH
PARSE
RETURN (*)
EXECUTE

12. Examine the following code:
CREATE OR REPLACE PROCEDURE myproc ISCURSOR c_curs IS SELECT view_name FROM user_views;
BEGIN
FOR v_curs_rec IN c_curs LOOP
EXECUTE IMMEDIATE 'DROP VIEW ' || v_curs_rec.view_name;
END LOOP;
END;
What will happen when this procedure is invoked?

All views in the user's schema will be dropped. (*)
The procedure will not compile successfullybecause the syntax of EXECUTE IMMEDIATE is incorrect.
The procedure will raise an exception becauseDynamic SQL can drop tables but cannot drop views.
The procedure will raise an exception becauseone of the views is a complex view.

13. Which of the following SQL statements can be included ina PL/SQL block only by using Dynamic SQL? (Choose two.) (Choose all correct answers)
DELETE
SAVEPOINT
ALTER (*)
SELECT ..... FOR UPDATE NOWAIT
GRANT (*)
SAVEPOINT
ALTER (*)
SELECT ..... FOR UPDATE NOWAIT
GRANT (*)

14. MARY wants HENRY to be able to query her EMPLOYEEStable. Mary executes the following code:
DECLARE v_grant_stmt VARCHAR2(50);
BEGIN
v_grant_stmt := 'GRANT SELECT ON employees TO henry';
DBMS_SQL.EXECUTE(v_grant_stmt);
END;
Mary has successfully granted the privilege to Henry. True or False?

True
False (*)

15. Package MULTIPACK declares the following global
variable:
g_myvar NUMBER;
User DICK executes the following:
multipack.g_myvar := 45;
User HAZEL now connects to the database. Both users immediately execute:
BEGIN
DBMS_OUTPUT.PUT_LINE(multipack.g_myvar);
END;
What values will Dick and Hazel see?

Dick: 45, Hazel: 45
Dick: 45, Hazel: 0
Dick: 45, Hazel: null (*)

Dick: 0, Hazel: 0
Both queries will fail because the syntax ofDBMS_OUTPUT.PUT_LINE is incorrect

16. Package CURSPACKdeclares a global cursor in the package specification. The packagecontains three public procedures: OPENPROC opens the cursor; FETCHPROCfetches 5 rows from the cursor's active set; CLOSEPROC closes the cursor.
What will happen when a user session executes the following commands in the order shown?
curspack.openproc; --line 1
curspack.fetchproc; --line 2
curspack.fetchproc; --line 3
curspack.openproc; --line 4
curspack.fetchproc; --line 5
curspack.closeproc; --line 6

The first 15 rows will be fetched.
The first 10 rows will be fetched, then thefirst 5 rows will be fetched again.
The first 5 rows will be fetched three times.
An error will occur at line 2.
An error will occur at line 4. (*)

17. Which of the following statements about a packageinitialization block is true?

It cannot contain any SQL statements.
It is an anonymous block at the end of apackage body. (*)
It is a procedure in a package that must beinvoked before the rest of the package can be used.
It is an anonymous block in the package specification.
It is executed automatically every time anyglobal variable in the package is referenced.

18. A public function in a package is invoked from within aSQL statement. The function's code can include a COMMIT statement. True or False?
True
False (*)

19. Package TAXPACK declares a global variable G_TAXRATENUMBER(2,2). The value of the tax rate is stored in table TAXTAB in the database. You want to read this value automatically into G_TAXRATE eachtime a user session makes its first call to TAXPACK. How would you dothis?

Declare the global variable as: g_taxrate NUMBER(2,2) := SELECT tax_rate FROM taxtab;

Create a database trigger that includes thefollowing code: SELECT tax_rate INTO taxpack.g_taxrate FROM taxtab;

Add a private function to the package body ofTAXPACK, and invoke the function from the user session.
Add a package initialization block to thepackage body of TAXPACK.(*)

20. Which two of these declarations cannot be in the same package specification?
PROCEDURE myproc (p1 NUMBER, p2 VARCHAR2);
PROCEDURE myproc (p1 VARCHAR2, p2 NUMBER);
PROCEDURE myproc (p1 NUMBER, p2 CHAR);
PROCEDURE myproc (p1 NUMBER);

1 and 2
1 and 3 (*)
2 and 3
3 and 4
1 and 4

21. Which of the followingare NOT stored inside the database? (Choose two.) (Choose all correct answers)
A PL/SQL package specification
A database trigger
An anonymous block (*)
An application trigger (*)
A sequence

22. You can use a trigger to prevent rows from being deletedfrom the EMPLOYEES table on Mondays. True or False?
True (*)
False

23. What type of database object would you create to writean auditing record automatically every time a user connects to the database?
A procedure
A complex view
A trigger (*)
A function
A package

24. A business rule states that an employee's salary cannotbe greater than 99,999.99 or less than 0. The best way to enforce thisrule is by using:
A datatype of NUMBER(7,2) for the SALARYcolumn
A database trigger
A check constraint (*)
An application trigger
A view

25. The following objects have been created in a user's schema:
-a function FUNC1
-A package PACK1 which contains a public procedure PACKPROC and aprivate function PACKFUNC
-a trigger TRIGG1.
The procedure and functions each accept a single IN parameter of typeNUMBER, and the functions return BOOLEANs. Which of the following callsto these objects (from an anonymous block) are correct? (Choose two.) (Choose all correct answers)

pack1.packproc(25); (*)
SELECT func1(100) FROM dual;
trigg1;
IF pack1.packfunc(40) THEN ...
IF func1(75) THEN ... (*)

26. You can code COMMIT and ROLLBACK statements in a trigger body. True or False?
True
False (*)

27. A trigger can be created in the database or within anapplication. True or False?

True (*)
False

28. An Oracle directory called FILESDIR has been created byexecuting:
CREATE OR REPLACE DIRECTORY filesdir AS 'C:\NEWFILES';
Which of the following will create a new text file calledC:\NEWFILES\EMP_REPORT.TXT ?
UTL_FILE.CREATE('FILESDIR','EMP_REPORT.TXT');
UTL_FILE.FOPEN('C:\NEWFILES\EMP_REPORT.TXT','w');
UTL_FILE.FOPEN('FILESDIR','EMP_REPORT.TXT','w'); (*)
UTL_FILE.OPEN('FILESDIR','EMP_REPORT.TXT','c');

29. Why is it better to use DBMS_OUTPUT only in anonymousblocks, not inside stored subprograms such as procedures?
Because DBMS_OUTPUT cannot be used inside procedures
Because anonymous blocks display messageswhile the block is executing, while procedures do not display anythinguntil their execution has finished
Because DBMS_OUTPUT should be used only fortesting and debugging PL/SQL code (*)
Because DBMS_OUTPUT can raise a NO_DATA_FOUND exception if used inside a packaged procedure

30. Which of the following best describes the purpose of theUTL_FILE package?

It is used to load binary files such asemployees' photos into the database. employees' photos into the database.
It is used to read and write text files stored outside the database. (*)
It is used to find out how much free space isleft on an operating system disk.
It is used to query CHAR and VARCHAR2 columnsin tables.

31. What will be displayedwhen the following code is executed?
BEGIN
DBMS_OUTPUT.PUT('I do like');
DBMS_OUTPUT.PUT_LINE('to be');
DBMS_OUTPUT.PUT('beside the seaside');
END;

I do like to be beside the seaside
I do like to be beside the seaside
I do like to be (*)
I do like to be beside the seaside

32. Every subprogram which has been declared in a package specification must also be included in the package body. Triue or False?
True (*)
False

33. The following package specification has been created:
CREATE OR REPLACE PACKAGE mypack ISFUNCTION myfunc(p_funcparam DATE) RETURN BOOLEAN;
PROCEDURE myproc(p_procparam IN NUMBER);
END mypack;
Which of the following will correctly invoke the package subprograms? (Choose two.) (Choose all correct answers)
mypack.myfunc('22-JAN-07');
mypack.myproc(35); (*)
IF NOT mypack.myfunc(SYSDATE) THEN
DBMS_OUTPUT.PUT_LINE('Message');
END IF; (*)
myproc(40);
v_num := mypack.myproc(22);

34. Which one of the following can NOT be part of a Package?
Procedures
Explicit cursors
Triggers (*)
Functions
Global variables

35. What is wrong with the following syntax for creating apackage specification?

CREATE OR REPLACE PACKAGE mypack ISg_constant1 NUMBER(6) := 100;
FUNCTION func1 (p_param1 IN VARCHAR2);
FUNCTION func2;
g_constant1 NUMBER(6) := 100;
FUNCTION func1 (p_param1 IN VARCHAR2);
FUNCTION func2;
END mypack;

You cannot declare constants in the specification.
A package must contain at least oneprocedure.
The RETURN datatype of the functions must be specified.(*)
The first line should be: CREATE OR REPLACE PACKAGE SPECIFICATION mypack IS
Nothing is wrong, this code contains noerrors.

36. What is wrong with the following code?
CREATE OR REPLACE TRIGGER loc_triggBEFORE DELETE ON locations
BEGIN
RAISE_APPLICATION_ERROR(-20201,'Invalid delete');
ROLLBACK;
END;

END loc_trigg;
a trigger.
BEFORE DELETE OF locations (*)
 and execute successfully.
The last line should be: You cannot use RAISE_APPLICATION_ERROR inside
The second line should be: You cannot use ROLLBACK inside a trigger.
Nothing is wrong, this trigger will compile

37. What is wrong with the following code?
CREATE OR REPLACE TRIGGER emp_dept_triggBEFORE UPDATE OR DELETE ON employees, departmentsBEGIN
...
One trigger can be associated with only onetable(*)
The second line should be: BEFORE (UPDATE,DELETE) ON employees, departments
DML triggers must be row triggers, so FOREACH ROW is missing
The second line should be: BEFORE UPDATE OR DELETE ON employees OR departments

38. A DML statement trigger fires only once for eachtriggering DML statement, while a row trigger fires once for each rowprocessed by the triggering statement. True or False?
True (*)
False

39. The following code will successfully create emp_trigg: True or False?
CREATE OR REPLACE TRIGGER emp_triggBEFORE DELETE OF salary ON employeesBEGIN
RAISE_APPLICATION_ERROR(-20202,'Deleting salary is not allowed');
END;


True
False (*)

40. In a package, public components are declared in thespecification but private components are not. True or False?
True (*)
False

41. Package NEWPACKcontains several procedures and functions, including private functionPRIVFUNC. From where can PRIVFUNC be invoked? (Choose two.) (Choose all correct answers)

From an anonymous block
From any procedure in NEWPACK (*)
From any private function in another package
From any function in NEWPACK (*)
From any public procedure in another package

42. Which of the following will display the detailed code of the subprograms in package DEPTPACK in your schema ?

SELECT text FROM USER_SOURCE
WHERE name = 'DEPTPACK'
AND type = 'PACKAGE'ORDER BY line;

SELECT text FROM USER_SOURCE
WHERE name = 'DEPTPACK'
AND type = 'PACKAGE BODY'ORDER BY line; (*)

SELECT text FROM USER_SOURCE

WHERE object_name = 'DEPTPACK'AND object_type = 'PACKAGE BODY'ORDER BY line;
AND object_type = 'PACKAGE BODY'ORDER BY line;

SELECT text FROM USER_SOURCE
WHERE name = 'DEPTPACK'
AND type = 'BODY'ORDER BY line;

43. Package OLDPACK is in your schema. What will happen whenthe following statement is executed?
DROP PACKAGE oldpack;
The body will be dropped but thespecification will be retained.
The specification will be dropped but thebody will be retained.
Both the specification and the body will bedropped. (*)
The statement will fail because you must dropthe body before you can drop the specification.

44. When a change is made to the detailed code of a publicprocedure in a package (but not to the procedure's name or parameters),
both the specification and the body must be recompiled. True or False?
True
False (*)

45. Your schema contains four packages, each having aspecification and a body. You have also been granted privileges to accessthree packages (and their bodies) in other users' schemas. What will bedisplayed by the following query?
SELECT COUNT(*) FROM ALL_OBJECTSWHERE object_type LIKE 'PACK%'
AND owner <> USER;

14
7
3
6 (*)
0

46. We want to remove the specification (but not the body) of package BIGPACK from the database.
Which of the following commands will do this?

DROP PACKAGE bigpack;
DROP PACKAGE SPECIFICATION bigpack;
DROP PACKAGE bigpack SPECIFICATION;
DROP PACKAGE HEADER bigpack;
None of the above (*)

47. Examine the following code. To create a row trigger,
what code should be included at Line A?
CREATE TRIGGER dept_triggAFTER UPDATE OR DELETE ON departments--Line A
BEGIN ...

AFTER EACH ROW
FOR EVERY ROW
FOR EACH ROW (*)
ON EACH ROW
ON EVERY ROW

48. With which kind of trigger can the :OLD and :NEWqualifiers be used?
DDL triggers
Database Event triggers
Statement triggers
Row triggers (*)
AFTER triggers

49. In the following code: CREATE TRIGGER mytrigg INSTEAD OF INSERT OR UPDATE ON my_object_name FOR EACH ROW
BEGIN ...
my_object_name can be the name of a table. True or False?

True
False (*)

50. Examine the following trigger. It should raise anapplication error if a user tries to update an employee's last name. It should allow updates to all other columns of the EMPLOYEES table. Whats hould be coded at line A?
CREATE TRIGGER stop_ln_triggBEFORE UPDATE ON employeesBEGIN
--Line A
RAISE_APPLICATION_ERROR(-20201,'Updating last name not allowed');
END IF;
END;

IF UPDATING LAST_NAME THEN
IF UPDATING('LAST_NAME') THEN (*)
IF UPDATE('LAST_NAME') THEN
IF UPDATING THEN