PLSQL Semester 1 Final Exam

Section 7
   
  1.  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
     
  2.  The following code does not violate any constraints and will not raise an ORA-02292 error. What will happen when the code is executed?
BEGIN
    DECLARE
       e_constraint_violation EXCEPTION;
       PRAGMA EXCEPTION_INIT(e_constraint_violation, -2292);
    BEGIN
       DBMS_OUTPUT.PUT_LINE('Inner block message');
    END;
EXCEPTION
    WHEN e_constraint_violation THEN
       DBMS_OUTPUT.PUT_LINE('Outer block message');
END;
 (1) Points
     
    'Inner block message' will be displayed.
    The code will fail because the exception is declared in the inner block but is referenced in the outer block. (*)
 
    'Outer block message' will be displayed.
    The code will fail because line 4 should read: PRAGMA EXCEPTION_INIT(-2292, e_constraint_violation);
 
  3.  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. (*)
 
4.  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
 
 5.  While a PL/SQL block is executing, more than one exception can occur at the same time. True or False?  (1) Points
     
    True
     False (*)
 
 6.  Which of these exceptions can be handled by an EXCEPTION section in a PL/SQL block?  (1) Points
     
    A SELECT statement returns no rows
    A SELECT statement returns more than one row
    Any other kind of exception that can occur within the block
    All of the above (*)
    None of the above
 
 7.  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

8.  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
 
9.  Examine the following code fragment. At Line A, you want to raise an exception if the fetched salary value is greater than 30000. How can you do this?
DECLARE
    v_salary employees.salary%TYPE;
BEGIN
    SELECT salary INTO v_salary FROM employees
       WHERE employee_id = 100;
    IF v_salary > 30000 THEN
       -- Line A
    END IF;
...
(1) Points
     
    Test for WHEN VALUE_TOO_HIGH in the exception section.
    Use RAISE_APPLICATION_ERROR to raise an exception explicitly. (*)
    Test for WHEN OTHERS in the exception section, because WHEN OTHERS traps all exceptions.
    Define an EXCEPTION variable and associate it with an Oracle Server error number using PRAGMA EXCEPTION_INIT.
 
10.  Which kinds of exceptions are raised implicitly (i.e., automatically)? (Choose two.)  (1) Points (Choose all correct answers)
     
    Predefined Oracle Server errors such as NO_DATA_FOUND (*)
     User-defined errors
     All errors
     Non-predefined Oracle Server errors such as ORA-01400 (*)
 
 Section 7
     
 11.  Which of the following best describes a predefined Oracle Server error?  (1) Points
     
    Has a standard Oracle error number but must be named by the PL/SQL programmer
    Is not raised automatically but must be declared and raised explicitly by the PL/SQL programmer
    Has a standard Oracle error number and a standard name which can be referenced in the EXCEPTION section (*)
    Is associated with an Oracle error number using PRAGMA EXCEPTION_INIT
 
 12.  The following exception handler will successfully insert the Oracle error number and error message into a log table whenever an Oracle Server error occurs. True or False?
EXCEPTION
    WHEN OTHERS THEN
       INSERT INTO err_log_table (num_col, char_col)
          VALUES (SQLCODE, SQLERRM);
END;
(Assume that err_log_table has been created with suitable columns and datatypes.)
 (1) Points
     
    True
    False (*)
 
13.  Examine the following code. What message or messages will be displayed when this code is executed?
DECLARE
    v_last_name employees.last_name%TYPE;
    v_number NUMBER := 27;
BEGIN
    v_number := v_number / 0;
    SELECT last_name INTO v_last_name FROM employees
       WHERE employee_id = 999;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
       DBMS_OUTPUT.PUT_LINE('No rows were found');
    WHEN ZERO_DIVIDE THEN
       DBMS_OUTPUT.PUT_LINE('Attempt to divide by zero');
    WHEN OTHERS THEN
       DBMS_OUTPUT.PUT_LINE('An error occurred');
END;
(1) Points
     
    No rows were found
    Attempt to divide by zero (*)
    Attempt to divide by zero No rows were found
    An error occurred
    No message will be displayed
   
14.  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);

 15.  A user-defined exception is raised by using:  (1) Points
     
    FLAG exception_name;
    RAISE exception-name; (*)
    PRAGMA EXCEPTION_INIT
    RAISE(error_number, exception_name);
     
 16.  A user-defined exception must be declared as a variable of data type EXCEPTION. True or False?  (1) Points
     
    True (*)
     False
 
 17.  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 (*)
     
 18.  Department-id 99 does not exist. What will be displayed when the following code is executed?
DECLARE
    v_deptname departments.department_name%TYPE;
BEGIN
    SELECT department_name INTO v_deptname
       FROM departments WHERE department_id = 99;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
       RAISE_APPLICATION_ERROR(-20201,'Department does not exist');
END;
 (1) Points
     
    ORA-01403: No Data Found ORA-20201: Department does not exist
    ORA-01403: No Data Found
    ORA-20201: Department does not exist (*)
    None of the above
   
 Section 8
     
 19.  You have created procedure MYPROC with a single parameter PARM1 NUMBER. Now you want to add a second parameter to the procedure. Which of the following will change the procedure successfully?  (1) Points
     
    ALTER PROCEDURE myproc ADD (parm2 NUMBER);
    The procedure cannot be modified. Once a procedure has been created, the number of parameters cannot be changed.
    CREATE OR REPLACE PROCEDURE someproc (parm1 NUMBER, parm2 NUMBER);
(You do not need to repeat the detailed code of the procedure, only the header)
    REPLACE PROCEDURE someproc (parm1 NUMBER, parm2 NUMBER) IS BEGIN ...
    CREATE OR REPLACE PROCEDURE someproc (parm1 NUMBER, parm2 NUMBER) IS BEGIN ... (*)
 
20.  You have created the following procedure:
CREATE OR REPLACE PROCEDURE double_it
(p_param IN OUT NUMBER)
IS
BEGIN
p_param := p_param * 2;
END;
Which of the following anonymous blocks invokes this procedure successfully?  (1) Points
     
    BEGIN
EXECUTE double_it(20);
END;
 
    BEGIN
SELECT double_it(20)
FROM DUAL;
END;
 
    DECLARE
v_result NUMBER(6);
BEGIN
v_result := double_it(20);
END;
 
    DECLARE
v_result NUMBER(6) := 20;
BEGIN
double_it(v_result);
END; (*)
 
    BEGIN
double_it(20);
END;
 
21.  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.
 
22.  You want to create a procedure named SOMEPROC which accepts a single parameter named SOMEPARM. The parameter can be up to 100 characters long. Which of the following is correct syntax to do this?  (1) Points
     
    CREATE PROCEDURE someproc
    (someparm varchar2)
IS
BEGIN ...(*)
 
    CREATE PROCEDURE someproc
    (someparm varchar2(100) )
IS
BEGIN...
 
    CREATE PROCEDURE someproc
IS
    (someparm VARCHAR2;)
BEGIN...
 
    CREATE PROCEDURE someproc
    someparm varchar2(100);
IS
    BEGIN...
 
    CREATE PROCEDURE someproc
    (someparm 100)
IS
BEGIN ...
 
 23.  You have created a procedure named MYPROC that accepts three IN parameters A, B, and C (all numbers). Which of the following calls to MYPROC is NOT correct?  (1) Points
     
    myproc(5,10,20);
    myproc(a=>5,b=>10,20) (*)
    myproc(a=>5,b=>10,c=>20)
    myproc(5,10,c=>20)
 
 24.  Which of the following are characteristics of PL/SQL subprograms but not of anonymous PL/SQL blocks? (Choose three.)  (1) Points (Choose all correct answers)
     
    Can take parameters (*)
     Are stored in the database (*)
     Can begin with the keyword DECLARE
     Are named (*)
     Are compiled every time they are executed
   
 25.  A programmer creates a PL/SQL subprogram which is compiled and stored in the database. Two separate users then execute an application which invokes this subprogram four times. How many times must the subprogram be recompiled?  (1) Points
     
    Twice
    Four times
    None (*)
    Eight times
    Once

 26.  What is another name for a nested subprogram?  (1) Points
     
    Hosted subprogram
    Local subprogram (*)
    Limited subprogram

 27.  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
   
  28.  View and reload your code later by clicking on the History button in the SQL Commands window. True or False?  (1) Points
     
    True
     False (*)
 
  29.  A programmer wants to create a PL/SQL procedure named EMP_PROC. What will happen when the following code is executed?
CREATE OR REPLACE PROCEDURE emp_proc IS
    v_salary employees.salary%TYPE;
BEGIN
    SELECT salary INTO v_salary FROM employees
       WHERE employee_id = 999;
    DBMS_OUTPUT.PUT_LINE('The salary is: ' || v_salary);
END;
(1) Points
     
The statement will raise a NO_DATA_FOUND exception because employee_id 999 does not exist.
The statement will fail because the last line of code should be END emp_proc;
The statement will fail because you cannot declare variables such as v_salary inside a procedure.
The procedure will be created successfully. (*)
The statement will fail because the procedure does not have any parameters.
 
30.  The following procedure has been created:
CREATE OR REPLACE PROCEDURE defproc
(A IN NUMBER := 50,
B IN NUMBER,
C IN NUMBER DEFAULT 40)
IS .....
Which one of the following will invoke the procedure correctly?
(1) Points
     
defproc(30 => A);
defproc(30, 60 => C);
defproc(40, 70); (*)
defproc(10 => A, 25 => C);
defproc;
 
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.  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
 
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.  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 (*)
 
  35.  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 (*)
 
  36.  A benefit of user-defined functions is that the function can accept any SQL or PL/SQL data type. True or False?  (1) Points
     
    True
     False (*)
 
  37.  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.
     
  38.  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.
 
  39.  You want to see the names, modes and data types of the formal parameters of function MY_FUNC in your schema. How can you do this? (Choose two)  (1) Points (Choose all correct answers)
     
    Query USER_PARAMETERS
    Query USER_SOURCE (*)
    Query USER_FUNCTIONS
    SHOW PARAMETER my_func;
    DESCRIBE my_func; (*)
 
  40.  Examine the following code:
CREATE OR REPLACE FUNCTION add_func
(p_param1 NUMBER, p_param2 NUMBER)
RETURN NUMBER
IS
BEGIN
RETURN (p_param1 + p_param2);
END;
What will be displayed when the following SQL statement is executed?
SELECT add_func(6, add_func(3,8)) FROM dual;
 (1) Points
     
  23
  11
  66
  17 (*)
  An error message will be displayed because you cannot nest user-defined functions.
 
Section 9
     
 41.  Why will this function not compile correctly?
CREATE FUNCTION bad_one
IS BEGIN
    RETURN NULL;
END bad_one;
(1) Points
     
    You cannot RETURN a NULL.
    You must declare the type of the RETURN before the IS. (*)
    You must have at least one IN parameter.
    You must code CREATE OR REPLACE, not CREATE.
    The body of the function must contain at least one executable statement (as well as RETURN).
 
42.  Which of the following is a difference between a procedure and a function?  (1) Points
     
    Functions cannot be nested; procedures can be nested to at least 8 levels.
    A procedure can have default values for parameters, while a function cannot.
    An explicit cursor can be declared in a procedure, but not in a function.
    A function cannot be used within a SQL statement; a procedure can be used within SQL.
    A function must return a value, a procedure may or may not. (*)
 
43.  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.
 
 44.  Consider the following function:
CREATE FUNCTION ADD_EM
    (a NUMBER := 1,
    b NUMBER := 2 )
    RETURN NUMBER
IS BEGIN
    RETURN (a+b);
END ADD_EM;
Which one of the following blocks will NOT work correctly?
 (1) Points
     
    DECLARE
    x NUMBER;
BEGIN
    x:= add_em(b=4);
END; (*)

   DECLARE
    x NUMBER;
BEGIN
    x:= add_em(4);
END;

   DECLARE
    x NUMBER;
BEGIN
    x:= add_em(4,5);
END;

  DECLARE
    x NUMBER;
BEGIN
    x:= add_em;
END;

 None of them will work.
     
  45.  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.
 
46.  How do you specify that you want a procedure MYPROCA to use "Definer's Rights"?  (1) Points
     
    CREATE OR REPLACE PROCEDURE myproca
AUTHID CURRENT_USER IS...
 
    CREATE OR REPLACE PROCEDURE myproca
AUTHID OWNER IS...
 
    GRANT DEFINER TO myprocA;
 
    ALTER PROCEDURE myproca TO DEFINER;
 
    Definer's Rights are the default, therefore no extra code or commands are needed. (*)
 
     
47.  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
 
48.  What will happen when the following procedure is executed?
PROCEDURE log_usage (p_card_id NUMBER, p_loc NUMBER)
IS
  PRAGMA AUTONOMOUS_TRANSACTION
BEGIN
  INSERT INTO log_table (card_id, location, tran_date)
    VALUES (p_card_id, p_loc, SYSDATE);
  COMMIT;
END log_usage;
(1) Points
     
    The subprogram will fail because the PRAGMA statement must be before IS.
    The subprogram will fail because it is missing AUTHID CURRENT_USER before IS.
    The compilation will fail because a semicolon after AUTONOMOUS_TRANSACTION is required. (*)
    The program will compile successfully.
 
Section 6
     
  49.  Examine the following code. To create a row trigger, what code should be included at Line A?
CREATE TRIGGER dept_trigg
AFTER UPDATE OR DELETE ON departments
    -- Line A
BEGIN ...
(1) Points
     
    AFTER EACH ROW
    FOR EVERY ROW
    FOR EACH ROW (*)
    ON EACH ROW
    ON EVERY ROW
 
 50.  Examine this code:
CREATE TRIGGER de_trigg
    -- Line A
BEGIN ...

Which of the following are NOT valid at Line A ? (Choose two.) (1) Points (Choose all correct answers)
     
    AFTER LOGOFF ON SCHEMA (*)
    AFTER LOGON ON SCHEMA
    BEFORE LOGOFF ON SCHEMA
    BEFORE DISCONNECT ON SCHEMA (*)
    AFTER SERVERERROR ON SCHEMA