PLSQL Semester 2 Final Exam

 1.  User-defined exceptions must be declared explicitly by the programmer, but then are raised automatically by the Oracle Server. True or False?  (1) Points
     
    True
     False (*)
 
  2.  A user-defined exception is raised by using:  (1) Points
     
    FLAG exception_name;
    RAISE exception-name; (*)
    PRAGMA EXCEPTION_INIT
    RAISE(error_number, exception_name);
 
3.  There are no employees in department_id 99. What output will be displayed when the following code is executed?
DECLARE
    v_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_count
       FROM employees WHERE department_id = 99;
    IF v_count = 0 THEN
       RAISE NO_DATA_FOUND;
       DBMS_OUTPUT.PUT_LINE('No employees found');
    END IF;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
       DBMS_OUTPUT.PUT_LINE('Department 99 is empty');
END;
(1) Points
     
   No employees found
   No employees found Department 99 is empty
   Department 99 is empty (*)
   The block will fail because you cannot explicitly RAISE a predefined Oracle Server error such as NO_DATA_FOUND
 
  4.  Which of the following will successfully return a user-defined error message?  (1) Points
     
    RAISE_APPLICATION_ERROR('Error Raised',-22001);
    RAISE_APPLICATION_ERROR(-20257,'Error raised'); (*)
    RAISE_APPLICATION_ERROR(-22001,'Error Raised');
    RAISE_APPLICATION_ERROR('Error Raised',-20257);
 
 5.  What will be displayed when the following code is executed?
<<outer>>
DECLARE
    v_myvar NUMBER;
BEGIN
    v_myvar := 10;
    DECLARE
       v_myvar NUMBER := 200;
    BEGIN
       outer.v_myvar := 20;
       v_myvar := v_myvar / 0; -- this raises a ZERO_DIVIDE error
       outer.v_myvar := 30;
    END;
    v_myvar := 40;
EXCEPTION
    WHEN ZERO_DIVIDE THEN
       DBMS_OUTPUT.PUT_LINE(v_myvar);
END;
(1) Points
     
   10
   20 (*)
   30
   40
   200
 
  6.  What will happen when the following code is executed?
DECLARE
    e_excep1 EXCEPTION;
    e_excep2 EXCEPTION;
BEGIN
    RAISE e_excep1;
EXCEPTION
    WHEN e_excep1 THEN BEGIN
       RAISE e_excep2; END;
END;
(1) Points
     
    It will fail to compile because you cannot have a subblock inside an exception 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 in the same block.
    It will compile successfully and return an unhandled e_excep2 to the calling environment. (*)
 
     
7.  Exceptions declared in a block are considered local to that block, and global to all its sub-blocks. True or False?  (1) Points
     
    True (*)
     False
 
8.  What will happen 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 raised');
    WHEN e_inner_excep THEN
       DBMS_OUTPUT.PUT_LINE('Inner raised');
END;
(1) Points
     
    The code will fail to compile because e_inner_excep cannot be referenced in the outer block. (*)
    The code will propagate the e_outer_excep back to the calling environment.
    The code will execute successfully and 'Outer Raised' will be displayed.
    The code will fail to compile because e_inner_excep was declared but never RAISEd.
     
  9.  Which of the following EXCEPTION sections are constructed correctly? (Choose two.)  (1) Points
  (Choose all correct answers)
     
    EXCEPTION
    WHEN NO_DATA_FOUND THEN statement_1;
    WHEN OTHERS THEN statement_2;
END; (*)

    EXCEPTION
    WHEN OTHERS THEN statement_2;
    WHEN NO_DATA_FOUND 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; (*)

10.  Which of the following are good practice guidelines for exception handling? (Choose three.)  (1) Points
   (Choose all correct answers)
     
    Test your code with different combinations of data to see what potential errors can happen. (*)
    Use an exception handler whenever there is any possibility of an error occurring. (*)
    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. (*)
 
Section 7
     
11.  Which of the following is NOT an advantage of including an exception handler in a PL/SQL block?  (1) Points
     
    Protects the database from errors
    Code is more readable because error-handling routines can be written in the same block in which the error occurred
    Prevents errors from occurring (*)
    Avoids costly and time-consuming correction of mistakes
 
  12.  The following EXCEPTION section is constructed correctly. True or False?
EXCEPTION
    WHEN NO_DATA_FOUND OR TOO_MANY_ROWS
       THEN statement_1;
       statement_2;
       WHEN OTHERS
          THEN statement_3;
END;  (1) Points
     
    True (*)
    False
 
13.  Which of the following best describes a user-defined exception?  (1) Points
     
    A predefined Oracle Server error such as NO_DATA_FOUND
    A non-predefined Oracle Server error such as ORA-01400
    An error which is not automatically raised by the Oracle server (*)
    Any error which has an Oracle error number of the form ORA-nnnnn
 
14.  Which of the following are examples of predefined Oracle Server errors? (Choose three.)  (1) Points
(Choose all correct answers)
     
    TOO_MANY_ROWS (*)
    NO_DATA_FOUND (*)
    OTHERS
    ZERO_DIVIDE (*)
    E_INSERT_EXCEP
 
15.  Which of these exceptions would need to be raised explicitly by the PL/SQL programmer?  (1) Points
     
    OTHERS
    A SELECT statement returns more than one row.
    A check constraint is violated.
    A SQL UPDATE statement does not update any rows. (*)
    A row is FETCHed from a cursor while the cursor is closed.
 
16.  Examine the followiing code. Which exception handlers would successfully trap the exception which will be raised when this code is executed? (Choose two.)
DECLARE
    CURSOR emp_curs IS SELECT * FROM employees;
    v_emp_rec emp_curs%ROWTYPE;
BEGIN
    FETCH emp_curs INTO v_emp_rec;
    OPEN emp_curs;
    CLOSE emp_curs;
EXCEPTION ...
END;
(1) Points (Choose all correct answers)
     
WHEN CURSOR_NOT_OPEN
WHEN INVALID_CURSOR (*)
WHEN OTHERS (*)
WHEN NO_DATA_FOUND
WHEN INVALID_FETCH

17.  An attempt to update an employee's salary to a negative value will violate a check constraint and raise an ORA-02290 exception. Which of the following is a correct definition of a handler for this exception?  (1) Points
     
    DECLARE
    e_sal_excep EXCEPTION;
    PRAGMA EXCEPTION_INIT(-02290,e_sal_excep);

    DECLARE
    PRAGMA EXCEPTION_INIT(e_sal_excep,-02290);
    e_sal_excep EXCEPTION;

    DECLARE
    e_sal_excep EXCEPTION;
    PRAGMA EXCEPTION_INIT(e_sal_excep,-02290); (*)

    DECLARE
    e_sal_excep EXCEPTION;
    PRAGMA_EXCEPTION_INIT(e_sal_exception,-02290);
 
    DECLARE
    e_sal_excep EXCEPTION;
    PRAGMA EXCEPTION_INIT(e_sal_excep,02290);
     
 18.  How can you retrieve the error code and error message of any Oracle Server exception?  (1) Points
     
    By using the functions SQLCODE and SQLERRM (*)
    By using the functions SQLCODE and SQLERR
    By using RAISE_APPLICATION_ERROR
    By defining an EXCEPTION variable and using PRAGMA EXCEPTION_INIT
 
 Section 8
     
 19.  A nested subprogram can only be invoked from the main subprogram. True or False?  (1) Points
     
   True (*)
    False
 
 20.  A PL/SQL stored procedure can accept one or more input parameters and can return one or more output values to the calling environment. True or False?  (1) Points
     
    True (*)
    False
 
 Section 8
     
 21.  A stored PL/SQL procedure can be invoked from which of the following?

A PL/SQL anonymous block
Another PL/SQL procedure
A calling application
 (1) Points
     
    A only
    A and B
    A and C
    A, B and C (*)
    B and C
 
 22.  Which of the following are characteristics of PL/SQL stored procedures? (Choose three.)  (1) Points
 (Choose all correct answers)
     
    They are named PL/SQL blocks (*)
    They must return exactly one value to the calling environment.
    They can have an exception section. (*)
    They can be invoked from inside a SQL statement.
    They can accept parameters. (*)
 
 23.  One PL./SQL subprogram can be invoked from within many applications. True or False?  (1) Points
     
    True (*)
    False

24.  The following are the steps involved in creating, and later modifying and re-creating, a PL/SQL procedure in Application Express. In what sequence should these steps be performed?

Retrieve the saved code from "Saved SQL" in SQL Commands
Execute the code to create the procedure
Execute the code to re-create the procedure
Click on the "Save" button and save the procedure code
Modify the code in the SQL Commands window
Type the procedure code in the SQL Commands window
 (1) Points
     
    F,C,A,B,E,D
    F,B,D,A,E,C (*)
    E,D,F,C,A,B
    F,B,D,E,A,C
    F,B,C,D,E,A
 
25.  Which of the following can NOT be used as the datatype of a procedure parameter?  (1) Points
     
    A non-SQL datatype such as BOOLEAN
    The name of another procedure (*)
    A large object datatype such as CLOB
    A PLSQL record defined using %ROWTYPE
   
26.  Which of the following statements about actual parameters is NOT true?  (1) Points
     
    An actual parameter is declared in the calling environment, not in the called procedure
    An actual parameter must be the name of a variable (*)
    An actual parameter can have a Boolean datatype
    The datatypes of an actual parameter and its formal parameter must be compatible
    An actual parameter can have a TIMESTAMP datatype

27.  Examine the following procedure:
CREATE OR REPLACE PROCEDURE smallproc
  (p_param IN NUMBER)
IS
BEGIN ....
The procedure is invoked by:
DECLARE
  v_param NUMBER := 20;
BEGIN
  smallproc(v_param);
END;
Which of the following statements is true?
(1) Points
     
    p_param is a parameter and v_param is an argument
    p_param is a formal parameter and 20 is an actual parameter
    p_param is a formal parameter and v_param is an actual parameter (*)
    p_param and v_param are both formal parameters, while 20 is an actual parameter
    p_param is an actual parameter and v_param is a formal parameter
 
28.  A procedure will execute faster if it has at least one parameter.  (1) Points
     
    True
    False (*)
 
  29.  Which of the following best describes how an IN parameter affects a procedure?  (1) Points
     
    It describes the order in which the procedure's statements should be executed.
    It describes which parts of the procedure's code are optional or conditional.
    It makes the procedure execute faster.
    It passes a value into the procedure when the procedure is invoked. (*)
    It allows complex calculations to be executed inside the procedure.
 
 30.  Procedure SOMEPROC has five parameters named A, B, C, D, E in that order. The procedure was called as follows:
SOMEPROC(10,20,D=>50);
How was parameter B referenced?
(1) Points
     
    Positional (*)
    Named
    A combination of positionally and named
    A combination of named and defaulted
    Defaulted

 Section 8
     
 31.  What are the type of parameter modes?  (1) Points
     
    CHARACTER, NUMBER, DATE, BOOLEAN
    CONSTANT, VARIABLE, DEFAULT
    LOCAL, GLOBAL, BOTH
    IN, OUT, IN OUT (*)

32.  Which parameter mode is the default?  (1) Points
     
    IN (*)
    OUT
    NUMBER
    VARIABLE
    CONSTANT

33.  Procedure SOMEPROC has five parameters named A, B, C, D, E in that order. The procedure was called as follows:
SOMEPROC(10,20,D=>50);
How was parameter D referenced?
 (1) Points
     
    Positionally
    Named (*)
    A combination of positionally and named
    A combination of named and defaulted
    Defaulted
 
 Section 9
     
 34.  The following code shows the dependencies between three procedures:
CREATE PROCEDURE parent
IS BEGIN
    child1;
    child2;
END parent;
You now try to execute:
DROP PROCEDURE child2;
What happens?
 (1) Points
     
    You cannot drop CHILD2 because PARENT is dependent on it.
    CHILD2 is dropped successfully. PARENT and CHILD1 are both marked INVALID.
    The database automatically drops PARENT as well.
    CHILD2 is dropped successfully. PARENT is marked INVALID. CHILD1 is still valid. (*)
    The database automatically drops CHILD1 as well.
 
35.  Examine the following code: CREATE PROCEDURE parent
IS BEGIN
    child1;
    child2;
EXCEPTION
    WHEN NO_DATA_FOUND THEN NULL;
END parent;
Neither CHILD1 nor CHILD2 has an exception handler.
When PARENT is invoked, CHILD1 raises a NO_DATA_FOUND exception. What happens next?
(1) Points
     
    PARENT handles the exception, then CHILD1 continues to execute.
    CHILD1 ends abruptly. PARENT handles the exception and then ends. CHILD2 does not execute. (*)
    CHILD1 ends abruptly, PARENT handles the exception, then CHILD2 executes.
    CHILD1 ends abruptly, PARENT also ends abruptly and returns an unhandled exception.
    PARENT does not compile because you cannot use NULL; in an exception handler.

36.  Examine the following code (the code of CHILD2 is not shown):
CREATE PROCEDURE child1
IS v_salary employees.salary%TYPE;
BEGIN
  SELECT salary INTO v_salary FROM employees
  WHERE employee_id = 9999;
EXCEPTION
  WHEN NO_DATA_FOUND THEN NULL;
END child1;

CREATE PROCEDURE parent
IS BEGIN
  child1;
  child2;
EXCEPTION
  WHEN NO_DATA_FOUND THEN NULL;
END parent;
Employee_id 9999 does not exist. What happens when PARENT is executed?
(1) Points
     
    CHILD1 handles the exception successfully and ends. PARENT continues to execute and invokes CHILD2. (*)
 
    CHILD1 ends abruptly, PARENT handles the exception successfully and ends. CHILD2 does not execute.
 
    CHILD1 ends abruptly, then PARENT also ends abruptly with an unhandled exception.
 
    PARENT handles the exception, then CHILD1 resumes execution.
 
    PARENT fails to compile because you cannot have the same exception handler in two separate subprograms.
 
37.  The function avg_ann_sal returns the average annual salary for a particular department. The example below is a valid use of of this function. True or False?
SELECT first_name, last_name
FROM employees
WHERE avg_ann_sal(20) > 15000;
 (1) Points
     
    True (*)
    False
 
 38.  When creating a user-defined function, the size of the returned values may be up to the size of any PL/SQL data type. True or False?  (1) Points
     
    True
    False (*)
 
 39.  Which of the following is a legal location for a function call in a SQL statement? (Choose 3) (1) Points
 (Choose all correct answers)
     
    CREATE TABLE statement
    WHERE clause in a DELETE statement (*)
    The ORDER BY and GROUP BY clauses of a query (*)
    VALUES clause of an INSERT statement (*)
 
 40.  When must AUTHID CURRENT_USER be included in an autonomous transaction subprogram?  (1) Points
     
    When declaring Definer's rights
    When declaring Invoker's rights (*)
    When using COMMIT or ROLLBACK
    When using GRANT on the subprogram
 
 Section 9
     
 41.  User REYHAN creates the following procedure: CREATE PROCEDURE proc1 AUTHID CURRENT_USER IS v_count NUMBER; BEGIN SELECT COUNT(*) INTO v_count FROM tom.employees; END; User BILL wants to execute this procedure. What privileges will BILL need?  (1) Points
     
    EXECUTE on REYHAN.PROC1 and SELECT on TOM.EMPLOYEES (*)
    EXECUTE on REYHAN.PROC1
    SELECT on TOM.EMPLOYEES
    BILL needs no privileges
    None of the above. The procedure will fail to compile because REYHAN does not have SELECT privilege on TOM.EMPLOYEES.
 
 42.  How do you specify that you want a procedure MYPROCA to use Invoker's Rights?  (1) Points
     
    CREATE OR REPLACE PROCEDURE myproca
AUTHID CURRENT_USER IS... (*)

    Invoker's Rights are the default, therefore no extra code is needed.
 
    GRANT INVOKER TO myprocA;

    ALTER PROCEDURE myproca TO INVOKER;

    CREATE OR REPLACE PROCEDURE myproca
AUTHID OWNER IS...

  43.  Which of the following is a difference between a procedure and a function?  (1) Points
     
    A procedure can include DML statements, but a function cannot.
    A function must have at least one IN parameter, while parameters are optional for a procedure.
    A procedure can return a BOOLEAN datatype, while a function cannot.
    A function can be used inside a SQL statement, while a procedure cannot. (*)
    A procedure can include an EXCEPTION section, while a function cannot.
 
  44.  In a SELECT statement, where can a function NOT be used?  (1) Points
     
    In a GROUP BY or HAVING clause.
    A function can be used anywhere in a SELECT statement. (*)
    In a WHERE clause.
    In the column list (SELECT) clause.
    In an ORDER BY clause.
 
  45.  You have created a function named NEWFUNC. You now change some of the function code, and try to recreate the function by executing:
CREATE OR REPLACE FUNCTION newfunc .... ;
What happens?
 (1) Points
     
    The command fails because the function already exists.
    The function is automatically dropped and then recreated. (*)
    The command fails because you should execute: CREATE AND REPLACE ....;
    A second function named NEWFUNC_2 is created.
    The function is dropped but not recreated.
 
  46.  What is wrong with the following code?
CREATE FUNCTION badfunc
(p_param NUMBER(4))
RETURN BOOLEAN
IS BEGIN
RETURN (p_param > 10);
END badfunc;
 (1) Points
     
    P_PARAM must be declared AFTER the RETURN clause.
    P_PARAM must have a default value.
    The datatype of the IN parameter cannot have a precision or scale. It must be NUMBER, not NUMBER(4). (*)
 
    RETURN (p_param > 10); is wrong because you cannot return an expression.
    The NUMBER datatype must have a scale as well as a precision.
 
  47.  A function named MYFUNC has been created. This function accepts one IN parameter of datatype VARCHAR2 and returns a NUMBER.
You want to invoke the function within the following anonymous block:
DECLARE
v_var1 NUMBER(6,2);
BEGIN
-- Line A
END;
What could be coded at Liine A?
 (1) Points
     
    myfunc('Crocodile') := v_var1;
    myfunc(v_var1) := 'Crocodile';
    myfunc(v_var1, 'Crocodile');
    v_var1 := myfunc('Crocodile'); (*)
    myfunc('Crocodile', v_var1);
 
  48.  You try to create a function named MYFUNC. The function does not compile correctly because there are errors in your code. Which Dictionary view can you query to see the errors?  (1) Points
     
    USER_SOURCE
    USER_ERRORS (*)
    USER_OBJECTS
    USER_DEPENDENCIES
    USER_COMPILES
 
  Section 6
     
  49.  Which kinds of trigger can cause a mutating table problem? (Choose two.)  (1) Points
  (Choose all correct answers)
     
    BEFORE UPDATE row triggers (*)
    DDL triggers
    AFTER DELETE row triggers (*)
    Database Event triggers
    INSTEAD OF triggers
 
  50.  Examine the following trigger. It should raise an application error if a user tries to update an employee's last name. It should allow updates to all other columns of the EMPLOYEES table. What should be coded at line A?
CREATE TRIGGER stop_ln_trigg
BEFORE UPDATE ON employees
BEGIN
       -- Line A
    RAISE_APPLICATION_ERROR(-20201,'Updating last name not allowed');
    END IF;
END;
(1) Points
     
    IF UPDATING LAST_NAME THEN
    IF UPDATING('LAST_NAME') THEN (*)
    IF UPDATE('LAST_NAME') THEN
    IF UPDATING THEN